Hi there,
Way back in December 2012 I posted a query to this list with the subject
"Possible database handle leak in DBI?". There were a few replies to my
query, which can be found at
https://groups.google.com/forum/#!topic/perl.dbi.users/nElyR6hFka8
but no conclusive answer.
The lack of follow up was partly because we had our Christmas/summer break
and then in the new year there were other problems to deal with. Also the
issue seemed to occur less frequently, so tracking it down got put on the
backburner, where it has remained until now...
Recently the problem has been occurring more frequently again, causing our
mysql server to run out of connections. We've papered over the problem by
increasing the maximum number of connections and restarting our web server
each night. But it would be much more satisfying to figure out what is
going on here!
Quite a few things have changed since my post of 15 months ago (newer versions
of DBI, DBD::mysql, mysql etc, plus our Apache now uses fcgid rather than
fastcgi). I know I should give all the relevant version numbers, but given that
the problem has persisted over multiple versions I'm not sure that would help
all that much.
Instead I'll describe the one new fact that my recent debugging efforts have
turned up. Hopefully this may allow someone with a deeper understanding
of the perl DBI and/or DBD::mysql packages to suggest what might be happening
here, or at least some more debugging strategies I could try...
First, a brief recap. We have a web-based application that runs as a FosWiki
plugin under Apache/fcgid. The database connection used by this application
should be persistent due to the application calling DBI->connect_cached() on
each invocation.
What we see is that most of the time this works correctly, but every so
often the call to connect_cached() doesn't find the cached connection and
so creates a new one. This is despite a "mysql processlist" showing that
the old connection *is* still active.
The new piece of information that I have discovered recently is that on a
call to connect_cached() which *doesn't* return the cached database handle,
the database driver handle reference passed into connect_cached() has changed.
Since the dbh cache is obtained from "$drh->{CachedKids} ||= {}" I can now
understand why the cached handled is not found!
What I can't understand is why the driver handle passed into connect_cached()
has changed! Especially since none of our application code knows or cares
about the existence of the driver handle (and as stated in the DBI pod, the
driver handle object is rarely seen or used in applications).
I've got 1000's of lines of debugging showing this happening. Some of it
is my own, inserted as print statements or cluck calls directly into DBI.pm
and some of it the standard DBI debugging set to level 9. There is way too
much to include in this message, but I've included some small extracts below
to illustrate what I am seeing.
>From these small extracts I don't expect anyone to be able to debug this
for me. What I am hoping for though is perhaps some insight into the
circumstances in which the driver handle could change in this way.
One thing I did just notice is that our application calls connect_cached()
in the way shown in the DBI pod - that is:
my $dbh = DBI->connect_cached(...)
I don't know enough about perl internals to know exactly what this does.
But I did wonder if something like the following might be better given the
persistent nature of our application provided by fcgid.
my $dbi = new DBI;
my $dbh = $dbi->connect_cached(...)
Any other advice would be greatly appreciated.
Thanks,
Duncan
Extract from Debugging:
DBI::dr=HASH(0x7f7fe874fee8) trace level set to 0x0/9 (DBI @ 0x0/0) in DBI
1.630-ithread (pid 18887)
DBI 1.630-ithread default trace level set to 0x0/9 (pid 18887 pi
7f7ff7703000) at DBI.pm line 1494 via UM.pm line 191
...
No cached handle - creating new connection, dbh =
DBI::db=HASH(0x7f7fe86f8468)
...
DBI::dr=HASH(0x7f7fe874fee8) trace level set to 0x0/9 (DBI @ 0x0/0) in DBI
1.630-ithread (pid 18887)
DBI 1.630-ithread default trace level set to 0x0/9 (pid 18887 pi
7f7ff7703000) at DBI.pm line 1494 via UM.pm line 450
...
Using cached handle, dbh = DBI::db=HASH(0x7f7fe86f8468)
...
<lots of repetitions of the above>
...
DBI::dr=HASH(0x7f7fe299c5e8) trace level set to 0x0/9 (DBI @ 0x0/0) in DBI
1.630-ithread (pid 18887)
DBI 1.630-ithread default trace level set to 0x0/9 (pid 18887 pi
7f7ff7703000) at DBI.pm line 1494 via UM.pm line 191
...
No cached handle - creating new connection, dbh =
DBI::db=HASH(0x7f7fe2819db0)
...
