XF 2.2 Convert MyISAM to innoDB

xml

Active member
Currently I have MariaDB 10.3 with 120 MyISAM table and 97 innoDB and planning to convert.
1. Is there official script can do that? if not, what MyISAM tables I should start with to convert to innoDB?
2. Changing "default-storage-engine=MyISAM" to "default-storage-engine=innoDB" in my.cnf before converting MyISAM to innoDB, does it have any negative impact on database inconsistencies, corruption, and orphaned data?
 

Masetrix

Well-known member
We switched all tables, except the xf_search_index, to innodb in XF 2.2.x and found no problems.

You should also check whether you can upgrade MariaDB.
 
  • Like
Reactions: xml

nocte

Well-known member
The named CLI command changes table collation, not table type.

If it helps: In a quite new XF test installation (and without any manual changes) I only have 4 MyISAM tables:
  • xf_search_index
  • xf_session
  • xf_session_admin
  • xf_session_install
then there are a few MEMORY tables and the rest is InnoDB.
 
  • Like
Reactions: xml

xml

Active member
This solution worked for me, a 4.5 million post Database converted in 8 minutes

Run this SQL statement (in the MySQL client, phpMyAdmin, or wherever) to retrieve all the MyISAM tables in your database.
Replace value of the name_of_your_db variable with your database name.

SET @DATABASE_NAME = 'name_of_your_db';

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND ENGINE = 'MyISAM'
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY table_name DESC;

Then, copy the output and run as a new SQL query.
 

Chris D

XenForo developer
Staff member
Hopefully that all worked for you.

It may be worth checking a bit more in-depth just to make sure all tables are the correct engine. Most of the MyISAM tables should have been InnoDB already but some should be MyISAM and some should be MEMORY.

To be clear, these are the core XF tables that should be something other than InnoDB:

Table nameDefault engine
xf_attachment_viewMEMORY
xf_search_indexMyISAM
xf_sessionMyISAM
xf_session_activityMEMORY
xf_session_adminMyISAM
xf_session_installMyISAM
xf_thread_viewMEMORY

These are the only tables that, by default, should be anything other than InnoDB. All the others should be InnoDB.

As per @Xon's advice you may be able to change some others to InnoDB but in the interests of making as fewer changes as possible for now I'd recommend getting your database to the default state first of all, then consider changing the defaults down the line.

(We don't officially recommend deviating from the defaults but we're aware that some people do).
 

Weppa333

Active member
I'm sorry @AndyB and others, but unless the doc recently changed at that URL, there is not a single word about a script to "convert" remaining myisam to innodb in the doc, post upgrade?

Also and FYI I can confirm a XF1.1 installation upgraded to 2.1 or 2.2 keeps all tables as MyISAM, whereas a new install would be innodb on most tables.

I'd like to make my "imported" instances as close to current as they should be, so really, if there is a script, i'd like to find it :)
In the meantime I used the above trick + exlucding the tables that must not be converted from the output
 
Last edited:
We switched all tables, except the xf_search_index, to innodb in XF 2.2.x and found no problems.

You should also check whether you can upgrade MariaDB.
My forum run on a 4GB VPS and after converting xf_search_index to InnoDB it pushed my sever to the RAM limit and site was not responsive. Even the swap memory was full. I had to convert it back to MyISAM and everything was back to normal. This proves why xf_search_index is still kept in MyISAM. It may be different with Dedicated servers with High RAM installed
 
Top