MYSQL exception - Caused by: java.sql.SQLException: Incorrect string value

Owned by Former user (Deleted)
Last updated: Jan 27, 2021 by Finn Gale
1 min read

Cause

MySQL does not always support 4 byte characters

With the standard character set (utf8), MySQL does not support 4-byte UTF-8 characters (for example: 😊 or 'dogpile' 💩. – it saves here because JIRACloud doesnt use MySQL!). This means that some characters simply cannot be stored, and will be rejected by JIRA. In this case there isn't much that can be done, the character is legal but the database cannot support it.

References

Symptoms

  • JEMH fails to process email, possibly preventing other emails from being processed

Caused by: java.sql.SQLException: Incorrect string value: '\xD9\x8A\xD8\xB1\xD8\xAC...' for column 'EXCEPTION' at row 1

Resolutions

  1. See Handling MySQL 4-byte character support problems for some steps that can be taken to work around this

  2. Migrate to a database that is known to fully support 4-byte characters out-of-the-box, like PostgreSQL

  3. As of MySQL 5.7, it may be possible to use the utf8mb4 charset with JIRA 7.3.  We cannot personally vouch for this and so caution is advised: https://confluence.atlassian.com/jirakb/jira-does-not-work-with-emoji-4-byte-characters-429919955.html

  4. Convert the column being complained about to UTF-8. For example, updating the AUDITEVENTS table EXCEPTION column to UTF-8 in the JIRA database would be done with:
    ALTER TABLE `jira`.`AO_78C957_AUDITEVENTS` MODIFY COLUMN `EXCEPTION` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

     

  5. Some customers have found upgrading to using ‘mysql-connector-java_8.0.23’ database drivers and using the ‘utf8mb4’ character set and ‘utf8mb4_bin’ collation as described in the Atlassian database connection instructions can mitigate this issue:
    * https://confluence.atlassian.com/adminjiraserver0813/connecting-jira-applications-to-mysql-5-7-1027137456.html
    * https://confluence.atlassian.com/adminjiraserver0813/connecting-jira-applications-to-mysql-8-0-1027137457.html

 

You may also need to update your database driver…