An in-place upgrade of a MySQL database to Confluence 3.0 or higher fails with errors like the following in the logs:
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:
The engine and character set will be specified near the end of the output, as in:
To list all tables' status to see which engine is used, use the following:
There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes:
To resolve this problem,
- Configure all tables to use the InnoDB engine instead using a statement like:
You will need to repeat this for every table.
- To change the storage engine used by default so that new tables will always be created appropriately, you can use a query like:
- 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.
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.