Bug 39329 - mod_dbd reconnection and prepared statements
Summary: mod_dbd reconnection and prepared statements
Status: RESOLVED LATER
Alias: None
Product: Apache httpd-2
Classification: Unclassified
Component: mod_dbd (show other bugs)
Version: 2.2.0
Hardware: All All
: P2 normal with 1 vote (vote)
Target Milestone: ---
Assignee: Apache HTTPD Bugs Mailing List
URL:
Keywords: MassUpdate, PatchAvailable
Depends on:
Blocks:
 
Reported: 2006-04-17 23:10 UTC by Matthew Kent
Modified: 2018-11-07 21:09 UTC (History)
3 users (show)



Attachments
patch to retry for a valid connection (1.71 KB, patch)
2006-04-17 23:11 UTC, Matthew Kent
Details | Diff
Patch to retry a problem connection (2.24 KB, patch)
2006-05-08 00:09 UTC, Nick Kew
Details | Diff
now with two connection checks (2.38 KB, patch)
2006-05-18 18:18 UTC, Matthew Kent
Details | Diff
Reprepare Statements after autoreconnect (2.43 KB, patch)
2006-10-11 04:50 UTC, Eike Frost
Details | Diff
mod_dbd.c patched to invalidate the reslist of all stale connections (27.24 KB, text/plain)
2007-09-11 19:44 UTC, daniel qian
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Matthew Kent 2006-04-17 23:10:20 UTC
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.
Comment 1 Matthew Kent 2006-04-17 23:11:34 UTC
Created attachment 18116 [details]
patch to retry for a valid connection
Comment 2 Nick Kew 2006-04-18 01:34:38 UTC
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? 
Comment 3 Ruediger Pluem 2006-04-18 21:03:45 UTC
(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?

Comment 4 Nick Kew 2006-04-18 22:05:36 UTC
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. 
Comment 5 Matthew Kent 2006-04-18 22:52:39 UTC
(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.
Comment 6 Nick Kew 2006-05-08 00:06:38 UTC
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. 
Comment 7 Nick Kew 2006-05-08 00:09:04 UTC
Created attachment 18244 [details]
Patch to retry a problem connection
Comment 8 Matthew Kent 2006-05-18 18:16:40 UTC
(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)
Comment 9 Matthew Kent 2006-05-18 18:18:36 UTC
Created attachment 18318 [details]
now with two connection checks
Comment 10 Eike Frost 2006-10-11 04:50:19 UTC
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).
Comment 11 Matthew Kent 2007-07-06 18:33:07 UTC
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.
Comment 12 daniel qian 2007-08-22 07:33:50 UTC
(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? 
Comment 13 daniel qian 2007-09-11 19:44:18 UTC
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.
Comment 14 Ruediger Pluem 2007-09-12 11:48:34 UTC
Please provide a patch instead of the full mod_dbd.c (see also
http://httpd.apache.org/dev/patches.html)
Comment 15 Peter Poeml 2008-08-07 13:25:15 UTC
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?
Comment 16 William A. Rowe Jr. 2018-11-07 21:09:49 UTC
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.