A Quicky Queue

Q4m is a lovely looking package which brings queue semantics to MySQL rather than having to use another server (say RabbitQM) for your queues. However being a good open source project installation is a challenge. I'm not one to back away from a challenge though. So this is how I managed to install Q4m on my Mac. First some points of interest

• Don't install it on your Mac. Create a virtual Linux box using VirtualBox
• Install a decent distro on there like Debian (I'm assuming Debian 6; Squeeze)
• Make sure MySQL 5.1 is installed

Assuming you've done that you will want to install the following packages

apt-get install mysql-server-5.1 libmysqld-dev libssl-dev g++ libmysqlclient-dev gcc4.1 libdata-compare-perl liblist-moreutils-perl

You will be asked for the root user password here. Keep it blank for the moment if you are using this guide as a way of evaluating q4m. If not then please set it with sensible password.

Then it's off to get the MySQL source (I've used 5.1.49 here so change according the version of MySQL you had installed) and q4m (0.9.5). Then we'll un-tar the archives

cd $HOME

wget http://downloads.mysql.com/archives/mysql-5.1/mysql-5.1.49.tar.gz

wget http://q4m.kazuhooku.com/dist/q4m-0.9.5.tar.gz

tar zxvf mysql-5.1.49.tar.gz

tar zxvf q4m-0.9.5-tar.gz

cd q4m-0.9.5

Once you're in this situation you are so nearly there :). You'll need to configure and then make like so

./configure --with-mysql="$HOME/mysql-5.1.49" --prefix="$HOME/mysql-5.1.49" CPPFLAGS="-I/usr/include/mysql"

make

Assuming everything worked you will want to install the plugin to MySQL, run some scripts and then some tests. Doing this as root will make things work.

su

cp src/.libs/libqueue_engine.so /usr/lib/plugin/.

cp support-files/q4m-forward /usr/bin/.

mysql --user=root < support_files/install.sql

services mysql restart

DBI='dbi:mysql:database=test;host=127.0.0.1;port=3306' DBI_USER='root' DBI_PASSWORD='' ./run_tests.pl

Assuming everything is good you should see a lot of messages flying up the screen and a 100% pass rate. Congratulations on install q4m. Go have a coffee; you deserve it.

Red Deadlock Redemption - MySQL Edition

Howdy there. It has been over a year since I last put finger to keyboard and brought to you my tales of woe. Well here comes another one but this is about MySQL and deadlocks which are fast becoming my nemesis. MySQL seems to deadlock at the slightest of things. Too many processes, high contention tables, flies farting in Greenland; all seem to be a cause.

My latest deadlock problem was from trying to emulate Oracle sequences in MySQL.Sequences are most commonly used to emulate MySQL's auto-incrementing key except they have the advantage that they are not linked to a single table. They can also increment at a defined step increase and are atomic. The work I have been doing required this feature; the ability to atomically reserve a range of identifiers. So a table structure was defined (auto increment identifier, name of the sequence and the last assigned value) & the code was written but this left one very big problem. How do you seed the table? A naive approach was formulated

1. Select for update the sequence in question
2. If it existed then update the row with the new value
3. If not then insert a seed row

All well & good until a user reported back saying this procedure was deadlocking. How on Earth? I checked the row existed, if it did not then I inserted the data. All of this was carried out in one transaction so what was going wrong. You can find the condensed version of the logic from Gist which also causes a deadlock.

Well +1 for being able to replicate the issue; it always helps. So why do we get this failure? Well it is all because of InnoDB locking model and this little comment nestled in the depths of MySQL's documentation (thank you Tim Starling).

Note that if you use SELECT FOR UPDATE to perform a uniqueness check before an insert, you will get a deadlock for every race condition unless you enable the innodb_locks_unsafe_for_binlog option. A deadlock-free method to check uniqueness is to blindly insert a row into a table with a unique index using INSERT IGNORE, then to check the affected row count.

What what what?!? So what does this locking disabling do? Well it turns off next-key locking which combines index locking with gap locking in an attempt to avoid phantom reads (rows which appear in a table when you should have locked them normally caused by inserting new rows). So in the example process 1 and 2 locked the sequence table for rows which did not exist and to make sure no new rows appear. This stops both processes from inserting into the table so one has to fail.

Thankfully there are three options to resolve this. The first is to perform an insert ignore into the table before selecting for update. This will block the other process and the code cannot deadlock. The second way is to pre-populate the table with rows which we can then select for update. Number three is to retry the transaction & hope the deadlock does not re-appear.

All are valid but option two is my preferred solution since it cuts out have to perform an insert I know will fail every-time I want a new identifier range. If the schema & data was a bit more dynamic then option one would be the best solution. Option 3 has the feeling of being a band-aid but I concede sometimes thats all you can do.

Anyway one deadlock down. Another to go but that will have to wait for another time

Try::Tiny - 'Lightweight' try{} catch{} finally{}

As a lot of people will know error handling in Perl normally amounts to

my $val = eval { do_something_evil()};
die "Something evil happened: $@" if $@;

Not too much of a problem here except this (in some very odd cases) will not correctly trap the error and it means writing cleanup code can be hard (whilst trying not to clobber the value of $@ at the same time). This is not an easy thing to do so enter Try::Tiny which is a fantastic module giving you full try catch & finally support in Perl. Meaning the above code looks more like

