Posted on by

Drizzle’s transaction log is passing all tests!

In case you missed it here, we are very proud to announce that Drizzle’s transaction log is passing all of our tests.  For quite some time, David Shrewsbury, Stewart Smith, and Joe Daly have been putting a lot of love into the log code.  Please don’t be fooled by Dave’s praise of QA now that the storm has passed…you should have heard the names he called me and the things he plotted when we were rooting these bugs out ; )  However, now that there is a permanent record of his words, I’ll be reminding him about this post the next time my testing becomes a pain in his posterior and I feel him giving me the stink-eye in IRC (heheh)

With that said, we really have been putting tons of effort into making the log rock-solid.  This code will serve as the foundation for Drizzle replication and we can now be assured that any replication solutions will have a reliable log that will reflect the state of the server.  We have been beating up the code with the random query generator.  We have concocted several grammars that throw a variety of queries, in transactions and standalone, at the server.  After we have made the master do some work – we use a variety of connections and per-connection query counts, we produce SQL from the log, populate a validation server with the SQL, then compare Drizzledump files to ensure they match.  You should really check out the transaction_reader utility in drizzle/plugin/transaction_log/utilities – it allows a user to view the raw trx log contents, produce SQL from the log contents, and a few other neat tricks.  I’ll be blogging a bit more about how we used this for testing and troubleshooting very soon.

The transaction log worked well in most cases, the majority of our problems were in rooting out strange behavior around deadlocks and rolled back transactions.  I’d like to once again thank Stewart Smith, of the spork most flaming, for his valued assistance in finding these annoying bugs : )  If anyone wants to take a look at the bugs we’ve killed – you can check them out here.

We are far from being done – some of our next tasks include testing RabbitMQ, tweaking randgen tests to make certain we are totally crash-safe, and a few other things.  In the meantime, our randgen trx log tests run against every branch we intend to merge into trunk, so we’re keeping a close eye on making sure it stays solid.  We’ll keep you posted as our replication testing moves along.  Please keep trying Drizzle and helping us to improve it with your bug reports and feedback.

Posted on by

Testing status – Drizzledump migration

One of the key features of our first beta was drizzledump’s ability to migrate from a MySQL database.  For those who don’t know about this, the inimitable Andrew Hutchings enhanced drizzledump so that one could transfer data from MySQL directly to Drizzle without needing any intermediate files.  In this example, we connect to MySQL, running on 3306 and send the data in database my_mysql_schema, tables t1,t2, and t3 into our Drizzle server (port 4427).


$ drizzledump --compact --host=127.0.0.1 --port=3306 --destination-type=database --destination-host=localhost --destination-port=4427 --destination-user=root --destination-database=my_mysql_schema --user=root my_mysql_schema t1 t2 t3;

There are several ways of using this tool.  One could:

  • Transfer the data from all databases
    • $ drizzledump --compact --host=127.0.0.1 --port=3306 --destination-type=database --destination-host=localhost --destination-port=4427 --destination-user=root --all-databases --user=root  ;
  • Transfer data from schema ‘my_orig_schema’ on MySQL into schema ‘my_new_schema’ on Drizzle
    • $ drizzledump --compact --host=127.0.0.1 --port=3306 --destination-type=database --destination-host=localhost --destination-port=4427 --destination-user=root --destination-database=my_new_schema --user=root my_orig_schema
  • Transfer only the contents of specific schemas
    • $ drizzledump --compact --host=127.0.0.1 --port=3306 --destination-type=database --destination-host=localhost --destination-port=4427 --destination-user=root --databases --user=root my_schema1 my_schema3 my_schema5

The documentation can be found here and the blueprints for development and testing might be of interest as well.  The dev blueprint describes conversions and certain details that haven’t made it to the docs yet and the testing blueprint provides a list of bugs we’ve found. We found a number of minor, yet annoying bugs, but they have all been corrected at this time.

To return to the topic of testing – at first I had intended to use the randgen.  We already use it to test our ability to restore from drizzledump files.  While we did find some initial bugs with our tests, expressing certain things like indexes and foreign keys in a randgen grammar were proving problematic.

In the end, I created a new suite of tests for our standard test-runner.  The tests only have the following requirements:

  • You have a running MySQL server
  • You set two environment variables:
    • DRIZZLE_MYSQL_MIGRATE_TEST=1
    • DRIZZLE_MYSQL_MIGRATE_PORT= port where MySQL is running

