Asynchronous MySQL Queries in Perl Using DBD::mysql and AnyEvent

A lot of people use MySQL, and these days, asynchronous-style programming has really taken off. If you're involved in both of these camps, you may be wondering
how to send a query to MySQL and have it inform your event loop when it's ready with the results of that query. A common solution is to use a thread or child process
for each connection, and exchange data using IPC. However, if you're using Perl and DBD-mysql 4.019 or better, you have an alternative: the new asynchronous interface.

Using the new async flag that you can provide to the prepare method, along with the new mysql_fd method, it's fairly easy to have MySQL play nice with AnyEvent.
Here's a simple example:

#!/usr/bin/env perl

use strict;
use warnings;
use feature 'say';

use AnyEvent;
use DBI;

my $dbh = DBI->connect('dbi:mysql:', undef, undef, {
    PrintError => 0,
    RaiseError => 1,
});

my $cond = AnyEvent->condvar;

my $sth = $dbh->prepare('SELECT SLEEP(10), 3', { async => 1 });
$sth->execute;

my $timer = AnyEvent->timer(
    interval => 1,
    cb       => sub {
        say 'timer fired!';
    },
);

my $mysql_watcher = AnyEvent->io(
    fh   => $dbh->mysql_fd,
    poll => 'r',
    cb   => sub {
        say 'got data from MySQL';
        say join(' ', $sth->fetchrow_array);
        $cond->send;
    },
);

$cond->recv;

undef $sth;
$dbh->disconnect;

This script will print "timer fired!" about once a seconds for ten seconds, then "got data from MySQL", and finally "0 3", which is the data from our SELECT statement. Obviously, this example is pretty trivial, but you could easily do this with multiple MySQL connections.

(cross-posted from my blog)

10 Comments

That makes it easy to play with just about any eventy system, including POE, Reflex, and IO::Async. In fact, here's your sample program ported to Reflex: https://github.com/rcaputo/reflex/blob/master/eg/eg-20-mysql.pl

> cpanm DBD::mysql
DBD::mysql is up to date. (4.020)

still get DBD::mysql::db prepare failed: Async support was not built into this version of DBD::mysql at async_mysql.pl line 17.

working on Strawberry Perl on Windows

Sadly it seems that asynch support doesn't only not work on windows, it seems to have actively broken any and all error handling, making DBD::mysql act more like PHP than anything else. (Then again, MySQL is the PHP among databases.)

Please do take a look here: http://bugs.activestate.com/show_bug.cgi?id=91426

This is very cool!

@Mithaldu
just can confirm this bug after installing the new version, going back to 4.016

Email me at walde.christian@googlemail.com and i'll send you login data for a VNC server on a Win7 machine. :)

If you're going to use this idea, be aware that it's only a half-baked solution.

For this to work reliably, the file descriptor itself still needs to be in blocking mode, so that writes and reads to it are not interrupted if too much data is being written, or not enough is available to be read yet. This means that actually writing the query, and reading the response once it has arrived, are both done in a purely blocking way, and it's only the part where you wait for the start of a response to be available that is done asynchronously. You still run the risk of blocking during either writing the query, or reading the response.

A more robust solution would be to either use an actual asynchronous DBI library, if one exists for your event system + database, or failing that use the fork()-based compartmentising feature of the event system, to move all the DBI accesses into a child process so as to achieve full asynchronous communication with the main program.

Leave a comment

About Rob Hoelz

user-pic I'm a Perl programmer from Wisconsin. https://hoelz.ro