MySQL Table Creation Fails With Error 'specified key was too long'
Symptoms
The following error messages are reported in the JIRA log file:
[... 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]
Cause
There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes:
Resolution
- Before starting the application installation, make sure that you've followed the instructions provided in Connecting JIRA to MySQL
- Make sure that the InnoDB Dialect is installed in your MySQL server.
Change the storage engine used by default so that new tables will always be created appropriately, you can use a query like:
MySQL 5.6 and aboveSET GLOBAL default_storage_engine = 'InnoDB';Or, for MySQL 5.5 and below:
MySQL 5.5 and belowset GLOBAL storage_engine='InnoDb';
Last modified on May 22, 2020
Powered by Confluence and Scroll Viewport.