Hibernate and connection pools

I recently spent some time researching and fixing a bug in one of my applications. This is just a summary of its solution, for all those it may help.

Problem:
Mysql has a default time-out on idle connections. The default is 8 hours. If an application uses a connection that has been sitting idle in a connection pool beyond the database time-out period, the application throws an exception.

Solution:
Well, firstly, I thank all those who wrote on blogs/mailing-lists that I had to read to come to the solution, some of which I will quote from here.

To reporduce the problem, I first had to re-create it for testing. I updated mysql's my.ini (windows file, use the corresponding file in Unix/Linux environments) file. I added a wait_timeout value of 120 seconds.

[mysqld]
wait_timeout=120

This immediately produced the error after 2 minutes of reusing an idle connection.

org.hibernate.exception.GenericJDBCException: Cannot release connection
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
    ..
    ..
Caused by: java.sql.SQLException: Already closed.
    at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:84)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:181)
    at org.hibernate.connection.DatasourceConnectionProvider.closeConnection(DatasourceConnectionProvider.java:74)
    at org.hibernate.jdbc.ConnectionManager.closeConnection(ConnectionManager.java:451)
    ... 44 more

I was using commons-dbcp connection pool as a spring managed bean. Since I used appfuse's spring mvc archtype as a quickstart, it came configured with the commons-dbcp. (http://appfuse.org/display/APF/AppFuse+QuickStart).

Gavin King from Hibernate suggests not to use the commons-dbcp as it is faulty. (http://opensource.atlassian.com/projects/hibernate/browse/HB-959) So, taking his advice, my first take was to change the datasource to use a different one.

So, the next question was, if not dbcp, then which one? After a while I stumbled across this connection pool that hibernate supports. I must say, I had not heard of it before, and with an acronym (c3p0) it is not forgetteable. (http://www.hibernate.org/214.html)

Accordingly, I first changed the bean definition from

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="maxActive" value="100"/>
        <property name="maxWait" value="1000"/>
        <property name="poolPreparedStatements" value="true"/>
        <property name="defaultAutoCommit" value="true"/>
</bean>

to the bean definition that uses c3p0:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="driverClass" value="${jdbc.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
</bean>

Note the difference in some of the property names between them. driverClassName/driverClass, url/jdbcUrl, username/user etc. Since these are bean properties, and not resource definition (such as for a web container), the names can be whatever the bean-writers choose them to be.

Refer to this link for more discussion about setting up c3p0 as a bean.
http://forum.springframework.org/showthread.php?t=16309

As you can see, I did not set any other property in the bean definition. The reason requires a little bit of explanation.
In another link (http://forum.springframework.org/showthread.php?t=13078), you can see how the properties can be set.

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="driverClass" value="${jdbc.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <!--<property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>-->
    <property name="properties">
            <props>
                <prop key="c3p0.acquire_increment">5</prop>
                <prop key="c3p0.idle_test_period">100</prop>
                <prop key="c3p0.max_size">100</prop>
                <prop key="c3p0.max_statements">0</prop>
                <prop key="c3p0.min_size">10</prop>
                <prop key="user">${db.user}</prop>
                <prop key="password">${db.pass}</prop>
            </props>
        </property>
    </bean>

But, if you set the properties in this manner, c3p0 does not pickup the user and password as a regular 'property', you have to specify them as prop's in the properties 'property'. So, I yanked all from here, but kept the user and password. I moved all other properties to a c3p0.properties file.

c3p0.acquireIncrement=1
c3p0.idleConnectionTestPeriod=100
c3p0.initialPoolSize=5
c3p0.maxIdleTime=80
c3p0.maxPoolSize=10
c3p0.maxStatements=0
c3p0.minPoolSize=5

One thing that is easily 'missable' is the names of properties. They are named differently in hibernate for the corresponding properties in c3p0. For example, the property of most interest c3p0.idleConnectionTestPeriod is named in hibernate as c3p0.idle_test_period. That makes me wonder, if the property was correctly set in the second springforum link I quoted. (http://forum.springframework.org/showthread.php?t=13078)

So, if you set your c3p0 properties in a c3p0.properties files, you should use c3p0 property names, and in hibernate config files, you should use hibernate co-equivalents.

Another note of caution, even though it is mentioned in passing in the hibernate document (http://www.hibernate.org/214.html), is when you set any of the hibernate cp30 properties, there are 7 properties that hibernate overrides. So you should set all those properties in hibernate, if you do not want hibernate defaults to override cp30 settings or defaults. You will find a reminder in cp30 documentation as well. (http://www.mchange.com/projects/c3p0/index.html#hibernate-specific)

Here is my hibernate.cfg.xml snippet.

<session-factory>
        <property name="connection.pool_size">10</property>
        
        <property name="c3p0.acquire_increment">1</property> 
    <property name="c3p0.idle_test_period">100</property> <!-- seconds --> 
    <property name="c3p0.max_size">10</property> 
    <property name="c3p0.max_statements">0</property> 
    <property name="c3p0.min_size">5</property> 
    <property name="c3p0.timeout">80</property> <!-- seconds --> 
    ..
    ..

As you can see they are same as that in c3p0.properties file. In any case it does not matter, hibernate values will supercede any corresponding value set in c3p0.properties file.

So, what do we have here. We have idle_test_period as 100 seconds, and we had kept mySql's wait-timeout as 120 seconds. This means that the connection pool will discard any connection that has been idle for 100 seconds, and thus will not get used.

The logs show evidence.

DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0] C3P0PooledConnectionPool.finerLoggingTestPooledConnection(315) | Testing PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@17fd168] on IDLE CHECK.
DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2] C3P0PooledConnectionPool.finerLoggingTestPooledConnection(319) | Test of PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@f5b2da] on IDLE CHECK has
SUCCEEDED.
DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1] C3P0PooledConnectionPool.destroyResource(468) | Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@70ccb
DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1] NewPooledConnection.close(566) | com.mchange.v2.c3p0.impl.NewPooledConnection@70ccb closed by a client.
java.lang.Exception: DEBUG -- CLOSE BY CLIENT STACK TRACE
        at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:566)
        at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:234)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470)
        at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964)
        at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

So, a long story in short.

Use c3p0 as your connection pool if you are using hibernate. c3p0 can be configured as a resource bean in Spring. Set hibernate properties for c3p0 to override the c3p0 defaults and hibernate's own c3p0 defaults.

Happy programming.

By the way, I like this convention over configuration thing, but care is needed. I may write about it in another post.