Jira server throws SQL Exception while updating issues or importing data into MySQL due to encoding

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

症状

The below errors can be caused by any of the following actions. In all of these examples, JIRA applications are using a MySQL database.

  1. Editing an issue.
  2. Restoring from an XML backup.
  3. Importing issues using the Importers Plugin.
  4. 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-36135 - Getting issue details... STATUS .

ソリューション

  1. 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
  2. Check that the correct URL is used in dbconfig.xml, as in Connecting JIRA to MySQL.
  3. Verify the table collations are all utf8_bin by executing the below SQL on the JIRA database. It returns any table which doesn't have utf8_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';
  4. And verify the database collation is utf8_bin by running the below SQL. It should return utf8_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.

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

    tip/resting Created with Sketch.

    If this error is caused during an XML import, there is no need to create a backup as one is already being used.

  2. Jira を停止します。
  3. Create a new database for JIRA, as in Connecting JIRA to MySQL. Be sure to set your db to use utf8 encoding.
  4. Using the JIRA Configuration Tool, point JIRA to the new database.
  5. Start JIRA application.
  6. 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:

  1. Shutdown JIRA application.
  2. Backup the database to a file.
  3. Execute the following:

    Expand for Linux instructions...
     sed 's/SET = latin 1/SET = utf8/' dumpfile > newdumpfile

    または

     sed 's/CHARSET=latin1/CHARSET=utf8/' dumpfile > newdumpfile
    Expand for Windows instructions...
    1. Use a text editor and find the below string:

      'SET = latin 1'
    2. Replace it with the following:

      'SET = utf8'
  4. Restore the backup to a new database.
  5. Using the JIRA Configuration Tool, point JIRA to the new database.
  6. Start JIRA application.
最終更新日 2022 年 8 月 29 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.