[Deploying Sakai] MySQL: MyISAM or InnoDB?

Wolfgang Rohregger Wolfgang.Rohregger at mci.edu
Mon Aug 8 05:04:24 PDT 2011

I guess we had luck. At our pilot instance the default storage engine setting of MySQL had been set to MyISAM when we started with Sakai pilot, back then with version 2.6. I am currently testing a conversion process for the database, that I will share here. So far there have been no problems with this process. Before migration to 2.8 I will compare the "converted" schema to a vanilla 2.8 db schema to check if anything is missing.

As there are no foreign keys on MyISAM, I hope that none that should be in there are missing. I will see this when comparing the db schemas anyway.

Converting the database to InnoDB, assuming that the OS is Linux:

(1) Create database dump of Sakai, e.g. mysqldump --user=sakai -p --create-options --default-character-set=utf8 --hex-blob --disable-keys --single-transaction --comments=1 --complete-insert sakai -r SAKAIDUMP_FILE.sql

(2) Prepare a command file "InnoDB_conversion.txt" for the stream editor sed, containing one line with: s/ENGINE=MyISAM/ENGINE=InnoDB/g

(3) run sed: sed -f InnoDB_conversion.txt SAKAIDUMP_FILE.sql > result.sql
The command file from (2) tells sed to replace every occurence of "ENGINE=MyISAM" with "ENGINE=InnoDB". sed works even with very big files, so it will work even on big database dumps. The resulting database dump will be inside result.sql

(4) Check with a diff that only DDL statements have been modified: diff SAKAIDUMP_FILE.sql result.sql | less

(5) Don't forget to set the default-storage-engine on all MySQL databases by adding this line to the server part of the MySQL server configuration file: default-storage-engine = InnoDB

(6) Test this by restarting the MySQL server and querying the server with a MySQL client using the command "SHOW VARIABLES;". "default storage engine" or "database engine" should be set to "InnoDB".

(7) Now copy the dump on to a test system with its own MySQL server (with correct settings) and import it:
drop database sakai;
create database sakai default character set utf8;
source result.sql;

(8) Maybe there have been some tools, that couldn't execute their index creation. auto.ddl=true in sakai.properties should resolve this for the first run with the converted database.

(9) Start up the test Sakai instance and check if everything is working as expected.



Von: production-bounces at collab.sakaiproject.org [production-bounces at collab.sakaiproject.org] im Auftrag von Hedrick Charles [hedrick at rutgers.edu]
Gesendet: Sonntag, 07. August 2011 03:55
An: Steve Swinsburg
Cc: production at collab.sakaiproject.org; Wolfgang Rohregger
Betreff: Re: [Deploying Sakai] MySQL: MyISAM or InnoDB?

Even back in very early days, like 2.1, you really, really wanted to use InnoDB. We mistakenly started 2.0 with Myisam. It took us quite a while to clean up the damage.

On Aug 1, 2011, at 7:00:20 AM, Steve Swinsburg wrote:

I've had troubles running the Sakai 2.8.0 DB upgrade scripts for MySQL. After doing some research, there have been at least some indicators that parts, if not the whole, database enginge of MySQL might have been switched over to InnoDB.

Is InnoDB now used for all Sakai tables and should I migrate the old MyISAM tables to InnoDB?

More information about the production mailing list