Upgrade to Confluence 3.0 or higher with MySQL Database Fails Due to 'specified key was too long'
An in-place upgrade of a MySQL database to Confluence 3.0 or higher fails with errors like the following in the logs:
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:
show create table spacepermissions;
The engine and character set will be specified near the end of the output, as in:
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:
show table status;
There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes:
To resolve this problem,
- Configure all tables to use the InnoDB engine instead using a statement like:
alter table spacepermissions ENGINE=INNODB;
- To change the storage engine used by default so that new tables will always be created appropriately, you can use a query like:
set 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.