在 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 数据库关闭了它们.

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.

我在谷歌搜索中发现了关于使用 Connector/J 连接属性 'autoReconnect=true' 是否是一个好主意的冲突信息(以及即使连接恢复，客户端是否仍然会收到异常)，或者是否设置将定期驱逐或刷新空闲连接、借用测试等的其他属性.Grails 在下面使用 DBCP.我目前有一个简单的配置，如下所示，我正在寻找有关如何最好地确保在长时间不活动后从池中抓取的任何连接有效且未关闭的答案.

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)

推荐答案

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

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屋！