full fathom five

One of the first problems I faced when I deployed this blog was I'd wake up in the morning to check that it was still running from the previous night and find all its database connections hosed and the web app in dire need of a restart. If you're finding this too, here's how I solved it.

Essentially the problem is that MySQL will, after a fairly long period of inactivity (8hrs?), drop database connections. If your application is holding a bunch of these and it is inactive for that period of time then all your connections will be invalid. Unfortunately there is no way to asynchronously detect that you've been disconnected (and thus rectify the problem) so you'll only find out once you try to use the connection.

This in itself is nothing new, all the major web application servers I've used provide some strategy for handling this situation. It's just that Hibernate is such a powerful tool that alleviates so much of the Object-Relational headache that I expected it would have taken care of this for me too. Aparently I'm not alone: http://www.hibernate.org/117.html#A13. This is no criticism of Hibernate, more a compliment, in fact.

The FAQ entry is quite clear - Hibernate's default database connection pool is not well suited to production use - it is not error tolerant so failures like the above will not be recovered from. Luckily, it's no great pain to configure Hibernate so that it uses a decent connection pool that detects and recovers from these problems before your code has to choke on them.

Firstly, you need to decide on a database connection pool - DBCP or C3PO seem to be the two major ones supported directly. Since I've only used DBCP, here's what I did to get going with it.

Your Hibernate configuration probably looks something like this:

<hibernate-configuration>
    <session-factory>
        <!-- properties -->
        <property
         name="connection.driver_class"
           >com.mysql.jdbc.Driver</property>
        <property
         name="connection.url"
           >jdbc:mysql:/localhost/mydb</property>
        <property
         name="connection.username"
           >foo</property>
        <property name="connection.password"
           >bar</property>
        ...
        ...
    </session-factory>
</hibernate-configuration>

You need to amend this to select DBCP as the connection pool to use and provide it with properties to correctly configure it. Here are the parameters I use:

        <property name="dbcp.maxActive">15</property>
        <property name="dbcp.maxIdle">5</property>
        <property name="dbcp.maxWait">120000</property>
        <property name="dbcp.whenExhaustedAction">1</property>
        <property name="dbcp.testOnBorrow">true</property>
        <property name="dbcp.testOnReturn">true</property>
        <property name="dbcp.validationQuery">
            select count(*) from some-table
        </property>
        <property name="dbcp.ps.maxActive">0</property>
        <property name="dbcp.ps.maxIdle">0</property>
        <property name="dbcp.ps.maxWait">-1</property>
        <property name="dbcp.ps.whenExhaustedAction">2</property>

Briefly, the significance of each is as follows:

• maxActive - the maximum number of connections in the pool in use at any one time. The pool will not grow beyond this size
• maxIdle - the maximum number of idle connections in the pool at one time. The pool will shrink in size if there are more than this number of idle connections.
• maxWait - the maximum milliseconds to wait when blocked on a connection becoming free
• whenExhaustedAction - what to do when the pool has run out of available connections. 0 - fail, 1 - block, 2 - grow
• testOnBorrow - should the connection pool test each connection for liveliness before giving to clients. I set this to true.
• testOnReturn - should the connection pool test each connection for liveliness after the client has given it back. I set this to false.
• validationQuery - the SQL query to run to determine if the connection is alive - this must return at least one row. I recommend this is a simple select of count(*) on some table with a small number of rows so as to minimise network transfers between the database and the business tier.
• The last three properties (ps.* for PreparedStatement pooling) are there because Hibernate bombs out if they aren't present. The values I've used cause no PreparedStatement pooling. I leave their explanation as an exercise for the reader.

In case you're wondering why there was no property that selected DBCP as the connection pool implementation, it's because Hibernate infers this from the presence of the associated properties (dbcp.* for DBCP). I'm not sure I like this as it introduces uncertainty as to what will happen when more than one set of properites are present (will the first or last one win, will an exception be thrown?). Still it's only a minor quible.

One final problem you might encounter if deploying to a shared Tomcat instance (for example if you deploy your app through my excellent ISP Metawerx) is DBCP raising a ClassNotFoundException for your mysql driver even though it can be found by Hibernate and at other points in your code.

The context to this problem was that my ISP adds no jar files to his Tomcat server/lib nor to his shared/lib apart from those that ship with Tomcat. Additionally of note is that Tomcat ships with DBCP. With this knowledge in hand I initially tried putting a mysql jar file in my WEB-INF/lib and expected it to work. Sadly this wasn't the case - Hibernate could happily instantiate the driver but DBCP/JDBC could not when passed the same class name. The problem was related to classloading issues (DBCP was higher up in the classloader tree than MySQL and most classloaders only ask for a class definition up the tree, not down the tree) but was easily solved by placing a copy of DBCP in my web app's WEB-INF/lib directory.

So with your configuration updated and any classloading issues behind you, you should be ready to deploy your application without fear of waking up to screaming collection of broken connections.