Thanks to the utility dictionary plugin, we have the ability to SELECT environment variables and we use their presence to signal if we should run the test or not; if they aren’t set, the suite is skipped.  For example, we use this query in our checks:

SELECT VARIABLE_VALUE AS `TRUE` FROM DATA_DICTIONARY.ENVIRONMENTAL WHERE VARIABLE_NAME="DRIZZLE_MYSQL_MIGRATE_TEST";

When the tests run, they do the following:

  • populate the MySQL server with the relevant test-bed (tables + data)
  • call drizzledump to transfer the data from MySQL to Drizzle
  • perform various queries to ensure the transfer worked as expected (SHOW CREATE + SELECT * generally).

To run the tests:

./test-run --suite=mysql_migrate

You can expect to see the code in trunk shortly after this is posted.

We test every MySQL data type; we also test engines, indexes, foreign keys, and other setups.  While there are likely still some bugs left to be found, I believe that the tool should work smoothly for most users.  With that said, I would like to give a special thanks to Tim Soderstrom (aka Sweetums), MySQL DBA extraordinaire and Drizzle contributor.  He’s an early-adopter and helped us find a number of bugs while experimenting with migrating his own data to Drizzle.  Thanks again, Tim!

I hope that you’ll give the tool a spin and that you’ll write up a bug report if something isn’t working.  Questions and whatnot are always welcome on IRC and the mailing lists too : )

Posted on by

EXECUTE testing with randgen Transforms

A little while ago, we added EXECUTE functionality to the server.  This lets us either:

  • EXECUTE “SELECT * FROM TEST_TABLE”;
  • SET @var = “SELECT * FROM TEST_TABLE” ; EXECUTE @var;

We have added a new suite to our test tree and we’ve also started testing this with the randgen.  The random query generator allows for code known as a Validator.  This code executes on a per-query basis and can do whatever you need it to in order to verify a query.  Some examples:

  • ResultSetComparator – which compares result sets between two different servers for the same query.  Useful for checking result set correctness against a trusted validation server.  This tool, combined with the optimizer* grammars quickly found over 30 optimizer bugs in MySQL >:-)
  • ExecutionTimeComparator  – compares execution times between two servers.  This is useful when checking a patch for a regression, especially in the optimizer.

There is a special type of Validator known as a Transformer.  There are various Transforms that can be used on a query.  The randgen will alter the query in some way (a Transform).  Each Transform states how the result set should relate to the result set of the original query, for example a TRANSFORM_OUTCOME_SUBSET is expected when tacking on a LIMIT clause.  Some Transforms:

  • ExecuteAsSPTwice – This takes the original query, creates a MySQL stored procedure from it, then executes it twice in a row.  This was developed due to a MySQL bug
  • InlineSubqueries – Converts SELECT…WHERE col_int IN (SELECT col_int…) -> SELECT …WHERE IN (1,3,5) i.e. the actual values returned from the subquery

For Drizzle, we have created two new Transforms.  For each SELECT query generated by a given grammar, the randgen EXECUTE’s it as a string and as a variable.  I’m happy to report that the tests are passing with flying colors and will be added to our automated tests.

It was incredibly easy to create these new Transforms for the randgen.  Now, we get to try the functionality out against every SELECT we can generate via the randgen – we get to cover a lot more ground this way versus trying to craft these tests by hand (though we have added several such tests as previously noted).

Anyway, please feel free to kick the tires on this feature.  I leave it to you to check out EXECUTE…CONCURRENT ; )

Posted on by

Testing status report – Drizzle’s transaction log

It’s been a while since I’ve blogged about the work we are doing on the transaction log.  Basically, our priority has been to ensure that the log and supporting code is rock-solid before we move further along with replication.  The intent is to allow for a wide variety of replication solutions, all of which will be built on the log’s contents.  We’re very concerned with giving developers and users a solid foundation for whatever solution they may use.

In my last post on this topic, we had just created tests for the test-suite and had starting beating on the log with the randgen in single-user scenarios.  This was important as it helped us catch basic bugs before we moved on to more complicated testing.  We have since moved on to high-concurrency testing.  We use the randgen to generate a wide variety of queries, using 5+ connections.  Once all of the queries have been executed, we use the transaction_reader utility to generate SQL from the log file’s contents.  We use this log file to populate a validation server.  From there, we do a comparison of drizzledump output and report an error if any difference is found.

