在 MySQL、Grails 2 应用程序较长时间不活动期间保持池连接处于活动状态(或超时并获取新连接)的正确方法

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?
          }
    }

异常

    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)

推荐答案

最简单的方法是配置连接池，指定要运行的查询来测试连接，然后再将其传递给应用程序:

validationQuery="select 1 as dbcp_connection_test"
testOnBorrow=true

testOnReturn=true
testWhileIdle=true

minEvictableIdleTimeMillis
timeBetweenEvictionRunsMillis