Database errors when using MySQL and MyISAM tables

Still need help?

The Atlassian Community is here for you.

Ask the community


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

tip/resting Created with Sketch.

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:


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.





























Last modified on Sep 4, 2017

Was this helpful?

Provide feedback about this article
Powered by Confluence and Scroll Viewport.