XF 2.2 Convert MyISAM to innoDB

[xml]

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?

 

[AndyB]

There is a script. The Emoji and unicode support explains in the manual:

https://xenforo.com/docs/xf2/unicode/

 

[xml]

There is a script. The Emoji and unicode support explains in the manual:

https://xenforo.com/docs/xf2/unicode/

I run the script. The Emoji is enabled now, but still the database have 120 MyISAM tables and 97 innoDB

 

[Masetrix]

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.

 

[nocte]

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.

 

[xml]

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]

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 name	Default engine
xf_attachment_view	MEMORY
xf_search_index	MyISAM
xf_session	MyISAM
xf_session_activity	MEMORY
xf_session_admin	MyISAM
xf_session_install	MyISAM
xf_thread_view	MEMORY

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).

 

[xml]

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

I took that into account when I did the conversion. My next step is to optimize my.cnf file to go with innoDB engine

 

[Weppa333]

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

 

[faker.king]

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