Unexpected MySQL table engine change?

Question

I've been adding tables to MySQL databases by copying & pasting from a file into the mysql command-line app. My procedure is to copy the CREATE TABLE statement from the file and paste it into my development database and then also paste it into the test database on another machine. I'm the only one using the test database. My CREATE TABLE statements specify Engine=InnoDB. The test database has InnoDB enabled.

Recently another developer discovered that the tables on the test database are all MyISAM tables. I checked my file, it still says "Engine=InnoDB" for everything. I checked my development database, the tables are Engine=InnoDB there.

To prove that the test database has no problem with InnoDB I just ALTER TABLEd to set the engine to be InnoDB on all tables.

I hope that I've just made a copy & paste or other silly mistake. But it does seem weird that all the test tables are MyISAM...surely I can't be that consistent with my mistakes. All backups & restores have been done with mysqldump.

Is there a way of inadvertently changing engines?

Answer 1

I think this is another instance of a widely known MySQL pitfall; I have hit this problem a few times myself.

Basically the problem is that, if the InnoDB engine fails to start for whatever reason (usually configuration problems) -- then MySQL silently falls back to the MyISAM engine. Even if your statement says:

CREATE TABLE () ENGINE=InnoDB

then, if InnoDB isn't active, MySQL will happily create a MyISAM table without even warning you. Even if you later discover and fix the InnoDB problem, all tables created during the problem remain MyISAM. Bye-bye data integrity! :)

You can run SHOW ENGINES to see which engines are active. See this MySQL bug report for more details.