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"

Grails Richui Plugin – Supporting Expanded on TreeView

The TreeView support in the Grails Richui plugin does not support the setting of the “expanded” property. With a minor modification you can correct this.

What I want to do in my controller (based on the plugin’s example):

    .
    .
    .
    def xml = new MarkupBuilder(writer)
    xml.person(name: "John Doe"){
        books(name: "Books", expanded: true){
            book(name:"Book 1")
            //Optional id
            book(name:"Book 2", id: 1)
        }
    }
    .
    .
    .

To achieve this I modified de.andreasschmitt.richui.taglib.renderer.TreeViewRenderer in the src/groovy/de/andreasschmitt/richui/taglib/renderer directory where the plugin is installed. The renderTagContent method writes out a function to the page called createNode that is used elsewhere as its name implies. I added an “expand” argument to it and use that for setting the expanded property, so that the relevant lines now look like this.

    builder.yieldUnescaped "    function createNode(text, id, icon, pnode, expand){\n"
    builder.yieldUnescaped "        var n = new YAHOO.widget.TextNode(text, pnode, false);\n"
    builder.yieldUnescaped "        n.labelStyle=icon;\n"
    builder.yieldUnescaped "        if (expand == \"true\"){\n"
    builder.yieldUnescaped "            n.expanded = true;\n"
    builder.yieldUnescaped "        }\n"

Then on the references to it elsewhere in the code (there are two in the version I am using) I pass the expanded value from the XML, so it resembles this.

    builder.yieldUnescaped "    createNode(\"" + it.@name + "\", \"" + it?.@id + "\", \"" + it.@icon + "\", $parent, \"" + it.@expanded + "\");\n"