Change MyISAM to InnoDB

[Krytyk]

Hello,

I am using version MySQL v5.5.5-10.4.15-MariaDB-cll-lve. I would like to change the engine of some database columns (1/4 of them are on the MyISAM engine, including posts, topics, statuses, and the remaining 3/4 on InnoDB), so I have questions for you:

1. Can changing these tables delete some data? For example, if we have 3000 posts, it can suddenly become 2980 after conversion?
2. Which way is better:
   
   First way) Typing in turn for each table: ALTER TABLE wp_comments ENGINE=InnoDB;
   Second way) Manually changing the table setting:
   
   
   
   Does it not matter which of the two options?
3. Do I need to do anything else after doing either of these two options? And it will rebuild some itself from MyISAM to InnoDB?

I will be grateful, especially @bfarberfor help.

Thanks 😘 

Edited December 17, 2020 by Krytyk

[CoffeeCake]

That appears to be a table related to Wordpress, not IPS:

https://codex.wordpress.org/Database_Description#Table:_wp_comments

Here's MySQL documentation on changing engines:

https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html

[Krytyk]

12 hours ago, Paul E. said:

That appears to be a table related to Wordpress, not IPS:

This is a screen from another page only as an example.

12 hours ago, Paul E. said:

Here's MySQL documentation on changing engines:

Yes, but not everyone gave the same advice when looking at other topics. Hence, I want to mention this topic again.

Edited December 17, 2020 by Krytyk

[CoffeeCake]

In general, it's a better idea to make schema changes directly on the MySQL server itself via shell prompt. The web interface for phpMyAdmin simply is translated into commands that are sent to your database server, however you run the risk of those commands timing out.

If you have MyISAM tables in your IPS install, that may be best addressed with a support ticket. Before making any database changes, you'd want to make a full backup of your web file system and your database in the event things don't go as planned. You're asking if corruption might happen, and the answer is yes--you always run that risk. Backup, backup, backup!

Note that the row count displayed for an InnoDB table may not reflect the actual number of rows stored. Only an approximation is displayed. If you're looking at counts between a MyISAM table prior to changing the engine and after in InnoDB, you may very well see different numbers. This is by design.

To see how many rows there are, use SELECT count(*) FROM table;

[bfarber]

This isn't really something we'd "assist" with in a support ticket, other than recommending you do indeed move over to InnoDB.

As Paul E. said, phpmyadmin is simply a user interface for generating the raw MySQL query which will be sent behind the scenes. If you are already comfortable generating the raw query, just login to the MySQL console and run it directly. You will avoid timeout possibilities. And indeed, take a backup first.

[Krytyk]

I changed the engine from MyISAM to InnoDB via phpmyadmin (change to storage engine). Everything moved by itself without error. Do I have to do anything else?

[CoffeeCake]

24 minutes ago, Krytyk said:

Do I have to do anything else?

Yes, please send me....