Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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.

*Except Fisheye and Crucible

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

   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