Correct way to keep pooled connections alive (or time them out and get fresh ones) during longer inactivity for MySQL, Grails 2 app

Question

I have a grails app that has flurries of high activity, but then often periods of inactivity that can last several hours to over night. I notice that the first users in the morning get the following type of exception, and I believe this is due to the connections in the pool going stale and MYSql database closing them.

I've found conflicting information in Googling about whether using Connector/J connection property 'autoReconnect=true' is a good idea (and whether or not the client will still get an exception even if the connection is then restored), or whether to set other properties that will periodically evict or refresh idle connections, test on borrow, etc. Grails uses DBCP underneath. I currently have a simple config as below, and am looking for an answer on how to best ensure that any connection grabbed out of the pool after a long inactive period is valid and not closed.

dataSource {
        pooled = true
        dbCreate = "update"
        url = "jdbc:mysql://my.ip.address:3306/databasename"
        driverClassName = "com.mysql.jdbc.Driver"
        dialect = org.hibernate.dialect.MySQL5InnoDBDialect
        username = "****"
        password = "****"
        properties {
          //what should I add here?
          }
    }

Exception

    2012-06-20 08:40:55,150 [http-bio-8443-exec-1] ERROR transaction.JDBCTransaction  - JDBC begin failed
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 64,129,968 milliseconds ago.  The last packet sent successfully to the server was 64,129,968 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3851)
    ...... Lots more .......
Caused by: java.sql.SQLException: Already closed.
    at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)

Answer 1

The easiest is to configure the connection pool to specify the query to be run to test the connection before it is passed to the application:

validationQuery="select 1 as dbcp_connection_test"
testOnBorrow=true

This same "connection validation" query can be run on other events. I'm not sure of the defaults for these:

testOnReturn=true
testWhileIdle=true

There are also configuration settings that limit the "age" of idle connections in the pool, which can be useful if idle connections are being closed at the server end.

minEvictableIdleTimeMillis
timeBetweenEvictionRunsMillis

http://commons.apache.org/dbcp/configuration.html