You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Tyler MacDonald <ty...@yi.org> on 2006/01/31 22:23:12 UTC
mod_perl2 DBI handle freshining problem solved "once and for all"...
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
Re: mod_perl2 DBI handle freshining problem solved "once and for all"...
Posted by Tim Bunce <Ti...@pobox.com>.
On Tue, Jan 31, 2006 at 01:23:12PM -0800, Tyler MacDonald wrote:
> 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.
It shouldn't need to because the DBI's connect_cached() only returns a
handle if it's Active and ping() returns true. Neither should be true
for a disconnected handle.
Apache::DBI::connect also checks the cached handle using ping. The ping
can be tuned via Apache::DBI->setPingTimeOut so it's only called once
every few seconds. Perhaps some code somewhere has done that and that's
why you're seeing problems.
> Anyways, here's what I've done to solve the problem (i'm using postgres):
>
> - always use connect_cached, don't use Apache::DBI
connect_cached actually calls Apache::DBI::connect behind the scenes
if Apache::DBI is loaded first (as per the docs).
> - use this as my PerlPostConfigHandler:
> [...]
> - use this as my PerlPreConnectionHandler:
> [...]
Seems like hack without actually identifying the source of any problem
in the existing DBI or Apache::DBI code.
With a little more digging I'm sure you could identify _why_ Apache::DBI::connect
is giving you back a dead handle. Fixing that would 'help save the world' for
countless other users. Can you try that?
Tim.
Re: mod_perl2 DBI handle freshining problem solved "once and for
all"...
Posted by Clinton Gormley <cl...@traveljury.com>.
> Okay, thanks for the report and the patch. We definitely want this
> functionality to work. I haven't used Apache::DBI recently, but the
> current code was working fine for me on mp1. Can you file this bug
> report and patch on the RT queue for Apache::DBI on CPAN? That would
> make it easier to track.
Will do - whenever I've filed bugs and patches in RT (not for mod_perl
modules) I've had zero response - so I didn't bother.
>
> Also, this is only an issue if you use PerlPostConfigRequire, isn't it?
> I thought you said in your previous mail that it was still okay if you
> use PerlRequire to load your startup.pl (and are doing database work
> there).
No, my original patch was only tested with PerlRequire, and I was
inserting a PerlPostConfigHandler. But then when you I user
PerlPostConfigRequire instead, it didn't work because it had already
passed the PostConfig phase.
________________________________________________________________________
Clinton Gormley clinton@traveljury.com
www.TravelJury.com - For travellers, By travellers
Re: mod_perl2 DBI handle freshining problem solved "once and for
all"...
Posted by Perrin Harkins <pe...@elem.com>.
On Thu, 2006-02-02 at 00:43 +0100, Clinton Gormley wrote:
> That is now what I have seen in mp2. In fact the code that (according
> to the comments) is supposed to do that, does nothing but avoid
> connecting to the DB on the very first start. (when restart count == 1)
>
> The parent server immediately restarts and those connections ARE cached
> and lead to the problems of shared handles.
Okay, thanks for the report and the patch. We definitely want this
functionality to work. I haven't used Apache::DBI recently, but the
current code was working fine for me on mp1. Can you file this bug
report and patch on the RT queue for Apache::DBI on CPAN? That would
make it easier to track.
Also, this is only an issue if you use PerlPostConfigRequire, isn't it?
I thought you said in your previous mail that it was still okay if you
use PerlRequire to load your startup.pl (and are doing database work
there).
- Perrin
Re: mod_perl2 DBI handle freshining problem solved "once and for
all"...
Posted by Clinton Gormley <cl...@traveljury.com>.
> Apache::DBI doesn't cache handles that you open during startup or
> prevent you from calling disconnect on them. If you have it loaded
> correctly and you don't call disconnect on handles opened in startup,
> you should see warnings about the handles being DESTROYed without
> disconnect. If you don't see those, it means you are caching the
> handles somewhere in your own code.
That is now what I have seen in mp2. In fact the code that (according
to the comments) is supposed to do that, does nothing but avoid
connecting to the DB on the very first start. (when restart count == 1)
The parent server immediately restarts and those connections ARE cached
and lead to the problems of shared handles.
A few days ago I proposed a patch to Apache::DBI which handles the issue
for mp2:
--- Apache/DBI.pm.orig 2006-01-29 01:41:55.000000000 +0100
+++ Apache/DBI.pm 2006-01-29 01:43:34.000000000 +0100
@@ -11,6 +11,8 @@
require mod_perl2;
require Apache2::Module;
require Apache2::ServerUtil;
+ my $s = Apache2::ServerUtil->server;
+ $s->push_handlers(PerlChildInitHandler => \&childinit);
}
elsif (defined $modperl::VERSION && $modperl::VERSION > 1 &&
$modperl::VERSION < 1.99) {
@@ -45,13 +47,7 @@
sub connect_on_init {
# provide a handler which creates all connections during server
startup
- if (MP2) {
- if (!@ChildConnect) {
- my $s = Apache2::ServerUtil->server;
- $s->push_handlers(PerlChildInitHandler => \&childinit);
- }
- }
- else {
+ unless (MP2) {
carp "Apache.pm was not loaded\n" and return unless
$INC{'Apache.pm'};
if (!@ChildConnect and Apache->can('push_handlers')) {
Apache->push_handlers(PerlChildInitHandler => \&childinit);
@@ -188,6 +184,7 @@
sub childinit {
my $prefix = "$$ Apache::DBI ";
print STDERR "$prefix PerlChildInitHandler \n" if
$Apache::DBI::DEBUG > 1;
+ %Connected = ();
if (@ChildConnect) {
for my $aref (@ChildConnect) {
shift @$aref;
Re: mod_perl2 DBI handle freshining problem solved "once and for
all"...
Posted by Perrin Harkins <pe...@elem.com>.
Tyler MacDonald wrote:
>> DBI will replace it, removing all references to the one that failed to
>> ping, and it will go out of scope and get DESTROY'ed.
>
> And what if you don't want to reconnect? The possibly still
> connected, possibly not handle is still left around in {CachedKids}.
Why would you call connect_cached() if you don't want to reconnect?
>> When is this? DBI checks the handle when you call connect_cached, and
>> if it fails to ping, the old one is replaced in the cache and sent off
>> to garbage collection. If it isn't getting garbage-collected, that
>> probably means you have it in global or closure somewhere.
>
> And what if the connect fails?
It's the same as any other connect failure: you get an error message and
possibly a croak if you have RaiseError on.
- Perrin
Re: mod_perl2 DBI handle freshining problem solved "once and for all"...
Posted by Tyler MacDonald <ty...@yi.org>.
Perrin Harkins <pe...@elem.com> wrote:
> > And here's some more conjecture: What if the ping just times out
> > because the server is really, really busy?
> DBI will replace it, removing all references to the one that failed to
> ping, and it will go out of scope and get DESTROY'ed.
And what if you don't want to reconnect? The possibly still
connected, possibly not handle is still left around in {CachedKids}.
> When is this? DBI checks the handle when you call connect_cached, and
> if it fails to ping, the old one is replaced in the cache and sent off
> to garbage collection. If it isn't getting garbage-collected, that
> probably means you have it in global or closure somewhere.
And what if the connect fails?
(Sorry to keep bombarding you with conjecture... I'd just rather
hear "this is bulletproof or can be bulletproofed" than "this is expected to
screw up"...)
> > $dbh->transaction(sub {
> > ...
> > return $do_i_want_to_commit ? 1 : 0;
> > });
> >
> > especially now that I can nest them arbitrarily deep.
>
> If you wrap those in an eval and never do anything outside of one, it
> should work. I personally avoid doing things in sub refs because it
> caused so many problems for me when using Error's try/catch syntax.
That's what the 'transaction' method does; the code is run inside an
eval; if the sub ref returns true, it's committed, if it returns false or
raises an exception, it's rolled back.
> Well, the hack may work for you, and that may be all that matters in
> your case, but I don't think it's right to make other people think they
> need to use that instead of Apache::DBI or connect_cached.
I didn't!!! Tim told me to save the world!!! :)
- Tyler
Re: mod_perl2 DBI handle freshining problem solved "once and for
all"...
Posted by Perrin Harkins <pe...@elem.com>.
On Wed, 2006-02-01 at 14:06 -0800, Tyler MacDonald wrote:
> What if you
> can't ping the connection because of a temporary TCP/IP problem
[...]
> And here's some more conjecture: What if the ping just times out
> because the server is really, really busy?
DBI will replace it, removing all references to the one that failed to
ping, and it will go out of scope and get DESTROY'ed.
> I don't know if it does an explicit disconnect() itself in this
> case, but I do know the handle reference (which is essentally garbage at
> this point) is left laying around.
When is this? DBI checks the handle when you call connect_cached, and
if it fails to ping, the old one is replaced in the cache and sent off
to garbage collection. If it isn't getting garbage-collected, that
probably means you have it in global or closure somewhere.
> > I issue a rollback on all active connections at the end of every request.
> > Everyone who uses transactions at all should do that.
>
> I wrote DBIx::Transaction specifically so I wouldn't have to worry
> about that anymore. ;-) It's so much easier just to
>
> $dbh->transaction(sub {
> ...
> return $do_i_want_to_commit ? 1 : 0;
> });
>
> especially now that I can nest them arbitrarily deep.
If you wrap those in an eval and never do anything outside of one, it
should work. I personally avoid doing things in sub refs because it
caused so many problems for me when using Error's try/catch syntax.
> Yeah, I ditched Apache::DBI early in diagnosing this problem. Then I
> wrote that hack to solve it, which is what has started this whole thread. I
> was happy just posting the hack and leaving it at that, but you guys keep
> egging me on ;-)
Well, the hack may work for you, and that may be all that matters in
your case, but I don't think it's right to make other people think they
need to use that instead of Apache::DBI or connect_cached.
> They were done one at a time. To the best of my memory:
Sorry, I can't make a useful guess based on this. For one thing, you
would need to do them separately, in order to know which ones made the
difference.
- Perrin
Re: mod_perl2 DBI handle freshining problem solved
"once and for all"...
Posted by Henri Asseily <he...@shopzilla.com>.
>>> Apache::DBI overrides disconnect() to be a no-op, and
>>> connect_cached()
>>> doesn't. (But Apache::DBI doesn't do this during startup.)
>
> It would be reasonable for Apache::DBI to provide a way for
> applications
> to call disconnect() and have it actually disconnect.
>
If you want another hack :-), check out the source of DBIx::HA. I had
to go through a number of hoops to get around the Apache::DBI obsession
about holding on to the cached handle.
First you need to create a reverse lookup table:
---
if ($Apache::DBI::VERSION < 0.89) {
die "$prefix Requirement unmet. Apache::DBI must be at version 0.89 or
above";
}
# create a cached lookup table for finding the Apache::DBI cache key
index from the dsn
$DBIx::HA::ApacheDBIidx{$_->[0]} = _getApacheDBIidx(@$_);
Apache::DBI->setPingTimeOut($_->[0],
$DATABASE::conf{$dbname}->{'pingtimeout'} || -1);
}
sub _getApacheDBIidx {
# generates the ApacheDBI cache idx key from the passed dsn info
# first generate the same $idx key entry as ApacheDBI does
my @args = map { defined $_ ? $_ : "" } @_;
if ($args[0] =~ /^dbi:/i) { $args[0] =~ s/^dbi:[^:]+://io; }; # remove
the dbi:driver: piece
my $idx = join $;, $args[0], $args[1], $args[2];
if (3 == $#args and ref $args[3] eq "HASH") {
map { $idx .= "$;$_=$args[3]->{$_}" } sort keys %{$args[3]};
}
return $idx;
}
---
Then when you want to wipe the cached handle from Apache::DBI:
---
my $ApacheDBIConnections = Apache::DBI::all_handlers();
delete $$ApacheDBIConnections{$DBIx::HA::ApacheDBIidx{$olddsn}} if
($DBIx::HA::ApacheDBIidx{$olddsn});
---
Re: mod_perl2 DBI handle freshining problem solved "once and for all"...
Posted by Tim Bunce <Ti...@pobox.com>.
On Wed, Feb 01, 2006 at 02:06:22PM -0800, Tyler MacDonald wrote:
> Perrin Harkins <pe...@elem.com> wrote:
>
> > You have to expect certain basic things to work, and one of them is that a
> > connection which can't be ping'ed is not holding a table lock.
>
> I completely disagree. Here's some more conjecture: What if you
> can't ping the connection because of a temporary TCP/IP problem (eg;
> somebody tripping over the network cable)... the driver's ping timeout could
> easily expire before the TCP/IP stack's timeout expires, the cable coule be
> plugged back in, and then TCP/IP's happy, DBI experienced a ping timeout,
> but the connection is not technically "closed". If this can happen on my SSH
> sessions when my daughter yanks on my network cable, it could certainly
> happen on a database driver.
>
> And here's some more conjecture: What if the ping just times out
> because the server is really, really busy?
>
> > If it is, this is a much lower-level bug than DBI should try to deal with.
>
> If DBI doesn't plan on using a cached database handle anymore, it
> should at least disconnect() it and remove it's dangling reference from
> CachedKids. I don't know if it does an explicit disconnect() itself in this
> case, but I do know the handle reference (which is essentally garbage at
> this point) is left laying around.
I agree that it would be reasonable for $dbh->disconnect() to remove
the handle from CachedKids.
> > > When I just let a database handle fall off the face of the earth I
> > > generally get a warning like "DESTROY: issuing ROLLBACK for handle
> > > destroyed without disconnect". So it seems like a part of that logic is
> > > already in the new DBI.
>
> > Apache::DBI overrides disconnect() to be a no-op, and connect_cached()
> > doesn't. (But Apache::DBI doesn't do this during startup.)
It would be reasonable for Apache::DBI to provide a way for applications
to call disconnect() and have it actually disconnect.
> Yeah, I ditched Apache::DBI early in diagnosing this problem. Then I
> wrote that hack to solve it, which is what has started this whole thread. I
> was happy just posting the hack and leaving it at that, but you guys keep
> egging me on ;-)
Like detectives egging on the chief witness :)
Tim.
Re: mod_perl2 DBI handle freshining problem solved "once and for all"...
Posted by Tyler MacDonald <ty...@yi.org>.
Perrin Harkins <pe...@elem.com> wrote:
> > Yes and no. If you can't ping the server, but the TCP socket is
> > still open, that means you essentially have this TCP connection to the
> > server that's not being used, in an open state, for the rest of the lifetime
> > of your apache server instance. This could be a Bad Thing, say, if it's in
> > mid-transaction, keeping a table lock open...
> Sorry, but this sounds like total conjecture to me.
I completely agree. :)
> You have to expect certain basic things to work, and one of them is that a
> connection which can't be ping'ed is not holding a table lock.
I completely disagree. Here's some more conjecture: What if you
can't ping the connection because of a temporary TCP/IP problem (eg;
somebody tripping over the network cable)... the driver's ping timeout could
easily expire before the TCP/IP stack's timeout expires, the cable coule be
plugged back in, and then TCP/IP's happy, DBI experienced a ping timeout,
but the connection is not technically "closed". If this can happen on my SSH
sessions when my daughter yanks on my network cable, it could certainly
happen on a database driver.
And here's some more conjecture: What if the ping just times out
because the server is really, really busy?
> If it is, this is a much lower-level bug than DBI should try to deal with.
If DBI doesn't plan on using a cached database handle anymore, it
should at least disconnect() it and remove it's dangling reference from
CachedKids. I don't know if it does an explicit disconnect() itself in this
case, but I do know the handle reference (which is essentally garbage at
this point) is left laying around.
> I issue a rollback on all active connections at the end of every request.
> Everyone who uses transactions at all should do that.
I wrote DBIx::Transaction specifically so I wouldn't have to worry
about that anymore. ;-) It's so much easier just to
$dbh->transaction(sub {
...
return $do_i_want_to_commit ? 1 : 0;
});
especially now that I can nest them arbitrarily deep.
> > When I just let a database handle fall off the face of the earth I
> > generally get a warning like "DESTROY: issuing ROLLBACK for handle
> > destroyed without disconnect". So it seems like a part of that logic is
> > already in the new DBI.
> Apache::DBI overrides disconnect() to be a no-op, and connect_cached()
> doesn't. (But Apache::DBI doesn't do this during startup.)
Yeah, I ditched Apache::DBI early in diagnosing this problem. Then I
wrote that hack to solve it, which is what has started this whole thread. I
was happy just posting the hack and leaving it at that, but you guys keep
egging me on ;-)
> > I do know that after refactoring my code to not use Apache::DBI at
> > all, and not depend on connect_cached() to behave properly, (adding the
> > PostConfig and PreConnection handlers to be very paranoid about what happens
> > to the handles), the problem has gone away.
> That's too many changes at once to draw any conclusions.
They were done one at a time. To the best of my memory:
- Not using Apache::DBI came first. That made it so that after about
a dozen refreshes my database handles were OK again.
- PreConnection handler came next, along with wrapping my
disconnect() call in an eval{}. That brought it down to 2 or 3 refreshes.
- The PostConfig handler came next, along with deleting the dangling
references from CachedKids. I'm not sure if the CachedKids things helped,
but it certainly doesn't do any harm so I left it in. So these last two
changes may need to be diagnosed further.
> It currently checks to see if you've loaded Apache::DBI and sends all
> connect()/connect_cached() calls there, so that would have to be taken
> out to avoid a perpetual feedback loop.
Aaaah. :)
- Tyler
Re: mod_perl2 DBI handle freshining problem solved "once and for
all"...
Posted by Perrin Harkins <pe...@elem.com>.
On Wed, 2006-02-01 at 13:22 -0800, Tyler MacDonald wrote:
> The point here was that I *wanted* the disconnect() to take place.
> So I made sure Apache::DBI wasn't loaded when disconnect() was called before
> the fork, so that it wouldn't trump my attempt to disconnect the handle
> owned by the parent process.
Apache::DBI doesn't cache handles that you open during startup or
prevent you from calling disconnect on them. If you have it loaded
correctly and you don't call disconnect on handles opened in startup,
you should see warnings about the handles being DESTROYed without
disconnect. If you don't see those, it means you are caching the
handles somewhere in your own code.
> Perhaps, but how would that explain this paradox:
>
> - After receving this error, calling a disconnect() on the handle
> says "handle already disconnected", yet:
That could mean it was marked inactive when something tried to use the
broken handle and failed (giving you the error).
> - After attempting to re-connect() or connect_cache() to the
> database, the *new* handle also exhibits the same screwed up beahviour?
That would require some debugging to figure out. I would start by
checking to see if the handle is actually new or just the same one for
some reason.
> > For this purpose, "connected" and "pingable" are the same thing.
>
> Yes and no. If you can't ping the server, but the TCP socket is
> still open, that means you essentially have this TCP connection to the
> server that's not being used, in an open state, for the rest of the lifetime
> of your apache server instance. This could be a Bad Thing, say, if it's in
> mid-transaction, keeping a table lock open...
Sorry, but this sounds like total conjecture to me. You have to expect
certain basic things to work, and one of them is that a connection which
can't be ping'ed is not holding a table lock. If it is, this is a much
lower-level bug than DBI should try to deal with.
I issue a rollback on all active connections at the end of every
request. Everyone who uses transactions at all should do that.
> > Apache::DBI could be changed to rely on connect_cached internals, but it
> > pre-dates connect_cached by years. It also does important things that
> > connect_cached doesn't do, like automatic rollbacks and voiding
> > disconnect() and not caching handles during startup. (I didn't want to
> > the disconnect behavior, which is why I stopped using Apache::DBI.)
>
> When I just let a database handle fall off the face of the earth I
> generally get a warning like "DESTROY: issuing ROLLBACK for handle destroyed
> without disconnect". So it seems like a part of that logic is already in the
> new DBI.
Apache::DBI overrides disconnect() to be a no-op, and connect_cached()
doesn't. (But Apache::DBI doesn't do this during startup.)
> > At this point, I think we don't know what the problem was with your
> > database connections, and I'm not convinced it was a result of an
> > Apache::DBI bug.
>
> I do know that after refactoring my code to not use Apache::DBI at
> all, and not depend on connect_cached() to behave properly, (adding the
> PostConfig and PreConnection handlers to be very paranoid about what happens
> to the handles), the problem has gone away.
That's too many changes at once to draw any conclusions.
> > I do think it would be cool to change the code in Apache::DBI to use
> > connect_cached for some of the caching mechanics, although that would
> > require changes in DBI as well.
>
> Hmm. What changes in DBI?
It currently checks to see if you've loaded Apache::DBI and sends all
connect()/connect_cached() calls there, so that would have to be taken
out to avoid a perpetual feedback loop.
- Perrin
Re: mod_perl2 DBI handle freshining problem solved "once and for all"...
Posted by Tyler MacDonald <ty...@yi.org>.
Perrin Harkins <pe...@elem.com> wrote:
> > I am opening a handle before apache forks. However, I was
> > able to verify that Apache::DBI wasn't loaded yet at that point (no
> > $INC{'Apache/DBI.pm'}), and I was issuing a disconnect() before the fork
> > took place.
> If Apache::DBI wasn't loaded yet at that point, it won't work. It has
> to load before DBI in order to work.
The point here was that I *wanted* the disconnect() to take place.
So I made sure Apache::DBI wasn't loaded when disconnect() was called before
the fork, so that it wouldn't trump my attempt to disconnect the handle
owned by the parent process.
> > such luck. Inexplicably, I'd get a dead handle back, that said something
> > like "postgres server shutting down during query" (I don't have the exact
> > error message anymore, but it was something like that).
> That doesn't sound like a dead handle to me. It sounds like a handle
> that is active, but won't work because it is being used in multiple
> processes.
Perhaps, but how would that explain this paradox:
- After receving this error, calling a disconnect() on the handle
says "handle already disconnected", yet:
- After attempting to re-connect() or connect_cache() to the
database, the *new* handle also exhibits the same screwed up beahviour?
> > ... so *then* I tried explicitly calling disconnect() on
> > these handles if ping() fails. disconnect() would fail because the handle
> > was "already disconnected", and the handle did *not* disappear out of
> > CachedKids.
> As Tim pointed out, it doesn't need to be removed from CachedKids for
> this to work.
Fair enough, but I still think it should always be removed from
CachedKids on disconnect(), even if the handle is already disconnected: to
prevent any driver-specific buffers it had from sitting around forever, or
it's DESTROY from being called at an unexpected time way down the road where
a potential error won't have enough context to debug, etc...
> > Maybe Apache::DBI should push a PostConfigHandler into the
> > server that disconnects *every* DBI handle active before apache forks?
> It already has code that prevents handles opened during startup from
> being cached at all. If that code has bugs, it should be fixed. It's
> possible that a PostConfigHandler is more robust, but I don't think
> there's any equivalent for mp1.
Hmm. I'll chat with gozer about that and see if there's a way to
hack similar functionality in for mp1.
> > When Apache::DBI refreshes it's database handle [~line 127],
> > it doesn't look like it's disconnecting the old one. What if the old handle
> > is in a connected, but unpingable state?
> For this purpose, "connected" and "pingable" are the same thing.
Yes and no. If you can't ping the server, but the TCP socket is
still open, that means you essentially have this TCP connection to the
server that's not being used, in an open state, for the rest of the lifetime
of your apache server instance. This could be a Bad Thing, say, if it's in
mid-transaction, keeping a table lock open...
> Apache::DBI could be changed to rely on connect_cached internals, but it
> pre-dates connect_cached by years. It also does important things that
> connect_cached doesn't do, like automatic rollbacks and voiding
> disconnect() and not caching handles during startup. (I didn't want to
> the disconnect behavior, which is why I stopped using Apache::DBI.)
When I just let a database handle fall off the face of the earth I
generally get a warning like "DESTROY: issuing ROLLBACK for handle destroyed
without disconnect". So it seems like a part of that logic is already in the
new DBI.
> > Is there an easy way to enumerate *all* cached database
> > handles in DBI?
> See ChildHandles in a recent DBI.
Ah, I missed that. So installed_drivers only returns drivers you've
actually use()d at some point, and gives me handles for each? That's awesome
:)
> At this point, I think we don't know what the problem was with your
> database connections, and I'm not convinced it was a result of an
> Apache::DBI bug.
I do know that after refactoring my code to not use Apache::DBI at
all, and not depend on connect_cached() to behave properly, (adding the
PostConfig and PreConnection handlers to be very paranoid about what happens
to the handles), the problem has gone away.
> I do think it would be cool to change the code in Apache::DBI to use
> connect_cached for some of the caching mechanics, although that would
> require changes in DBI as well.
Hmm. What changes in DBI?
Thanks,
Tyler
Re: mod_perl2 DBI handle freshining problem solved "once and for
all"...
Posted by Perrin Harkins <pe...@elem.com>.
On Wed, 2006-02-01 at 11:27 -0800, Tyler MacDonald wrote:
> I am opening a handle before apache forks. However, I was
> able to verify that Apache::DBI wasn't loaded yet at that point (no
> $INC{'Apache/DBI.pm'}), and I was issuing a disconnect() before the fork
> took place.
If Apache::DBI wasn't loaded yet at that point, it won't work. It has
to load before DBI in order to work.
> So then I got rid of Apache::DBI entirely, deciding to rely
> entirely on DBI->connect_cached to do it's own pinging and reconnecting.
I have recently switched to doing it that way too.
> No
> such luck. Inexplicably, I'd get a dead handle back, that said something
> like "postgres server shutting down during query" (I don't have the exact
> error message anymore, but it was something like that).
That doesn't sound like a dead handle to me. It sounds like a handle
that is active, but won't work because it is being used in multiple
processes.
> ... so *then* I tried explicitly calling disconnect() on
> these handles if ping() fails. disconnect() would fail because the handle
> was "already disconnected", and the handle did *not* disappear out of
> CachedKids.
As Tim pointed out, it doesn't need to be removed from CachedKids for
this to work.
> Maybe Apache::DBI should push a PostConfigHandler into the
> server that disconnects *every* DBI handle active before apache forks?
It already has code that prevents handles opened during startup from
being cached at all. If that code has bugs, it should be fixed. It's
possible that a PostConfigHandler is more robust, but I don't think
there's any equivalent for mp1.
> Is
> there a valid use case for having the same DBI handle replicated across a
> bunch of apache child processes?
I think the Sybase driver can do this safely under certain conditions,
but generally no.
> When Apache::DBI refreshes it's database handle [~line 127],
> it doesn't look like it's disconnecting the old one. What if the old handle
> is in a connected, but unpingable state?
For this purpose, "connected" and "pingable" are the same thing.
> Why is Apache::DBI maintaining it's own cache in the first
> place? Shouldn't DBI->connect_cached be able to maintain that for us?
Apache::DBI could be changed to rely on connect_cached internals, but it
pre-dates connect_cached by years. It also does important things that
connect_cached doesn't do, like automatic rollbacks and voiding
disconnect() and not caching handles during startup. (I didn't want to
the disconnect behavior, which is why I stopped using Apache::DBI.)
> Is there an easy way to enumerate *all* cached database
> handles in DBI?
See ChildHandles in a recent DBI.
> As far as Tim's comment about "saving the world", I never
> realised that would become a full time job of it's own. ;-) I'm willing to
> refactor Apache::DBI when I have time (maybe even tonight).
At this point, I think we don't know what the problem was with your
database connections, and I'm not convinced it was a result of an
Apache::DBI bug. I do think it would be cool to change the code in
Apache::DBI to use connect_cached for some of the caching mechanics,
although that would require changes in DBI as well.
- Perrin
Re: mod_perl2 DBI handle freshining problem solved "once and for all"...
Posted by Tyler MacDonald <ty...@yi.org>.
Perrin Harkins <pe...@elem.com> wrote:
> I've never seen Apache::DBI or connect_cached return a dead handle. I
> have had problems in the past with forking apps where I get back a
> handle that still pings but has been shared across processes so it no
> longer really works. Were you doing something with forking? Or maybe
> opening handles during startup before apache forks?
Perrin, Ask Bjoern, Tim,
I am opening a handle before apache forks. However, I was
able to verify that Apache::DBI wasn't loaded yet at that point (no
$INC{'Apache/DBI.pm'}), and I was issuing a disconnect() before the fork
took place.
So then I got rid of Apache::DBI entirely, deciding to rely
entirely on DBI->connect_cached to do it's own pinging and reconnecting. No
such luck. Inexplicably, I'd get a dead handle back, that said something
like "postgres server shutting down during query" (I don't have the exact
error message anymore, but it was something like that).
... so *then* I tried explicitly calling disconnect() on
these handles if ping() fails. disconnect() would fail because the handle
was "already disconnected", and the handle did *not* disappear out of
CachedKids.
Thus my solution in a previous message, which Tim so fondly
called a "hack". :) It works in an extremely paranoid manner, wrapping both
the ping and disconnect methods inside an eval(), and explicitly deleting
the entry from CachedKids itself.
Going forward:
I think disconnect() should remove the handle from
{CachedKids} even if the handle is already disconnected. Common sense
dictates that you don't want an already disconnected handle to stick around
in your cache.
Maybe Apache::DBI should push a PostConfigHandler into the
server that disconnects *every* DBI handle active before apache forks? Is
there a valid use case for having the same DBI handle replicated across a
bunch of apache child processes?
When Apache::DBI refreshes it's database handle [~line 127],
it doesn't look like it's disconnecting the old one. What if the old handle
is in a connected, but unpingable state?
Why is Apache::DBI maintaining it's own cache in the first
place? Shouldn't DBI->connect_cached be able to maintain that for us? Which
leads me to:
Is there an easy way to enumerate *all* cached database
handles in DBI? The closest i've been able to come is to enumerate the
{CachedKids} of a specific driver handle, which I grab by using the
(undocumented) DBI->driver() method.
As far as Tim's comment about "saving the world", I never
realised that would become a full time job of it's own. ;-) I'm willing to
refactor Apache::DBI when I have time (maybe even tonight). After browsing
through it's code, I must warn you that while I would keep the interface the
same, I would change it's behaviour a fair bit (mainly the points listed
above). If this is cool with everyone, I'll proceed... if not, there's I'll
stick with my hack, there's plenty of other code I could be writing/fixing.
:)
- Tyler
Re: mod_perl2 DBI handle freshining problem solved "once and for
all"...
Posted by Perrin Harkins <pe...@elem.com>.
Tyler MacDonald wrote:
> 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.
I've never seen Apache::DBI or connect_cached return a dead handle. I
have had problems in the past with forking apps where I get back a
handle that still pings but has been shared across processes so it no
longer really works. Were you doing something with forking? Or maybe
opening handles during startup before apache forks?
- Perrin