This is part 11 in what’s shaping up to be the best part of a 6 week series (Part 1, 2, 3, 4, 5, 6, 7, 7.1, 8, 8.1, 9 and 10) on various history bits of MySQL, somewhat following my LCA2014 talk (video here).

One of my favorite MySQL stories is the introduction of the Random Query Generator – or randgen. While I’ve talked for a while now about the quality problems that were plaguing MySQL, there were positive steps going on. The introduction of continuous regression testing with pushbuild, increasing test case coverage and a focus on bug fixing were all helping. However… a database server is a complex beast that can accept arbitrary queries and transactions and at the very least is expected not to segfault. How on earth do you test it beyond simple manual testing (or automated manual testing: running the same set of static queries)?

Well, at some point in development, David Axmark (one of the founders of MySQL AB) decided that if they were going to be a real database, they should have a test suite – so he wrote one. It was comprised of two parts: a small binary named mysqltest which basically read a file of SQL, ran it against the server and then displayed the results (and also had a couple of small bits of language such as variables and loops) and the other half was a shell script that started a mysqld and ran mysqltest for each .test file in the mysql-test/t/ directory and diffed the output against the mysql-test/r/*.result files.

This system still exists today, although the shell script has been replaced by a perl script which has been replaced with mysql-test-run.pl version 2 which dealt with replication, starting MySQL Cluster daemons, running tests in parallel and dealt with Windows a lot better. The mysqltest binary is largely unchanged however, and so the now MUCH larger suite of SQL based tests would be familiar a dozen years ago, when the mysql-test suite was in infancy.

But how do you ensure you’re testing enough of the server? Well… you can do code coverage analysis and write more tests manually, but wouldn’t it be much better if you could automatically generate tests? Wouldn’t it be great if you could automatically find a set of queries that caused the server to crash? Or a set of queries where the results differed between versions or other database servers (which would strongly indicate that somebody was buggy)?

Well, it turns out that Microsoft had a similar problem back in the 1990s with Microsoft SQL Server and a system called RAGS was used in the development of Microsoft SQL Server 7.0 (released in 1998).

So, fast forward about ten years and a relatively new hire of MySQL AB, Philip Stoev was working on something called “RQG” or Random Query Generator for MySQL AB. The basic idea was to be able to supply a grammar of what type of queries you would like it to generate, point it at a MySQL server and see what happens.

It turns out that what happened was that after a while, the MySQL server would segfault. To a room of MySQL Server developers, this was a pretty amazing advancement in testing the server – an automated way to find new bugs!

Throwing the Random Query Generator at the in-progress MySQL 6.0 optimizer caused a new innovation: automatic stack trace deduplication and test case minimization. RQG would automatically pair down the list of generated queries to the minimal set required to crash the server, so that you could import that set of queries into the existing mysqltest based regression test suite.

The dataset that RQG would work against was rather fixed, it was about 10 already prepared tables with some easily generated data. In MySQL Cluster, we had a set of C++ classes that ran a multithreaded application that would use integer columns as a checksum for the whole row, so updates would also update the checksum and at the end of your test (which may cause node failures in various scenarios) you can check that each row is intact and hasn’t been corrupted. So, IIRC it may have been me (or Jonas, I honestly cannot remember) who asked if such a feature would be added to RQG and the reply was “when I can no longer crash the server in 4 or 5 queries… I’ll think about it”.

So, developers expressed happiness, and Philip expressed this: “Why are you happy about this? We are about 10 years behind Microsoft in QA, why are you possibly happy about this?”

So with this great new tool, the true state of the current server version, the in-development server version (6.0) and all the optimizer enhancements it was bringing, the new Falcon storage engine and the in progress Maria (now Aria) storage engine would be exposed.

The news was not good.

(although, in the long run, the news was really good, and it’s because of RQG that we now have a really solid MySQL Server)

Further reading:

• Massive Stochastic Testing of SQL, from VLDB98
• A Genetic Approach for Random Testing of Database Systems