Incorrect string value issue during migration database in Jira.

Problem

I was using H2 database (embedded database) for Jira but this caused a performance problem which consumes a lot of CPU.

the embedded database can fail any time, it's not as secure as MySQL. It's just a basic DB for testing purposes. When your Jira instance grows you'll experience performance problems as well.

Quoted from: So whats wrong with the embedded database?

When I tried to migrate Jira database from H2 to MySQL, I got an error, 'Incorrect string value: '\xF0\x9F\x98\x96...' due to multibyte problems.

My environment

• $ lsb_release -a: Ubuntu 14.04.5 LTS
• $ mysqld -V: Ver 5.5.62-0ubuntu0.14.04.1
• Jira v7.12.1

Cause

MySQL seems not to handle well multibyte characters.

Resolution

Use PostgreSQL instead MySQL. That's because you will have less multibyte problem by using PostgreSQL.¹ The easiest way to migrate to PostgreSQL is the followings:

1. Create an export of your data as an XML backup. See Backing up data for details.
2. Create a new database on your new database server to house JIRA's data. See the appropriate database configuration guide in the Connecting JIRA to a database section for the database creation instructions.
3. Shut down your JIRA server.
4. Make a backup of your JIRA home directory and JIRA installation directory.
5. Delete the dbconfig.xml file in your JIRA home directory.
6. Restart JIRA and you should see the first step of the JIRA setup wizard for configuring your database connection.
7. Configure JIRA's connection to your new database (created in step 2 above) and click the 'Next' button.
8. On the 'Application Properties' setup page, click the 'import your existing data' link and restore your data from the XML backup created in step 1 above.

Quoted from: Migrating JIRA's data to a different type of database server

Reference

• SQL Exception while updating issues or importing data in JIRA applications with MySQL due to encoding
• 'Incorrect string value' error thrown when restoring XML backup in Confluence

---

1. I spent many hours on multibyte things, but many people recommend to convert your data's char-code into a specific char-code during import of Jira's data. I tried everything but all the approaches did not fix the problem in my case. ↩