MySQL Forge

Resources for the MySQL Community

Category: RandomQueryGenerator

RandomQueryGeneratorToDo

The following items are known to be deficient or missing from the Random Query Generator. Ideas or patches on how to solve those are very welcome!

As you put items on this page, please put a small note as to why the feature would be nice to have, e.g. how it is going to increase productivity, prevent mistakes or allow new stuff to be tested.

Please note that this page resides on forge.mysql.com, and is therefore public.

You may use the Random Query Generator team's Launchpad Blueprints to track the design and implementation of specific functionality. Specifications may be added to or listed at the following wiki page:

RandomQueryGeneratorSpecifications

Contents 1 The Framework 2 The Validators 3 The Grammar 4 The Executor 5 The Data Generator 6 Unsorted stuff by Matthias Leich 7 Simplification 8 Combinations 9 Reporters 10 Other generators 11 Replication

[edit] The Framework

• Even though most of the work is done by Perl Objects, the framework is still run by a collection of Perl scripts that call each other in a nested fashion. This causes issues with passing the success/failure status, process management and starting and stopping the MySQL server.

• The RQG is dependent on MTR for starting the server. There have been numerous regressions in MTRv1 over the past year.

• The Validators and Executors are not specified with individual options, such as --validator=Validator1 --validator=Validator2

• Query simplification seems to remove columns used in the partition function which causes errors. It seems best to declare a column 'off-limits' for removal if it is in the partition function

• We need to allow the use of '--engine=<engine>' in 3 way DB comparisons. This clause currently works with PostgreSQL, but seems to wreak havoc on Derby

[edit] The Validators

• The ErrorMessageCorruption Validator attempts to detect memory corruption by looking for non-alphanumeric characters inside error messages. This is generally useful, however may produce false alarms on tests that use character sets and non-ascii data.

[edit] The Grammar

• No lexer. The grammar file is parsed using regular expressions. This causes numerous issues where constructs that appear to be valid would not be parsed correctly.

• Inconsistent quoting. The generator does not quote _digit and _letter, however it automatically quotes _varchar(1). This may cause issues when you want to do CREATE TABLE `_varchar(2)` or anything else along those lines.

• Restrictions on the use of Perl code. Due to not lacking a lexer, a several f constructs are currently not allowed inside Perl code snippets - comments, the pipe character.

• Alter util/simplify_grammar.pl to allow us to define certain optimizations as a success point. This will allow us to reduce a grammar to produce only queries that will hit a certain optimization.

• With tables of different structure, the following may produce an invalid query:

SELECT _field FROM _table ;

Because the field is chosen before the table. Possible solutions are to only pick a table that contains the field already selected, or to pick the table first even though it appears later in the statement.

• It is not possible to pick a field that is provably present in two tables just selected, such as a field that will be useful for the USING () clause of a join.

• It is difficult to mix CREATE/DROP with DML statements. Any proportion and table naming causes 33% to 50% of "Table does not exist" errors.

[edit] The Executor

• Memory leaks. The DBD::mysql module appears to leak memory. Maybe a problem in fetchall_arrayref(), threfore this call is not used inside the RQG.

• The query result is loaded in memory at once. If you issue a SELECT that returns a huge result set, this result set will be converted into a Perl data structure, resulting in numerous data copies, with each representation bigger then the previous (mysql -> mysql C library -> DBD::mysql -> RQG).

One solution would be to use mysql_use_result() discard the result altogether if no Validators are defined to process this query. On the other hand, we could allow a Validator to process the result set one row at a time and still use mysql_use_result(). However, this would cause affected_rows to become unreliable. In addition, the MySQL server may be forced to hold locks for longer, disrupting the concurrency of the test.

[edit] The Data Generator

• The Data Generator is still a form-less Perl script. It needs to be abstracted into a separate Object with a next() method that generates the next data item

• Perl code can not be used to provide the Data Generator with data values, unlike the Grammar.

• Allow for composite keys (either via column specifications or as shown below)

• • Allow specifying key columns by name pk => [ (int_signed, varchar_5, datetime), (date, varchar_10) ]

[edit] Unsorted stuff by Matthias Leich

I am new with RQG. So I cannot exclude that there are already existing or simple solutions for the issues I found. And thank you for this great tool. Aside of its great capabilities around testing, at least I have a lot fun when using RQG. Feel free to remove any of my entries if they are outdated.

