Symptoms

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;

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,

1. Configure all tables to use the InnoDB engine instead using a statement like:

   
   alter table spacepermissions ENGINE=INNODB;
   

   You will need to repeat this for every table.
2. 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';

3. 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.