Addressing Database Connection Timeouts with c3p0

Many of our web applications run in Apache Tomcat and communicate with a MySQL database to persist data. After leaving one such development instance of an application up overnight, I was greeted with an error message when after trying to access it the next day. Looking at the Tomcat log, I found the following exception message:

   Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
        The last packet successfully received from the server was 241,479,103 milliseconds ago.
        The last packet sent successfully to the server was 241,479,103 milliseconds ago. is longer than the server configured value of 'wait_timeout'. 
        You should consider either expiring and/or testing connection validity before use in your 
        application, increasing the server configured values for client timeouts, or using the 
        Connector/J connection property 'autoReconnect=true' to avoid this problem.

The application was using commons-dbcp from the Apache Commons project. Below was the Spring configuration of the data source that connected to the database.

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${batch.jdbc.driver}" />
        <property name="url" value="${batch.jdbc.url}" />
        <property name="username" value="${batch.jdbc.user}" />
        <property name="password" value="${batch.jdbc.password}" />
    </bean>

Apparently, the connection to the database had timed out and needed to reconnect. After some searching, several posts mentioned that adding autoReconnect=true to the end of the database URL would fix the problem, but an exception would still be thrown before a reconnect is attempted.

A more popular answer seemed to be replacing the use of commons-dbcp with c3p0. I swapped out the dependency on commons-dbcp with:

    <dependency>
        <groupId>c3p0</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.1.2</version>
        <scope>runtime</scope>
    </dependency>

Followed by a new definition of the data source:

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${batch.jdbc.driver}" />
        <property name="jdbcUrl" value="${batch.jdbc.url}" />
        <property name="user" value="${batch.jdbc.user}" />
        <property name="password" value="${batch.jdbc.password}" />
        <property name="maxPoolSize" value="${mysql.db.maxPoolSize}"/>
        <property name="maxIdleTime" value="${mysql.db.maxIdleTime}"/>
        <property name="maxConnectionAge" value="${mysql.db.maxConnectionAge}"/>
        <property name="acquireRetryAttempts" value="${mysql.db.acquireRetryAttempts}"/>
        <property name="maxIdleTimeExcessConnections" value="${mysql.db.max.idle.time.excess.connections}"/>
        <property name="idleConnectionTestPeriod" value="${mysql.db.idle.connection.test.period}"/>
        <property name="preferredTestQuery" value="${mysql.db.preferred.test.query}"/>
        <property name="testConnectionOnCheckout" value="${mysql.db.test.connection.on.checkout}"/>
        <property name="testConnectionOnCheckin" value="${mysql.db.test.connection.on.checkin}"/>
        <property name="checkoutTimeout" value="${mysql.db.checkout.timeout}"/>
    </bean>

As you can see, there are lot more properties that can be set on the ComboPooledDataSource. The following additional properties settings appear to do the trick, although I must admit not spending much time yet fine tuning them:

    mysql.db.maxPoolSize = 10
    mysql.db.maxConnectionAge = 0
    mysql.db.acquireRetryAttempts = 5
    mysql.db.max.idle.time.excess.connections = 300
    mysql.db.idle.connection.test.period = 540
    mysql.db.preferred.test.query = SELECT 1
    mysql.db.test.connection.on.checkout = false
    mysql.db.test.connection.on.checkin = false
    mysql.db.checkout.timeout = 60000
    mysql.db.maxIdleTime = 500

For now, the connection issue has been resolved in the application. Hope this helps someone out in a similar situation. Thanks for reading.