Symptoms
When running a Confluence installation or upgrade process, errors like the following are seen in the atlassian-confluence.log file:
Cause
The MyIsam storage engine used by default in MySQL 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.
Resolution
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:
To change the storage engine used by default so that new tables will always be created appropriately, you can use a query like:
If you have a dump created with MyISAM, it's necessary to execute the command bellow to change it to InnoDB:
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.
6 Comments
Hide/Show CommentsJun 07, 2009
Werner Mueller
if you dont want (or cant) change the sytem default the parameter can be added to the connection string:
jdbc:mysql://localhost:3306/databasename?autoReconnect=true&sessionVariables=storage_engine=InnoDB
this can be changed in the confuence config xml (keep in mind to use & amp ; in the xml file for the &
Mar 21, 2011
Brett Ryan
A good tip that maybe should be recommended for all MySQL based connection strings as a belt and bracers incase further upgrades add tables.
Jul 08, 2009
Tim Friendshuh
We're upgrading from 2.81 and ran into this error too - but we're already using innodb as the default engine. Is there anything else that would cause this particular error?
2009-07-08 11:35:27,474 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table SPACEGROUPPERMISSIONS add constraint FK487D958B16994414 foreign key (SPACEGROUPID) references SPACEGROUPS (SPACEGROUPID)
2009-07-08 11:35:27,478 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Can't create table 'confluenceqa01.#sql-18c4_70ec' (errno: 150)
2009-07-08 11:35:27,479 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
java.sql.SQLException: Can't create table 'confluenceqa01.#sql-18c4_70ec' (errno: 150)
Nov 29, 2009
Darryl Lee
The fix for us was to do a mysqldump, then:
sed -e 's/ENGINE=MyISAM/Engine=InnoDb/g' source.sql > destination.sql
Feb 15, 2011
Huw Evans (Adaptavist.com)
If you're using sed in the manner described above you risk overwriting instances of
ENGINE=MyISAMin the content. I'll refer you to this comment.Mar 21, 2011
Brett Ryan
I recommend also setting the default storage engine in
/etc/my.cnf