Comment with emoji fails to save with DataAccessException error in Jira server

Still need help?

The Atlassian Community is here for you.

Ask the community

The content on this page relates to database updates which are not supported by our Atlassian Support Offerings. Consequently, Atlassian can not guarantee providing any support for it. Please be aware that this material is provided for your information only and using it is done so at your own risk.

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.


Please be advised that this is strictly a workaround for customers who are seeking into migrating/importing Jira application to MySQL database (where other database e.g. Postgres is not an option) meanwhile retaining emojis and special characters in the database is a must. It is extremely important that you carry out the necessary testings and validations prior to rolling out changes to the production instance.

When this workaround is in place, you will see the following error in the health check tool and it is safe to ignore. This health check result will be rectified once we have completed the improvement request here: JRASERVER-36135 - Getting issue details... STATUS


Problem

When you're super happy meanwhile celebrating a successful roll-out of your code that you would like to insert emojis like " 😊🍻" or even inserting a special character, an error message pops up.

Occasionally, you would also see an internal server error with temporary glitch.

And the following appears in the atlassian-jira.log:

2018-06-20 10:32:39,741 http-nio-9764-exec-25 ERROR admin 632x202x1 1xnpcwm 0:0:0:0:0:0:0:1 /rest/api/2/issue/TEST-13/comment [c.a.j.rest.exception.ExceptionInterceptor] Returning internal server error in response
java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.atlassian.plugins.rest.common.interceptor.impl.DispatchProviderHelper$ResponseOutInvoker$1.invoke(DispatchProviderHelper.java:192)
	... 
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:INSERT INTO jiraaction (ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: '\xF0\x9F\x98\x8A\x0D\x0A...' for column 'actionbody' at row 1)
	at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:570)
	at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:198)
	... 300 more
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x8A\x0D\x0A...' for column 'actionbody' at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
	at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:562)
	... 301 more

This also commonly happen when you're importing Jira XML backup to a brand new Jira instance connected to MySQL database instance.


Diagnosis

Environment

Jira application connected to MySQL database server with UTF8 collation.

Cause

MySQL prior to version 5.7.x does not support 4 byte characters. Related improvement request:  JRASERVER-36135 - Getting issue details... STATUS

Workaround

Upgrade your MySQL database to version 5.7.x and switch the corresponding character set to utf8mb4 instead of utf8. In order to achieve this, please follow the steps below:

Please be advised that MySQL version 5.7.x is only supported for Jira version 7.3 or later versions as described in the official Supported platforms documentation.


  1. Shutdown MySQL database.

    If you have an existing Jira installation, please make sure you shutdown the Jira application gracefully and generate a Jira database backup before moving on to the next step.

  2. Apply and update the necessary configuration settings in the MySQL configuration file e.g. /etc/my.cnf.

    [mysqld]
    character-set-server = utf8mb4
    collation-server = utf8mb4_unicode_ci
  3. Restart your MySQL database and verify the changes by running the following SQL query.

    mysql> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+--------------------+
    | Variable_name            | Value              |
    +--------------------------+--------------------+
    | character_set_client     | utf8mb4            |
    | character_set_connection | utf8mb4            |
    | character_set_database   | utf8mb4            |
    | character_set_filesystem | binary             |
    | character_set_results    | utf8mb4            |
    | character_set_server     | utf8mb4            |
    | character_set_system     | utf8               |
    | collation_connection     | utf8mb4_unicode_ci |
    | collation_database       | utf8mb4_unicode_ci |
    | collation_server         | utf8mb4_unicode_ci |
    +--------------------------+--------------------+
    10 rows in set (0.01 sec)
  4. Create a Jira database based on the new character set utf8mb4.

    CREATE DATABASE jira CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

    If you have an existing Jira installation, you will need to modify the database and its corresponding tables and columns to match the character set and collation. You may refer to the following SQL query as reference.

    # For each database:
    ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
    # For each table:
    ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
    # For each column:
    ALTER TABLE table_name CHANGE column_name column_type CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

    Please do not copy and execute the above SQL queries blindly. You will need to replace the relative database_name, table_name, column_name and column_type depending on your specific instances. Redeploying a Jira instance with the correct character set and collation and then reimport the data will be the quickest workaround to avoiding any possible mistake or miss.

    For further details on how to fix the collation and character set of a MySQL database manually  please reference the linked article

As a result, emojis and special characters can inserted and rendered within descriptions, comments or any other text fields.

Known Limitation

Emojis and special characters may not be rendered properly on gadgets i.e. Activity Streams.


Last modified on May 18, 2021

Was this helpful?

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