XML backup from MySQL fails to import to Jira server
Generating XML backup from MySQL will include 4-byte characters if there is any 4-byte data. Importing the XML backup into a new MySQL database will break the import with the following error:
There was a problem restoring ActiveObjects data for the plugin JIRA Agile(com.pyxis.greenhopper.jira) #6.7.11 plugin. Importing table AO_60DB71_QUICKFILTER failed. Please check the log for details.
In the log file:
Caused by: java.sql.BatchUpdateException: Incorrect string value: '\xF0\x9F\x94\x8D' for column 'NAME' at row 1
Since the character is
\xF0\x9F\x94\x8D - there is 4 segment which tells us that this is related to the 4-byte character. As per stated in JIRA Supported Platform, JIRA does not support 4-byte characters, regardless of MySQL version.
- The error in the log file will indicate which table and column the import is failing and then you can reverse engineer it by looking at the data in the database. For example from the error above:
- And then, you should look in the JIRA instance the XML backup created to review all the data in
You can clean the XML backup file, in this case,
activeobject.xmlfiles, with the following command:
LANG=C sed 's/[\xF0-\xF7].../abc/g' activeobjects.xml > activeobjects.xml.cleaned
This command does not include all the 4-byte characters that exist. More a better regex, you can look at the https://unix.stackexchange.com/a/12545.
- Redo the import.