« My SSH Private Key is in a Proprietary Format? | Main | What happened to mysql.com Search? »

April 9, 2006

Where was this MySQL option when I needed it?

Digging around the MySQL 5.0 SQL mode docs tonight and see this option:

NO_ENGINE_SUBSTITUTION - Prevents automatic substitution of the default storage engine when a statement such as CREATE TABLE specifies a storage engine that is disabled or not compiled in. (Implemented in MySQL 5.0.8)

Where was that when I needed it?

Back when I switched data storage from the MyISAM to InnoDB storage engine we got burned by not having this option. I had enabled InnoDB on the development box to allow the programmers to start using foreign keys in their database schemas. There was a disconnect on pushing the new functionality up to the testing environment and it went up before InnoDB was enabled on the test database. Unfortunately none of the developers who were pushing the DDL statements up to testing noticed that InnoDB wasn't enabled because MySQL ignored the parts of the SQL that specified foreign keys. MySQL also translated ENGINE=InnoDB into ENGINE=MyISAM without batting an eye.

Of course it's very easy to enable InnoDB, and having to recreate the tables in the test environment didn't cause much of a ripple.

Nice to see that now (once you're on 5.0.8 or later) you can prevent this kind of mishap.

Posted by mike at April 9, 2006 10:36 PM