By Joshi-Mysql-Oracle on Feb 25, 2015
System QA took the approach of using Random Query Generator (RQG) to generate different SQL’s for multitude of data types available and supported in MySQL.
This approach would help in easily testing the validity of currently
supported data types and changes (if any) to the same.
Following steps explain What and How this was accomplished
- Generate the table
structure and generate insert queries for different data types through RQG and
redirect to a file.
Use gendata.pl which is part of RQG to create Table structure ( for this instance we have used DATE related data type )
$ perl gendata.pl --dsn=dbi:mysql:host=127.0.0.1:port=3306:user=root:database=rqg --spec=ex_dt.zz
- Generate SQL queries
using gensql and redirect the output to a file
For the following grammar we generated insert queries
INSERT INTO _table(_field) VALUES (data);
date | time | datetime | year | timestamp ;
_date |1000-01-01 | 999-01-01| 999-00-00 |9999-12-31|10000-13-32 ;
_time|-838:59:59 | -839:59:59 |-838:61:61 |838:59:59| 838:62:63 ;
_datetime|999-00-00 00:00:-10| 9999-12-31 23:59:59|10000-12-31 23:59:59 ;
_timestamp|1970 _01_01 00:00:01 |1970-00-00 00:00:01 |2038-01-19 03:14:07 |2038-02-19 03:14:07 ;
$ perl gensql.pl --grammar=ex_dt.yy --queries=10 --dsn=dbi:mysql:host=127.0.0.1:port=3306:user=root:database=rqg > generatedsqls.txt
Note: One can tune the –queries and the corresponding grammar to increase or decrease the data set generated
- Execute the queries
through Connector Application and collect the results
Created a sample Connector Application to
- Open up a connection ( with default settings ) to MySQL Server
- Read the SQL’s generated from Step 2 (generatedsqls.txt )
- Execute the insert query that is read on to MySQL Server
- Retrieve the data inserted using Select operation
- Capture the output/response for the Select operation
- Perform same operation(s)
as in Step 3 with MySQL Client
- Compare the output captured
from Step 3 and Step 4
If there is any difference in the resultant then the analysis would clearly point to the difference in handling the datatype/data by different connectors.
Currently System QA has tried this with MySQL Connector/Python and MySQL Connector/Java and identified some differences.
For instance with DATE type following difference is noticed
Data inserted: '999-00-00'
Data retrieved from MySQL Client: '0999-00-00'
Data retrieved from MySQL C/Java: '0998-11-30'
We can try and test this with different Connectors ( MySQL Connector/ODBC, MySQL Connector/C, MySQL Connector/C++,
MySQL Connector/Java,MySQL Connector Python )
The same approach can be easily expanded to other data types or operations by using the
features in RQG, if there are any changes to data types in MySQL Server.
Amrutha Rao and Madhusudhan Joshi
System QA Team