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.

Recommended reading: The Worst Five Minutes

Good blog post by Adam Knight, about the importance of first impressions and usability when it comes to software products:

The Worst Five Minutes

I remember from some years ago a "rule" that a new user should be able to get MySQL installed and up and running within 15 minutes. Whether this was and still is easy or not I shall not say... it depends. But it should still be in the back of the mind of any software engineer, that the product's first impression is important, and that usability must be taken seriously.

Get to know the Random Query Generator

In the MySQL QA teams in Oracle we have been using a tool called the Random Query Generator (or "RQG" for short) for some time now. The main RQG testing effort has been on new server development, including regression testing. The recent MySQL 5.6 GA is a result of a couple of years of hard work, including a lot of RQG testing and bugfixing as a result of that.

I can easily say that the RQG has helped making MySQL 5.6 a better release than it would otherwise be. It is of course not our only testing tool, but there are plenty of bugs this tool has uncovered that would likely not have been seen in our other testing. Such issues include:

• crashes and asserts
• memory management bugs (valgrind errors)
• wrong results

The main clue to the power of the RQG is its ability to automatically generate SQL queries based on some grammar file. This makes it possible to produce SQL statements that you would not have been able to cook up manually even if you tried for years.

I plan to write a series of blog posts which go into some more details, including specific techniques and use of the tool. In the mean time, if you are interested I recommend that you spend some time reading more at the RQG's home at Launchpad,  or reading some getting started documentation on Github.

If you just want to get started right away, this is what you need:

• Perl (version 5.10 or newer is recommended)
• A MySQL Server installation (other databases are also supported to some degree, including Java DB, but MySQL has had the main focus)
• Some Perl modules:
• DBI
• DBD::mysql
• Digest::MD5
• Note: For more than running just simple test runs, you need a few other Perl modules as well, see docs for details. Note that it pays off having a relatively recent version of Perl, as more modules are part of core Perl then, meaning you won't have to install that many yourself.
• The Bazaar version control system (to get the most recent RQG code).

Once you think you have everything set up, branch the RQG code from launchpad, like this:

bzr branch lp:randgen

Then take the RQG for a test drive like this (unix style):

cd randgen

perl runall-new.pl \
  --grammar=conf/examples/example.yy \
  --queries=1000 \
  --threads=1 \
  --basedir=/path/to/your/mysql/installation \
  --vardir=$PWD/vardir

The test run should take less than a minute in total. You should see in the output that RQG starts a server, creates a test database, starts some validator (more on that later), starts running queries (the actual queries are not printed in this case), and at the end shuts down the server and reports the end result (should be STATUS_OK).

...
# 2013-02-14T08:18:57 Test completed successfully.
# 2013-02-14T08:18:57 GenTest exited with exit status STATUS_OK (0)
# 2013-02-14T08:18:57 Stopping server on port 10630
# 2013-02-14T08:18:57 [14332] runall-new.pl will exit with exit status STATUS_OK (0)

If you want to see what kind of queries the RQG actually produced, you can check out the file vardir/mysql.log (since the example grammar is relatively simple the SQL statements will be simple too).

6 Query     UPDATE `A` SET `pk` = 0 WHERE `col_varchar_key` < 9 LIMIT 6
6 Query     DELETE FROM `AA` WHERE `col_varchar_key` = 0 LIMIT 4
6 Query     UPDATE `C` SET `col_varchar_key` = 5 WHERE `col_varchar_key` < 8 LIMIT 0

You can also add the option --sqltrace=MarkErrors to the command line in order to make the RQG print all generated queries to standard output. This is what --sqltrace does, while the MarkErrors setting ensures that any failed statements (i.e. illegal statements that are produced from the grammar but refused by MySQL) are marked (prefixed) with "# [sqltrace] ERROR <errno>:". Example:

INSERT INTO `BB` ( `col_datetime_nokey` ) VALUES ( 1 );
# [sqltrace] ERROR 1062: UPDATE `DD` SET `pk` = 7 WHERE `col_int_key` < 7 LIMIT 4;

Error 1062 means "Duplicate entry for key", and is a perfectly valid error in this case.

Feel free to experiment, study the code, and try different settings. I hope to present some more specific details in this blog later.

Moose in the back yard

Since I started working from home more or less full time I have thought lunch time was one of the definitive downsides... no more office cafeteria with nice hot soups and delicious "wienerbrød" (spandauer/danish) and hot meal on Fridays, no more humorous talks with colleagues, coffee machine, waffles... Well, today was the exception.

As a Norwegian working man I try to enjoy my boring slice of bread for lunch in the kitchen while listening to the radio and reading newspapers and other stuff ending up in the mail box throughout the week. Usually nothing exciting happens during the meal. Today, however, I noticed some movement in the corner of my eye, outside on the lawn behind our house. And there I saw one... no two... no three (!) moose stopping to have a chew on our apple trees (winter time, so no apples) before continuing on towards the next house...

























Even though I have seen moose in the wild up-close several times before, I have never seen them in this neighborhood, a relatively densely populated area (yet pretty close to fields and forests). Wow, that was cool. Maybe it's not that bad to eat lunch at home after all?

More photos available here. There's even a short video:

MySQL 5.6 GA is released

The day has come, MySQL 5.6 is now generally available (GA), ready for production use (at the time of writing the latest version number if 5.6.10). To download, go to www.mysql.com or go directly to the download page.

As part of MySQL Server Quality Assurance I have been "intimate" with parts of this release for a long time... it comes with many new great features, bug fixes and other improvements. While I won't claim it to be bug free (after all, no software is), I feel confident that it is a big step forward from earlier releases for the majority of use cases.

Give it a spin!

New home for Random Query Generator docs

One of the tools we use to qualify MySQL Server is the Random Query Generator (RQG for short). This is an SQL generator (and more) to test database systems such as MySQL. I plan to post more on that later.

The RQG is an open source tool written in Perl, and is available on Launchpad. Up until now the RQG documentation has been part of the MySQL Forge wiki. As you may have seen from this blog post, MySQL Forge is going away soon (August 1st). Unlike other parts of the Forge wiki, the Random Query Generator documentation has moved to github, and will continue to be maintained by the RQG community:

https://github.com/RQG/RQG-Documentation/wiki

The RQG docs is a great starting point for getting to know the Random Query Generator and its capabilities.

Welcome to my loft

Happy New Year, everyone!

This is my very first blog post ever, and as such it is going to be short. Hopefully I will find the time and inspiration to put something here soon that may be of interest to others.

I am a Norwegian with some geeky and some not so geeky interests. I suspect that some of the geeky stuff on this blog will relate to my current job as a QA engineer for the MySQL database at Oracle, but do not be surprised to see other things here as well. My initial hope is that I will be able to post something often enough to not make this blog appear abandoned by the same time next year.

Until next time!