some useful additions to query generator

I've been on vacation this week, and decided to fine-tune some old QA code. Opened the manual to see the syntax for a select statement, and afterwards added to my random select generator the following:

• all index hints (force, use, ignore, for join, for order by, for group by)
• lock in share mode, for update
• key_block_size for individual indexes
• hash, btree, rtree for individual indexes
• unique, fulltext, spatial for indexes

Especially important is the 'lock in share mode' addition. The reason is InnoDB
has many serious bugs with this locking mode (insert ... select, and others) in read committed mode.

So, I don't need multitable delete or update to reproduce those bugs, since I can just do a simple select locking in share mode. For example, the following bugs previously went without proper testcase until I discovered this:

assert btr/btr0pcur.c line 217 -innodb_locks_unsafe_for_binlog or read committed
5.1.35 crashes with Failing assertion: index->type & DICT_CLUSTERED
Strange error messages about locks from InnoDB

putting it all together

Last post was about Rainbow testing. So that's old news, and I lost count of the number of bugs it found, and have been fixed. Probably 30+ crashes.

Now, I have to put together all these odds & ends of code I wrote disparately over the last 18 months into a single collection to enable complete end-to-end testing.

I have now got roughly the following:

o) random table maker
o) query maker based on any tables, (using predefined rules)
o) data generator for any tables
o) complete charset collection for each valid character
o) database of mysql functions, data types
o) multi-threaded testing environment for any queries
o) query results comparer for any queries
o) rainbow, which will provide help in making good coverage of functions, etc.

What's needed still? Well, I would like 56 hours in a day ... More posts later when v0.001 of the integrated is semi-functional :)

some results of rainbow

ok folks. here's some results:

mysql> select last_errno,count(*) from 
queryqueue group by last_errno;
+------------+----------+
| last_errno | count(*) |
+------------+----------+
|          0 |  1600796 |
|       1048 |     1971 |
|       1053 |        1 |
|       1139 |       35 |
|       1267 |    19722 |
|       1270 |     4243 |
|       1271 |     8944 |
|       1416 |     2284 |
|       1580 |    23225 |
|       2003 |       28 |
|       2013 |     1606 |
+------------+----------+
11 rows in set (0.00 sec)

error 2013 means lost connection to server (read: server crashed).
so there are many bugs found already. 1606 crashes out of 1.6 million
executed queries, is great.

check my rss feed for the exact bugs ...

sql rainbow tables

I thought of a brilliant QA plan. Create sql rainbow tables! Just trust me, this system of QA will be very powerful in bug finding. Especially important will be the comparison of old/new versions of mysql. Writing a prototype now, on my day off.

Just during prototype development, I've discovered a handful of bugs. I'm testing all functions that are documented in the manual. This is alot that I've written up:

mysql> select count(*),category from func group by category;
+----------+--------------+
| count(*) | category     |
+----------+--------------+
|        6 | arithmetic   |
|        7 | bit          |
|       60 | casting      |
|       47 | comparison   |
|       52 | datetime     |
|       18 | encryption   |
|      127 | geometry     |
|       17 | information  |
|        7 | logical      |
|       32 | mathematical |
|        9 | misc         |
|       47 | string       |
|        2 | xml          |
+----------+--------------+
13 rows in set (0.00 sec)

Let me explain this rainbow tables concept with a simple example.
Suppose we have a function, like "GREATEST()".

I run a query like this:

SELECT GREATEST(col1,col2,col3) FROM table0;

The columns col1,col2,col3 will form any combination of column type. So we have int, date, string, blob, spatial! Given a combination of all datatypes and all function parameters, it leaves us with a good few million queries. Of course, a few million queries is not so much for a machine to execute quickly..

new direction for test development

I've been thinking about this today. My attempts thus far to write random queries which stress various parts of the server have worked, and served a purpose - but have been rather huge and clumsy.

For example a 5 union select, each comprising of 7 joins did have it's value.. The problem with huge random queries is simply that the results cannot be verified as easily as they were constructed (unless you run the same on various DBMS). These huge tests are mostly only good for checking if the server crashes.

Now, I wish to try write testcases that have these properties:

• small
• results are self-verified
• each test is random, but reproducible given a seed value
  

Tonight I'll throw together a code-generator to do simple insert/select and post any interesting results here later.