MySQL Connector/J: autoReconnectForPools

[Brian R. Wainwright]

Does anyone know if the "autoReconnectForPools" parameter for the org.gjt.mm.mysql.Driver is what I need to use to resolve stale connections to the database throwing:

 
STACKTRACE:
java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Connection reset
** BEGIN NESTED EXCEPTION ** 
java.net.SocketException
MESSAGE: Connection reset
STACKTRACE:
java.net.SocketException: Connection reset
        at java.net.SocketInputStream.read(SocketInputStream.java:168)
        at java.io.BufferedInputStream.fill(BufferedInputStream.java:183)
        at java.io.BufferedInputStream.read(BufferedInputStream.java:201)
 

because MySQL closes connections after 8 hours of inactivity? My specific implementation, just as an FYI (although I'm not sure if its totally relevant) is I'm using Hibernate as a persistence layer and their DBCPConnectionPool implementation.
Thanks!
--BW

[Wayne L Johnson]

I've used DBCP with Oracle and I ran into a similar problem. The fix was to specify a "validQuery" parameter (something simple and quick) that the underlying connection pool would use to validate the query before handing it back. Since then my app has been running (2 months and counting) w/out any problem.
I have used hibernate a bit in connection with Tomcat and JNDI, and I believe you can define the DataSource to include a "validQuery" parameter. Hopefully it will solve your problem.

[Brian R. Wainwright]

Thanks Wayne. You are indeed correct. For MySQL a valid validQuery would be something like: "select 1" - for anyone listenign in hibernate you can specify this in the connection pool thusly:

 <property name="dbcp.validationQuery">select 1</property>

--BW