Converting MyISAM and InnoDB tables

Last updated: 30 June 2021

Changing the storage engine for a database table is surprisingly easy, but there are a few things to be aware of. This article looks at how to check what storage engines are used and how to convert individual tables from MyISAM to InnoDB. I also cover how you can use a script to convert a large number of tables.

Buyer beware

Changing the storage engine is an ALTER operation that changes the database structure in the background. For instance, the testdb database I used for the article about advantages of InnoDB over MyISAM creates the following files in /var/lib/mysql/testdb:

# ls -1 /var/lib/mysql/testdb/
db.opt
products.frm
products.MYD
products.MYI
suppliers.frm
suppliers.MYD
suppliers.MYI

This is a typical MyISAM structure. The .MYD files store the table data and the .MYI files store any indexes. When you convert a table to InnoDB the structure changes. Here are the new files for the products table after it has been converted to InnoDB:

# ls -1 /var/lib/mysql/testdb/products.*
/var/lib/mysql/testdb/products.frm
/var/lib/mysql/testdb/products.ibd

The .ibd file stores both table data and indexes, and the .frm file contains structural and identification data for the table.

In short, changing the storage engine for a table isn’t as easy as flipping a switch. Quite a bit of magic is done in the background. So, the usual caveats apply:

• Put your website in maintenance mode. This prevents visitors are triggering SQL queries while you are altering tables.
• Always make a backup before you change a storage engine.

Checking the storage engine

You can easily see the storage engine for individual tables via phpMyAdmin. When you select a database you get a list with all tables. The Type column shows the engine.

Image: both tables in the test database use MyISAM.

Alternatively, you can execute the following command in phpMyAdmin or on the MariaDB command line to get a list of table names and engines for the database testdb:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'testdb' AND ENGINE IS NOT NULL;

Obviously, you need to change the name of the database. If you run the command in phpMyAdmin you are likely to see some warnings, but the output should be correct.

Changing the storage engine

The quickest way to change the engine for a table is by entering another command, either via phpMyAdmin or from the MariaDB command line. The syntax of the command is as follows:

ALTER TABLE table ENGINE = 'engine';

So, to change the storage engine for the products and suppliers table to InnoDB you can use the following two commands:

ALTER TABLE products ENGINE = 'InnoDB';
ALTER TABLE suppliers ENGINE = 'InnoDB';

Image: changing the database engine using the phpMyAdmin console.

If you prefer a graphical interface then you can instead change the storage engine via the Operations tab in phpMyAdmin:

• Select the table from the left-hand pane.
• Select the Operations tab.
• Pick the new engine from the Storage Engine field under Table options.
• Hit the Go button in the bottom-right of the Table options box.

Image: the storage engine is one of the table options in phpMyAdmin.

Changing the storage engine for multiple tables

So far I have altered one table at the time. If you got lots of tables then you might want to automate the job. You can do so via a script that finds all MyISAM tables and then runs the ALTER TABLE command on them. This simply runs one ALTER TABLE command after the other, so you want to make sure that the database user has passwordless access.

If you don’t want to know all the details, there is an example script on our Bitbucket account. The remainder of this section is a walk-through of how to convert tables via a script. For this example I will again use the testdb database. It has just two MyISAM tables, but that’s enough to explain how it works.

Finding MyISAM tables

First, you need a list with tables that use the engine you want to convert. For the testdb database you can list the tables with the following command:

MariaDB [test]> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'testdb' AND ENGINE = 'MyISAM';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| products   | MyISAM |
| suppliers  | MyISAM |
+------------+--------+

Of course, to be able to convert the products and suppliers table you need just the table names (so that you can next feed them to ALTER commands). The --skip-column-names option in the below command suppresses the column headers and the --batch option gets rid of the table:

$ mysql --skip-column-names --batch -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'testdb' AND ENGINE = 'MyISAM'"
products
suppliers

Altering MyISAM tables

We are now ready to read and process the data. One of the safer ways to do so is via the Bash shell’s readarray builtin. Here, I use the command to read the table names into an array named myisam_tables:

$ readarray -t myisam_tables < <(mysql --skip-column-names --batch -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'testdb' AND ENGINE = 'MyISAM'")

We can now loop through the array. The basic syntax for that is:

$ for table in "${myisam_tables[@]}"; do
> echo "$table"
> done
products
suppliers

So, we can use this command to convert the tables:

$ for table in "${myisam_tables[@]}"; do
> mysql testdb -e "ALTER TABLE $table ENGINE = 'InnoDB'"
> done

Converting from InnoDB to MyISAM

Of course, you can convert the other way as well. The main thing to be aware of is that InnoDB tables may have foreign key constraints. If that is the case then you will encounter this error:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

To convert the table from InnoDB to MyISAM you first need to remove any constraints. In general, that is not a good idea. Well-designed databases use constraints to ensure data integrity between linked tables. Unless you have a really sound reason for switching to a database engine that doesn’t support foreign key constraints you probably don’t want to go that route.