-
Notifications
You must be signed in to change notification settings - Fork 20
RandomQueryGeneratorSimplification
Since the RQG generates random queries, it has the potential to find crashes and other issues when the query is very large. Simplifying the query and the test case in general to the smallest possible form is required in order to determine the true impact of the bug and facilitate debugging.
If you are using the RQG to test the Optimizer by comparing two different server versions, or a storage engine by comparing two different storage engines, a facility is provided that will attempt to reduce the offending query to the smallest possible form and provide a mysqltest test case for the issue. To enable it, use the ResultsetComparatorSimplify Validator.
If the test detects an issue, the query will be simplifed and printed along with the diff between the servers for the simplified query. In addition, a mysqltest test case will be placed in /tmp that contains the CREATE, INSERT, SELECT sequence that is required to reproduce the bug with the smallest number of tables and columns per table.
The query simplification works by removing parts of the query until the servers start to agree. It does not do the following:
- Remove non-integer fields from the query. Many bugs are reproducible with more basic data types, such as integers and strings. Using datetime and enums may distract from the root issue and the overall impact of the bug. To achieve this, use only integers in your grammar and data definition files before you move to more complex field types.
- Replace impossible SQL with a realistic one. If your grammar creates SQL that a human would never create, such as 5 BETWEEN '00:00:00' AND '2009-09-09', the bug may become more understandable and be considered more important if it was using only realistic comparisons and integers.
- Rewrite the query by changing the fields and tables that are used. Sometimes, the test case can be made simpler by changing the tables and the fields that participate. Changing a SELECT field to a constant value may allow a table to be entirely removed from the test case;
- Remove indexes or partitioning from the table or replace merge tables by simple base tables. For many bugs, those are frequently not needed and you can remove them by editing your .ZZ file;
- Remove unnecessary table rows. The Simplifier will remove unnecessary table columns, but will dump tables in their entirety. To make your test cases as small as possible, either use only the smaller predefined tables, or edit your .ZZ file to include smaller tables. Another option is to pass the generated test case through the mysqltest simplifier that will remove the individual INSERT statements that create the unnecessary rows;
- Simplify queries that require edits in two places simultaneously in order to convert from one, more complex, valid query into another, simpler, valid query. For example INSERT (...) VALUES (...) will not be simplified further because the column and the value lists need to be shortened at the same time, which the existing algorithm does not allow;
The RQG provides a general mechanism for query simplification, the GenTest::Simplifier::SQL module. You can define your own "oracle" function that returns 0 if the bug is no longer repeatable and 1 if the bug is still repeatable. The Simplifier will call this function repeatedly during the simplification process.
If the bug is sporadic, e.g. the query only fails every now and then, you can still apply simplification by creating an oracle function that will run the query 10000 times and report 1 (bug still repeatable) only if some of those 10000 runs have failed. Here is an example that will simplify a query that returns a different number of rows from time to time:
$| = 1;
my $query = "SELECT 1 FROM DUAL";
my $dsn = 'dbi:mysql:host=127.0.0.1:port=19306:user=root:database=test';
my $dbh = DBI->connect($dsn);
my $simplifier = GenTest::Simplifier::SQL->new(
query => $query,
oracle => sub {
my $query = shift;
my %outcomes;
foreach my $trial (1..5000) {
my $sth = $dbh->prepare($query);
$sth->execute();
return 0 if $sth->err() > 0;
$outcomes{$sth->rows()}++;
return 1 if scalar(keys %outcomes) > 1;
}
return 0;
}
);
my $simplified = $simplifier->simplify();
print "Simplified query: $simplified\n";
In this example, we define an oracle function that reruns each query 5000 times and records the number of rows returned from each invocation. If the rows returned ever diverge, we return 1 back to the Simplfier. We return 0 if the query was syntactically invalid (which happens frequently during the simplification) or if all 5000 runs agree on the correct number of rows to return (meaning that the simplifier has cut too much from our query).
The GenTest::Simplifier::SQL can be used to simplify crashing queries as well (but not tables or data). The only special consideration is to restart the server after each crash. The util/simplify-crashes.pl script does that.
In order to avoid fully evaluating any intermediate queries that happen to be correct and long-running, the simplifier attempts to kill a query after it has been running for 5-10 seconds. The assumption is that in the general case, any crashes in the Optimizer will happen immediately, during the optimization phase, rather than during the retrieval/sorting phase. You can also put --mysqld=--max-join-size=N in util/simplify-crashes.pl so that N is the smallest value that lets the original crashing query still pass and crash the server.
Manually editing the original query and replacing any references to large tables with references to smaller tables can also be considered. Chances are that the crash will still happen, however it will take less time to simplify the query and the final simplified test case will be smaller.
Since the simplifier repeatedly crashes the server, it uses ulimit -c 1 to limit the size of the core files being produced (and uses ulimit -c 0 at the end of the test to restore the "default"). Also, you may observe that a crash reporting facility, such as abrt gets involved which slows the simplification process considerably. The dirtiest way to disable abrt is to uninstall it altogether using sudo yum remove abrt.
Simplification of grammar files is available using the Gentest::Simplifier::Grammar module. Again, you need to specify an oracle() procedure that will guide the simplification effort.
A ready-made script that simplifies grammars that result in a bad effect, is available from util/simplify-grammar.pl. Please
- copy the file simplify-grammar_template.cfg to for example 1.cfg which serves a a configuration file for the simplifier
- adjust the settings in 1.cfg so that they fit to your usage case and environment
- run perl util/simplify-grammar.pl --config=1.cfg
It is possible to use mask and mask-level inside the config file that you pass to util/simplify-grammar.pl. Masking will be applied before the simplification process starts, since it would not make sense to pass those options to each intermediate RQG run while the grammar is being changed.
Alternatively, you can use the util/mask-grammar.pl script to mask the grammar manually before you pass it to util/simplify-grammar.pl.
The util/simplify-mysqltest.pl scripts implements simplification of mysqltest cases (including ones produced by RQG) using the algorithm described in "Simplifying and Isolating Failure-Inducing Input (2002)" by Andreas Zeller, Ralf Hildebrandt.
Please use a configuration file and/or provide a set of valid command-line options to configure it for the specific failure that is being sought. A sample configuration file iation file for the simplifier
- adjust the settings in 1.cfg so that they fit to your usage case and environment
- run perl util/simplify-grammar.pl --config=1.cfg
It is possible to use mask and mask-level inside the config file that you pass to util/simplify-grammar.pl. Masking will be applied before the simplification process starts, since it would not make sense to pass those options to each intermediate RQG run while the grammar is being changed.
Alternatively, you can use the util/mask-grammar.pl script to mask the grammar manually before you pass it to util/simplify-grammar.pl.
The util/simplify-mysqltest.pl scripts implements simplification of mysqltest cases (including ones produced by RQG) using the algorithm described in "Simplifying and Isolating Failure-Inducing Input (2002)" by Andreas Zeller, Ralf Hildebrandt.
Please use a configuration file and/or provide a set of valid command-line options to configure it for the specific failure that is being sought. A sample configuration file is provided as simplify-mysqltest_template.cfg in the top level of the Random Query Generator directory structure.
The script also accepts CSV log files as produced by the --log-output=file mysqld option.
The script takes a single string (specified via the variable expected_mtr_output), which, if present in the MTR output, would mean that the test failed. To produce a repeatable failure with a specific string, use the --die mysqltest construct inside your .test file.
It is also possible to determine success/failure based on a comparison of mysql-test result files from two different servers (this was added 2010-01-27). To do this, specify two servers (basedir and basedir2) in your configuration file, or on the command-line. In this mode, the simplifier will ignore expected_mtr_output. The MTR option record is required for this to work as intended.
Simplification is performed on a per-line basis. Individual queries are not simplified and multi-query lines will not be broken down.
It is possible to reduce the data from a large-scale database to the smallest ammount required to reproduce the bug by using the mysqltest simplifier against the mysqldump output. This may work at mysqldump files of up to 1Gb, involving up to 100K individual lines, but may require up to 10Gb of free space and over 4Gb of memory.
First, dump the participating data with the following mysqldump<tt> command line. It ensures that the data is set up for faster loading using multi-line <tt>INSERT, but at the same time, individual INSERTs are not overly large
mysqldump \ --skip-comments \ --lock-all-tables \ --add-locks \ --disable-keys \ --extended-insert \ --quick \ --max_allowed_packet=5K \ --net_buffer_length=5K
Next, use the simplify-mysqltest_template-huge.cfg as a starting point when configuring the simplification run. Put the queries that demonstrate that the bug is present into the footer section.
To break-down multi-line CREATE TABLE, INSERT and CREATE PROCEDURE statement, you can pass your test case through the following sed command line. Presuming the processed test case still fails, it will have each CREATE TABLE field definition, INSERT row data and CREATE PROCEDURE statement on a separate line, allowing for them to be simplified independently.
sed 's/\([,;]\)/\1\n/g' packed-test-case.test > broken-down-test-case.test
To break down the individual CREATE TABLE , INSERT , SELECT queries that remain after processing a CSV log file, you can use the following perl code to beak individual queries into smaller fragments, that can then be fed back into the simplify-mysqltest.pl script.
perl -i.bak -p -e 's{(,|\.|\(|\)|[A-Z] )}{\1\n}sgo' /tmp/opt.test
perl -i.bak -p -e 's{(;|,|\)| [A-Z])}{\n\1}sgo' /tmp/opt.test
Essentially, we split at various punctuation marks, as well as before and after a capital letter, meaning that SQL constructs that appear in capital, such as DISTINCT will be put on their own separate line, so that the MTR simplifier can optimize them away.
Note however that agressive splitting will result in a longer test case that may take forever to simplify. Alternatively, you may paste the original test case in Microsoft Word and then apply line breaks manually. Usually, splitting the individual rows from a multi-line INSERT statement and splitting the crashing query itself would be sufficient.
TODO: For test cases where --threads is greater than 1, simplification can be achieved by using a smaller value for --threads and by progressively reducing the grammar.
The RQG can be used to narrow down the simplest query pattern that will trigger a desired EXPLAIN output. Note that this will not provide a grammar in which 100% of the statements trigger the particular optimization, nor the grammar that fully describes all cases where the optimization will be triggered.
1. Edit $match_string in lib/GenTest/Validator/ExplainMatch.pm to point to the optimization in question. This validator causes the test to terminate with a distinct status code if this EXPLAIN output is encountered;
2. Edit util/simplify-grammar.pl and specify --validator=ExplainMatch and $desired_status_code to STATUS_CUSTOM_OUTCOME.
3. Run the simplified grammar with --validator=ExplainMatch to see the first query that triggers the desired optimization.
If you want to target a particular error message, please edit the lib/GenTest/Executor/MySQL.pm , define the message there and set it to cause the test to fail, e.g. label it as STATUS_CUSTOM_OUTCOME. This way the test will abort immediately once the error is seen and you can target the error in your simplification process.
Another way is to run util/simplify-grammar.pl with an appropriate configuration file.
Sometimes a particular bug will not express itself as a crash or as a client-side error that you can trap. An example would be a mysqld message that only appears in the error log.
In order to target such errors, convert them into assertions by adding DBUG_ASSERT(0) at the location where the error message is generated. Then, you can adjust util/simplify-grammar.pl to target STATUS_SERVER_CRASHED situations.
We are using util/simplify-grammar.pl and have to adjust the configuration file so that it fits to our use case.
The order of checks performed by the oracle function within util/simplify-grammar.pl is:
- 1. Did our current RQG run (call of runall.pl) end with a status code in desired_status_codes ?
- 2. If 1. is fulfilled, did our current RQG run end with a log containing our search patterns in expected_output ?
- 3. If 2. is fulfilled than the last simplification attempt had success.
Example 1:
I got a server crash and the output of RQG contains a
backtrace with
#6 0x00007fbe65a87f09 in __assert_fail () from /lib/libc.so.6
#7 0x000000000076107d in lock_tables (thd=0x2263e08, .....) at sql_base.cc:5049
#8 0x00000000007d6d85 in mysql_update (thd=0x2263e08, .....) at sql_update.cc:226
#9 0x000000000070ccca in mysql_execute_command (thd=0x2263e08) at sql_parse.cc:3118
The search pattern needs to have a high selectivity in order to avoid not intended results of a simplification. Please be aware that
- - line numbers and addresses (good for non debug server crashes) might change if a source file gets modified
- - "non guilty" threads might also show within their backtrace that they are in the same routine (example: "mysql_update") like the "guilty" thread
expected_output => [
'__assert_fail.{1,150}lock_tables.{1,150}mysql_update'
],
The desired status code should be also assigned in order to avoid not intended results of simplification.
desired_status_codes => [STATUS_SERVER_CRASHED]
Example 2:
I have a grammar which reveals various bugs and I do not care
for which of these bugs I get the first simplified grammar.
desired_status_codes => [+STATUS_ANY_ERROR],
or something like
desired_status_codes = [ STATUS_SERVER_DEADLOCKED , STATUS_SERVER_CRASHED ],
expected_output => [
' '
],
Some general recommendation:
- There are situation where it is rather ineffective to be too much focused on some specific bug.
- Please imagine you have a huge grammar and the following situation:
- * 50 % of all RQG runs end with server crash
- * 10 % of all server crashes show a from whatever reason especially interesting backtrace.
- In case you focus on this specific crash
desired_status_codes => [+STATUS_SERVER_CRASHED],
expected_output expected_output => [ '<pattern derived from backtrace>' ]
- than you will have at simplification begin
- * a "poor" likelihood to hit exact this bad effect of 10%
- --> Significant risk that even the initial oracle check fails except you go with many trials.
- * waste a lot runtime and other resources by running into the other crashes
- because the simplifier cannot use this result as "progress"
- In case you simply focus on the crash and nothing in addition
desired_status_codes => [+STATUS_SERVER_CRASHED],
expected_output expected_output => [ ' ' ]
- than you will have at simplification begin
- * a likelihood to hit a crash of 50%
- --> better chance to have success in the initial oracle check
- * reach some simplified grammar much earlier
- Than you can
- 1. Report this bug
- 2. Disable the part of the grammar which seems to be the reason for the bug reported
- 3. Run simplification again
- You will get the next simplified grammar for the next bug much earlier.
A good starting point are the values used within the RQG run where the bad effect showed up. The seed value of this run has to be assigned to "initial_seed". Nevertheless some modifications can have some impact on the speed of the grammar simplification.
- My experiences are based on development and excessive use of the conf/runtime/WL5004_sql.yy grammar.
- This grammar is dedicated to check concurrent DDL, DML and transactions on the same objects like tables, views etc.
- Other grammars might show different properties.
- initial_seed
- Please assign here the seed value used in the RQG run where the bad effect showed up.
- simplify-grammar.pl uses initial_seed for the computation of the final seed value which gets assigned to runall.pl. The grammar simplifier also varies the seed value if it makes sense.
- The sensitivity to seed values decreases with increasing number of threads. But it is on the first view a bit surprising that seed values can have some significant impact even if the we already have around six concurrent threads.
- threads
- Observations in case of average concurrency bugs
- threads=2 -- very low likelihood to hit the problem at all
- threads between 3 and 12 -- likelihood per overall runtime goes rapid up
- threads=12 till 32 (depends on power of hardware) -- optimal setup with good likelihood per overall runtime
- threads>32 -- likelihood per overall runtime goes down
- Bigger numbers of threads usually cause longer overall runtimes because of
- - the corresponding higher resource consumption might reach limits of the hardware (CPU,I/O,RAM)
- - deadlock detection and resolution will last longer because queues involving more sessions are possible
- - the same is valid for conflict resolution by server timeouts
- even tiny modifications of the grammar