Our randgen grammars use varying levels of ‘noise’.  We issue some pretty awful SQL at times, but when we consulted with the DBA’s at Rackspace, they said they see such things regularly so our log had better be able to handle it : )  We found a number of bugs by throwing fuzzy queries at the server.  Most of these were issues where one query out of several within a transaction would fail and this would cause problems for the entire transaction.  Fortunately, David Shrewsbury and Joe Daly are very devoted to killing any such bugs I may find : )

We have now automated our randgen tests for the transaction log.  That means that these tests will be run against every patch before it can be pushed to trunk; we’ll have early feedback if something breaks.  We also have a param-build job that runs these tests.  If a developer has been working on this code, they can run the tests against their branch to find out if they have broken anything.

At the time of this writing, I would say that the log is pretty solid.  We do have a couple of troublesome outstanding bugs that show up in concurrent testing:

  • Differences between slave and master in concurrent testing scenarios – randgen tests using many threads to operate on the same set of tables are producing differences between the master server and a validation server populated from the transaction log’s contents.  Still tracking down the exact interaction that is causing this to fail.
  • Transaction ID not unique – we are seeing cases where different transactions in a concurrent environment are using the same transaction id’s

We are still in the process of testing things, but David Shrewsbury and Marcus Ericsson have been making progress with the Tungsten Replicator.  We’ll be working on testing scenarios using that solution once it is ready.  Any developers interested in replication are encouraged to give the transaction log a spin with their favorite solution.  The basics definitely work well, and now would be the time to chime in with your thoughts / needs for the log.  We realize that the concurrency problems are an issue and we’re actively working on resolving these, but things are in a state where one could start testing basic functionality as they saw fit.

As always anyone with any questions, recommendations, or whatever are welcome to contact us via IRC or the mailing list.

Posted on by

Sphinx documentation for Drizzle’s test-runner now available

In case you missed it, Drizzle is now using Sphinx to produce our documentation.  If you have sphinx installed (version 1+), you can generate them yourself with `make html`.  It is easy to work with (it’s Python, after all) and creates some very nice looking docs.

For those of you familiar with MySQL, test-run is similar to mysql-test-run, but with some adjustments for Drizzle.  It allows a user to run the test suite to ensure the system is performing correctly.  You can view the code coverage we achieve here.

One of the most important things people can do to help us move Drizzle from beta to GA is to try it out.  We do test very heavily, but extra sets of eyes are always helpful.  Let us know if things are broken or if you have thoughts on how things could work better; we welcome the feedback.

In the future, I intend to expand on the testing documents to include writing test cases and documenting the language features that are available in test-run.  Additionally, I will be writing up docs on how to use the randgen with Drizzle.  Please let us know via the mailing list / IRC / whatever if you have any specific information you’d like to see documented.

Also, if you are interested in contributing, but don’t necessarily want to hack on the code, I encourage you to tinker with the documentation – we are more than happy to accept patches : )  You can find the source files in drizzle/docs.

Posted on by

Basic replication from Drizzle’s transaction log now being tested.

Just a quick update on the state of Drizzle’s transaction log as it’s been a while since I’ve mentioned it.

As I wrote earlier, we have already spent some time on basic tests of the transaction log structure – i.e. can we capture INSERT/UPDATE/DELETE/etc correctly?

Well, the next phase of testing is underway and we’re now beating up on things with the randgen!

At present, we are working with a single thread and throwing a large number of randomly generated queries at the log. We have the randgen set up so that it runs 20-30 queries per cycle and we run several hundred cycles. Once these queries have been executed, we make use of drizzled/message/transaction_reader to create SQL from the transaction log contents.

The example below assumes you’ve started the server via ./test-run –start-and-exit –mysqld=–transaction-log.enable and have created some tables to play with.  We call the transaction_reader like this:

drizzled/message/transaction_reader var/master-data/transaction.log

As an example, a query like:

UPDATE dd SET col_bigint=5 LIMIT 5;

Will show up as:

START TRANSACTION;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=3389;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=2329;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=3634;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=2369;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=3674;
COMMIT;

We then send this SQL to a validation server (just another instance of Drizzle) and then compare drizzledump files between the master and the slave.

So far, we’ve found a couple of new crashes, some minor issues with the transaction_reader program, and a couple of issues where the log actually fails to capture data (only UPDATEs have failed in this way so far). I’d like to give a special mention to David Shrewsbury and Joe Daly for their awesomely fast responses to the bugs I’ve found so far : ) We maintain a list of all  transaction log bugs we have found with the testing blueprint.  Most of these bugs are already closed (thanks Dave and Joe!).

Our next steps will be to tweak our single-threaded grammars a bit further, then we will move on to concurrent testing. We’ll be repeating the testing process I laid out above, except that we will let the multiple threads run to completion, say five to ten thousand queries apiece, and then replicate and validate. At the moment, we’re shooting for testing to be complete in time for next week’s milestone release.

Posted on by

Code coverage – now with branches!

We have now upgraded our lcov testing to take advantage of lcov 1.9‘s branch coverage.

While code coverage isn’t the be-all-end-all of testing, it is a very useful tool in helping us target areas that could use more tender, loving care (by which I mean beating them mercilessly with our test tools).  It doesn’t prove completeness of testing – it merely helps us move in that direction : )  The addition of branch-level coverage gives us another dimension to help us expand our testing.  We’re also making use of the –demangle-cpp option to produce neater function names for the function-level coverage.

You can check out the updated reports here.  We gather the code coverage of our test suite with every push to trunk and store the data for general analysis.

In other news, Brian has added two new functions for testing.  These changes were a part of another blueprint to remove print_stack_trace and stack dump. While I haven’t had a chance to do anything with them yet, I think these will be very useful and look forward to seeing what kinds of tests our community will be able to cook up : )

  • Crash the server:

% ./drizzled/drizzled –plugin-add=crash_function

select crash();

  • Shutdown the server:

% ./drizzled/drizzled –plugin-add=shutdown_function

select shutdown()

Finally, I have been playing with the syslog plugin and it is awesome!  It is easy to setup and is very easy to use.  The data produced will be very useful in testing as well.  An example produced via a randgen run for your enjoyment:

Sep  2 20:06:16 mahmachine drizzled[4340]: thread_id=4 query_id=200380 db=”test” query=”SELECT    SUM(  table1 . `col_int_key` ) AS field1 FROM  c AS table1  RIGHT  JOIN g AS table2 ON  table1 . `col_varchar_1024` =  table2 . `col_varchar_1024_key`  WHERE table1 . `col_int` <> table1 . `col_int_key`   ORDER BY field1″ command=”Query” t_connect=1283472376187915 t_start=486 t_lock=367 rows_sent=1 rows_examined=2 tmp_table=0 total_warn_count=0



Well, that’s it for now.  We have some exciting work coming up and we look forward to seeing what kinds of awesome plugins are being developed out there.   Now back to hacking.
Posted on by

Testing the data dictionary in a concurrent environment

So, Brian wrote a bug the other day, asking me to do more testing of Drizzle’s data dictionary.  Specifically, we wanted to look for how things behaved in a concurrent environment as this is often a killer for table functions / what people are most likely to forget.



What we came up with was the following plan:
1)  Generate a test that only looked at data dictionary tables, with several users generating the same queries.
2)  If step 1 looks good, we will slowly introduce background workloads (SELECT / UPDATE / etc) while we continue with the workload from step 1.



This resulted in a couple of new randgen grammars:
data_dict_concurrent_drizzle.yy – this grammar generates nothing but queries against the data dictionary tables.  At present, these are mostly of the variety:
  • SELECT * FROM data_dictionary_table
  • SHOW PROCESSLIST | VARIABLE | TABLE STATUS | etc
This is designed to stress the data dictionary, either alone or with another randgen process generating a background workload.



proclist_subquery_drizzle.yy – this grammar is the same as optimizer_subquery_drizzle (generating *nasty*, subquery-heavy SELECTs), but also allows for SHOW PROCESSLIST commands. This is mainly designed to stress the server / PROCESSLIST.  This grammar is nice as it is a single test that can just be run with several threads.



I am happy to report that in a data dictionary-only environment, the server was able to handle things very well.  I was running up to 100 connections, 100k queries per connection and things looked good.



