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/
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 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.
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.
- Did a SQLdump using PHPMyAdmin of the database (View Picture)
- Did a find and replace of “myISAM” with “INNODB” (without quotes, of course) (View Picture)
- 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
- Looking at the TYPE of table, as it appears in PHPMyAdmin. (View Picture)
- Running SHOW ENGINES as a SQL command (View Picture)
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?