You may want to check the source of DBIx::HA.
I did a lot of that type of work, see below for a sample. Be warned though that I
think some of that code could be cleaned up now that we have
$h->{ChildHandles}. It's a lifesaver.
Also having the upgraded error handler will allow for cleaner code as well.
I also did not implement going to the proper row as Tim suggested, as I didn't
need that functionality.
if (DBIx::HA::_isactivedb ($dsn)) {
($res, $to) = &_execute_with_timeout ($dsn, $sth);
$orig_error_code = $DBI::err;
$orig_error_string = $DBI::errstr;
if ($to) {
# It was a timeout error. The database handle may be in
an unstable state, we must clear it.
# To check if the database is still active, we try to
connect to it again.
# If that succeeds, then the database is fine. If not,
then we need to fail over.
my ($dummy_dsn, $username, $auth, $attrs) =
@{$DATABASE::conf{&DBIx::HA::_getdbname($dsn)}->{'active_db'}};
my $newdbh = &DBIx::HA::_connect_with_timeout($dsn,
$username, $auth, $attrs);
if (! $newdbh) {
# Ooops. Even a new database connect fails.
It's time to reconnect and reexecute
warn "$prefix in execute: execution failed with
timeout, database unavailable. Attempting reconnect (with potential failover). Statement:
$sql ; dsn: $dsn \n" if (DBIx_HA_DEBUG);
($dsn, $sth, $res) = _reexecute ($dsn, $sql,
$sth);
} else {
# the reconnect worked, so the database is fine.
# get rid of the old database handle
warn "$prefix *** TIMEOUT! server busy: $sql ; dsn:
$dsn \n" if (DBIx_HA_DEBUG);
$dbh->swap_inner_handle($newdbh);
eval { $newdbh->disconnect; };
undef $newdbh;
}
} elsif (! defined $res) {
# We got an error code from the server upon statement
execution.
# We will let the client decide what to do and let it
be.
warn "$prefix *** ERROR: $orig_error_code;
$orig_error_string \n" if (DBIx_HA_DEBUG);
warn "$prefix in execute: bad sql: $sql ; dsn: $dsn \n"
if (DBIx_HA_DEBUG);
}
} else { # current db is not active
eval { $sth->finish; };
($dsn, $sth, $res) = _reexecute ($dsn, $sql, $sth);
}
Tim Bunce wrote:
On Wed, Jan 25, 2006 at 02:42:18PM -0500, Steven Lembark wrote:
I'm using swap_inner_handle in a HandleError sub to re-connect
the existing database handle. The trick is to re-connect if
the error warrants it, return false to the caller, which then
allows the caller to re-try the failed operation and keep
going.
Q: Do I need to use $if_active = 3 to re-prepare the cached
statement handles if I use swap_inner_handle?
The inner handle carries all the DBI info, including CachedKids.
So swap_inner_handle is a simple and total brain transplant.
my $sth = $dbh->prepare_cached( $queryz{$name}, $queryargz )
if $d = $dbh->selectall_arrayref( $sth, undef, @_ );
The selectall_* calls normally obviate the need for $if_active;
however swapping the database handles might require re-preparing
all of the file handles using
my $sth = $dbh->prepare_cached( $queryz{$name}, $queryargz, 3 )
after etching the database handle's brains with swap_inner_handle;
or setting some magic "this needs to be re-prepared the next pass"
flag on all of the statement handles?
Or is the best way to just
my $kidz = $dbh->CachedKids;
delete @{ $kidz }{ keys %$kidz };
and be done with it?
Or just $dbh->{CachedKids} = {};
Or is some similar maintainence done automatically by the
swap_inner_handle?
swap_inner_handle just does what it says - swaps handles. That's it.
I suspect what you'll need to do (for maximum transparency) is perform a
swap_inner_handle on each of the old dbh's kids to replace the now
defunct sth's with new ones freshly prepared using the new dbh.
If so then it's kind'a handy that the DBI now has a $h->{ChildHandles}
attribute.
If you really want to get fancy you could each check $old_sth->rows and
then fetch that many rows from the new $sth to leave it in the same
'place' (hopefully!) as the original. Just how mad do you want to be?
You're going to have to do some thinking and testing yourself on this
one Steven. You're the first to walk down this particular path.
Should be an interesting journey though!
Tim.
