Tweaking The Grails Datasource To Cope With MySQL’s Foibles

The application I am maintaining is falling prey to MySQL’s infamous “kill connections after 8 hours of inactivity” feature.

The users arrive each morning to find that their application is unworkable, ‘cos all the connections have “gone away.”

This IS a feature and not a bug, and it does make sense (the server has to manage its resources appropriately [for it] and 8 hours is as good a timeout as 1 or 1000)…but it is darned inconvenient, nonetheless.

Following the lead set by Sudarshan Acharya in Grails, DBCP & Stale Connections, I came up with this little class:

import org.apache.log4j.Logger
import org.codehaus.groovy.grails.commons.ApplicationAttributes

class DataSourceUtils {
  static final Logger log = Logger.getLogger(DataSourceUtils)

//
// fix for the database killing idle connections
//
// http://sacharya.com/grails-dbcp-stale-connections/
//
  private static final ms = 1000 * 15 * 60

  public static tuneDataSource = {servletContext ->
    def ctx = servletContext.getAttribute(ApplicationAttributes.APPLICATION_CONTEXT)
    ctx.dataSource.with {d ->
      d.setMinEvictableIdleTimeMillis(ms)
      d.setTimeBetweenEvictionRunsMillis(ms)
      d.setNumTestsPerEvictionRun(3)
      d.setTestOnBorrow(true)
      d.setTestWhileIdle(true)
      d.setTestOnReturn(true)
      d.setValidationQuery('select 1')
      d.setMinIdle(1)
      d.setMaxActive(16)
      d.setInitialSize(8)
    }

    if (log.infoEnabled) {
      log.info "Configured Datasource properties:"
      ctx.dataSource.properties.findAll {k, v -> !k.contains('password') }.each {p ->
        log.info "  $p"
      }
    }
  }
}

It’s easy to use, just plop it into BootStrap.groovy:

class BootStrap {

  def init = {servletContext ->
    DataSourceUtils.tuneDataSource (servletContext)
...

The configuration may be a bit more ‘aggressive’ than generally needed, but better safe than sorry!

Of course the ‘correct’ way to handle all this is to let the container control the datasource and then pick up the managed resource via JNDI. I’ll go that way eventually, but there’s plenty else in the application that needs fixing up first.

One can also establish a bigger value for MySQL’s wait_timeout property, but this isn’t really a good solution (one still has to be prepared for other causes of loss of connection). Many people suggest adding “autoReconnect=true” to the connection URL. This may be OK for a standalone application, but it is simply not recommended for use with connection pools. I think the doco uses language like “side effects related to session state and data consistency” when touching on this issue…scary!

[...edit...]
With Grails 1.3.1 there is a standard way of doing this.