You're visiting the Confluence Knowledge Base. Visit the Confluence Knowledge Base Home for an overview.

Skip to end of metadata
Go to start of metadata

Symptoms

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:

Cause

There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes:

http://bugs.mysql.com/bug.php?id=4541

Resolution

To resolve this problem,

  1. Configure all tables to use the InnoDB engine instead using a statement like:
    You will need to repeat this for every table.
  2. To change the storage engine used by default so that new tables will always be created appropriately, you can use a query like:
  3. 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.

Related Content

 Expand to see related content
Unknown macro: {dynamiccontentbylabel}

Help us improve!
Is this article helpful?
Is it well written?
Is the content complete?

5 Comments

  1. Just made an update from 2.9.1 to 3.0.0_01 (1st with a developer license) using MySQL 5.0.51a-3ubuntu5.4 (Ubuntu).
    This fix was very useful, but i have to alter the following tables too:

    After that it seems to work.

  2. Hit exactly the same issue when upgrading from 3.4.9 to 3.5.

    After switching all tables' storage engine from MyISAM to InnoDB, problem fixed.

  3. Same problem here, here is the list of sql statements - might be handy for the next in line (smile)

  4. Add to /etc/my.cnf:

    Else, 3.5.2 (at least) does not specify INNODB, as I believe it should (or could).

    (I guess only one of those additions is necessary, but after three upgrade tries I was too tired to find out.)

  5. Since this is required, it would be really helpful if Confluence explicitly checked the type of the tables before just failing cryptically in an upgrade.