Quick Changing the MySQL Engine on Existing Tables

On install, MySQL has its default storage engine set to MyISAM. It's heavily used, but IMHO, a bad engine to use for most applications. MySQL does offer a variety of other engines, including Memory, Merge, Archive, Federated, and of course InnoDB. They each have their uses and their pros and cons. I personally prefer InnoDB, as it is the only engine that is transaction-safe, ACID compliant, and actually supports proper foreign key constraints – i.e. the only one that truly is similar to other relational databases like PostgreSQL, MS SQL Server, and the like. It is still not as good as them, but it works with tweaking, beating, configuration manipulation, and heavy trigger implementation.

Suffice to say, in my MySQL databases, all my tables are going to be InnoDB. In our environment, however, particularly our the production server, we do not have control over the database config right now. It is a legacy config from MySQL 4 that is just…yeah, moving on. So when I first was made to switch from using PostgreSQL to MySQL, I quickly discovered the default engine was still at MyISAM. One of my many peeves about MySQL is it will often accept commands it didn't support without an error. New schemas I wrote with proper normalization to the third form, foreign key constraints, etc were running but the keys were dropping. Why? That darn default engine!

At the time we couldn't get the configuration changed, and when we were able to get it changed, it sometimes changed back! I quickly learned to write all of my schemas such that the engine type is explicitly set for the table, as well as the collation. For example:

CREATE TABLE base_genres (
genrelabel VARCHAR(100) NOT NULL
) ENGINE InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

However, when you're working with third-party applications, the developers may or may not have done this. A recent example is when we went to install Mura and Slatwall on one of our boxes for a client to try it out. It was a newer box, and we hadn't made time to tweak the DB config since it is our staging server and we didn't really have anything new being staged on it (except of course for this). Suffice to say, the DB engine was still set to MyISAM. Now when Mura installed, it used the option to set the engine on each table, so they all came over as InnoDB. Slatwall, however, did not. Now I don't blame Slatwall as one of its selling points is being very database agnostic, however without the transactional stuff, our Slatwall installation kept FUBARing and we couldn't figure out why!

After realizing the mess up with the DB, we needed to fix it. Now we could have reconfigured the server, then just reinstalled everything, but what if the problem wasn't discovered until a few weeks later? And you had legit data to keep? A back up/restore would keep the MyISAM types. Fortunately, MySQL provides a command for changing table engines on live tables:

ALTER TABLE [tablename] ENGINE=InnoDB;

But Slatwall v2 has 160 tables….do you really want to do that by hand? Yeah, I didn't think so. There are a couple ways you could generate all the ALTER statements using something like Dreamweaver or Eclipse, but you could also just get MySQL to do it for you. A side benefit is you can generate them for ALL your DBs, so if you saw the light and want to get rid of all the MyISAMs at once, you can. Run the following in phpMyAdmin. MySQL Workbench, or the like versus the command line if you have a lot of databases/tables (for easier copy/pasting). You can, of course, adjust the where statement as desired to limit it to a single database, filter out specific tables, etc.

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;') 
FROM information_schema.tables
WHERE engine = "MyISAM"
AND table_type = "BASE TABLE"
AND table_schema NOT IN ('mysql','phpmyadmin')

Results of running the query

Copy the results, paste back into your SQL query and run it. Done! Now keep in mind, this will NOT restore the foreign key constraints that may have been part of the DB schema when the database was built (hence why its better to just do the declaration at the start). You'll have to add those back in yourself, but at least when you put them in this time, they will stay! And, of course, you can also use this to change to and from other engine types as well.