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:

Old default (5.7.4):

mysql> SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

New default (5.7.5):

mysql> SELECT @@sql_mode;
+---------------------------------------------------------------+
| @@sql_mode                                                    |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

As you can see, the big news here is the addition of the following sql_modes:

ONLY_FULL_GROUP_BY

Non-deterministic grouping queries will be rejected.

STRICT_TRANS_TABLES

Invalid and missing-value data exchanging statements will result in an error instead of a warning.

Above is my own very brief summary of these SQL modes. I recommend reading the 5.7 docs on the subject and the release notes for official detailed information.

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:

Adding a mode to sql_mode:

SET sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));

Removing a mode from sql_mode:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));

Example: Add ONLY_FULL_GROUP_BY to sql_mode:

mysql> SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------------------------+
| @@sql_mode                                |
+-------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------+
1 row in set (0.00 sec)

-- It works even if sql_mode is empty:

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.32 sec)

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.06 sec)

mysql> SET sql_mode=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)

NOTE: If the mode is already set in the sql_mode variable, no change is made, and no error is given, which is quite neat.

Example: Remove ONLY_FULL_GROUP_BY from sql_mode:

mysql> SELECT @@sql_mode;
+---------------------------------------------------------------+
| @@sql_mode                                                    |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

If the specified mode is not actually set in sql_mode, no change is made and no error is given.

UPDATE (2014-12-12): The developer behind the new ONLY_FULL_GROUP_BY improvements in 5.7, Guilhem Bichot, has published a blog entry on the details of his work.