MySQL Table Creation Fails With Error 'specified key was too long'
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
The following error messages are reported in the JIRA log file:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[... 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 above
1SET GLOBAL default_storage_engine = 'InnoDB';Or, for MySQL 5.5 and below:
MySQL 5.5 and below
1set GLOBAL storage_engine='InnoDb';
Was this helpful?