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.