在 MySQL、Grails 2 应用程序较长时间不活动期间保持池连接处于活动状态（或超时并获取新连接）的正确方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11125962/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Correct way to keep pooled connections alive (or time them out and get fresh ones) during longer inactivity for MySQL, Grails 2 app
提问by Peter
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.
我有一个 grails 应用程序，它有一系列的高活动，但通常会持续几个小时到一夜之间的不活动期。我注意到早上的第一批用户收到以下类型的异常，我相信这是由于池中的连接过时并且 MYSql 数据库关闭了它们。
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.
我在谷歌搜索中发现了关于使用 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 = "****"
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)
采纳答案by spencer7593
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
回答by aiolos
I don't know if it is the best way to handle database connection, but I had the same problems as you described. I tried a lot and ended up with the c3p0 connection pool.
我不知道这是否是处理数据库连接的最佳方式，但我遇到了与您描述的相同的问题。我尝试了很多，最终得到了c3p0 连接池。
Using c3p0 you could force your app to refresh the database connection after a certain time.
使用 c3p0，您可以强制您的应用程序在一段时间后刷新数据库连接。
Place the c3p0.jarinto your libfolder and add your configuration to conf/spring/resources.groovy.
将c3p0.jar放入您的lib文件夹并将您的配置添加到conf/spring/resources.groovy.
My resources.groovylooks like this:
我的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
}
}
