Take the Moodle ACID Trip

How to Migrate Your MySQL Database from MyISAM to INNODB

Anyone seen this message after upgrading a Moodle 1.9 instance to Moodle 2.0 in their admin panel? This is what I saw after upgrading to Moodle 2.0 from Moodle 1.9:

"Database tables are using MyISAM database engine, it is recommended to use ACID compliant engine with full transaction support such as InnoDB."

Why Convert from MyISAM to InnoDB?

In addition to the fact that the InnoDB storage engine will be mandatory (InnoDB storage engine highly recommended) in future versions of Moodle 2.x, consider the other benefits as mentioned below:

While the MyISAM format has low overhead and generally the fastest performance among MySQL storage engines, it does not have advanced features like transactions, rollbacks, and row-level locking. InnoDB has these features and is also fully ACID-compliant (atomicity, consistency, isolation, and durability). ACID compliance is one of the touchstones of high-end database systems. (Source)

Are there any drawbacks to converting?

The extra features in InnoDB require more resources in terms of CPU, memory, and disk space. After conversion to InnoDB, the database in my application used triple the disk space it did as MyISAM. In addition, because multiple databases are stored in the same data file, backups and restores may be more complicated.


SWITCHING FROM MyISAM to INNODB
Below is an illustrated walkthrough (watch the video in 2 parts) of the steps to migrate Moodle 1.9 database type (MyISAM) and upgrade it to Moodle 2.x (INNODB). 

The instructions are adapted the information here:
  1. Do a SQLdump using PHPMyAdmin (or your favorite MySQL tool) of the database (View Picture)
  2. Complete a find and replace of "myISAM" with "INNODB" (without quotes, of course) in the SQL dump text file (View Picture)
  3. To improve speed of conversion, add the following instructions to your SQL dump text file:
    • Place "SET AUTOCOMMIT = 0;" without quotes at the start of the SQL dump file
    • Place "COMMIT;" without quotes to the end of the SQL dump file
  4. Import the SQL dump file that I'd made changes to into the MySQL server. (View Picture)
  5. Success, it all worked fine. I verified it by looking 
    1. Looking at the TYPE of table, as it appears in PHPMyAdmin. (View Picture)
    2. Running SHOW ENGINES as a SQL command (View Picture)
Other free or no-cost MySQL tools you can use that will get the job done, in fact, that you may need if your SQLdump file is exceedingly large (100 megs or more):
Comments