• implemented Jul20 Duplicate entries within test grammar files ( conf/*.yy ) like

 table_name:
    ABC ;
 ...
 table_name:
    DEF ;

cause bad effects ( the last assignment wins all time ).

A check within RQG which catches such duplicates would be very helpful.

• implemented Jul20 Predefined constructs ( like _table ) which I missed when working on very similar (amount and names of columns) tables:

• _field_list - comma separated random list containing the names of all existing columns

Example of _field_list content and usage:

 CREATE TABLE def SELECT _field_list FROM abc

becomes finally

 CREATE TABLE def SELECT f3,f1,f2 FROM abc;

• _field_count - number of existing columns

Example of _field_count and usage:

 SELECT * FROM abc ORDER by ( _field_count - 1 )

• Statement parts like

_field <comparison operator> <_digit or _year or ...>

see warnings about data truncation or fail in strict SQL_MODE quite often because the static assigned data value does not fit to the data type of the random picked column. It would be nice but not extreme important to have a solution like for example

_field > $_field_value

where $_field_value provides data of the correct type.

• A piece of the grammar like

LIKE '%int%'

gets expanded to something like

LIKE '%146079744%'

though there is no '_' before the 'int'.

Workaround (by Philip): Use

LIKE '%INT%'

instead.

Use case: SHOW FULL COLUMNS FROM t1_161 LIKE ....

• '_field' gets not expanded (most probably because of the inconsistent quoting mentioned above).
• A piece of a string which occurs quite often in column names is "int". Therefore I tried this.

• It might be useful to have strict separated name spaces for the RQG builtin variables (Example: _field), user defined grammar items and hardcoded strings.

This separation could be done by requiring that user defined grammar items must follow some naming convention (Ugly looking example: "__update" where the starting "__" are the required stuff).

If the RQG would check that the user defined grammar follows this naming convention than probably a lot debugging in grammars with unexpected behavior could be avoided.

Example:
table:
  table_name | table_name | (SELECT * FROM table_name) ;
table_name:
  ...
... <many lines later> ...
pick_table_name1:
  # Fill $table_name1
  SET @aux = 'Merge table will use base_table_name_sl '; <more statements>
Outcome: Some statements were missed at all, grammar items had no value etc. 

• It might be useful if RQG would give a warning in case he meets a grammar item <> 'query' which is nowhere used. This grammar item occupies a name of the name space and might be a time bomb.

Example: see last example

• Finding bugs in grammars is partially very costly.

We should have a grammar syntax checker.

https://blueprints.launchpad.net/randgen/+spec/yy-grammar-syntax-checker

• In case of mysqltest and especially grammar simplification an expected output pattern needs to be assigned. This pattern is usually derived from some backtrace.

Finding the right pattern is quite error prone.

The assigned pattern might be either plain wrong (example:typo) or too restrictive (example: distance between two expected words fluctuates).

Bad effect: After some hours with simplifier runs failing on first oracle check it turns out that the pattern was wrong.

It would be nice to have a tool which checks if some expected output pattern given on command line or per config file matches the content of given file.

https://blueprints.launchpad.net/randgen/+spec/expected-output-checker

• Just some raw ideas for automatic grammar simplification:

• The current grammar simplifier has only one strategy which goes deeper (into some element of a rule) as soon as his attempt to remove some element failed.

This has the following disadvantages

• The simplifier goes very early into simplification of details whereas more frequent used or top level rules could be simplified too. There is the hypothesis that giving the removal of such frequent used/top-level rules prioritity leads to a speedup of simplification process.
• In case the simplification process gets interruptet the next simplification process will focus first on again on the the same grammar rules where the first simplification was not successful.

• Therefore we should have at least one alternative simplification strategy.

https://blueprints.launchpad.net/randgen/+spec/alternative-grammar-simplification-strategy

• Any improvement is welcome though I am aware that the structure of a specific grammar has a high impact how fast a grammar simplification could be. So the grammar author will be all time to some significant extend responsible for the speed of possible grammar simplification.

• Raw idea how to find new crash/assert bugs even if the current grammar suffers frequent from known server bugs which cause test aborts:

Have a negative list with string patterns which occur within the backtrace in case some known bug is met. This negative list is stored within a file to be processed by the RQG simplifier.

 Example:
 Item_func::fix_fields # Bug....
 Diagnostics_area::set_ok_status # Bug....

The RQG simplifier could check if any string from the negative list occurs within the backtrace. Reasons for backtrace existence:

• server crash
• assert
• RQG simplifier detected deadlock, hanging server etc. + killed the server process

If any of the negative list strings is found than the simplification attempt is valuated as not successful.

Greedy hunt mode 1: Check the grammar with seed 1 to n (n is configurable).

Greedy hunt mode 2: Check the grammar with seed n (n is configurable).

Both hunt modes stop if

• a backtrace "<>" negative list is found and positive list is empty.
• a backtrace "<>" negative list is found but "=" positive list is found.

My first prototype (I am aware that shell + GNU tools have portability problems and that it could be coded more elegant) for proof of concept:

PROT=/dev/shm/prt1
SEED=1
RUN=1
while [ $RUN -eq 1 ]
do
   perl runall.pl --mem \
   --basedir=/work2/6.0/mysql-next-bugfixing --threads=1 --queries=10000 \
   --reporter=Deadlock,Backtrace --mysqld=--table-lock-wait-timeout=1 \
   --mysqld=--innodb-lock-wait-timeout=1 --grammar=conf/bugnnnn-experimental.yy --gendata=conf/WL5004_data.zz --seed=$SEED | tee $PROT.$SEED 2>&1
   grep -n 'exit status 0' $PROT.$SEED
   RC=$?
   if [ $RC -eq 1 ]
   then
      bug47249='MDL_global_lock\:\:is_lock_type_compatible'
      bug47150='Field_long\:\:val_int'
      bug46615='Query_cache\:\:invalidate'
      # bug46958 has a pattern like bug46425
      bug46425='Diagnostics_area\:\:set_ok_status'
      CNT=`egrep \
      -e "$bug47249" \
      -e "$bug47150" \
      -e "$bug46615" \
      -e "$bug46425" \
      $PROT.$SEED | wc -l`
      if [ $CNT -eq 0 ]
      then
         RUN=0
      fi
   fi
   rm $PROT.$SEED
   SEED=`expr $SEED + 1`
done

performed well and I found several new bugs. It must be mentioned that my huge grammar ( ~ 1500 lines, 67 KiB ) fits very good to this script. My grammar is

• very sensitive to changes of seed
• (a) runs with a high likelihood into a known bug
• (b) checking many features

(a) and (b) are the most problematic properties. If I disable the checking of some features because I want to avoid (a), than (b) is no more valid. But than

I also get a rapid decrease of the chance that my grammar catches any unknown bug.

• It would be nice if RQG including all tools like the simplifier would use

• the environment variable MTR_BUILD_THREAD for the computation of

• vardirs -- where the tables, server logs etc. are stored
• ports

This would make parallel runs of RQG easier.

• MTR version 2 for starting the server etc.

• There are new Replication test scenarios coming up where I am in the moment unsure if we can run them via RQG.

1. Execute a statement mix on the master server.
2. Cause that the slave server crashes
3. Restart the slave server and let him recover
4. Bring master and slave server into a state so that it can be checked if both are in sync

• Extend the yy grammar so that the user to be connected can be assigned.

[edit] Simplification

• done It would be nice if we could alter lib/gentest/simplifier/Test.pm to have the mysqldump port

be passed as an option from the simplification script.

• done There are times when I have chosen to use an alternate port (19500 for example) for a simplification run while I do other RQG work using the standard ports (19306/19308). However, as the mysqldump port is hardcoded, we cannot get the simplified tables without directly altering Test.pm with the chosen port prior to kicking off the crash-simplfier

• The query simplifier is uncapable of properly reducing constructs such as X OR Y OR Z since both the OR and the Y must be removed together, but they are at different levels and locations within the parse tree

• The query simplifier removes AS which must remain for the query to be compatible with PostgreSQL

• The simplifier reduces ON (X = Y) to ON (X), which is not a boolean, but an integer expression and is thus not allowed in JavaDB, PostgreSQL

• The simplifier is unable to reduce (f1 ,f1 ) IN (SELECT a ,b ) even if a multi-part IN is not required to reproduce the bug, since it requires removing items from both sides of the IN at the same time, in order to arrive at a valid query. At the same time, JavaDB does not support multi-part IN.

• In a rule such as query: select | select | select | select | select | select | update , the grammar simplifier will take out each select individually, rather than all of them together, which causes a major increase in the number of test runs required to completely remove an unneeded element.

• The mysqltest simplifier operates on individual test lines, and not on individual queries and query parts. Running the original algorithm but on a character level should allow further simplification.

• The mysqltest simplifier is oblivious to the fact that the top of the test case is important and it is unlikely that the issue will be reproduced if the top is removed. Same applies for the last few lines of the test.

• If the mysqltest contains a lot of connection/connect statements, the simplifier will slice and dice the test case in a way that will frequently produce a "connection X not found in connection pool" errors from MTR. To avoid that, simplification must happen in a way that entire connections are removed together with all the queries that are issued by them.

• If simplifying a replication test from a CSV, the "--source include/master-slave.inc" and "--sync_slave_with_master" lines will not be added automatically.

• In the final stages, the mysqltest simplification algorithm degenerates so that it tries to remove the same one or two lines from the test over and over again. It appears that the runtime degenerates to almost quadratic, slower than a human would be.

• The speed of the simplification is limited by the time it takes to complete a successful test and by the time it takes to detect a deadlock (if one is present during the run). Determining the maximum duration of the test or the deadlock timeout is currently made manually, which may cause sub-optimal values to be chosen. Furthermore, a different value is required at the start of the simplification and at the end -- towards the end, test failure happens faster and therefore the original duration/timeout may slow things unnecessary.

• Grammar simplification is implemented depth-first. If it is possible to remove most of the grammar, this works just fine, however if a particular deeply-nested grammar component is required to reproduce a bug, the simplifier will spend a lot of time trying to find it. It would be more efficient to try to remove as much general stuff as possible (e.g. all DDL statements) before drilling down into the specifics.

• If there is a non-optional subrule, the same grammar element may be visited once. First, the higher-level element is removed and then the lower-level one, even though if there are no variants within the rule, those two things would cause the same outcome.

• There is no way to simplify the non-grammar things that influence the test, e.g. RQG of mysqld command-line arguments, the set of available storage engines, databases, etc.

• If a certain rule has several components, only one of which is required to reproduce the failure, the simpifier will take a while to arrive to the simplified arrangement. Imagine a grammar query: A | B | C | D | E where the issue only required B to reproduce. The existing Simplifier will try to remove each of A..E until it is determined that B is the required component, for a total of five trials. An alternative approach would be to try only A..E independently and stop as soon as the problem is reproducible. This way, the search can stop as soon as it is found that B is sufficient to reproduce the issue, so only two trials will be run.

• Valgrind failures can not be used as target for simplification, since they are silently placed in the error log and neither the RQG, nor MTR are looking for them automatically. A workaround would be to grep the error log after each test run, but this will not cause early test run aborts as soon as a warning is generated.

• If a given grammar rule has only a single component, the Grammar Simplifier will first attempt to remove the entire rule, and then attempt to remove the individual component. This causes duplicate work, and in particular violates the idea that if there is a single component and no alternatives, this component must be mandatory. The only good side-effect of this is that, if the failure is sporadic, it may be a good idea to check things twice.

• The intermediate grammars that are produced during the simplification process contain all grammar rules, including rules that are not longer reachable from "query" and should have been removed. This means that it is difficult to tell how far simplification has progressed by looking at an intermediate grammar -- one needs to follow the grammar starting from the "query" rule in order to determine the active rules. The final grammar that is dumped after simplification is already properly trimmed down, so the solution would be to take the trimming code from where it is now and paste it elsewhere so that it is run on each step of the simplification process.

• --mem can not be used with grammar simplification, because the simplify-grammar.pl tries to also use --vardir and both are not allowed together.

[edit] Combinations

Currently the combinations facility only acts on the top-level query element. If you have lower-level rules such as "ddl" and "dml", the Combinations facility will not open them up and treat their items as individual query types to be enabled/disabled. A solution would be to take the N top-most levels of the query tree and generate combinations using all of them, not just the "query".

[edit] Reporters

A DynamicBinlogFormat Reporter can be used to periodically change the binlog format for a running test. Combined with --rpl_mode, this allows tests to exercise all replication modes without having to have the SET BINLOG_FORMAT coded in the grammar.

A DynamicStorageEngine Reporter can be used to periodically change the default storage engine, so that grammars contain CREATE TABLE will pick the new engine without having to have ENGINE = innodb|myisam|memory hard-coded in the grammar. This would allow the test to only use the engines that are currently available on the server. At the same time ALTER-ing from one engine into another will still require a specific construct in the grammar.

[edit] Other generators

In addition to GenTest::Generator::FromGrammar (which is the actual RQG), other generators could be handy. A GenTest::Generator::FromFile which takes queries from a file sequentially could be useful if the RQG generates an interesting queries after several hours (or days) which one would want to re-run in the framework.

One could also envision alternative random query generators based on external powerful tools, like DGL (http://cs.baylor.edu/~maurer/dgl.php).

[edit] Replication

MTR --start-and-exit does not work if a replication slave from a previous run is still present. On the other hand, using the Shutdown Reporter, while it does cause both the master and the slave to shut down, causes the final slave sync and dump in runall.pl to fail, this being reported with an error code of 255.