Problem

When running a Confluence installation or upgrade process, errors like the following are seen in the atlassian-confluence.log file:

ERROR [main\] [hibernate.tool.hbm2ddl.SchemaUpdate\] execute Unsuccessful: alter table SPACEGROUPPERMISSIONS add constraint FK487D958B16994414 foreign key (SPACEGROUPID) references SPACEGROUPS (SPACEGROUPID)
ERROR [main\] [hibernate.tool.hbm2ddl.SchemaUpdate\] execute Can't create table './confluence/#sql-9c3_258.frm' (errno: 150)
ERROR [main\] [hibernate.tool.hbm2ddl.SchemaUpdate\] execute could not complete schema update
java.sql.SQLException: Can't create table './confluence/#sql-9c3_258.frm' (errno: 150)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)

ERROR [main\] [hibernate.tool.hbm2ddl.SchemaUpdate\] execute could not complete schema update
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes

Cause

The MyIsam storage engine used by default in MySQL 5.6 and earlier doesn't support foreign key constraints. There is a page in the documentation that describes this:

http://confluence.atlassian.com/display/DOC/Known+Issues+for+MySQL#KnownIssuesforMySQL-MySQLStorageEngine

When the installation or upgrade process attempts to add foreign key constraints to a MyISAM table, it will encounter problems, and the installation or upgrade may not be completed successfully.

Resolution

The solution is to change the storage engine for Confluence tables to InnoDb as recommended in our documentation.

You can do this for individual tables using a query like:

ALTER TABLE <TABLE_NAME> 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';

If you have a dump created with MyISAM, it's necessary to execute the command below to change it to InnoDB:

sed -e 's/ENGINE=MyISAM/Engine=InnoDb/g' source.sql > destination.sql

You should also check your MySQL server configuration file (my.cnf) to ensure that the storage engine setting will be retained after a restart. The "Storage Engine Section" of the MySQL documentation provides more details.