MySQL Forge

Resources for the MySQL Community

Category: RandomQueryGenerator

RandomQueryGeneratorFiltering

Contents 1 Filtering queries 2 The .ff file 3 Applying filtering 4 Forcing MySQL to be ANSI-compatible

[edit] Filtering queries

Sometimes it is necessary to filter out certain queries from the sequence of generated queries before they are even executed. For example, a known crashing bug early in the test would prevent further bugs from being discovered down the line.

[edit] The .ff file

Which queries to be filtered out is controlled by a .ff file that contains a list of individual rules to be applied. Each rule has a name and can be either a regular expression, a subroutine, or a literal string. For example

$rules = {
        'test_regexp'   => qr{update}si,
        'test_string'   => 'DELETE FROM `DD` WHERE `time_nokey` < 0 LIMIT 4',
        'test_sub'      => sub { m{insert}sio }
};

The first rule is a regular expression which will be applied over each query individually. Note that SQL elements such as ( and . have special meaning inside regular expressions, so must be escaped with a \

The second rule is a string literal, which must match completely and exactly for the query to be filtered. Use a regexp if you want more flexible or partial matches.

The final rule is a subroutine, which is called with $_ and $_[0] set to the query in question. If the routine returns true, the query will be skipped.

[edit] Applying filtering

Filtering can be applied by passing the name of the .ff file to gentest.pl or runall.pl with the --filter parameter. Filtering is not applied during the data generation phase, e.g. to gendata.pl.

For example, this command-line:

$ perl runall.pl \
  --basedir=/build/bzr/azalea-bugfixing/\
  --grammar=conf/example.yy \
  --threads=1 \
  --queries=10 \
  --filter=conf/example.ff

produces the following output:

# 13:33:45 Loaded 3 filtering rules from 'conf/example.ff'
...
# 13:33:45 Query: INSERT INTO `C` ( `datetime_key` ) VALUES ( 3 ) filtered out by code rule test_sub
...
# 13:33:45 Query: DELETE FROM `DD` WHERE `time_nokey` < 0 LIMIT 4 filtered out by literal rule test_string.
...
# 13:33:45 Query: INSERT INTO `E` ( `datetime_key` ) VALUES ( 4 ) filtered out by code rule test_sub

[edit] Forcing MySQL to be ANSI-compatible

The Filtering mechanism can be used to remove certain non-ANSI queries from the query stream so that the queries can be executed by non-MySQL databases. For example:

'limit'   => qr{limit}si

would remove all queries containing LIMIT which is not a standard construct.