Bug 45407 - auto reconnect in apr_dbd_mysql disturb normal work with prepared statements
Summary: auto reconnect in apr_dbd_mysql disturb normal work with prepared statements
Alias: None
Product: APR
Classification: Unclassified
Component: APR (show other bugs)
Version: HEAD
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: Apache Portable Runtime bugs mailinglist
Depends on:
Reported: 2008-07-16 04:49 UTC by Marko Kevac
Modified: 2009-10-08 15:46 UTC (History)
5 users (show)

Introduce reconnection option to MySQL DBD driver (2.69 KB, patch)
2008-07-21 16:39 UTC, Bojan Smojver
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Marko Kevac 2008-07-16 04:49:53 UTC
Auto reconnect option in apr_dbd_mysql is set to 1:

#if MYSQL_VERSION_ID >= 50013
    my_bool do_reconnect = 1;

This forces mysql_ping(), which is called by dbd_mysql_check_conn(), to reconnect if connection to MySQL server was lost.

But lost connection also means that all prepared statements are lost. And because statements are prepared only when dbd_construct() is called, one can not use prepared statements any more.

I think we should set do_reconnect to 0 by default.
Comment 1 Marko Kevac 2008-07-16 04:53:17 UTC
dbd_construct() is from mod_dbd

Anyway. There are no way to know if mysql_ping() reconnected to MySQL server or if connection was just fine.

So we don't know if we should prepare statements one more time.
Comment 2 Bojan Smojver 2008-07-21 02:09:46 UTC
I think dbd_construct() should remember if it successfully prepared that statement in the past (i.e. if the statement was generally good). When _pselect() fails and we had a good prepared statement, then this means that prepared statement was lost due to reconnect, so it should be prepared again.

Does that make sense?
Comment 3 Marko Kevac 2008-07-21 04:28:24 UTC
Well, this will probably work, but I think that it is not so good solution.

First, when pselect() fails, it gives error 2013 (CR_SERVER_LOST), but connection exists and select() works well. This is a little bit confusing and it is not clear for user whether he should reconnect or he just needs to prepare statements one more time.

Second, thing you are talking about should be implemented not only in mod_dbd, but also in modules, that prepare statements by themself, but use mod_dbd for pools and easy API. It's not good design I think.

What do you think?
Comment 4 Bojan Smojver 2008-07-21 04:35:54 UTC
>First, when pselect() fails, it gives error 2013 (CR_SERVER_LOST), but connection exists and select() works well.

I think this is the key here. It is obvious that if select() works (or if apr_dbd_check_conn() is OK) that connectivity of that particular prepared statement is the problem (i.e. because the connection for which it was prepared was lost). So, the logical thing to do it to prepare the statement again. No?
Comment 5 Marko Kevac 2008-07-21 04:57:08 UTC
It is obvious if you know about this. But if someone sees error 'connection lost' and he does not that this can possibly be error with prepared statement, his first step will be to reconnect or invalidate reslist element.

Anyway, that's not the point. Let's say that we know that if pselect() returns 'connection lost' error, we should prepare statements one more time.

There are two types of prepared statements.
First, ones from DBDPrepare (mod_dbd). Second, statements that were prepared with apr library in modules that use mod_dbd or in modules that don't use mod_dbd at all.

mod_dbd does not know anything about statements that were prepared in other modules. So feature 'if pselect gives error, prepare one more time' should be implemented in mod_dbd and in all modules, that use apr_dbd_mysql.

This 'lot of coding in a lot of places' can be avoided with just turning off reconnect. Maybe not by default, but there should be option.

In this case, after connection lost, mysql_ping will not reconnect and it will return error. apr_reslist element will be invalidated and new connection will be created and statements will be prepared (in dbd_construct). It is not large overhead, becaule mysql_ping reconnects anyway.
Comment 6 Bojan Smojver 2008-07-21 05:08:12 UTC
> Maybe not by default, but there should be option.

I think this is what we should do. MySQL DBD driver understand some options, we can teach it to understand this too.
Comment 7 Bojan Smojver 2008-07-21 16:39:59 UTC
Created attachment 22294 [details]
Introduce reconnection option to MySQL DBD driver
Comment 8 Bojan Smojver 2008-07-21 16:40:52 UTC
Does the patch work for you? In your connection string you should say reconnect=0.
Comment 9 Marko Kevac 2008-07-21 23:28:06 UTC
Yes. It works as it should. Thank you.

I think that information about problem with reconnect, prepared statements and explanation how to avoid it should be in mod_dbd documentation on httpd.apache.org. What should I do to initiate documentation change process? Open bug?
Comment 10 Bojan Smojver 2008-07-21 23:39:57 UTC
Excellent, thanks for testing.

Yes, open a bug for httpd to get this documented.

I'll commit the patch to the trunk and if there are no objections by other developers, I'll backport it to 1.3.x.
Comment 11 Peter Poeml 2008-08-07 13:24:17 UTC
This fix should fix bug 39329 as well, as far as I read it. Or am I misunderstanding something?
Comment 12 Bojan Smojver 2008-08-07 16:10:01 UTC
Maybe. I think this is something for folks playing with mod_dbd and MySQL to test.
Comment 13 Greg Petras 2008-12-08 15:26:44 UTC
I have run into this problem on RHEL 4, running Apache 2.2.10 and MySQL 5.0.67. The patch doesn't seem to fix the problem for me.

I tried using reconnect=0 in my database connection string. The relevant part of my configuration is below (the DBDParams is all on one line, just in case it wraps).

<VirtualHost *:80>

   DBDriver mysql
   DBDParams host=localhost,port=3306,user=my_db_user,pass=my_db_pass,dbname=auth_db,reconnect=0,sock=/var/lib/mysql/mysql.sock
   DBDPersist on

   <Directory /path/to/protected/files>
      AllowOverride None
      Order allow,deny
      Allow from all
      AuthName "protected"
      AuthType Digest
      AuthDigestProvider dbd
      Require valid-user
      AuthDBDUserRealmQuery "SELECT digest FROM users WHERE username = %s AND realm = %s" 


Is there something I can provide to give more information? Or, does my configuration above need to be adjusted?

Thanks in advance for any help you can provide.