Moodle’s ACID Trip – Converting #Moodle Databases from MyISAM to INNODB

Note: This started out as an email to Moodle Mayhem asking for help, but then turned into an exploratory journey. Since the process doesn’t seem to be articulated anywhere in detail, such as a Moodle wiki page, I thought it might be fun to go through it and see what happens. This is the result.

Image Source: http://3.bp.blogspot.com/_GQ8xBTDqzkc/SS4fQhtoHkI/ AAAAAAAAAPo/VQskgfRbwbI/s400/AcidTrip.jpg

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

If you’re like me, the question must have come up…This has a fascinating overview of what all that means:

http://www.linux.com/archive/feed/46370

And, here’s a quick explanation as to why you would want to convert from MyISAM to InnoDB:

Why would you want to convert an existing MySQL database from MyISAM to InnoDB? 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. I needed these features to solve my problem.

And why you wouldn’t want to:

Using the InnoDB storage engine in MySQL is relatively easy, but it does come with a price. 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.

Also reading these Moodle points:

Moodle Tracker – http://bit.ly/fjQcBH

Move to InnoDB will be mandatory (InnoDB storage engine highly recommended) in future versions of Moodle 2.x:

http://moodle.org/mod/forum/discuss.php?d=154351

So the question going around in my head is, “how do i migrate 1.9 dbases to InnoDB easily?“

Unless someone has an easier answer, I’m back to the first link mentioned in this email:

http://www.linux.com/archive/feed/46370

Not much information on Moodle’s wiki stub page (would this step by step be worth adding?) 

http://docs.moodle.org/en/Migration_from_MyISAM_to_InnoDB

TRYING IT OUT – SWITCHING FROM MyISAM to INNODB

As I was exploring what to do, I decided to take my Moodle 1.9 database and upgrade it to Moodle 2.0.For fun, I did a quick reading of the information here for relevant information then plunged in with these steps:

1. Did a SQLdump using PHPMyAdmin of the database (View Picture)
2. Did a find and replace of “myISAM” with “INNODB” (without quotes, of course) (View Picture)
3. To improve speed (as recommended in the notes, I did the following:
• Added “SET AUTOCOMMIT = 0;” without quotes to the start of the SQL dump file
• Added “COMMIT;” without quotes to the end of the SQL dump file
4. Imported 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)

Since it all seems to be working fine, what did I do wrong? 

;->

The only question going around in my head now is, “How do I set the INNODB type as the default when creating a database in MySQL?”


One approach might be that it’s done when Moodle 2.0 installation script runs and creates the tables with INNODB. So, to adjust my question, Will Moodle 2.0 on a fresh install create tables with INNODB as the database type?

Ok, the answer to that question is, “Yes” as shown in a new Moodle 2.0 installation (View Picture). So it appears the conversion from MyISAM to INNODB need only be done for Moodle 1.9 upgrade to Moodle 2.0 databases.

Thanks for coming along on this “acid trip” with me! I’m definitely open to feedback since I don’t pretend to be an expert at this process.