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

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

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

The following appears in the 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

OR

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)

OR

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))


Cause

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

Resolution

  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:

    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:

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

Alternative Resolution

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

    OR

     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.
Last modified on Sep 25, 2019

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.