mod_vhost_dbd does not take the MySQL timeout into account

[GoogleCodeExporter]

What steps will reproduce the problem?

1. Set your MySQL server's wait-timeout (persistent connections) to n seconds.
2. Make an HTTP request to the server. The document root must be set by 
mod_vhost_dbd.
3. Wait approximately n seconds, try step 2. again. The MySQL connection stored 
by the module is lost, resulting in HTTP 500 on the client side.

What version of the product are you using? On what operating system?

Apache 2.2.22 / Debian 7.7

Please provide any additional information below.

The error is triggered in mod_vhost_dbd.c, at line 207. n seconds after the 
first request, the connection to the MySQL daemon is shutdown by the server. 
When the module retrieves it and calls apr_dbd_pselect() to query the database, 
this function returns with error code 2013 (CR_SERVER_LOST). The clients are 
then served HTTP 500 (Internal Server Error) until the module renews its 
connection to the server.

Original issue reported on code.google.com by jwhs-s...@gmx.fr on 28 Oct 2014 at 7:47

[GoogleCodeExporter]

Database connection lifetimes are managed by mod_dbd, not by mod_vhost_dbd.

There are several ways you can ensure that mod_dbd does not try to use 
connections which have timed out:

* For threaded platforms (any mpm except prefork) - if you have MySQL set to 
timeout in n seconds, you should set DBDExptime to some number of seconds less 
than n, and also set DBDKeep to zero.  This will prevent mod_dbd from retaining 
connections older than n seconds.

* For prefork mpm - you can set DBDPersist to "off" to prevent mod_dbd from 
retaining connections at all.  Note that this may reduce performance.

see:  https://httpd.apache.org/docs/2.2/mod/mod_dbd.html

* For any mpm,  if you configured mod_dbd with  "DBDriver mysql", and you are 
using MySQL version 5.0.13 or higher, you can add the reconnect=1 parameter to 
your DBDParams directive.  This will reconnect timed-out connections.

See: MYSQL_OPT_RECONNECT in 
https://dev.mysql.com/doc/refman/5.7/en/mysql-options.html
and also: https://dev.mysql.com/doc/refman/5.7/en/gone-away.html

Please let me know if this solves your problem.

Original comment by Thomas.D...@gmail.com on 29 Oct 2014 at 2:52

• Changed state: Started
• Added labels: Type-Other
• Removed labels: Type-Defect

[GoogleCodeExporter]

I am using prefork-mpm (I need mod_php which isn't thread-safe) and despite the 
fact that I'm running MySQL 5.5.40, setting the reconnect option doesn't fix 
the issue. This may due to the fact that prepared statements are involved :

See section "SQL Prepared Statements, Caveat": 
http://httpd.apache.org/docs/2.2/mod/mod_dbd.html

In this case, there may be something possible from mod_vhost_dbd: if DBD is 
expected to reconnect, and apr_dbd_pselect returns CR_SERVER_LOST, reset the 
prepared statement made by the module and try again. I'm not sure if it's 
relevant to mod_vhost_dbd though...

Strange behaviour however: setting DBDExptime < n and DBKeep = 1 seems to 
eradicate the problem... While I do appreciate it, I'm quite surprised of the 
effects of these parameters since they're supposedly destined to threaded MPMs 
(actually, setting DBDKeep = 0 causes HTTP 500).

Original comment by jwhs-s...@gmx.fr on 29 Oct 2014 at 4:17

[GoogleCodeExporter]

You are right about reconnecting and prepared statements.   See bug 
https://issues.apache.org/bugzilla/show_bug.cgi?id=45407

You might want to add a comment to this re-opened APR bug, since managing the 
database connection pool belongs in mod_dbd.   Connection problems like this 
shouldn't be fixed in the modules that use dbd (like mod_authn_dbd, 
mod_vhost_dbd,  mod_log_dbd, etc.), lest all these modules implement their own 
private and different  solutions.

I'm pleased that setting DBDExptime < n worked for you.

Closing this issue - but feel free to add additional comments if you like.   
I'm curious if you ran into this problem using the MySQL default value of 28800 
seconds (8 hours) for wait_timeout?  or do you have a shorter connection 
lifetime for some reason?

Original comment by Thomas.D...@gmail.com on 31 Oct 2014 at 7:59

• Changed state: Done

[GoogleCodeExporter]

Oh, IMHO, 28800 seconds would be a terrible idea in setups where components 
such as mod_vhost_dbd are required. Plus, aside from the fact that a huge part 
of my HTTP requests need to go through mod_vhost_dbd (thus triggering at least 
1 SQL query), I must also take other services into consideration.

In my particular case, almost every service on my machine is relying on the 
MySQL database, and this includes the DNS server, PAM, and even NSS. Each of 
these components receives more requests than Apache. This means that my MySQL 
daemon receives a significant amount of connections (even though the queries 
are pretty lightweight themselves).

While I trust the developers of all these lovely programs, I also know that a 
single mysql_close missing somewhere will leave a MySQL connection opened, yet 
unused. Additionally, some of these programs never save/reuse their 
connections. For these reasons, all my services must do their job in less than 
60 seconds per connection. Of course, the behaviour of dbd_aquire eradicates 
such problems in mod_vhost_dbd, since connections share the HTTP request 
timeout.

I am considering reducing this number to 30 seconds, which is PHP's maximum 
execution time. When you take the PHP scripts away (which may hold the 
connection), no connection is supposed to last more than 2 seconds, unless 
there's a problem.

Original comment by jwhs-s...@gmx.fr on 31 Oct 2014 at 10:24