Convert from MySQL MyISAM to InnoDB Storage Engine
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
At one point in time, you may have accidentally set up your databases using the MyISAM storage engine. Although MyISAM is fast, it lacks transactions and foreign key constraints, thus increasing the likelihood of database-based corruption.
To change to InnoDB
Backup everything, and make a copy without create table information
mysqldump -uroot dbname> dbbackup.sql mysqldump -uroot dbname --no-create-info > db_no_create.sql- Download a fresh copy of the same version of Confluence
- Create a new database (create database foo)
Set up Confluence against foo, taking care to ensure that you've got the engine and all other settings set correctly
With the introduction of Active Objects, some tables are not populated immediately upon creating a Confluence instance. Active Objects database tables are generated lazily, meaning they won't be created until a feature that requires them is made. For example, the
AO187CCC_SIDEBAR_LINKtable won't be created until a space is made. Some Active Object tables are also created by plugins (such as Team Calendars for Confluence), and these will need to be installed to generate the necessary tables in the new database.- Once you're done with setup, shutdown Confluence
Make a backup, without data
mysqldump -uroot foo --no-data> db_ddl.sqlMake a 3rd database, restore the db_ddl.sql, then the db_no_create_.sql
mysql -uroot newdb < db_ddl.sql mysql -uroot newdb < db_no_create.sqlEdit
confluence_home/confluence.cfg.xml, change thehibernate.connection.urlto reference your new database. Ensure that your the connection string is of the following form:mysql://DBHOST/DBNAME?autoReconnect=true&characterEncoding=utf8&useUnicode=true&sessionVariables=storage_engine=InnoDB