Observations from a tall Norwegian on anything from his day-to-day work as a software geek to random topics of interest.

Friday, September 26, 2014

Adding or removing individual SQL modes in MySQL's sql_mode variable

Oracle recently published the MySQL 5.7.5 Development Milestone release, a pre-production release providing numerous improvements to the MySQL server. You can download the release here: http://dev.mysql.com/downloads/mysql/5.7.html

This release carries some incompatible changes, as explained in the release notes and in the blog post describing the release. During my work in the Server QA team I have experienced some of these changes first hand already, and we have had to modify some tests and tools to adapt to some of it.

One very big change (well, some may not notice at all, while others may need to adjust their tools and applications) is the new default value of sql_mode:

It is also worth mentioning that the ONLY_FULL_GROUP_BY mode is greatly improved in 5.7.5, as described in Roland Bouman's blog. This is a welcome change by many, including myself :)

Now, down to business...

We have found that one old way of setting SQL mode is no longer considered "safe" for us. We want to test the default values of the server as much as possible, because that is likely what most customers will use. However, in some cases we need to set certain sql_modes in order to get the desired behavior - either for testing that specific behavior, or because this behavior helps making testing other things easier.

The sql_mode variable for a session can be set like this, from a MySQL client:

SET sql_mode=<value>

for example:

SET sql_mode='NO_ENGINE_SUBSTITUTION'

to ensure that the server will not revert to a different storage engine for a table "behind our back".

This works fine as long as you are sure that you do not want any other sql_modes, or as long as the old value of sql_mode is empty. However, now that the default contains no less than three SQL modes, some more care needs to be taken to make sure we are not disabling an sql_mode that we actually want to keep.

The sql_mode variable does not work the same way as e.g. optimizer_switch, where you can set individual flags and the others will remain the same. With sql_mode, what you set is exactly what you get.

It would be nice to be able to easily add or remove a single mode to/from the sql_mode variable, wouldn't it? Here are a couple of hopefully helpful tricks for setting and removing individual modes from the sql_mode variable, without risking the removal of modes that are not mentioned in your statement: