I'm using mod_dbd with persistant mysql connections and another custom module to do vhost document root lookups against a database. The problems is that my mod_dbd connections reach the MySQL daemons wait_timeout of 600 seconds and are closed automatically. ap_dbd_acquire fails for these resources and returns NULL which leaves me without a db connection to work with. Attached is a suggested patch to invalidate the bad resources and try to acquire another a good one, which should be a newly opened and valid connection.
Created attachment 18116 [details] patch to retry for a valid connection
I'm not sure this is safe: the potential problem is that your while() loop never terminates, if some backend returns SUCCESS on acquire but some other status on check_conn. I can't think of a realistic case, but that's not the point. I think I'd be happy with a variant on the patch that keeps a count and bails out explicitly if things get silly. Anyone else?
(In reply to comment #2) > > I think I'd be happy with a variant on the patch that keeps a count and bails > out explicitly if things get silly. Anyone else? Sounds reasonable to me. What about about the maximum number of entries in the resource list as upper limit for the counter?
DBDKeep is surely a maximum. The problem in question isn't going to apply to servers around peak load. The implication of looping would seem to be tearing down and opening lots of connections all at once - could be a Bad Thing. IMHO A better fix - which alas can't be easily retrofitted - would be to return an EAGAIN status and leave the application to retry.
(In reply to comment #4) > DBDKeep is surely a maximum. The problem in question isn't going to apply to > servers around peak load. > > The implication of looping would seem to be tearing down and opening lots of > connections all at once - could be a Bad Thing. IMHO A better fix - which > alas can't be easily retrofitted - would be to return an EAGAIN status and > leave the application to retry. That would be nice. Just to give you an idea of how your code is being used; these servers are under fairly heavy load but the lookup results are stored in memcached and eventually the db connections begin to idle long enough to get killed off. Then every minute or so a bad resource is acquired and nuked, adds a bit of fluff to the error log, but works pretty well overall. Thanks for looking at this so quickly.
Just revisiting this, I hacked (In reply to comment #0) > I'm using mod_dbd with persistant mysql connections and another custom module to > do vhost document root lookups against a database. > > The problems is that my mod_dbd connections reach the MySQL daemons wait_timeout > of 600 seconds and are closed automatically. Come to think of it, I thought mysql_ping (as used in the check_conn function) would reopen the connection if it had timed out. ISTR it doing that for me when I tested it. Adding a new patch (untested). Not decided whether to test and commit it.
Created attachment 18244 [details] Patch to retry a problem connection
(In reply to comment #6) > Just revisiting this, I hacked (In reply to comment #0) > > I'm using mod_dbd with persistant mysql connections and another custom > module to > > do vhost document root lookups against a database. > > > > The problems is that my mod_dbd connections reach the MySQL daemons > wait_timeout > > of 600 seconds and are closed automatically. > > Come to think of it, I thought mysql_ping (as used in the check_conn function) > would reopen the connection if it had timed out. ISTR it doing that for me > when I tested it. > > Adding a new patch (untested). Not decided whether to test and commit it. Just tested the latest patch and it almost works but bumps into an issue I've run into with mysql before: when a connection is closed the first mysql_ping will always fail and the handle needs a second command to actually reopen the connection despite what the manual says (http://dev.mysql.com/doc/refman/5.0/en/mysql-ping.html). Made a slight change to your patch and its working fine in testing so far. Also, as noted in the manual page, as of 5.0.3 MYSQL_OPT_RECONNECT is not on by default, the driver needs to set it after mysql_init like mysql_options(&mysql, MYSQL_OPT_RECONNECT, &my_true)
Created attachment 18318 [details] now with two connection checks
Created attachment 18989 [details] Reprepare Statements after autoreconnect This patch adds one line attempting to recreate prepared statements after a reconnection. This appears to fix the issue, although more errorchecking may be neeeded. (Patch is working nicely on 2.2.3).
Just retested this with mod_dbd from trunk (r553563) and apr-util 1.2.7 + current apr_dbd_mysql.c compiled against MySQL 5.0.27 and everything works as expected with no modifications. Reconnection no longer seems to be an issue as the need for 2 mysql_pings seems to have dissapeared, see the last couple replies in http://bugs.mysql.com/bug.php?id=9271 but I believe for any earlier MySQL versions it will still be required. Now the bad part is since mysql_ping in MySQL 5.0.27 (5.0.20 and later I think) doesn't return an error anymore, just reconnects transparently, and since prepared statements are cleared as per http://www.mysql.org/doc/refman/5.0/en/auto-reconnect.html I'm not sure how you can detect that a mysql_ping has done a reconnection and reinit your prepared statements. I suppose you'd need to track the id of the mysql connection and look for a change before and after calling apr_dbd_check_conn.
(In reply to comment #11) > Just retested this with mod_dbd from trunk (r553563) and apr-util 1.2.7 + > current apr_dbd_mysql.c compiled against MySQL 5.0.27 and everything works as > expected with no modifications. Reconnection no longer seems to be an issue as > the need for 2 mysql_pings seems to have dissapeared, see the last couple replies in > http://bugs.mysql.com/bug.php?id=9271 > but I believe for any earlier MySQL versions it will still be required. > Now the bad part is since mysql_ping in MySQL 5.0.27 (5.0.20 and later I think) > doesn't return an error anymore, just reconnects transparently, and since > prepared statements are cleared as per > http://www.mysql.org/doc/refman/5.0/en/auto-reconnect.html > I'm not sure how you can detect that a mysql_ping has done a reconnection and > reinit your prepared statements. I suppose you'd need to track the id of the > mysql connection and look for a change before and after calling apr_dbd_check_conn. I tried the patch and found something might be a performance issue. this part of the patch : + if ((rv == APR_SUCCESS) || (rv == APR_ENOTIMPL)) { + rv = dbd_prepared_init(pool, svr, rec); + break; + } gets executed even if the first apr_dbd_check_conn returns a success. Is this on purpose?
Created attachment 20794 [details] mod_dbd.c patched to invalidate the reslist of all stale connections The mod_dbd.c is taken from the trunk before being patched. It simply recreate a resource if all the connection resource in a reslist fail check_conn. Re-prepare the statement doesnt seem to completely fix the problem as mysql_ping doesnt seem to restore the connection handle either. Re-create all resource wont be a problem because it only happens once for every idle timeout. this patch requires auto-reconnect to be turned off for mysql. Now it works on all my servers.
Please provide a patch instead of the full mod_dbd.c (see also http://httpd.apache.org/dev/patches.html)
I may be mistaking something, but it seems to me this bug should be fixed with the fix for bug 45407. What do you think?
Please help us to refine our list of open and current defects; this is a mass update of old and inactive Bugzilla reports which reflect user error, already resolved defects, and still-existing defects in httpd. As repeatedly announced, the Apache HTTP Server Project has discontinued all development and patch review of the 2.2.x series of releases. The final release 2.2.34 was published in July 2017, and no further evaluation of bug reports or security risks will be considered or published for 2.2.x releases. All reports older than 2.4.x have been updated to status RESOLVED/LATER; no further action is expected unless the report still applies to a current version of httpd. If your report represented a question or confusion about how to use an httpd feature, an unexpected server behavior, problems building or installing httpd, or working with an external component (a third party module, browser etc.) we ask you to start by bringing your question to the User Support and Discussion mailing list, see [https://httpd.apache.org/lists.html#http-users] for details. Include a link to this Bugzilla report for completeness with your question. If your report was clearly a defect in httpd or a feature request, we ask that you retest using a modern httpd release (2.4.33 or later) released in the past year. If it can be reproduced, please reopen this bug and change the Version field above to the httpd version you have reconfirmed with. Your help in identifying defects or enhancements still applicable to the current httpd server software release is greatly appreciated.