Apache::DBI and DBI's connect_cached both claim to keep your database
handles fresh and happy, but I've had numerous problems trying to get either
of them to work properly. If a database connection is dropped, sometimes I'd
have to refresh the page two or three times before the "internal server
error" goes away and my webpages are happily reconnected to the database
again.
Part of the problem seems to be that disconnecting a database handle doesn't
always remove it from {CachedKids}. At least, in the case where DBI thinks
it's already disconnected, it doesnt bother removing it.
Anyways, here's what I've done to solve the problem (i'm using postgres):
- always use connect_cached, don't use Apache::DBI
- use this as my PerlPostConfigHandler:
sub PostConfig {
my $drh = DBI->driver("Pg");
my $n = 0;
while(my($k, $v) = each(%{$drh->{CachedKids}})) {
eval { $v->disconnect if $v->{Active} };
delete $drh->{CachedKids}->{$k};
$n++;
}
warn "$$: Cleared $n postgresql database handles before forking.\n" if $n;
return DECLINE_CMD;
}
- use this as my PerlPreConnectionHandler:
sub DisconnectDead {
my $drh = DBI->driver("Pg");
my $n = 0;
while(my($k, $v) = each(%{$drh->{CachedKids}})) {
if(! eval { $v->ping }) {
eval { $v->disconnect if $v->{Active} };
delete $drh->{CachedKids}->{$k};
$n++;
}
}
warn "$$: Cleared $n stale postgresql database handle(s).\n" if $n;
return DECLINE_CMD;
}
This seems reasonably robust, after doing something like:
# ps ax | grep "postmaster: pmx" | grep -v grep | awk '{print $1}' | xargs kill
The first reload is a bit sluggish, but always works.
Cheers,
Tyler
