Convert your MyISAM Database to InnoDB with phpMyAdmin

InnoDB is a more robust and better database table type for modern applications like MODX Revolution compared to the older MyISAM table type. With InnoDB, you should see improvements in platform I/O, your sites should perform better and faster, and they should better survive database hiccups that can occur, too. MODX Revolution 2.6 will default to InnoDB for new installations, if supported.

The steps below will allow you to update existing installations to support InnoDB tables today. Credit is due to MODXer, Garry Nutting, for creating the method used in this tutorial.

Update: If you have a larger database, it's more reliable to convert MyISAM to InnoDB via the command line.

Requirements

You'll need the following in order to convert your database:

1. The current version of phpMyAdmin ("PMA") and some rudimentary knowledge/experience in using it
2. A database version that supports full text indexes for InnoDB table types

Process

First, ALWAYS back up your database before doing anything potentially destructive. Once you have that done, you're ready to go:

1. Launch PMA. You must explicitly click on the name of your database in the top of the left column. There are sometimes multiple databases, so make sure to click the right one. Do not click on the one labelled information_schema.
2. Click the Query tab

3. In the box at the lower right, paste the following code into the "SQL query on database …" box, replacing "your-db-name" with your actual DB name:

   
   SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
   FROM INFORMATION_SCHEMA.TABLES
   WHERE ENGINE='MyISAM'
   AND table_schema = 'your-db-name'; 
   

   
   

4. Press the submit query button to see a list of tables to alter to InnoDB
5. Click the "+Options" link above the results, choose the "Full texts" option, and press the "Go" button in the shaeded area at the lower right
6. Above the results, tick the "Show all" checkbox
7. Copy all the queries using the "Copy to clipboard" link in the "Query results operations" box below the results
8. Paste the result into a text editor and copy all the lines starting with "ALTER TABLE" to your clipboard
9. Click the SQL tab above the results
10. Paste the ALTER TABLE statements into the textarea and press the "Go" button in the lower right shaded area

To confirm your database was successfully converted, click your database name in the left column, and all table types should now say InnoDB. We hope you find this useful as you prepare for the future of MODX Revolution.