DBI: connect to another database if the first database does not exist

Question

I am trying to sort our old code that serves two purposes. It uses DBI to create the database, and then uses DBI to connect to this database. Unfortunately, he used the same code for each. This means that if you create a sales database, and then when you reconnect, you must explicitly call $dbh->do('use sales') . This leads to various problems, such as developers forgetting to do this, or the database descriptor reconnects and forgets which database it was in.

What we're trying to do as a first-pass fix is ​​for the DBI::connect() HandleError to use HandleError to reconnect to MySQL if the database does not exist, which allows us to create the database. For various inherited reasons (yes, we were all there) it is much harder to try to catch the "Unknown database" error outside of the connect() method.

So my first pass when 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); } 

This works, and it is currently transparent to the end user, but it also feels like a steaming bunch of ones and zeros. Is there a better way to reconnect to another database if the connection fails?

Answer 1

I'm not sure if this will work, but it might be preferable to use 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); }