Skip to content

RandomQueryGenerator3WayDBComparision

Jump to bottom Edit New page
philip-stoev edited this page Jul 17, 2012 · 1 revision

If you run gentest.pl with three dsns (--dsn1, --dsn2 and --dsn3) you run a 3-way database comparision. The default validator is ResultsetComparator3 and you will get a report on how the results differs and if there is a minority (that is, two of the agree and the third get a different result on a query).

For more information, read also:

Supported databases

We support 3 databases:

  • MySQL (http://www.mysql.com/)
    • *NOTE* - the 'engines' option for gentest does not currently seem to play well with javadb, but does appear to work with PostgreSQL.
    • If you want to do a 3 way compare using different engines, start the MySQL server using
 --mysqld=--default-storage-engine=X

Simplification and test case generation

There is also a validator for simplification of queries and automatic test case generation called ResultsetComparator3Simplify. This validator requires that MySQL is the first dsn. The other two may be any other supported database.

Tests are generated the same way as for ResultsetComparatorSimplify. These tests may be run on JavaDB/Derby or PostgreSQL by using translateMysql to translate them from MySQL to the apropriate database.

Note: A simplified testcase will only be generated if dsn1 (that is MySQL) is the minority.

Example

A small example which illustrates the concept. Make a small simple grammar like this: 

query:
   SELECT pk,`int_nokey` FROM CC ORDER BY `int_nokey` LIMIT 1 OFFSET 5 ;
Due to the difference in the default ordering of NULLs, this query will behave differently on different databases (this is not a bug, but illustrates the use of 3-way database comparision).

Start the needed servers, run RQG like this (change the users and ports to fit your configuration): 

perl ./gentest.pl \
     --gendata \  
     --grammar=sample.yy \
     --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=bj136805 \
     --queries=1 \
     --threads=1

NOTE: It might be necessary to add database, user and pw clauses to the Postgres dsn: 

 --dsn3=dbi:Pg:dbname=test\;user=pcrews\;password=''
 ^ You can create the database and user via psql.  <br>
 Even if you create a user without a pw, you might still need to pass the empty string (or create a user with '' as the pw)

The output from this run will look like this: 

# 11:44:05 Starting 
# 11:44:05  ./gentest.pl \ 
# 11:44:05  --gendata \ 
# 11:44:05  --grammar=sample1.yy \ 
# 11:44:05  --dsn1=dbi:mysql:host=127.0.0.1:user=root:database=test \ 
# 11:44:05  --dsn2=dbi:JDBC:hostname=localhost;port=1234;url=jdbc:derby:/tmp/testdb;create=true \ 
# 11:44:05  --dsn3=dbi:Pg:user=bj136805 \ 
# 11:44:05  --queries=1 \ 
# 11:44:05  --threads=1
# 11:44:05 Starting 
# 11:44:05 # gendata-old.pl \ 
# 11:44:05 # --dsn=dbi:mysql:host=127.0.0.1:user=root:database=test
# 11:44:05 Creating table A, size 0 rows, engine  .
# 11:44:05 Creating table B, size 1 rows, engine  .
# 11:44:05 Creating table C, size 20 rows, engine  .
# 11:44:05 Creating table D, size 100 rows, engine  .
# 11:44:05 Creating table E, size 1000 rows, engine  .
# 11:44:05 Creating table AA, size 0 rows, engine  .
# 11:44:05 Creating table BB, size 1 rows, engine  .
# 11:44:05 Creating table CC, size 20 rows, engine  .
# 11:44:05 Creating table DD, size 100 rows, engine  .
# 11:44:06 Starting 
# 11:44:06 # gendata-old.pl \ 
# 11:44:06 # --dsn=dbi:JDBC:hostname=localhost;port=1234;url=jdbc:derby:/tmp/testdb;create=true
# 11:44:06 Creating table A, size 0 rows, engine  .
# 11:44:06 Creating table B, size 1 rows, engine  .
# 11:44:06 Creating table C, size 20 rows, engine  .
# 11:44:06 Creating table D, size 100 rows, engine  .
# 11:44:06 Creating table E, size 1000 rows, engine  .
# 11:44:07 Creating table AA, size 0 rows, engine  .
# 11:44:08 Creating table BB, size 1 rows, engine  .
# 11:44:08 Creating table CC, size 20 rows, engine  .
# 11:44:08 Creating table DD, size 100 rows, engine  .
# 11:44:08 Starting 
# 11:44:08 # gendata-old.pl \ 
# 11:44:08 # --dsn=dbi:Pg:user=bj136805
# 11:44:08 Creating table A, size 0 rows, engine  .
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
# 11:44:08 Creating table B, size 1 rows, engine  .
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b"
# 11:44:08 Creating table C, size 20 rows, engine  .
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "c_pkey" for table "c"
# 11:44:08 Creating table D, size 100 rows, engine  .
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "d_pkey" for table "d"
# 11:44:08 Creating table E, size 1000 rows, engine  .
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "e_pkey" for table "e"
# 11:44:09 Creating table AA, size 0 rows, engine  .
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "aa_pkey" for table "aa"
# 11:44:09 Creating table BB, size 1 rows, engine  .
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "bb_pkey" for table "bb"
# 11:44:09 Creating table CC, size 20 rows, engine  .
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "cc_pkey" for table "cc"
# 11:44:09 Creating table DD, size 100 rows, engine  .
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dd_pkey" for table "dd"
# 11:44:09 Reporters: (none)
# 11:44:09 Validators: ResultsetComparator3
# 11:44:09 Starting 1 processes, 1 queries each, duration 3600 seconds.
# 11:44:09 -----------
# 11:44:09 Result content mismatch between MySQL 5.4.5-beta and JavaDB Version N/A.
# 11:44:09 Query1: SELECT pk,`int_nokey` FROM CC ORDER BY `int_nokey` LIMIT 1 OFFSET 5
# 11:44:09 Query2: SELECT pk,int_nokey FROM CC ORDER BY int_nokey OFFSET 5 ROWS FETCH NEXT 1 ROWS ONLY
# 11:44:09 --- /tmp//randgen15235-1254390249-server0.dump       2009-10-01 11:44:09.000000000 +0200
# 11:44:09 +++ /tmp//randgen15235-1254390249-server1.dump       2009-10-01 11:44:09.000000000 +0200
# 11:44:09 @@ -1 +1 @@
# 11:44:09 -20  1
# 11:44:09 +18  3
# 11:44:09 -----------
# 11:44:09 Result content mismatch between MySQL 5.4.5-beta and Postgres 08.03.0800.
# 11:44:09 Query1: SELECT pk,`int_nokey` FROM CC ORDER BY `int_nokey` LIMIT 1 OFFSET 5
# 11:44:09 Query2: SELECT pk,int_nokey FROM CC ORDER BY int_nokey LIMIT 1 OFFSET 5
# 11:44:09 --- /tmp//randgen15235-1254390249-server0.dump       2009-10-01 11:44:09.000000000 +0200
# 11:44:09 +++ /tmp//randgen15235-1254390249-server1.dump       2009-10-01 11:44:09.000000000 +0200
# 11:44:09 @@ -1 +1 @@
# 11:44:09 -20  1
# 11:44:09 +18  3
# 11:44:09 Minority report: MySQL 5.4.5-beta(dsn1) differs from the two others
# 11:44:09 -----------
# 11:44:09 Child process completed with error code 28.
# 11:44:09 Started periodic reporting process...
# 11:44:10 Killing periodic reporting process with pid 15234...
# 11:44:10 Test completed with failure status 28.
If you need a simpliefied test case from this run, use ResultsetComparartor3Simplify like this (--gendata is dropped since we already have the data in all the databases): 
perl ./gentest.pl \
     --grammar=sample.yy \
     --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=bj136805 \
     --queries=1 \
     --threads=1 \
     --validator=ResultsetComparator3Simplify
The result will be: 
# 11:54:32 Starting 
# 11:54:32  ./gentest.pl \ 
# 11:54:32  --grammar=sample1.yy \ 
# 11:54:32  --dsn1=dbi:mysql:host=127.0.0.1:user=root:database=test \ 
# 11:54:32  --dsn2=dbi:JDBC:hostname=localhost;port=1234;url=jdbc:derby:/tmp/testdb;create=true \ 
# 11:54:32  --dsn3=dbi:Pg:user=bj136805 \ 
# 11:54:32  --queries=1 \ 
# 11:54:32  --threads=1 \ 
# 11:54:32  --validator=ResultsetComparator3Simplify
# 11:54:32 Reporters: (none)
# 11:54:32 Validators: ResultsetComparator3Simplify
# 11:54:32 Starting 1 processes, 1 queries each, duration 3600 seconds.
# 11:54:32 Started periodic reporting process...
# 11:54:33 -----------
# 11:54:33 Result content mismatch between MySQL 5.4.5-beta and JavaDB Version N/A.
# 11:54:33 Query1: SELECT pk,`int_nokey` FROM CC ORDER BY `int_nokey` LIMIT 1 OFFSET 5
# 11:54:33 Query2: SELECT pk,int_nokey FROM CC ORDER BY int_nokey OFFSET 5 ROWS FETCH NEXT 1 ROWS ONLY
# 11:54:33 --- /tmp//randgen15879-1254390873-server0.dump       2009-10-01 11:54:33.000000000 +0200
# 11:54:33 +++ /tmp//randgen15879-1254390873-server1.dump       2009-10-01 11:54:33.000000000 +0200
# 11:54:33 @@ -1 +1 @@
# 11:54:33 -20  1
# 11:54:33 +18  3
# 11:54:33 -----------
# 11:54:33 Result content mismatch between MySQL 5.4.5-beta and Postgres 08.03.0800.
# 11:54:33 Query1: SELECT pk,`int_nokey` FROM CC ORDER BY `int_nokey` LIMIT 1 OFFSET 5
# 11:54:33 Query2: SELECT pk,int_nokey FROM CC ORDER BY int_nokey LIMIT 1 OFFSET 5
# 11:54:33 --- /tmp//randgen15879-1254390873-server0.dump       2009-10-01 11:54:33.000000000 +0200
# 11:54:33 +++ /tmp//randgen15879-1254390873-server1.dump       2009-10-01 11:54:33.000000000 +0200
# 11:54:33 @@ -1 +1 @@
# 11:54:33 -20  1
# 11:54:33 +18  3
# 11:54:33 Minority report: MySQL 5.4.5-beta(dsn1) differs from the two others
# 11:54:33 -----------
# 11:54:33 Simplified query1 (MySQL 5.4.5-beta):  SELECT `int_nokey`  FROM CC  ORDER  BY `int_nokey`  LIMIT  5   ;
# 11:54:33 Simplified query2 (JavaDB Version N/A):  SELECT int_nokey  FROM CC  ORDER  BY int_nokey  FETCH FIRST 5 ROWS ONLY   
# 11:54:33 ;
# 11:54:33 --- /tmp//randgen15879-1254390873-server0.dump       2009-10-01 11:54:33.000000000 +0200
# 11:54:33 +++ /tmp//randgen15879-1254390873-server1.dump       2009-10-01 11:54:33.000000000 +0200
# 11:54:33 @@ -1,5 +1,5 @@
# 11:54:33  0
# 11:54:33  1
# 11:54:33 -NULL
# 11:54:33 -NULL
# 11:54:33 -NULL
# 11:54:33 +1
# 11:54:33 +1
# 11:54:33 +2
# 11:54:34 Dumping .test to /tmp/158791254390874.test
# 11:54:34 Passing queries: /tmp/15879-1254390874-passing.txt; failing queries: /tmp/15879-1254390874-failing.txt
# 11:54:34 Child process completed with error code 28.
# 11:54:35 Killing periodic reporting process with pid 15878...
# 11:54:35 Test completed with failure status 28.
A simple fix to this problem is of course to use NULLS FIRST (which MySQL don't understand), and change the sample grammar to 
query:
   SELECT pk,`int_nokey` FROM CC ORDER BY `int_nokey` /*JavaDB:Postgres: NULLS FIRST */ LIMIT 1 OFFSET 5 ;
See RandomQueryGeneratorIndependentGrammars for more details on how to write portable grammars. Category:RandomQueryGenerator
Add a custom footer
Add a custom sidebar

Clone this wiki locally