my $val = try {

do_something_evil();

}

catch {

die "Something evil happened: $_";

};

Since I am a Java programmer first this syntax looks more natural to me. What is really impressive though is that finally support has now been added allowing us to do:

my $val = try {

do_something_evil();

}

catch {

die "Something evil happened: $_";

}

finally {

warn 'But first maybe some cleanup';

};

This is a perfect tool for those situations where local just does not cut it (say resetting a value in an object no matter what happens to the code).

Anyway next time you want to do error handling in Perl have a look at Try::Tiny; you won't regret it (and if you're wondering why I'm recommending this have a look at Try::Tiny's commit history on github & you may see why).

TAP That!

TAP (Test Anything Protcol) is Perl's simple test output format for reporting (amongst other things) success & failure of test assertions. This output is what you see printed to screen

ok 1 - Hello

not ok 2 - Nothing right

One thing I wanted to do was to build an object which can be run in two modes

• As a normal test script (using Test::More)
• As an object which can assert all tests are good (and confess if anything goes wrong)

So how can I do this? Thankfully Test::More delegates to Test::Builder::new (which is a singleton instance) for its assertion code. The builder has an output method which when passed a scalar reference Test::Builder will write the TAP output to that scalar. Couple this with TAP::Parser we can scan the TAP output and confess accordingly like so.

use Test::Builder;

use Test::More;

use Carp;

my $tap;

my $t = Test::Builder->new->output(\$tap);

ok(1, 'Hello');

fail('FAIL');

done_testing();

my $tap_parser = TAP::Parser->new({

tap => $tap

});

while ( my $result = $tap_parser->next() ) {

if(!$result->is_ok()) {

confess('Error during tests: '.$result->as_string());

}

print $result->as_string(), "\n";

}

Bingo! Now I'm sure there's a module somewhere which already does this but I like how easy this is.

Renaming a MySQL database without having access to datafiles

If you've used MySQL then you will know this situation; your database name is wrong & MySQL does not support any kind of command like

rename database my_db to my_real_name;

In fact the only way to do this is to rename the directory name the database files are held in on the data store; or so I thought. Turns out that the rename table command comes to the rescue with this beautiful line

As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:

Giving us something quite simple looking like this:

RENAME TABLE my_db.tbl_name TO my_real_name.tbl_name;

Pin this together with a loop over all tables in a schema & bob is your uncle you've got a database re-namer without going anywhere near the datafiles. The thing that confuses me is why MySQL dropped support for the rename function (which is still documented) when they could have implemented it like this; I'm sure they had their reasons.

Operator Precedence

It's that time again when I mention something that's already in the perldocs; this one is about operator precedence. Anyway today I found out that something like

my @a = qw(1);
print 'Yippeee!' if ( ! scalar(@a) >= 2 );

What does this print out? Yippeee!? Nope. The problem here is operator precedence. You may read it as the count of @a being greater than or equal to 2 and negate this (after all that's what ! normally means). Well what actually gets evaluated is ! count and then if this result is >= 2. Possible solutions to this are

print 'Yippeee!' if ( ! (scalar(@a) >= 2) );
print 'Yippeee!' if ( scalar(@a) < 2);

So the first deals with it by adding () to make sure the evaluation order is respected, the second replaces it for a more logical test.

What is the take home message? Everything in Perl has a precedence order.

each() in Perl can be dangerous

I'm a Java programmer by trade and one of the first things that is hammered into us (right after never using + for multiple string concatenation) is how to correctly iterate through a Map. Now the reason why is if you want to iterate through a Map's contents using keys only and then performing the value retrieval using its get() method then that is really inefficient. Java's hash implementations are good but considering the majority of the time keys are stored with their values in the backing datastructures; to get the pair out at the same time makes sense. So the first time you come to Perl you think

how on earth do I iterate through a hash?

and then you see the each() method. This gives rise to the following code:

while( my ($key, $value) = each(%my_hash)) {
  print $key, '->', $value, "\n";
}

This all seems fine & dandy until we want to do something like this:

our %my_hash = (a=>1,b=>2);
my @elements = get_from_somewhere();
print "Starting\n";
OUTERLOOP: foreach my $el (@elements) {
  while( my $key, $value) = each(%my_hash)) {
    print $key, "\n";
    if($key eq 'a') {
      last OUTERLOOP;
    }
  }
}

Still seems okay right? Wrong! See the problem here comes from iteration of the EntrySet belonging to the hash & not to another data structure so on multiple invocations of this code the iteration of the map continues from where it left-off. So if the elements from the hash are returned in the order a,b & we execute it twice you will see:

Starting
a
Starting
b
a

To get to the correct expected output you need:

our %my_hash = (a=>1,b=>2);
my @elements = get_from_somewhere();
print "Starting\n";
OUTERLOOP: foreach my $el (@elements) {
  foreach my $key (keys %my_hash) {
    my $value = $my_hash{$key};
    print $key, "\n";
    if($key eq 'a') {
      last OUTERLOOP;
    }
  }
}

So what's the solution? Well if I said never use each then I would be stupid & wrong. each makes sense. What I would recommend is if you might break out of a loop using a data structure where you may require early termination of a hash structure which is persistent between calls then do not use each. Or use one of the Iterator functions/modules in CPAN to provide a more Java like method of access to hashes; but then that wouldn't be Perl :).