Tomcat/Jetty, DBCP, and Stale Connections

In developing applications using Grails we were running into exceptions each morning when users try to access them. The ultimate manifestation was something like:

errors.GrailsExceptionResolver org.springframework.dao.DataAccessResourceFailureException: could not execute query;
nested exception is org.hibernate.exception.JDBCConnectionException: could not execute query
org.codehaus.groovy.runtime.InvokerInvocationException: org.springframework.dao.DataAccessResourceFailureException: could not execute query;
nested exception is org.hibernate.exception.JDBCConnectionException: could not execute query

In DB2 the low-level exception was:

Caused by: com.ibm.db2.jcc.a.sm: [jcc][t4][2030][11211][3.51.90]
A communication error occurred during operations on the connection's underlying socket, socket input stream, or socket output stream.
Error location: T4Agent.sendRequest().  Message: Connection reset by peer: socket write error. ERRORCODE=-4499, SQLSTATE=08001

This was wrapping a java.net.SocketException.

The underlying cause is that we sever all connections to the database for a nightly backup and the applications did not find out about it until they tried to run a query against a stale connection. As it happens though, Tomcat and Jetty (and probably many others) both use Apache Commons DBCP for connection pooling and there are configuration settings that can mitigate this problem; they are documented here – http://commons.apache.org/dbcp/configuration.html.

The settings we are trying in order to remedy the problem are:

    validationQuery="select 1 from sysibm.sysdummy1"
    testWhileIdle="true"
    timeBetweenEvictionRunsMillis="600000"

We are also starting out with these settings which are not particularly related, but might be of interest to some.

    initialSize="4"
    maxActive="100"
    maxIdle="8"
    minIdle="2"
    maxWait="2000"
    removeAbandoned="true"
    logAbandoned="true"

Advertisement