Friday, June 19, 2009

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

No comments: