Correct way to keep pooled connections alive (or time them out and get fresh ones) during longer inactivity for MySQL, Grails 2 app
Solution 1
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
Solution 2
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.
Using c3p0 you could force your app to refresh the database connection after a certain time.
Place the c3p0.jar
into your lib
folder and add your configuration to conf/spring/resources.groovy
.
My resources.groovy
looks like this:
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
}
}
Peter
Technical senior executive specializing in creation and leadership of top-tier engineering teams that care deeply about the underlying business. Experienced developer and architect, specializing in highly scalable cloud-based SaaS applications.
Updated on August 30, 2020Comments
-
Peter over 3 years
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)
-
Peter almost 12 yearsThanks for the link, somehow missed that page by going straight to the javadocs. I'll implement a combination of those and see how it works.
-
spencer7593 almost 12 yearsUnfortunately, DBCP still doesn't have a configuration to limit the age of the connection, or the number of times a connection is used, before it is evicted from the pool. (Other connection pools implementations do.)
-
Peter almost 12 yearsalthough, spencer7593, that shouldn't matter if the connections are being used, or evicted/refreshed when they go bad from inactivity, I think, as it won't impact the user.
-
spencer7593 almost 12 years@Pete: it mattered to us, because of a cursor leak on Oracle server connections, where the validationQuery would run just fine, but more complex statements would throw an exception (out of cursors), problem only happened on connections that had been reused a large number of times, but never happened on "new" connections. Limiting the number of uses, via a different connection pool implementation, was a workaround to the problem. (Our issue was not related to idle time.)
-
spencer7593 almost 12 years+1 Some connection pool implementations provide more robust features than DBCP. More recent versions of DBCP do support eviction when "max idle time" is elapsed, but DBCP is still lacking other configuration options, such as maximum age and maximum number of uses.
-
Visionary Software Solutions over 9 yearsI've found C3PO to work really well when I run into connection timeout issues on Grails stacks.
-
Attila Csipak about 5 years@spencer7593 Some update on that: DBCP 2.0+ (released in 2014) supports the maxConnLifetimeMillis property which does exactly what you were missing back in 2012. It took some time, but they got there...