Recently I set up a CentOS based mysql server (Actually I used RPMs from mysql.com since the ones that come with CentOS 5.4 contain some ugly replication bugs). I overlooked, that the default storage engine was MyISAM while our corporate standard (and applications requirement) is InnoDB.

Fortunally I found out about ”ALTER table ENGINE=…;”.

Since I am very lazy I created myself a little helper, that generates per MyISAM table a matching ALTER TABLE statement. So all I had to do was to copy the result of the query back to mysql and lean back.

The statement I talk about is:

select concat('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE InnoDB;')
from information_schema.tables where table_schema <> 'mysql'
AND table_schema <> 'information_schema'
AND engine = 'MyISAM';

It queries the information_schema for MyISAM tables outside mysql (the database) and outputs a corresponding statement.

If you found that useful, please leave me a comment.