The Setup

Imagine, if you will, the following scenario:

• You design a whole new database schema for your cool new scalable web-application. You’re using MySQL and the InnoDB datbase engine for everything, because your schema is so cool it uses all sorts of foreign keys and transactions and the like.
• You quickly set up MySQL and get your application going with your new schema on your development staging machine.
• You get MySQL up and running on your live server, play around with it for a bit to make sure it’s working, and then set up a my.cnf file with all sorts of caching and security goodies in it.
• You do a backup from your dev machine, restore it to the live server, and ta-daa!!! Your web application is up and running on your live server.

What you might not have noticed, especially if you – like me – have a few thousands rows of data, is that MySQL might have screwed you along the way and not really told you all that clearly.

The Problem

After a few days of operation on my live server, I started to notice a few weird things—foreign keys weren’t being enforced properly, and there were some values in the database that probably shouldn’t have been possible. I furrowed my brows and put it on my list of stuff to investigate.

Well, yesterday, I added a new table to the database, and it went something like this:

mysql> CREATE TABLE Fudgecicles
>(
>  id INTEGER AUTO_INCREMENT PRIMARY KEY,
>  value VARCHAR(255) NOT NULL,
>)
>ENGINE = InnoDB;

Query OK, 0 rows affected, 1 warning (0.10 sec)

Where did that warning come from?

mysql> SHOW WARNINGS;

It is here that MySQL tells me:

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1266 | Using storage engine MyISAM for table 'Fudgecicles' |
+---------+------+-----------------------------------------------------+

Augh! No! Bad! Bad, MySQL, Bad! Why on earth would it do that? I didn’t misspell InnoDB or even use “incorrect” casing in the name. There’s nothing wrong with the schema I specified and I’ve done this hundreds of times before.

Well, after some research, I then tried the following:

mysql> SHOW ENGINES;

And MySQL helpfully gave me the following:

+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | DISABLED| Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO      | Supports transactions and page-level locking                   |
| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO      | Example storage engine                                         |
| ARCHIVE    | YES     | Archive storage engine                                         |
| CSV        | NO      | CSV storage engine                                             |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | NO      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| ISAM       | NO      | Obsolete storage engine                                        |
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

The InnoDB database engine had been disabled somewhere along the way and I hadn’t even noticed. It is enabled, by default, on the standard Linux and Mac OS X MySQL binaries that I’ve been downloading. So something changed along the way that made this all stop.

Far worse, I then began to worry about my existing tables, all of which we supposed to be InnoDB. Upon executing the following for each of them:

mysql> SHOW CREATE TABLE Fudgecicles;

I would see something like:

| Table       | Create Table
| Fudgecicles | CREATE TABLE `Fudgecicles` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Sure enough, every single one of my tables was MyISAM, and not the InnoDB it was supposed to be.

The Reason

The problem turns out to be that InnoDB is somewhat finicky about my.cnf settings, and will frequently refuse to operate if settings change in this file in such a way that makes any existing data incompatible with the way it would write new data.

In my case, it turns out that changing the settings for the InnoDB binary data and log file sizes were somehow incompatible with the existing binary data and log files (ibdata1 and ib_logfileX). Upon starting the server, InnoDB finds this inconsistent state and simply refuses to start up. This is the first problem.

The second, and far more serious problem, is that MySQL just switches the database persistence engine on you and only provides a little warning. If you’re loading in thousands – if not tens of thousands – of rows, those warnings are easily lost in the scroll-a-thon that ensues. This is bad behaviour. MySQL should simply refuse to create your table if your selected persistence engine is not available.

The Solution

Fixing this problem involved three parts. The first, and easiest, is to get the InnoDB engine back. You have two choices:

1. You can either revert to the original settings you had for the my.cnf file to remove the inconsistent state that is freaking out poor ole’ InnoDB.
2. You can delete the idbdata1 and ib_logfileX files with the inconsistent sizes, thus removing in a different way the same inconsistent state, and allowing InnoDB to startup with your new (and presumably better) configuration values in my.cnf.

I chose the second method.

You then stop and restart the MySQL database engine, and InnoDB will be back. You can verify that all is well in InnoDB land by executing:

mysql> SHOW InnoDB STATUS;

And you will receive a gloriously long and detailed set of information on how things are going.

Part two of the process involved converting my tables back to InnoDB.

mysql>  ALTER TABLE FishSticks ENGINE = InnoDB;

This proves to be tedious and time consuming, as you cannot simply go from table A to table Z doing the conversion – because of the foreign keys and references, they have to be done in the right order. When one of the ALTER TABLE statements fails, you can find out what happened by re-executing the SHOW InnoDB STATUS command – it will tell you why it wouldn’t convert the table to InnoDB.

But, eventually, I got them all done. It was then that I noticed that none of the foreign keys were set up properly any more.

So, the last step of the process is to re-establish the FOREIGN KEYs. I did this by, for each foreign key in each table I had, executing the following commands:

mysql> ALTER TABLE FishSticks DROP KEY [dead foreign key name];
mysql> ALTER TABLE FishSticks ADD FOREIGN KEY (keyname) REFERENCES Table (fieldname);

The good news is that saving my database and getting back to all sorts of InnoDB goodness only took about an hour in total, for about 30 tables or so.

However, if MySQL had simply reported an error a few days earlier instead of blithely just switching tables types behind the scenes, I might have avoided this whole mess in the first place. Oh well, at least I learned a few neat little commands I can play around with now! Lesson learned!

Here’s to hoping that this article helps some other folks solve the same problem a bit quicker!