Hi,
We are using DBI (v1.620) and DBD::mysql (v4.021) on a NetBSD system to access
a remote MySQL database (v5.1.62) from a locally written application running
under perl v5.14.2 using fastcgi (v0.74).
Our application uses the DBI connect_cached() method so that it can reuse
a persistent database connection for each invocation.
Occasionally we get a mysql "too many connections" error. Investigation (via
lsof and "mysqladmin processlist) shows that many of the 15 fastcgi processes
running our script have 10 or more open connections to our database server.
The max number of connections our server is set to accept is 151 (I assume
this is the default).
Because, for unrelated reasons, our web server is reloaded each night, which
restarts the fastcgi processes, the max limit is only occasionally hit (perhaps
once every 1-2 weeks). But periodically checking with lsof or mysqladmin
usually shows a number of the fastcgi processes with several open connections.
The problem is because we are not sure what circumstances trigger the number
of connections to grow we don't know how to prevent the occasional "too many
connections" error. To prevent any suggestion of the obvious, the connection
parameters/attributes are *definitely* the same for each connect_cached()
call so that isn't the reason why the exiting open handle couldn't be used.
I've unsuccessfully searched the archives of this list for any mention of a
database handle leak (apologies if I missed one). I also tried various general
google searches with no results.
So I am now trying to debug this myself. So far I've tried two approaches.
Firstly, in our application, each time it called connect_cached() I dumped out
the contents of $dbh->{Driver}->{CachedKids} and the value of the "Active"
attribute for each of the cached handles. That showed that there was only
ever at most one cached entry, but the db HASH value that got printed out
did occasionally change.
My next debugging attempt involved adding some print statements to the
DBD::_::dr::connected_cached() routine in DBI.pm. These attempted to determine
if there was no matching item in the cache, or if there was a matching item,
which of $dbh->FETCH('Active') or $dbh->ping was causing it to not be used.
I was expecting it to be $dbh->FETCH('Active') returning 0, but in fact
this debugging indicated that it is the former case - ie: no matching
cache entry.
Unfortunately I don't understand DBI.pm well enough to know how an item
could drop out of the cache while the corresponding database connection
remains active.
Can anyone enlighten me as to whether this sounds like a bug in DBI/DBD or
whether there is some intended behaviour occurring that I don't understand?
If the former, any tips on how to debug further would be greatly appreciated.
Thanks,
Duncan
