MySQL Index Creation Fails With Error "specified key was too long"

On initial JIRA startup integrated with MySQL the following errors are reported:

[... lots of table creations here ...]
2010-12-16 15:28:10,062 main WARN      [core.entity.jdbc.DatabaseUtil] Entity "Worklog" has no table in the database
2010-12-16 15:28:10,218 main ERROR      [core.entity.jdbc.DatabaseUtil] Could not create declared indices for entity "GroupAttribute"
2010-12-16 15:28:10,218 main ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX idx_goup_attr_dir_name_lval ON cwd_group_attributes (directory_id, attribute_name, lower_attribute_value)
Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
SQL Exception while executing the following:
CREATE UNIQUE INDEX uk_group_attr_name_lval ON cwd_group_attributes (group_id, attribute_name, lower_attribute_value)
Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
2010-12-16 15:28:10,288 main ERROR      [core.entity.jdbc.DatabaseUtil] Could not create declared indices for entity "Membership"
2010-12-16 15:28:10,289 main ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX idx_mem_dir_parent_child ON cwd_membership (lower_parent_name, lower_child_name, membership_type, directory_id)
Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
2010-12-16 15:28:10,425 main ERROR      [core.entity.jdbc.DatabaseUtil] Could not create declared indices for entity "UserAttribute"
2010-12-16 15:28:10,425 main ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX idx_user_attr_dir_name_lval ON cwd_user_attributes (directory_id, attribute_name, lower_attribute_value)
Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
2010-12-16 15:28:13,801 main INFO      [atlassian.jira.startup.JiraStartupLogger]

JIRA's performance may suffer if you have a large number of users and/or groups defined (even if they in an external directory).

Cause

There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes:

• http://bugs.mysql.com/bug.php?id=4541

Resolution

The resolution steps are different for new installation and for existing installations with data.

Resolution for a New Installation

In this case we need to configure JIRA to use InnoDB before any of its tables are created.

1. Before starting the application installation (i.e. none of JIRA's tables can already be created), ensure that you have followed the instructions provided in Connecting JIRA to MySQL.
2. Ensure that the InnoDB Dialect is installed on your MySQL server.
3. Change the storage engine used by JIRA so that new tables will always be created under InnoDB. There are two main ways of doing this:
   • Option 1: Change MySQL settings*: Set the default-storage-engine to InnoDB and restart MySQL. This makes InnoDB the default engine for all new tables on your MySQL instance. This could affect other applications using your MySQL instance.
   • Option2: Change connection URL used for JIRA*: You can configure JIRA to use InnoDB by default by adding sessionVariables=storage_engine=InnoDB to your JDBC URL in the $JIRA_HOME/dbconfig.xml file. This change will be isolated to JIRA. The location of the JDBC URL has changed between JIRA 4.3.x and 4.4.x. For example:
     jdbc:mysql://localhost/jiradb?useUnicode=true&characterEncoding=UTF8 will be changed to jdbc:mysql://localhost/jiradb?useUnicode=true&characterEncoding=UTF8&sessionVariables=storage_engine=InnoDB.
4. Install JIRA.

Resolution for existing JIRA installation having MyISAM tables with data

Option 1: Migrate the MyISAM tables in place

1. Stop JIRA.
2. Take a backup of your database in case things go wrong.
3. Make sure that the InnoDB Dialect is installed on your MySQL server.
4. Change the storage engine used by JIRA so that new tables will always be created under InnoDB with the following, as from these docs:

   ALTER TABLE t1 ENGINE = InnoDB;
   

5. Migrate all JIRA's current tables to InnoDB. For example, you can:
   • Run the following command to generate an SQL script that will migrate your tables from MyISAM to InnoDB:

     mysql -NBe "select concat('alter table ',  TABLE_NAME, ' engine = InnoDB;') from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '<database>' and engine = 'MyISAM'" -u <user> -p <database>
     

     In this command <database> is the name of your database and <user> is a user who can access <database>.

   • Check to generated script to ensure that it does not do anything you don't expect.
   • Run the generated script. This can take some time.
6. Restart JIRA.

Option 2: Perform a JIRA backup and restore

This will require taking an XML backup of JIRA and restoring it to a new database.

1. Take an XML backup of JIRA.
2. Stop JIRA.
3. Make sure that the InnoDB Dialect is installed on your MySQL server.
4. Change the storage engine used by JIRA so that new tables will always be created under InnoDB with the following, as from these docs:

   ALTER TABLE t1 ENGINE = InnoDB;
   

5. Create a new database for JIRA.
6. Configure JIRA to connect to the new database.
7. Start JIRA.
8. Restore the backup you took earlier.