Thursday, February 14, 2013

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 \
  --grammar=conf/examples/example.yy \
  --queries=1000 \
  --threads=1 \
  --basedir=/path/to/your/mysql/installation \

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] 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.

Friday, February 8, 2013

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:

Tuesday, February 5, 2013

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 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!