Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-36135

Add support for 4 byte characters in MySQL connection

XMLWordPrintable

    • 1,112
    • 300
    • Hide
      Atlassian Update – 21 December 2018

      Dear Jira users,

      We’re glad to announce that this issue will be addressed in our upcoming 8.0 release.

      You can find more details about our 8.0 beta release here — https://community.developer.atlassian.com/t/beta-for-jira-8-0-is-up-for-grabs/25588

      Looking forward to your feedback!

      Kind regards,
      Syed Masood
      Product Manager, Jira Server and Data Center

      Show
      Atlassian Update – 21 December 2018 Dear Jira users, We’re glad to announce that this issue will be addressed in our upcoming 8.0 release. You can find more details about our 8.0 beta release here — https://community.developer.atlassian.com/t/beta-for-jira-8-0-is-up-for-grabs/25588 Looking forward to your feedback! Kind regards, Syed Masood Product Manager, Jira Server and Data Center
    • We collect Jira feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      • 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.

              Unassigned Unassigned
              bberenberg Boris Berenberg (Inactive)
              Votes:
              206 Vote for this issue
              Watchers:
              210 Start watching this issue

                Created:
                Updated:
                Resolved: