Libdrizzle News

Wow, what an insane year. Looking back on it, a lot of stuff has happened this year for me. Let's look back:

• With the help of Patrick Crews and Andrew Hutchings, and building on the work of Jay Pipes, I managed to implement a pretty solid replication solution in Drizzle and made the slave plugin available.
• Implemented multi-source replication (more accurate name than multi-master) in Drizzle.
• I ventured into new territory by giving my first-ever presentation at the MySQL User Conference in Santa Clara, CA.
• Rackspace made life interesting and forced me find another position. I joined a local startup company as their first real hire. Two of the founders are former coworkers and current friends of mine, and the timing worked out well for all parties.
• For my new job, I had to learn Python, GIT, and how to work with AWS systems. Python rocks. I feel like I can fly now.
• Lost 20 lbs! Woohoo!

The new job has kept me unbelievably busy, so I haven't had the time to contribute more to Drizzle, other than a bug fix here or there. That makes me a sad panda, but priorities have changed. Perhaps next year will offer more opportunity.

I don't know what 2012 has in store for me, but hopefully it is just as exciting. I hope everyone has a Happy New Year.

One of the best things that can happen to a piece of software is for people to actually use it.

I’ve been fortunate enough to have received feedback on the tool from several members of both the Percona and Drizzle teams.  The most common and strongly emphasized comments were in regards to what a terrible, terrible name dbqp really is in terms of saying, seeing, and typing it ; )

As that isn’t something that can be disputed (it’s really annoying to use in conversations *and* to type several dozen times a day), the project has been renamed to kewpie.  For those that follow such things, I did present on another tool with that name at the last MySQL Conference, but *that* tool is a nice-to-have, while the test-runner sees daily use.  Better to save the good names for software that actually stands a chance of being used, I say : )

While there are probably 1*10^6 other things I need to do (Stewart is a merciless slave driver as a boss, btw…heheh), the fact that we are merging the tool into the various Percona branches meant it should be done sooner rather than later.  The tool is currently in our 5.1 branch and I have merge requests up for both Drizzle and Xtrabackup (dbqp was living there too).

I have several other interesting things going on with the tests and tool, which I’ll be blogging about over at MySQL Performance Blog.  Later this week, I’ll be talking about what we’ve been doing to work on this bug ; )

 

Also, the Percona Live MySQL Conference in DC is just around the corner.  There are going to be some great speakers and attendees

You could use this in a Vagrant setup if you like (I’ve done so for testing).

Step 1) Set the following in your Vagrantfile:

Vagrant::Config.run do |config| config.vm.box = "lucid32" config.vm.box_url = "http://files.vagrantup.com/lucid32.box" config.vm.provision :puppet end

Step 2) Get puppet-apt helper.

I used https://github.com/evolvingweb/puppet-apt and put it in a manifests/ directory like so:

$ mkdir manifests $ cd manifests $ git clone git://github.com/evolvingweb/puppet-apt.git

Step 3) Write your puppet manifest:

import "puppet-apt/manifests/init.pp" import "puppet-apt/manifests/ppa.pp" class drizzlebuild { apt::ppa { "ppa:drizzle-developers/ppa": } package { "drizzle-dev": ensure => latest, } } include drizzlebuild

Step 4) “vagrant  up” and you’re done! Feel free to build Drizzle inside this VM.

I’m sure there may be some more proper way to do it all, but that was a pretty neat first intro to me to Puppet and friends :)

Drizzle is known for its plugins, but the DATA_DICTIONARY.PLUGINS table has a column for MODULE_NAME and there’s a MODULES table. What is the relation between Drizzle modules and plugins? A Drizzle module provides one or more plugin of potentially various types. Or, analogously: a Drizzle module is a bookshelf and each plugin is a book. [...]

or… “the case of Stewart recognizing parameters to the read() system call in strace output”.

Last week, a colleague asked a question:

I have an instance of MySQL with 100 tables and the table_definition_cache set to 1000. My understanding of this is that MySQL won’t revert to opening the FRM files to read the table definition, but we can see from strace:

[pid 19876] open("./db/t1.frm", O_RDONLY) = 32 <0.000013> [pid 19876] read(32, ""..., 10) = 10 <0.000011> [pid 19876] close(32) = 0 <0.000012> [pid 19876] open("./db/t2.frm", O_RDONLY) = 32 <0.000014> [pid 19876] read(32, ""..., 10) = 10 <0.000012> [pid 19876] close(32) = 0 <0.000012> [pid 19876] open("./db/t3.frm", O_RDONLY) = 32 <0.000014> [pid 19876] read(32, ""..., 10) = 10 <0.000011> [pid 19876] close(32) = 0 <0.000011> [pid 19876] open("./db/t4.frm", O_RDONLY) = 32 <0.000013>

So, why is this? It turns out that this triggered a memory for me from several years ago. I’ve since discovered the blog post in which I mention it: drop table fail (on the road to removing the FRM). That blog post is from 2008, almost three years ago to the day.

Since we completely reworked how metadata works in Drizzle, it has enabled us to do some truly wonderful things, including more in depth testing of the server. Amazingly enough, spin-offs from this work included being able to find out and then test that the ENUM limit of 65,535 has never been true (but now is in Drizzle), produce a CREATE TABLE statement that took over four minutes to execute and get a more complete view of how the Storage Engine API is called.

But back to what the above strace shows. In MySQL 5.5 you can find in sql/datadict.cc a function named dd_frm_type(). In MySQL 5.1, for some reason yet unknown to humans, it lives in sql/sql_view.cc as mysql_frm_type(). What this code snippet does is:

• open the FRM
• read 10 bytes (“header”)
• check if it’s a view by doing a string compare for “TYPE=VIEW\n” being the first bytes of the FRM file. This is due to VIEWs being stored as the plain text of the SQL query inside the FRM file instead of the normal binary format FRM.
• some legacy check for a generic table type (I think, I haven’t gone back into the deep history of the FRM file format to confirm)
• return the fourth byte for the DB_TYPE. i.e. what storage engine it is.

We can ignore the upper limit on number of storage engines for MySQL and understanding the relationship between the range of numbers for dynamic assignment and what this means for on-disk compatibility of data directories is left as an exercise for the reader.

This code is called from several code paths in the server:

• DROP TABLE
• RENAME TABLE
• DROP VIEW
• open table
• filling INFORMATION_SCHEMA tables (I think it is actually the TABLES table, but didn’t look closely)

An example of how this is used is that in the DROP TABLE code path, MySQL uses this magic byte to work out which Storage Engine to ask to drop the table. The main consequence of this bit of code is that MySQL may cause unnecessary disk IO for information it already has cached (often at least twice – in InnoDB itself and in the table_definition_cache).

Further reading:

• MySQL Forge Internals document on the FRM file format
• Stewart’s blog post on the FRM file format (from early 2009, towards the very end of removing the FRM file from Drizzle if I remember correctly)

Way back in 2009, Monty Taylor got fed up with maintaining a set of common autotools foo across several projects (one of which was Drizzle) and started the pandora-build project.  Basically, it’s a collection of the foo you need for autotools to do things like: use it properly, detect a bunch of common libraries, enable crap-tons of compiler warnings (and -Werror) and write an application/library with plugins (that are auto-discovered and built).

(and don’t worry, there’s also modes to disable -Werror and different compiler warnings if you’re working on an old code base that really doesn’t build cleanly)

There’s also templates for Quickly to get you up and started really quickly.

Basically, for the past 3 years, whenever I’ve gone to write some small project (or got sufficiently annoyed with the broken build system on an old one), I’ve turned to pandora-build to solve my problems.

Recently, I’ve had the need to use the plugin infrastructure of pandora-build in a new project (I’ve used it extensively in Drizzle of course). The one bit that pandora does not take care of for you is the dlopen() code to load plugins at run time… although I do wonder about turning some of that code into a bit of a library just because a bunch of it is pretty common….

Of course, a task for me is to write up a blog post on how I did it all, but for the moment I thought I’d just share :)

I’ve just arrived at ANU in Canberra for the Open Source Developers Conference 2011 (OSDC). I’ve spoken at several of the past OSDCs that have been held around Australia: 2005, 2007, 2008, 2010 and now 2011. It’s one of those conferences with great energy and great people that’s organised by dedicated members in the community who build the conference they want to go to.

I’ll be giving two talks this year:

• Dropping ACID: Eating Data in a Web 2.0 Cloud World
• Drizzle 7: GA and Supported: Current and Future Features

I tend to speak highly of the random query generator as a testing tool and thought I would share a story that shows how it can really shine. At our recent dev team meeting, we spent approximately 30 minutes of hack time to produce test cases for 3 rather hard to duplicate bugs. Of course, I would also like to think that the way we have packaged our randgen tests into unittest format for dbqp played some small part, but I might be mildly biased.

The best description of the randgen’s power comes courtesy of Andrew Hutchings – “fishing with dynamite“. This is a very apt metaphor for how the tool works – it can be quite effective for stressing a server and finding bugs, but it can also be quite messy, possibly even fatal if one is careless. ; ) However, I am not writing this to share any horror stories, but glorious tales of bug hunting!

The randgen uses yacc-style grammar files that define a realm of possible queries (provided you did it right…the zen of grammar writing is a topic for another day). Doing this allows us to produce high volumes of queries that are hopefully interesting (see previous comment about grammar-writing-zen).

It takes a certain amount of care to produce a grammar that is useful and interesting, but the gamble is that this effort will produce more interesting effects on the database than the hand-written queries that could be produced in similar time. This is especially useful when you aren’t quite sure where a problem is and are just trying to see what shakes out under a certain type of stress. Another win is that a well-crafted grammar can be used for a variety of scenarios. The transactional grammars that were originally written for testing Drizzle’s replication system have been reused many times (including for two of these bugs!)

This brings us to our first bug:
mysql process crashes after setting innodb_dict_size

The basics of this were that the server was crashing under load when innodb_dict_size_limit was set to a smaller value. In order to simulate the situation, Stewart suggested we use a transactional load against a large number of tables. We were able to make this happen in 4 easy steps:
1) Create a test case module that we can execute. All of the randgen test cases are structured similarly, so all we had to do was copy an existing test case and tweak our server options and randgen command line as needed.

2) Make an altered copy of the general, percona.zz gendata file. This file is used by the randgen to determine the number, composition, and population of any test tables we want to use and generate them for us. As the original reporter indicated they had a fair number of tables:

$tables = { rows => [1..50], partitions => [ undef ] };

The value in the ‘rows’ section tells the data generator to produce 50 tables, with sizes from 1 row to 50 rows.

3) Specify the server options. We wanted the server to hit similar limits as the original bug reporter, but we were working on a smaller scale.
To make this happen, we set the following options in the test case:

server_requirements = [["--innodb-dict-size-limit=200k --table-open-cache=10"]]

Granted, these are insanely small values, but this is a test and we’re trying to do horrible things to the server ; )

4) Set up our test_* method in our testcase class. This is all we need to specify in our test case:

def test_bug758788(self): test_cmd = ("./gentest.pl " "--gendata=conf/percona/innodb_dict_size_limit.zz " "--grammar=conf/percona/translog_concurrent1.yy " "--queries=1000 " "--threads=1") retcode, output = execute_randgen(test_cmd, test_executor, servers) self.assertTrue(retcode==0, output)

The test is simply to ensure that the server remains up and running under a basic transactional load

From there, we only need to use the following command to execute the test:
./dbqp.py –default-server-type=mysql –basedir=/path/to/Percona-Server –suite=randgen_basic innodbDictSizeLimit_test
This enabled us to reproduce the crash within 5 seconds.

The reason I think this is interesting is that we were unable to duplicate this bug otherwise. The combination of the randgen’s power and dbqp’s organization helped us knock this out with about 15 minutes of tinkering.

Once we had a bead on this bug, we went on to try a couple of other bugs:

Crash when query_cache_strip_comments enabled

For this one, we only modified the grammar file to include this as a possible WHERE clause for SELECT queries:

WHERE X . char_field_name != 'If you need to translate Views labels into other languages, consider installing the <a href=\" !path\">Internationalization</a> package\'s Views translation module.'

The test value was taken from the original bug report.
Similar creation of a test case file + modifications resulted in another easily reproduced crash.
I will admit that there may be other ways to go about hitting that particular bug, but we *were* practicing with new tools and playing with dynamite can be quite exhilarating ; )
parallel option breaks backups and restores

For this bug, we needed to ensure that the server used –innodb_file_per_table and that we used Xtrabackup‘s –parallel option. I also wanted to create multiple schemas and we did via a little randgen / python magic:

# populate our server with a test bed test_cmd = "./gentest.pl --gendata=conf/percona/bug826632.zz " retcode, output = execute_randgen(test_cmd, test_executor, servers) # create additional schemas for backup schema_basename='test' for i in range(6): schema = schema_basename+str(i) query = "CREATE SCHEMA %s" %(schema) retcode, result_set = execute_query(query, master_server) self.assertEquals(retcode,0, msg=result_set) retcode, output = execute_randgen(test_cmd, test_executor, servers, schema)

This gave us 7 schemas, all with 100 tables per schema (with rows 1-100). From here we take a backup with –parallel=50 and then try to restore it. These are basically the same steps we use in our basic_test from the xtrabackup suite. We just copied and modified the test case to suit our needs for this bug. With this setup, we need a crash / failure during the prepare phase of the backup. Interestingly this only happens with this number of tables, schemas, and –parallel threads.

Not too shabby for about 30 minutes of hacking + explaining things, if I do say so myself. One of the biggest difficulties in fixing bugs comes from being able to recreate them reliably and easily. Between the randgen’s brutal ability to produce test data and queries and dbqp’s efficient test organization, we are now able to quickly produce complicated test scenarios and reproduce more bugs so our amazing dev team can fix them into oblivion : )

So I’m back from the Percona dev team’s recent meeting.  While there, we spent a fair bit of time discussing Xtrabackup development.  One of our challenges is that as we add richer features to the tool, we need equivalent testing capabilities.  However, it seems a constant in the MySQL world that available QA tools often leave something to be desired.  The randgen is a literal wonder-tool for database testing, but it is also occasionally frustrating / doesn’t scratch every testing itch.  It is based on technology SQL Server was using in 1998 (MySQL began using it in ~2007, IIRC).  So this is no knock, it is merely meant to be an example of a poor QA engineer’s frustrations ; )  While the current Xtrabackup test suite is commendable, it also has its limitations. Enter the flexible, adaptable, and expressive answer: dbqp.

One of my demos at the dev meeting was showing how we can set up tests for Xtrabackup using the unittest paradigm.  While this sounds fancy, basically, we take advantage of Python’s unittest and write classes that use their code.  The biggest bit dbqp does is search the specified server code (to make sure we have everything we should), allocate and manage servers as requested by the test cases, and do some reporting and management of the test cases.  As the tool matures, I will be striving to let more of the work be done by unittest code rather than things I have written : )

To return to my main point, we now have two basic tests of xtrabackup:

Basic test of backup + restore:

1. Populate server
2. Take a validation snapshot (mysqldump)
3. Take the backup (via innobackupex)
4. Clean datadir
5. Restore from backup
6. Take restored state snapshot and compare to original state

Slave setup

1. Similar to our basic test except we create a slave from the backup, replicating from the backed up server.
2. After the initial setup, we ensure replication is set up ok, then we do additional work on the master and compare master and slave states

One of the great things about this is that we have the magic of assertions.  We can insert them at any point of the test we feel like validating and the test will fail with useful output at that stage.  The backup didn’t take correctly?  No point going through any other steps — FAIL! : )  The assertion methods just make it easy to express what behavior we are looking for.  We want the innobackupex prepare call to run without error?
Boom goes the dynamite!:

# prepare our backup cmd = ("%s --apply-log --no-timestamp --use-memory=500M " "--ibbackup=%s %s" %( innobackupex , xtrabackup , backup_path)) retcode, output = execute_cmd(cmd, output_path, exec_path, True) self.assertEqual(retcode, 0, msg = output)

From these basic tests, it will be easy to craft more complex test cases.  Creating the slave test was simply matter of adapting the initial basic test case slightly.  Our plans include: *heavy* crash testing of both xtrabackup and the server, enhancing / expanding replication tests by creating heavy randgen loads against the master during backup and slave setup, and other assorted crimes against database software.  We will also be porting the existing test suite to use dbqp entirely…who knows, we may even start working on Windows one day ; )

These tests are by no means the be-all-end-all, but I think they do represent an interesting step forward.  We can now write actual, honest-to-goodness Python code to test the server.  On top of that, we can make use of the included unittest module to give us all sorts of assertive goodness to express what we are looking for.  We will need to and plan to refine things as time moves forward, but at the moment, we are able to do some cool testing tricks that weren’t easily do-able before.

If you’d like to try these tests out, you will need the following:
* dbqp (bzr branch lp:dbqp)
* DBD:mysql installed (test tests use the randgen and this is required…hey, it is a WONDER-tool!) : )
* Innobackupex, a MySQL / Percona server and the appropriate xtrabackup binary.

The tests live in dbqp/percona_tests/xtrabackup_basic and are named basic_test.py and slave_test.py, respectively.

To run them:
$./dbqp.py –suite=xtrabackup_basic –basedir=/path/to/mysql –xtrabackup-path=/mah/path –innobackupex-path=/mah/other/path –default-server-type=mysql –no-shm

Some next steps for dbqp include:
1)  Improved docs
2)  Merging into the Percona Server trees
3)  Setting up test jobs in Jenkins (crashme / sqlbench / randgen)
4)  Other assorted awesomeness

Naturally, this testing goodness will also find its way into Drizzle (which currently has a 7.1 beta out).  We definitely need to see some Xtrabackup test cases for Drizzle’s version of the tool (mwa ha ha!) >: )

Replication in Drizzle is very simple and multi-source replication is supported. For a walk through of multi-master (multi-source) replication see David Shrewsbury’s excellent post here. Because it was very succinctly here I am quoting a lot of his provisioning a new slave post on replication here. But I have added in some detail on the slave.cfg file for clarity for newbies like me, as well as some more detail on the options and their purpose.

A lot of this can also be found in the documentation but here I’m going to walk through the steps. Also see the slave docs here for any questions you may have.

For our purposes we will walk through the features of setting up basic replication between a master and slave server.

You will need to set up your slave.cfg file before you do anything else. It should be located in the “/usr/local” directory but could also be located anywhere you like. Mine is in the /tmp/slave.cfg.

This is a typical setup.

master-host = “your ip address”
master-port = 4427
master-user = kent
master-pass = samplepassword
io-thread-sleep = 10
applier-thread-sleep = 10

Setting up the master is the next step. An important requirement is to start the master Drizzle database server with the –innodb.replication-log option, and a few other options in most circumstances. More options can be found in the options documentation. These are the most common options needed for a replication master. For example:

The InnoDB replication log must be running:

–innodb.replication-log

PID must be set:

–pid-file=/var/run/drizzled/drizzled.pid

the address binding for Drizzle’s default port (4427):

–drizzle-protocol.bind-address=0.0.0.0

The address binding for systems replicating through MySQL’s default port (3306):

–mysql-protocol.bind-address=0.0.0.0

Data Directory can be set other than default:

–datadir=$PWD/var

For more complex setups, the server id option may be appropriate to use:

–server-id=?

To run Drizzle in the background, thereby keeping the database running if the user logs out:

–daemon

So the start command looks like this on my server:

master> usr/local/sbin/drizzled \
–innodb.replication-log \
–pid-file=/var/run/drizzled/drizzled.pid \
–drizzle-protocol.bind-address=0.0.0.0 \
–mysql-protocol.bind-address=0.0.0.0 \
–daemon

Starting the slave is very similar to starting the master but there are a couple of steps before you are ready to start it up. The following is quoted from David’s blog post on simple replication.

“

1. Make a backup of the master databases.
2. Record the state of the master transaction log at the point the backup was made.
3. Restore the backup on the new slave machine.
4. Start the new slave and tell it to begin reading the transaction log from the point recorded in #2.

Steps #1 and #2 are covered with the drizzledump client program. If you use the –single-transaction option to drizzledump, it will place a comment near the beginning of the dump output with the InnoDB transaction log metadata. For example:
master> drizzledump –all-databases –single-transaction > master.backup
master> head -1 master.backup
– SYS_REPLICATION_LOG: COMMIT_ID = 33426, ID = 35074

The SYS_REPLICATION_LOG tells the slave where to start reading from. It has two pieces of information:

• COMMIT_ID: This value is the commit sequence number recorded for the most recently executed transaction stored in the transaction log. We can use this value to determine proper commit order within the log. The unique transaction ID cannot be used since that value is assigned when the transaction is started, not when it is committed.
• ID: This is the unique transaction identifier associated with the most recently executed transaction stored in the transaction log.

Now you need to start the server without the slave plugin, then import the backup from the master, then shutdown and restart the server with the slave plugin. This is straight out of the docs:

slave> sbin/drizzled –datadir=$PWD/var &
slave> drizzle < master.backup slave> drizzle –shutdown

Now that the backup is imported, restart the slave with the replication slave plugin enabled and use a new option, –slave.max-commit-id, to force the slave to begin reading the master’s transaction log at the proper location:
“

You need two options for sure, the add slave plugin and defining the slave.cfg file. So the most basic start command is:

slave> /usr/local/sbin/drizzled \
–plugin-add=slave \
–slave.config-file=/usr/local/etc/slave.cfg

A more typical startup will need more options, My startup looks like this:

slave> /usr/local/sbin/drizzled \
–plugin-add=slave \
– datadir=$PWD/var \
–slave.config-file=/usr/local/etc//slave.cfg \
–pid-file=/var/run/drizzled/drizzled.pid \
–drizzle-protocol.bind-address=0.0.0.0 \
–mysql-protocol.bind-address=0.0.0.0 \
–daemon \
– slave.max-commit-id=33426

The slave.max-commit-id is found in the dump file that we made from the master and tells the slave where to start reading from.

If you need more info for your particular setup you can view a lot of detail in the sys replication log and the innodb replication log tables that will help you with clarity.

Two tables in the DATA_DICTIONARY schema provide the different views into the transaction log: the SYS_REPLICATION_LOG table and the INNODB_REPLICATION_LOG table.

drizzle> SHOW CREATE TABLE data_dictionary.sys_replication_log\G
*************************** 1. row ***************************
Table: SYS_REPLICATION_LOG
Create Table: CREATE TABLE `SYS_REPLICATION_LOG` (
`ID` BIGINT,
`SEGID` INT,
`COMMIT_ID` BIGINT,
`END_TIMESTAMP` BIGINT,
`MESSAGE_LEN` INT,
`MESSAGE` BLOB,
PRIMARY KEY (`ID`,`SEGID`) USING BTREE,
KEY `COMMIT_IDX` (`COMMIT_ID`,`ID`) USING BTREE
) ENGINE=InnoDB COLLATE = binary

drizzle> SHOW CREATE TABLE data_dictionary.innodb_replication_log\G
*************************** 1. row ***************************
Table: INNODB_REPLICATION_LOG
Create Table: CREATE TABLE `INNODB_REPLICATION_LOG` (
`TRANSACTION_ID` BIGINT NOT NULL,
`TRANSACTION_SEGMENT_ID` BIGINT NOT NULL,
`COMMIT_ID` BIGINT NOT NULL,
`END_TIMESTAMP` BIGINT NOT NULL,
`TRANSACTION_MESSAGE_STRING` TEXT COLLATE utf8_general_ci NOT NULL,
`TRANSACTION_LENGTH` BIGINT NOT NULL
) ENGINE=FunctionEngine COLLATE = utf8_general_ci REPLICATE = FALSE

There you are, you should be up and running with your replication set up.

For more details you can always check the online documentation. And make sure you check out dshrewsbury.blogspot.com.

The Fremont beta2, version 2011.11.29, is out and ready to be tested.

In this release:
* continuing refactoring, restructuring, and code quality improvements
* many more documentation improvements
* documentation available at docs.drizzle.org
* fixes to libdrizzle .pc support
* fixes to build scripts
* additional bugs fixed

The Drizzle download file can be found here

I’m surprised and delighted to see that the Drizzle documentation was updated recently. Last time I looked, it was the original documentation which was missing, among other things, information about its 70+ plugins. So Henrik and I began filling in missing pieces of crucial information like administering Drizzle. I even generated skeleton documentation for every [...]

The entire drizzle.org domain was unavailable for about 10 hours today. This made our website, documentation, jenkins master and mail server inaccessible. On the other hand as we use public services such as Launchpad and Freenode for code repository, bug tracking, mailing list and IRC, this meant that development work continued as active as ever - in fact I think it was the most active day on IRC #drizzle channel in a while!

The DNS outage was related to our transferring of the drizzle.org domain from an individual Drizzle developer to Software in the Public Interest, Inc, our umbrella non-profit corporation. We don't know exactly why, but something went wrong between the registrars, so that the Whois record listed Tucows, the sponsoring registrar used by SPI, as the new registrar, but all other information was still pointing to the old registrar, including some Godaddy nameservers. As Godaddy eventually stopped answering DNS queries for drizzle.org - as they should - the drizzle.org domain became unavailable. 10 hours later the issue was fixed, and the correct SPI nameservers started to propagate through the DNS system. At the time of this writing, everything should have been working normally for some hours already.

And yes, in related news, drizzle.org is now transfered to the ownership of Software in the Public Interest. This is yet another step in our process of becoming a solid non-profit community project, with fiscal services provided by the SPI. So far the experience has been enjoyable and we've really felt a warm welcome into the family of SPI hosted free and open source software projects.

On that note I'd like to thank Ganneff, Solver and Hydroxide from the #spi  channel for actively helping in troubleshooting and fixing the problem today.

So I’m back from the Percona dev team’s recent meeting.  While there, we spent a fair bit of time discussing Xtrabackup development.  One of our challenges is that as we add richer features to the tool, we need equivalent testing capabilities.  However, it seems a constant in the MySQL world that available QA tools often leave something to be desired.  The randgen is a literal wonder-tool for database testing, but it is also occasionally frustrating / doesn’t scratch every testing itch.  It is based on technology SQL Server was using in 1998 (MySQL began using it in ~2007, IIRC).  So this is no knock, it is merely meant to be an example of a poor QA engineer’s frustrations ; )  While the current Xtrabackup test suite is commendable, it also has its limitations. Enter the flexible, adaptable, and expressive answer: dbqp.

One of my demos at the dev meeting was showing how we can set up tests for Xtrabackup using the unittest paradigm.  While this sounds fancy, basically, we take advantage of Python’s unittest and write classes that use their code.  The biggest bit dbqp does is search the specified server code (to make sure we have everything we should), allocate and manage servers as requested by the test cases, and do some reporting and management of the test cases.  As the tool matures, I will be striving to let more of the work be done by unittest code rather than things I have written : )

To return to my main point, we now have two basic tests of xtrabackup:

Basic test of backup + restore:

1. Populate server
2. Take a validation snapshot (mysqldump)
3. Take the backup (via innobackupex)
4. Clean datadir
5. Restore from backup
6. Take restored state snapshot and compare to original state

Slave setup

1. Similar to our basic test except we create a slave from the backup, replicating from the backed up server.
2. After the initial setup, we ensure replication is set up ok, then we do additional work on the master and compare master and slave states

One of the great things about this is that we have the magic of assertions.  We can insert them at any point of the test we feel like validating and the test will fail with useful output at that stage.  The backup didn’t take correctly?  No point going through any other steps — FAIL! : )  The assertion methods just make it easy to express what behavior we are looking for.  We want the innobackupex prepare call to run without error?
Boom goes the dynamite!:

# prepare our backup
cmd = ("%s --apply-log --no-timestamp --use-memory=500M "
"--ibbackup=%s %s" %( innobackupex
, xtrabackup
, backup_path))
retcode, output = execute_cmd(cmd, output_path, exec_path, True)
self.assertEqual(retcode, 0, msg = output)

From these basic tests, it will be easy to craft more complex test cases.  Creating the slave test was simply matter of adapting the initial basic test case slightly.  Our plans include: *heavy* crash testing of both xtrabackup and the server, enhancing / expanding replication tests by creating heavy randgen loads against the master during backup and slave setup, and other assorted crimes against database software.  We will also be porting the existing test suite to use dbqp entirely…who knows, we may even start working on Windows one day ; )

These tests are by no means the be-all-end-all, but I think they do represent an interesting step forward.  We can now write actual, honest-to-goodness Python code to test the server.  On top of that, we can make use of the included unittest module to give us all sorts of assertive goodness to express what we are looking for.  We will need to and plan to refine things as time moves forward, but at the moment, we are able to do some cool testing tricks that weren’t easily do-able before.

If you’d like to try these tests out, you will need the following:
* dbqp (bzr branch lp:dbqp)
* DBD:mysql installed (test tests use the randgen and this is required…hey, it is a WONDER-tool!) : )
* Innobackupex, a MySQL / Percona server and the appropriate xtrabackup binary.

To run them:
$./dbqp.py –suite=xtrabackup_basic –basedir=/path/to/mysql –xtrabackup-path=/mah/path –innobackupex-path=/mah/other/path –default-server-type=mysql –no-shm

Some next steps for dbqp include:
1)  Improved docs
2)  Merging into the Percona Server trees
3)  Setting up test jobs in Jenkins (crashme / sqlbench / randgen)
4)  Other assorted awesomeness

Naturally, this testing goodness will also find its way into Drizzle (which currently has a 7.1 beta out).  We definitely need to see some Xtrabackup test cases for Drizzle’s version of the tool (mwa ha ha!) >: )

The Drizzle project regularly gets people asking what they can do to get involved in the project.

One very easy way to brush up on your C++ skills and dip your toe into our open development process is to fix minor warnings.

We are very proud that Drizzle builds with zero warnings for with "gcc -Wall -Wextra".

But we can be even better!  Our JenkinsCI system has a target that is even more picky, and also a target that runs cppcheck.

Go to one of those pages, pick a build log off the build history, find a warning that you think you can fix, and then ask us in the #drizzle channel on Freenode how to send your fix to us.

After you've done that a few times, you'll be ready to fix some low hanging fruit.

We've had people graduate from this process into becoming a Google Summer of Code student, and eventually having a full time paying job hacking on Drizzle and other open source software.

And it all starts with writing a simple warning fix.

Here are the slides to my second talk at last week's Percona Live event in London:

Fixed in drizzle
View more presentations from Henrik Ingo

read more

It is finally here!

The Fremont beta is out and ready to be tested.

In this release:

• Multi-master replication (no conflict resolution)
• UUID's for replication (thanks to Joe Daly)
• JSON interface available
• Percona Innodb patches merged
• Xtrabackup in-tree
• IPV6 data type available
• query login plugin (syslog) is enabled / on by default
• Ability to publish transactions to zeromq
• Improvements to logging stats plugin
• Work on stored procedure interface
• Removal of drizzleadmin utility
• Removal of HailDB engine
• Improvements to Drizzle documentation (thanks to Daniel Nichter, Henrik Ingo, and Vijay Samuel for their efforts)
• Revamped testing system with suites of randgen, sysbench, sql-bench, and crashme tests
• Continued code refactoring

The Drizzle download file can be found here

Just wanted to blog about some of the latest updates to dbqp.  We just merged some interesting changes into Drizzle (just in time for the impending Fremont beta).  In additional to general code cleanup / reorganization, we have the following goodies:

Randgen in the Drizzle tree

One of the biggest things is that the random query generator (aka randgen) is now part of the Drizzle tree.  While I did some of the work here, the major drivers of this happening were Brian and Stewart:

1. Brian makes a fair argument that the easier / more convenient it is to run a test, the greater the likelihood of it being run.  Additional tools to install, etc = not so much.  Having something right there and ready to go = win!
2. Stewart is also a fan of convenience, lotsa testing, and working smarter, not harder.  As a result, he did the initial legwork on merging the randgen.  I do suspect there is still much for me to learn about properly bzr joining trees and whatnot, but we’ll get it right soon enough ; )

This doesn’t mean we won’t be contributing any changes we make back to the main randgen project / branch, it is strictly to facilitate more testing for Drizzle.  As we already have our randgen tests packaged into dbqp-runnable suites, running these tests is even easier : )

–libeatmydata

Another request fulfilled in this update is the ability to use Stewart’s libeatmydata to speed up testing.  By default, dbqp uses shared memory as a workdir, similar to mysql-test-run’s –mem option (this can be bypassed in dbqp with –no-shm, fyi).  However, this isn’t always perfect or desirable to do.

An alternative is to use libeatmydata, which disables fsync() calls.  As the name implies, you don’t want to use it if care about your data, but for general testing purposes, it can greatly speed up test execution.

If you have the library installed / on your machine, you can use it like so:  ./dbqp –libeatmydata [--libeatmydata-path ] …

By default, libeatmydata-path is /usr/local/lib/libeatmydata.so (as if you used make install)

Multiple server types

IMHO, this is one of the coolest new tricks.  dbqp can now handle more than just Drizzle servers / source!  The ultimate idea is to allow tests that utilize more than one type / version of a server to have more interesting tests : )  This will be useful for scenarios like testing Drizzledump migration as we can feed in one (or more) MySQL servers and a Drizzle tree and make sure we can migrate data from all of them.

We also intend to utilize dbqp for testing a variety of Percona products, and it is kind of handy to be able to run the code you are testing ; )  I already have the tool running Percona / MySQL servers and have some randgen tests working:

$ ./dbqp.py --default_server_type=mysql --basedir=/percona-server/Percona-Server --mode=randgen
Setting --no-secure-file-priv=True for randgen usage...
20111013-163443 INFO Linking workdir /dbqp/workdir to /dev/shm/dbqp_workdir_pcrews_9dbc7e8a-2872-45a9-8a07-f347f6184246
20111013-163443 INFO Using mysql source tree:
20111013-163443 INFO basedir: /percona-server/Percona-Server
20111013-163443 INFO clientbindir: /percona-server/Percona-Server/client
20111013-163443 INFO testdir: /dbqp
20111013-163443 INFO server_version: 5.5.16-rel21.0
20111013-163443 INFO server_compile_os: Linux
20111013-163443 INFO server_platform: x86_64
20111013-163443 INFO server_comment: (Percona Server with XtraDB (GPL), Release rel21.0, Revision 188)
20111013-163443 INFO Using default-storage-engine: innodb
20111013-163443 INFO Using testing mode: randgen
20111013-163443 INFO Processing test suites...
20111013-163443 INFO Found 5 test(s) for execution
20111013-163443 INFO Creating 1 bot(s)
20111013-163449 INFO Taking clean db snapshot...
20111013-163452 INFO bot0 server:
20111013-163452 INFO NAME: s0
20111013-163452 INFO MASTER_PORT: 9307
20111013-163452 INFO SOCKET_FILE: /dbqp/workdir/bot0/s0/var/s0.sock
20111013-163452 INFO VARDIR: /dbqp/workdir/bot0/s0/var
20111013-163452 INFO STATUS: 1
20111013-163506 ===============================================================
20111013-163506 TEST NAME [ RESULT ] TIME (ms)
20111013-163506 ===============================================================
20111013-163506 main.blob [ pass ] 8624
20111013-163516 main.create_drop [ pass ] 2862
20111013-163524 main.many_indexes [ pass ] 1429
20111013-163547 main.optimizer_subquery [ pass ] 17153
20111013-163558 main.outer_join [ pass ] 4243
20111013-163558 ===============================================================
20111013-163558 INFO Test execution complete in 69 seconds
20111013-163558 INFO Summary report:
20111013-163558 INFO Executed 5/5 test cases, 100.00 percent
20111013-163558 INFO STATUS: PASS, 5/5 test cases, 100.00 percent executed
20111013-163558 INFO Spent 34 / 69 seconds on: TEST(s)
20111013-163558 INFO Test execution complete
20111013-163558 INFO Stopping all running servers...

