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

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:

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.