Jira + Mysql = Error importing data? Two simple solutions.

24 Sep

Many versions of MySQL will throw a fit if you try to insert four-byte UTF-8 characters. If your find yourself importing a ticketing system into JIRA and run into an error that looks like the one below, there is hope!

Error importing data: org.apache.commons.lang.exception.NestableException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting:...

…(SQL Exception while executing the following:INSERT INTO jiraaction (ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: ‘xEFxBFxBD…’ for column ‘actionbody’ at row 1))

Fortunately, there are two simple solutions:

Option 1: Get with the times

Make sure you are using MySQL 5.5.3 and convert your table and DB encodings to utf8mb4. Once you’ve backed up your database, run the following SQL:


ALTER DATABASE jira CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
USE jira;
ALTER TABLE `jiraaction` charset=utf8mb4;
ALTER TABLE `jiraissue` charset=utf8mb4;

I’ve only listed the two most likely culprits here, but you may have others. You might as well convert all of the tables in your jira database. Mathias Bynens has an excellent writeup on the utf8mb4 conversion.

Option 2:

If you are stuck on an older version of MySQL, you can change the datatypes for the following tables:

DB Table Old type New type
jiraaction actionbody LONGTEXT LONGBLOB
jiraissue DESCRIPTION LONGTEXT LONGBLOB
jiraissue SUMMARY VARCHAR(255) VARBINARY(255)

Note that this has implications for fulltext search, it is not a panacea for all your multi-byte woes.

Either of these fixes will get you over the hump and have you using your database in no time.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: