Do we have to convert MySQL from MYISAM to InnoDB

[SherryX]

Today our Database server ran out of disk space and one of the confluence tables crashed.

Confluence seems to be using MYISAM files and not innodb, but in confluence.cfg.xml the DB storage_engine is set to innodb.

We're preparing for the next upgrade and migration to new data centre, my question is - do we need to convert DB to innodb when upgrade/migration, if yes, what's the process to do that? Is it OK just set DB connection to sessionVariables=storage_engine=InnoDB?

Currently we are using confluence 3.4.6, JIRA 4.2.2, Bamboo3.2.2, Cruicible 2.4.4

Thanks

[Andrew Frayling · Accepted solution]

Hi Sherry,

You don't *have* to convert to InnoDB, but it is highly recommended as you can get all sorts of data corruption issues with MYISAM as it's not a transactional storage format.

To convert tables to InnoDB you need to run SQL statements to convert the tables that have been created as MYISAM:

CONVERT TABLE <table_name> ENGINE=InnoDB;

As you've mentioned, I'd also set the DB connection to sessionVariables=storage_engine=InnoDB.

Hope that helps,

Andrew.

[SherryX]

Thanks. Is the following statement the same as above?

ALTER TABLE <table_name> ENGINE=InnoDB;

Also is that OK to upgrade MySQL to the latest version? We're at MySQL5.1 now.

[Andrew Frayling]

Sorry, yes it should be ALTER not CONVERT. Atlassian don't support the latest version of MySQL, but I'm running Confluence 4.0.5 with MySQL 5.5 in a test environment without any problems. Atlassian only support MySQL 5.1 (http://confluence.atlassian.com/display/DOC/Supported+Platforms), but that may just be because they haven't tested against 5.5 yet, or it may be because there are problems with 5.5 that I haven't hit yet. If you do go with the latest version of MySQL and hit problems you may be told that your configuration is unsupported and asked to downgrade MySQL to see if the issue occurs on a supported configuration. For production systems I'd stick with supported configurations, I.e. 5.1, just to be safe so you know that if you do get any issues you can call on Atlassian support to help rectify the problem. Andrew.

[SherryX]

Great, that's really helpful, thank you very much for the information!