正确的方式来保持池中的连接活着（或定时更新并获得新鲜的连接），以便在MySQL，Grails 2应用程序长时间处于非活动状态 [英] Correct way to keep pooled connections alive (or time them out and get fresh ones) during longer inactivity for MySQL, Grails 2 app

问题描述

我有一款Grails应用程序，它具有高活跃度的闪光，但经常处于不活动状态，可能会持续数小时至一晚。我注意到，早上的第一批用户会得到以下类型的异常，我认为这是由于池中的连接过时以及MYSQL数据库关闭它们造成的。

我在Googling中发现使用Connector / J连接属性'autoReconnect = true'是一个好主意（以及客户端是否仍会获得即使连接被恢复也是一个例外），或者是否设置其他属性来定期驱逐或刷新闲置连接，借用测试等。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 = ****
属性{
 //我应该在这里添加什么？ 
 
 
 $ / code $ / pre 
 $ b $例外
 
 
  2012-06-20 08：40：55,150 [http-bio-8443-exec-1] ERROR transaction.JDBCTransaction  -  JDBC开始失败
 com.mysql .jdbc.exceptions.jdbc4.CommunicationsException：从服务器成功接收的最后一个数据包是64,129,968毫秒前。成功发送到服务器的最后一个数据包是64,129,968毫秒前。比服务器配置的'wait_timeout'值长。在应用程序中使用之前，应考虑过期和/或测试连接有效性，增加服务器配置的客户端超时值，或者使用Connector / J连接属性'autoReconnect = true'来避免此问题。 
 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）
 ......更多....... 
导致：java.sql.SQLException：已经关闭。 
 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 
  
  http://commons.apache.org/dbcp/ configuration.html  
 
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)

解决方案
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

                        
这篇关于正确的方式来保持池中的连接活着（或定时更新并获得新鲜的连接），以便在MySQL，Grails 2应用程序长时间处于非活动状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！