Jira Server or Data Center throws SQL Exception while updating issues or importing data into MySQL due to encoding
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Fisheye および Crucible は除く
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Fisheye および Crucible は除く
症状
The below errors can be caused by any of the following actions. In all of these examples, JIRA applications are using a MySQL database.
- Editing an issue.
- Restoring from an XML backup.
- Importing issues using the Importers Plugin.
- Creating issues with Mail Handler
atlassian-jira.log
に次のメッセージが表示される。
SQL Exception while executing the following:UPDATE jiraissue SET pkey=?, PROJECT=?, REPORTER=?, ASSIGNEE=?, issuetype=?, SUMMARY=?, DESCRIPTION=?, ENVIRONMENT=?, PRIORITY=?, RESOLUTION=?, issuestatus=?, CREATED=?, UPDATED=?, DUEDATE=?, VOTES=?, TIMEORIGINALESTIMATE=?, TIMEESTIMATE=?, TIMESPENT=?, WORKFLOW_ID=?, SECURITY=?, FIXFOR=?, COMPONENT=? WHERE ID=? (Incorrect string value: '\xC2\x93from...' for column 'DESCRIPTION' at row 1
または
SQL Exception while executing the following:INSERT INTO jiraaction (ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: '\xC2\x95\x09AKA...' for column 'actionbody' at row 1)
または
Unexpected error occurred during import: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:UserHistoryItem][id,118305][username,admin][entityId,11204][lastViewed,1402954147248][type,Dashboard] (SQL Exception while executing the following:INSERT INTO userhistoryitem (ID, entitytype, entityid, USERNAME, lastviewed, data) VALUES (?, ?, ?, ?, ?, ?) (Deadlock found when trying to get lock; try restarting transaction))
原因
- JIRA applications require UTF-8 character encoding to function correctly and this error is generated as the character encoding is not specified in URL on the JDBC driver, or the database is configured with the incorrect character encoding.
- Moreover, if old JIRA applications database are using UTF-8 with 4 byte and trying import to MySQL database will also get this error as per - JRA-36135Getting issue details... STATUS .
ソリューション
Verify the System Encoding encoding is
utf-8
in Administration > System Info > System Encoding. If it is not, add the below arguments (only for Java7 version or lower as in Java8 utf-8 is set by default) to the JVM as in Setting Properties and Options on Startup.-Dfile.encoding=utf-8 -Dsun.jnu.encoding=UTF-8
- Check that the correct URL is used in
dbconfig.xml
, as in Connecting JIRA to MySQL. Verify the table collations are all
utf8_bin
by executing the below SQL on the JIRA database. It returns any table which doesn't haveutf8_bin
as the collation. Ideally it should return an empty set (0 rows):SELECT DISTINCT C.collation_name, T.table_name FROM information_schema.tables AS T, information_schema.`collation_character_set_applicability` AS C WHERE C.collation_name = T.table_collation AND T.table_schema = DATABASE() AND C.collation_name != 'utf8_bin';
And verify the database collation is
utf8_bin
by running the below SQL. It should returnutf8_bin
:SELECT default_collation_name FROM information_schema.schemata S WHERE schema_name = (SELECT DATABASE() FROM DUAL);
If the database is not utf8_bin
for both the tables and database, it will need to be recreated with the below steps. There may be other ways of altering the database to fix it that are not covered in this document.
Using JIRA's XML backup utility, create a backup of the database. If you are unable to create an XML backup due to the size of your database, please see our alternative resolution below.
If this error is caused during an XML import, there is no need to create a backup as one is already being used.
- Jira を停止します。
- Create a new database for JIRA, as in Connecting JIRA to MySQL. Be sure to set your db to use utf8 encoding.
- Using the JIRA Configuration Tool, point JIRA to the new database.
- Start JIRA application.
Restore the XML backup created earlier.
代替ソリューション
If it isn't possible to create an XML backup of the MySQL instance, the following can be done:
- Shutdown JIRA application.
- Backup the database to a file.
Execute the following:
- Restore the backup to a new database.
- Using the JIRA Configuration Tool, point JIRA to the new database.
- Start JIRA application.