DBI: Connect to a different database if the first database doesn't exist

Question

I'm trying to sort our some legacy code that serves two purposes. It uses DBI to create a database and later uses DBI to connect to that database. Unfortunately, it used the same code for each. That means that if you create a sales database, later, when use reconnect, you must explicitly call $dbh->do('use sales'). That leads to all sorts of problems, such as developers forgetting to do that or the database handle reconnecting and forgetting which database it was in.

What we're trying to do as a first pass fix is to have the DBI::connect() method use HandleError to reconnect to MySQL if the database doesn't exist, thus allowing us to create the database. For various legacy reasons (yeah, we've all been there), it's much harder to try to trap the "Unknown database" error outside of the of the connect() method.

Thus, my first pass at solving this is as follows:

use strict;                                                                                                                                               
use warnings;
use DBI;
use PadWalker 'peek_my';
my $dbh = DBI->connect(
    $dsn,
    $user,
    $pass,
    {   RaiseError  => 1,
        PrintError  => 0,
        HandleError => \&reconnect_if_unknown_database,
    },
);

sub reconnect_if_unknown_database {
    my ($msg, $drh, $dbh) = @_;
    return unless $msg =~ /Unknown database/;

    my ($dsn, $user, $pass, $attr) = @{peek_my(1)}{qw/$dsn $user $pass $attr/};

    unless ($dsn && $user && $pass && $attr) {
        return;    # don't do this if we can't get everything
    }

    # they're all scalar refs.
    $_ = $$_ foreach $dsn, $user, $pass, $attr;

    unless ($dsn =~ s/^[^;]+;/DBI:mysql:mysql;/) {
        return;    # can't parse dsn, so return
    }
    delete $attr->{HandleError};    # infinite loops tickle

    $_[2] = DBI->connect($dsn, $user, $pass, $attr);
}

That works and it's currently transparent to the end user, but it also feels like a steaming pile of ones and zeros. Is there a better way of reconnecting to a different database on connect failure?

Eleazar

A complete test case would be a big help. Without a clear explanation of "used the same code for each" it's hard to be sure how to help. – Blackboard

Tim: I guess a better description would have been "can I get DBI to reconnect on failure, changing only the DSN?" I don't know if there's a better way of doing this. – Eleazar

Answer 1

I am not sure if this would work, but it might be preferable to using PadWalker:

use strict;use warnings;
use DBI;

my %attr = (RaiseError => 1, PrintError => 0);

my $dbh = DBI->connect(
    $dsn,
    $user,
    $pass,
    {
        %attr,
        HandleError => sub {
            reconnect_if_unknown_database(
                $dsn, $user, $pass, \%attr, @_
            )
        },
    },
);

sub reconnect_if_unknown_database {
    my ($dsn, $user, $pass, $attr, $msg, $drh, $dbh) = @_;

    return unless $msg =~ /Unknown database/;

    return unless $dsn =~ s/^[^;]+;/DBI:mysql:mysql;/;

    $_[-1] = DBI->connect($dsn, $user, $pass, $attr);
}

Director

You know what they say just another level of indirection ;-) – Lavatory

Probably should put @_ at the end of the list, since you don't have any real control of its size. – Mothering