Database errors when using MySQL and MyISAM tables
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
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:
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.
The solution is to change the storage engine for Confluence tables to InnoDb as recommended in our documentation.
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.
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.