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 second 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.