Add support for 4 byte characters in MySQL connection

• NOTE: This suggestion is for JIRA Server. Using JIRA Cloud? See the corresponding suggestion.

  Since MySQL 5.5+, there is support for 4 byte characters. JIRA is capable of handling these characters when using other databases. Due to some limitations in how MySQL is implemented, JIRA won't just work by setting the 4 byte encoding utf8mb4. MySQL's InnoDB engine is limited to a maximum size of 767 bytes for any field used in an index, and there are various places in JIRA that use a VARCHAR(255) field in an index. This means that JIRA will encounter an error such as the below if you try to simply install into an utf8mb4 database:

  2013-10-30 11:43:23,723 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
  CREATE TABLE cwd_directory_attribute (directory_id DECIMAL(18,0) NOT NULL, attribute_name VARCHAR(255) NOT NULL, attribute_value VARCHAR(255), CONSTRAINT PK_cwd_directory_attribute PRIMARY KEY (directory_id, attribute_name))
  Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
  

  A full list of InnoDB restrictions including this one is listed at http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html

Now that MySQL 5.7 supports utf8mb4 better, the main restriction is actually that you need to set the server encoding and JDBC URL such that the connection uses the right encoding. This is documented in workaround 2 below.

Workaround 1

You can switch to another of the databases supported by Jira like PostgreSQL following the guide Switching database - Migrating JIRA's data to a different type of database server. If you are having problems parsing the XML backup, then you could process the data manually and remove any 4 byte characters before importing to avoid the problem. If difficulties persist, you can reach out to Atlassian Support for assistance or to the Atlassian community.

Workaround 2

Upgrade to JIRA 7.3 or later versions which MySQL 5.7 is supported. MySQL 5.7.9 defaults the table row format to DYNAMIC which supports indexes longer than 767 bytes and it works well with utf8mb4 collation. For more information, please refer to this KB article: https://confluence.atlassian.com/jirakb/emojis-and-special-characters-for-jira-with-mysql-952604493.html

• It is important to mention that this involves a configuration that is not supported and we cannot guarantee it will work for everyone and the reason to include it here is for users who have no choice but to use MySQL as their database to have an additional option.