Upgrade to Confluence 3.0 or higher with MySQL Database Fails Due to '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
An in-place upgrade of a MySQL database to Confluence 3.0 or higher fails with errors like the following in the logs:
1
2
3
4
5
6
2009-06-02 15:07:39,147 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: create index sp_comp_idx on SPACEPERMISSIONS (PER
MTYPE, PERMGROUPNAME, PERMUSERNAME)
2009-06-02 15:07:39,149 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Specified key was too long; max key length is 1000 bytes
2009-06-02 15:07:39,151 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
This error only occurs when the MyISAM engine is used for the table and the character set is set to UTF8. You can determine which engine and character set your table uses with the following statement:
1
show create table spacepermissions;The engine and character set will be specified near the end of the output, as in:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
spacepermissions | CREATE TABLE `spacepermissions` (
`PERMID` bigint(20) NOT NULL,
`SPACEID` bigint(20) DEFAULT NULL,
`PERMTYPE` varchar(255) NOT NULL,
`PERMGROUPNAME` varchar(255) DEFAULT NULL,
`PERMUSERNAME` varchar(255) DEFAULT NULL,
`CREATOR` varchar(255) DEFAULT NULL,
`CREATIONDATE` datetime DEFAULT NULL,
`LASTMODIFIER` varchar(255) DEFAULT NULL,
`LASTMODDATE` datetime DEFAULT NULL,
PRIMARY KEY (`PERMID`),
KEY `sp_puname_idx` (`PERMUSERNAME`),
KEY `sp_pgname_idx` (`PERMGROUPNAME`),
KEY `sp_permtype_idx` (`PERMTYPE`),
KEY `sp_spaceid_idx` (`SPACEID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
To list all tables' status to see which engine is used, use the following:
1
2
show table status;
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
To resolve this problem,
Configure all tables to use the InnoDB engine instead using a statement like:
1 2alter table spacepermissions ENGINE=INNODB;You will need to repeat this for every table.
To change the storage engine used by default so that new tables will always be created appropriately, you can use a query like:
1set GLOBAL storage_engine='InnoDb';Alternatively, a fast way to alter all tables is to use mysqldump to make a backup of your db, then use something like sed or a text editor with search and replace to replace all instances of "MyISAM" with "InnoDB". Then reimport the backup you made.
You must run these statement before upgrading. If you have already attempted the upgrade and failed, you will need to restore both your database and confluence-cfg.xml file from a backup. Once you do that and run the statement above, you should be able to perform the upgrade without further problems.
Was this helpful?