Perl DBI MySQL failover databases

Further to the last post on Eval, here is how we can try server A, then if it fails, use server B instead. The hub of the issue is ‘or die’ , ‘or warn’ and ‘or’. Here is my test code using DBI to connect to the database.

#! /usr/bin/perl -w

use strict;
use DBI;

print "Nothing going wrong at this point...\n";
my $dbh;                                                           
$dbh = DBI->connect("DBI:mysql:database_name:localhost","database_username","database_password", {PrintError => 1, RaiseError => 0});
print "\$dbh is $dbh\n";

I’ve changed details to protect the innocent here, but the output from the script is:

Nothing going wrong at this point...
$dbh is DBI::db=HASH(0x801271480)

Next I change the the localhost address to black_hole which will make it fail. Note that we have set the DBI built in error trapping to PrintError => 1, RaiseError => 0. This is explicitly telling DBI to emulate the ‘or warn’ and not ‘or die’, we ant the error to be thrown and the script to carry on. To clarify, PrintError ~ warn and RaiseError ~ die. Our output now looks like this:

Nothing going wrong at this point...
DBI connect('database_name:black_hole','database_username',...) failed: Unknown MySQL server host 'black_hole' (1) at ./dbitest.pl line 9
Use of uninitialized value $dbh in concatenation (.) or string at ./dbitest.pl line 11.
$dbh is

All good so far. using the same ‘short circuit’ behaviour we see in the the ‘or die/warn’ syntax we can add another DBI option in like this:

#! /usr/bin/perl -w

use strict;
use DBI;

print "Nothing going wrong at this point...\n";
my $dbh;

$dbh = DBI->connect("DBI:mysql:database_name:black_hole","database_username","database_password", {PrintError => 1, RaiseError => 0}) or
$dbh = DBI->connect("DBI:mysql:database_name:localhost","database_username","database_password", {PrintError => 0, RaiseError => 1});
print "\$dbh is $dbh\n";

The output is as follows:

Nothing going wrong at this point...
DBI connect('database_name:black_hole','database_username',...) failed: Unknown MySQL server host 'black_hole' (1) at ./dbitest.pl line 9
$dbh is DBI::db=HASH(0x801266948)

So the first connection failed, the second succeeded. Notice I used the PrintError => 1 option with the primary and and the RaiseError = 1 option for the secondary. This is because I want the program to stop if it can’t connect to anything at all.

Ah! But what about eval ???

We can wrap the eval bits discussed previously with the DBI/warn/die info above. The problem with the initial solution is that its pretty limited in what you can do when you see a failure. I’d like to know that the first server is not allowing MySQL connections, also maybe i’d like to start logging by a completely different method entirely? I’ve re-written the test script now to include and eval function and provide just the functionality I need:

#! /usr/bin/perl -w

use strict;
use DBI;

print "Nothing going wrong at this point...\n";
my $dbh;
$dbh = eval{DBI->connect("DBI:mysql:database_name:black_hole","database_username","database_password", {RaiseError => 1, PrintError => 0})};

if($@){
        print "Eval caught this: $@";
        $dbh = $dbh = DBI->connect("DBI:mysql:database_name:localhost","database_username","database_password", {RaiseError => 1});
        # code to send alert email? 
}

print "\$dbh is $dbh\n";

Which all outputs:

Nothing going wrong at this point...
Eval caught this: DBI connect('datanase_name:black_hole','database_username',...) failed: Unknown MySQL server host 'black_hole' (1) at ./dbitest.pl line 8
$dbh is DBI::db=HASH(0x80116a0c0)

Another happy day!