MySQL Bugs: #62060: Stop recommending messing with the "wait

Bug #62060	Stop recommending messing with the "wait_timeout" variable.
Submitted:	2 Aug 2011 16:23	Modified:	10 Oct 2011 20:08
Reporter:	Eduardo Pérez Ureta	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Philip Olson	CPU Architecture:	Any
Tags:	dbcp, timeout

Description:
At:
http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html
autoReconnect property
you suggest to:
"... Alternatively, investigate setting the MySQL server variable "wait_timeout" to some high value rather than the default of 8 hours."

I think you should stop recommending messing with "wait_timeout" as it will just make time time span higher but keep the problem around.

How to repeat:
As you recommend at:
http://dev.mysql.com/doc/refman/5.5/en/connector-j-usagenotes-troubleshooting.html#qandait...
21.3.5.3.4: I have a servlet/application that works fine for a day, and then stops working overnight
"... need to use a connection pool that handles stale connections ..."

Suggested fix:
You should recommend setting up:
http://commons.apache.org/dbcp/apidocs/org/apache/commons/dbcp/BasicDataSource.html#valida...
http://commons.apache.org/dbcp/apidocs/org/apache/commons/dbcp/BasicDataSource.html#valida...
or similar in other database connection pooling systems.

Thank you for the documentation request.

> You should recommend setting up:
> http://commons.apache.org/dbcp/apidocs/org/apache/commons/dbcp/BasicDataSource.html#valida...
> http://commons.apache.org/dbcp/apidocs/org/apache/commons/dbcp/BasicDataSource.html#valida...
> or similar in other database connection pooling systems.

No, because this is generally "useless noise" queries that are issued on every connection pool checked out, to prevent an edge case.

Applications should *not* be written to expect JDBC connections to live forever, or never have communications errors. Using a connection pool does *not* relieve the developer of this issue. Connections may die (network switch dies, cord pulled, server crashes) in the middle of a transaction. Applications should be able to deal with this possibility.

I agree with you. But at least you should not recommend messing around with the "wait_timeout" variable.

It's a last-ditch option, which is why it's also listed last. While my opinion still stands that applications should handle this, some applications are black boxes and can not be changed.

Updated the docs, to reflect that it's the last/final option:

-                connections properly. Alternatively, investigate setting
-                the MySQL server variable "wait_timeout" to some high
-                value rather than the default of 8 hours.</entry>
+                connections properly. Alternatively, as a last option,
+                investigate setting the MySQL server variable
+                <literal role="sysvar">wait_timeout</literal> to a high
+                value, rather than the default of 8 hours.</entry>