The other tests are another matter.  While these are somewhat simple tests, they have proven highly effective so far:
  • Bug #627733: Crash in InnodbTrxTool::Generator::populate_innodb_locks (this=0x7f26140046f0) at plugin/innobase/handler/data_dictionary.cc:269
  • Bug #627742: Assertion failed – in drizzled::plugin::TableFunction::Generator::push (this=0x23876c0, arg=<value optimized out>, length=<value optimized out>) at drizzled/plugin/table_function.cc:185
  • Bug #628398: Crash / segfault in copy_fields (join=0x284f868, end_of_records=false) at drizzled/sql_select.cc:6228
  • Bug #628891: Crash / assertion failed – in drizzled::Diagnostics_area::set_eof_status (this=0x7f9f3c2c4258, session=0x7f9f3c2c3b10) at drizzled/diagnostics_area.cc:120



For each of these bugs, data dictionary queries were being executed while another query was also being processed.  It should be noted that our newest team member, Andrew Hutchings, had Bug#627742 fixed in less than 24 hours : )



I still have a few more scenarios to run through, but it appears that we have shaken out most of the bugs in this area.  Our next steps will be to install such tests in our build and test system to prevent regressions / catch new bugs and to fix the remaining crashes noted above.
Posted on by

Where to find randgen testing information / documentation

I know I write a lot about testing with the randgen. In case anyone is interested, I wanted to share some links to randgen documentation and information. It really is an amazing and powerful tool and has found many bugs in MySQL, MariaDB, and Drizzle.

  • It can generate queries faster than anyone could ever think of them.  This provides breadth of input testing and allows for stress testing (multi-threaded scenarios are supported)
  • A number of Validators are available to determine query correctness.  These include server-server comparison validators for testing result sets, execution time comparison validators for comparing query execution time, and others.  Custom validators are implemented fairly easily
  • Grammar-based testing allows for faster generation of test queries.  Rather than thinking of individual queries, one can focus on ‘patterns’ of queries and have the randgen generate hundreds of queries for the same amount of time and effort.

With that little sales pitch out of the way, here are the links to documentation.

  • MySQL Forge – general documentation
  • Drizzle wiki – information on how to run randgen tests and what to look for
  • DrizzleQA – links to information about all Drizzle testing

I will be writing more about how to use this tool in the future, but I’d definitely welcome feedback on what people are interested in.  The tool has applications for testing patches for regressions, validating upgrades based on current workloads, stress testing, etc. – feel free to let me know what you’d like to learn about.  Discussion is always welcome – stop by IRC (#drizzle) or drop us a line on our mailing list.

Posted on by

Testing restoration from drizzledump files

Currently, we have a lot of uses for drizzledump + restore:  upgrading Drizzle, backing up data, etc.  Since we are putting people in a position to rely on these tools, it seemed prudent that we improve our testing in this area.


Our plan was to include both test-suite tests as well as more vigorous testing with the randgen.  The test-suite tests are pretty straightforward and are in tests/t/drizzledump_restore.test.  You can get Drizzle here to check them out if reading test cases is your idea of fun : )  Of course, the randgen tests are the ones that do the interesting, high-volume stuff.


In a nutshell, the randgen test randomly generates any number of test tables in a database. The test tables have random composition (columns and datatypes used) and data population.  Once the tables have been generated, we call drizzledump, restore the tables from the dump file to a new database, then do a table-to-table comparison of the original and restored tables.  We report error and debugging information if we find a difference, and go on our happy way otherwise.


This test is now a part of our regular build and test system and runs on every push to trunk.  We make use of a standard, repeatable set of queries and we also make use of the randgen’s ability to randomize data and queries and use –seed=time for a smaller run of the same test.  This gives us a larger spread of tests each run.  Fortunately, the randgen is nice enough to report the seed value for repeatability in case we encounter a bug.
# 2010-08-17T21:32:20 Converting –seed=time to –seed=128208074
Feeding the randgen the same seed value for a grammar / gendata file combination will produce the same tables, data, and queries each time.


I’m happy to report that the test currently passes with flying colors.  Interestingly enough, I discovered what happens when you try to have several clients running drizzledump, creating and altering test tables, and restoring data from dumpfiles and it isn’t pretty.  Please note that our test is designed to run with only a single thread at a time : )


If anyone is interested in testing or anything else related to Drizzle, stop by IRC (#drizzle) or drop us a line on our mailing list.