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:

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:


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.


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:

    LANG=C sed 's/[\xF0-\xF7].../abc/g' activeobjects.xml > activeobjects.xml.cleaned

    (info) 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.


最終更新日 2019 年 9 月 25 日


Powered by Confluence and Scroll Viewport.