XML backup from MySQL fails to import to Jira server

Summary

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:

Console 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:

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.

Solution

Resolution

Solution #1

1. 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:

   1. Table AO_60DB71_QUICKFILTER.

   2. Column NAME.

2. And then, you should look in the JIRA instance the XML backup created to review all the data in NAME under table AO_60DB71_QUICKFILTER.

Solution #2

1. You can clean the XML backup file, in this case, entities.xml and activeobject.xml files, with the following command:

   1 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.

2. Redo the import.