Expect to see this up and running tests against Percona Server in the next week or so.  I’ll be writing more about this soon.

Native / unittest mode

This hasn’t made it into the Drizzle tree yet.  To ease merging the code with Percona Server / Xtrabackup, I’ve created a separate launchpad project.  One of the things we needed was the ability to write complex tests directly.  It is currently easy to plug new tools into dbqp, but we essentially needed a new tool for certain testing needs.

Our solution for this was to allow dbqp to run python unittest modules.  We still have a bit of work to do before we have some demo tests ready, but we will be creating some expanded Xtrabackup tests using this system very soon.  So far, it is turning out to be pretty neat:

./dbqp.py --default_server_type=mysql --basedir=/percona-server/Percona-Server --mode=native
20111013-190744 INFO Killing pid 1747 from /dbqp/workdir/bot0/s0/var/run/s0.pid
20111013-190744 INFO Linking workdir /dbqp/workdir to /dev/shm/dbqp_workdir_pcrews_9dbc7e8a-2872-45a9-8a07-f347f6184246
20111013-190744 INFO Using mysql source tree:
20111013-190744 INFO basedir: /percona-server/Percona-Server
20111013-190744 INFO clientbindir: /percona-server/Percona-Server/client
20111013-190744 INFO testdir: /dbqp
20111013-190744 INFO server_version: 5.5.16-rel21.0
20111013-190744 INFO server_compile_os: Linux
20111013-190744 INFO server_platform: x86_64
20111013-190744 INFO server_comment: (Percona Server with XtraDB (GPL), Release rel21.0, Revision 188)
20111013-190744 INFO Using default-storage-engine: innodb
20111013-190744 INFO Using testing mode: native
20111013-190744 INFO Processing test suites...
20111013-190744 INFO Found 1 test(s) for execution
20111013-190744 INFO Creating 1 bot(s)
20111013-190749 INFO Taking clean db snapshot...
20111013-190750 INFO bot0 server:
20111013-190750 INFO NAME: s0
20111013-190750 INFO MASTER_PORT: 9306
20111013-190750 INFO SOCKET_FILE: /dbqp/workdir/bot0/s0/var/s0.sock
20111013-190750 INFO VARDIR: /dbqp/workdir/bot0/s0/var
20111013-190750 INFO STATUS: 1
20111013-190756 ===============================================================
20111013-190756 TEST NAME [ RESULT ] TIME (ms)
20111013-190756 ===============================================================
20111013-190756 main.example_test [ pass ] 1
20111013-190756 test_choice (example_test.TestSequenceFunctions) ... ok
20111013-190756 test_sample (example_test.TestSequenceFunctions) ... ok
20111013-190756 test_shuffle (example_test.TestSequenceFunctions) ... ok
20111013-190756
20111013-190756 ----------------------------------------------------------------------
20111013-190756 Ran 3 tests in 0.000s
20111013-190756
20111013-190756 OK
20111013-190756
20111013-190756 ===============================================================
20111013-190756 INFO Test execution complete in 6 seconds
20111013-190756 INFO Summary report:
20111013-190756 INFO Executed 1/1 test cases, 100.00 percent
20111013-190756 INFO STATUS: PASS, 1/1 test cases, 100.00 percent executed
20111013-190756 INFO Spent 0 / 6 seconds on: TEST(s)
20111013-190756 INFO Test execution complete
20111013-190756 INFO Stopping all running servers...

This really only scratches the surface of what can happen, but I’ll be writing more in-depth articles on what kind of tricks we can pull off as the code gets more polished.

Three non-testing bits:

1)  Percona Live London is just around the corner and members of the Drizzle team will be there.

2)  We are *this* close to Fremont beta being ready.  The contributions and feedback have been most welcome.  Any additional testing / etc are most appreciated.

3)  Drizzle is now part of the SPI!

 

Drizzle source tarball, version 2011.10.27 has been released.

In this release:

• Continued code refactoring
• Cleanup of test system
• Document cleanup

 

The Drizzle download file can be found here

Its been a while since I ve blogged but I couldn’t think of a better time to resume blogging than when Drizzle was officially became associated to Software in the Public Interest. I ve been a part of Drizzle for almost a year and a half now and my passion for Drizzle seems to grow every day. It is always good to see changes that happen for good and this is one of them I guess. Now that Drizzle is a part of SPI, it has a legal entity behind it which is always good. How can you benefit from this you may ask. If you are a US tax payer, then any donation that you make will be tax deductible and all your valuable contributions will be used towards the betterment of Drizzle. The easiest way to donate is using a credit card at Click & Pledge. The SPI website lists some alternative methods such as using a cheque.  So, please do make your valuable contributions towards Drizzle. As always I feel proud to be a part of the Drizzle family and will continue to strive for the betterment of Drizzle.