保持连接池活着（或时间出来买新鲜的）为MySQL不再闲置时，正确的做法Grails的2个应用程序保持

我有一个具有高活性的飘雪，但后来经常不活动，可以持续几个小时，过夜的时期一个Grails应用程序。我注意到，在上午第一个用户可以得到以下类型的异常的，我相信这是由于在游泳池去陈旧和MySQL数据库关闭它们的连接。

我在谷歌上搜索关于是否使用Connector / J连接属性“autoReconnect的=真”是一个好主意（和客户端是否会仍然得到即使连接，然后恢复除外），或是否设置发现冲突信息其他性质，将周期性地逐出或刷新的空闲连接，上借试验等Grails使用DBCP下方。我现在有一个简单的配置如下，并在寻找如何最好地确保任何连接抓起出池后长期不活动时段是有效的，而不是封闭的答案。

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)

Answer 1:

最简单的是配置连接池指定查询要运行它传递给应用程序之前测试连接：

validationQuery="select 1 as dbcp_connection_test"
testOnBorrow=true

此相同的“连接验证”查询可以在其他事件上运行。我不知道的情况下请的：

testOnReturn=true
testWhileIdle=true

也有配置设置，限制了池中的空闲连接，如果空闲连接在服务器端关闭它可以是有用的“年龄”。

minEvictableIdleTimeMillis
timeBetweenEvictionRunsMillis

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

Answer 2:

我不知道这是否是处理数据库连接的最佳方式，但像你描述我有同样的问题。我尝试了很多，最终与c3p0连接。

使用C3P0你可能会迫使您的应用程序刷新经过一定时间的数据库连接。

请将c3p0.jar到您lib文件夹，并添加配置conf/spring/resources.groovy 。

我resources.groovy看起来是这样的：

import com.mchange.v2.c3p0.ComboPooledDataSource
import org.codehaus.groovy.grails.commons.ConfigurationHolder as CH

beans = {
    /**
    * c3P0 pooled data source that forces renewal of DB connections of certain age
    * to prevent stale/closed DB connections and evicts excess idle connections
    * Still using the JDBC configuration settings from DataSource.groovy
    * to have easy environment specific setup available
    */
    dataSource(ComboPooledDataSource) { bean ->
        bean.destroyMethod = 'close'
        //use grails' datasource configuration for connection user, password, driver and JDBC url
        user = CH.config.dataSource.username
        password = CH.config.dataSource.password
        driverClass = CH.config.dataSource.driverClassName
        jdbcUrl = CH.config.dataSource.url
        //force connections to renew after 4 hours
        maxConnectionAge = 4 * 60 * 60
        //get rid too many of idle connections after 30 minutes
        maxIdleTimeExcessConnections = 30 * 60
    }
 }

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