-
Notifications
You must be signed in to change notification settings - Fork 20
RandomQueryGeneratorComparison
It is possible to use the Random Query Generator to run comparison tests against two servers, products or configurations. The queries that are generated by the grammar will be executed against both servers and any differences in the returned result sets or error message codes will be reported.
At the end of the test, the two servers will be dumped and the contents of the dumps will be compared.
Unless your workload is entirely SELECTs, you should only use single-threaded tests with --threads=1. Multithreaded tests are likely to suffer from subtle timing issues that will cause false positives. This will likely be the case even if all your non-SELECT queries are executed by a single thread or otherwise synchronized.
By default, the ResultsetComparator Validator will be automatically used in comparison tests. This Validator reports any differences between the servers. It also terminates the test if the servers may have diverged unacceptably, that is, if an UPDATE statement has returned a different value for affected_rows. If the differences are only seen for SELECT statements, the test will be allowed to continue in order to gather more material for further examination.
If you manually specify the ResultsetComparatorSimplify Validator, the RQG will attempt to simplify each reported case of difference between the two servers and produce an MTR mysqltest .test case. Simplification however causes extra queries to be run against the servers, which may cause side effects for the main test.
If you rely on the RQG to start up your servers by calling runall.pl, a comparison test will be performed if you specify both a --basedir1 and a --basedir2 option, or both a --vardir1 and a --vardir2 option.
If you run the RQG against servers that are already running via the gentest.pl script, a comparison test will be triggered if you specify both --dsn1 and --dsn2.
The RQG provides various ways to compare two or more servers or database products.
You can execute the same grammar against two different binaries by specifying two --basedirN options:
--basedir1=/path/to/server1 \ --basedir2=/path/to/server2
If there are no two servers or products capable of running the desired queries for a classical two-way comparison, the Transformer Validator can be used to rewrite the queries into various equivalent forms and execute them against the same server for comparison purposes. For more information, see RandomQueryGeneratorTransforms.
You can compare two servers running with different command-line options:
--mysqld1=--server1-optionA=value --mysqld1=--server1-optionB=value \ --mysqld2=--server2-optionA=value --mysqld2=--server2-optionB=value
To compare between two different storage engines in a MySQL-derived product, you can use the --default-storage-engine server command-line option:
--mysqld1=--default-storage-engine=MyISAM \ --mysqld2=--default-storage-engine=Maria
In this case, your grammar should not contain DDL statements with explicit ENGINE and the test should not be started with a --enigine option.
Note that there are plenty of differences between storage engines, especially when it comes to transactions. Such a comparison test may generate false positives. You may need to use the ExplicitRollback Validator that will issue an explicit ROLLBACK after each transactional error until the start of a new fresh transaction.
For MySQL-derived products, options that are not supported on the command line and any other startup SQL commands that must be different between the two servers can be placed in separate --init-file files:
--mysqld1=--init-file=/path/to/init/file/1 \ --mysqld2=--init-file=/path/to/init/file/2
If your servers support the /*!NNNNN */ syntax for version-specific comments (e.g. MySQL, MariaDB), you can include them in your grammar like this:
create_table: CREATE TABLE T1 (F1 INTEGER) /*!50100 PARTITION BY HASH (F1) */ ;
A server with a version number less than 5.4 will ignore the PARTITION clause, so a comparison test involving such a server can be used to validate partitioning against an older reference server that does not support that feature.
Main article: RandomQueryGenerator3WayDBComparision
Comparisons involving non MySQL-derived products can not be run with runall.pl since this script can not start non MySQL-derived servers. The servers need to be started beforehand so that the test can run with gentest.pl:
perl ./gentest.pl \ --dsn1=dbi:mysql:host=127.0.0.1:user=root:database=test \ --dsn2=dbi:JDBC:hostname=localhost\;port=1234\;url=jdbc:derby:/tmp/testdb\;create=true \ --dsn3=dbi:Pg:user=pguser
It is important for the grammar to generate at least some SQL queries that are valid across servers. Some products (MySQL, MariaDB) will interpret the /*! */ comment syntax as product-specific features that will be ignored by other products. For example, if your grammar contains a construct such as:
create_table: CREATE TABLE T1 (F1 INTEGER) /*! ENGINE = InnoDB */ ;
you can run a comparison test between a MySQL-derived product and a SQL-standard compatible one. The RQG will send the entire query to both servers, but the MySQL-related products will honor the ENGINE=InnoDB clause, whereas other products will ignore it.
Alternatively, you can embed the name of the product that should process the comment at the start of the comment itself:
query: SELECT f1 FROM t1 ORDER BY f1 /*JavaDB:Postgres: NULLS FIRST */;
in which case JavaDB and Postgres will process the NULLS FIRST, but the RQG will remove it before the query is passed to MySQL-derived products.
If you want to force the two servers in the comparison to use different syntaxes for features that should return identical results, you can use the /*executorN */ comment syntax. Such comments will only be processed by the N-th server, and ignored by all others. For example, in order to compare non-persistent against persistent virtual columns, you could write:
create_table: CREATE TABLE T1 (F1 INTEGER , V1 INTEGER AS ( F1 ) /*executor1 VIRTUAL */ /*executor2 PERSISTENT */ );
The table will be created with a non-persistent virtual column on the first server (the one specified with --basedir1, --vardir1 or --dsn1 option), and as persistent on the second server.