Search This Blog

02 August 2010

Tip that will save hours of sleep...

This tip is several months late but here goes anyway.

In my opinion, when you have an environment where you're using multiple MySQL storage engines (using the right tool for the job and all that jazz), there is nothing more annoying than to have tables created or altered into a storage engine that you did not request. The default behaviour of MySQL is to use the default engine if the requested storage engine is not available.

Why is this annoying? Because when a specific storage engine is requested, it needs to be using that specific engine and anything else is an error.

Fortunately, there is an SQL-Mode in MySQL which will enable the most desirable behaviour ... that is, if an engine, specifically requested, is not available, there will be an error instead of substituting the default storage engine: NO_ENGINE_SUBSTITUTION

Everyone should put this into their site's my.cnf files. I am glad to see more and more people using it.

(My only apology is that I am sorry I failed to argue strongly enough to make this behaviour default when I implemented it, years ago)