Grails turns database connection pooling on by default, and uses the Apache Commons DBCP library to do the pooling.

With many of the popular JDBC drivers, this can cause a failure when the application is accessed after a long idle time, commonly when a business application sits unused overnight, and then is accessed first-thing in the morning. The first user in the morning gets an HTTP 500 error, and then for subsequent users, everything is ok.

There are several ways to solve this, including changing to a different connection pool, but Sudarshan Acharya has a blog post from 2009 that explains a fairly easy way to configure DBCP to resolve this issue. Of course, there’s a “gotcha” here, or I wouldn’t be blogging about it:

As Tom Eastman points out, if you use Sudarshan’s example with Grails 1.3.x, you get an exception something like:

ERROR context.GrailsContextLoader - Error executing bootstraps: groovy.lang.MissingMethodException: No signature of method: org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy.setMinEvictableIdleTimeMillis() is applicable for argument types: (java.lang.Integer) values: [900000] org.codehaus.groovy.runtime.InvokerInvocationException: groovy.lang.MissingMethodException: No signature of method: org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy.setMinEvictableIdleTimeMillis() is applicable for argument types: (java.lang.Integer) values: [900000]

As Graeme Rocher helpfully points out, this is because the properties of org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy changed with Grails 1.3.0, and you must now set the properties on ‘dataSourceUnproxied’ instead of setting them on ‘dataSource’. With this change, everything works as expected.

So, here’s an example of the new configuration of BootStrap.groovy, with Graeme’s suggested change: :::groovy def ctx = servletContext.getAttribute( ApplicationAttributes.APPLICATION_CONTEXT ) def dataSource = ctx.dataSourceUnproxied

println "configuring database connection pool"

dataSource.setMinEvictableIdleTimeMillis(1000 * 60 * 30)
dataSource.setTimeBetweenEvictionRunsMillis(1000 * 60 * 30)
dataSource.setNumTestsPerEvictionRun(3)
dataSource.setTestOnBorrow(true)
dataSource.setTestWhileIdle(false)
dataSource.setTestOnReturn(false)
dataSource.setValidationQuery("SELECT 1")

dataSource.properties.each { println it }