Comment with emoji fails to save with DataAccessException error in Jira server
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-36135Getting issue details... STATUS
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
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.
Jira application connected to MySQL database server with
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.
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.
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
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)
Create a Jira database based on the new character set
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
column_typedepending 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.
Emojis and special characters may not be rendered properly on gadgets i.e. Activity Streams.