Java+Tomcat, Dying database connection?

Question

I have a tomcat instance setup but the database connection I have configured in context.xml keeps dying after periods of inactivity.

When I check the logs I get the following error:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was68051 seconds ago. The last packet sent successfully to the server was 68051 seconds ago, which 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.

Here is the configuration in context.xml:

<Resource name="dataSourceName" 
        auth="Container" 
        type="javax.sql.DataSource"
        maxActive="100" 
        maxIdle="30" 
        maxWait="10000" 
        username="username" 
        password="********"
        removeAbandoned = "true"
        logAbandoned = "true"
        driverClassName="com.mysql.jdbc.Driver" 
        url="jdbc:mysql://127.0.0.1:3306/databasename?autoReconnect=true&amp;useEncoding=true&amp;characterEncoding=UTF-8"  />

I am using autoReconnect=true like the error says to do, but the connection keeps dying. I have never seen this happen before.

I have also verified that all database connections are being closed properly.

Answer 1

Tomcat Documentation

DBCP uses the Jakarta-Commons Database Connection Pool. It relies on number of Jakarta-Commons components:

* Jakarta-Commons DBCP
* Jakarta-Commons Collections
* Jakarta-Commons Pool

This attribute may help you out.

removeAbandonedTimeout="60"

I'm using the same connection pooling stuff and I'm setting these properties to prevent the same thing it's just not configured through tomcat. But if the first thing doesn't work try these.

testWhileIdle=true
timeBetweenEvictionRunsMillis=300000

Answer 2

Just to clarify what is actually causing this. MySQL by default terminates open connections after 8 hours of inactivity. However the database connection pool will retain connections for longer than that.

So by setting timeBetweenEvictionRunsMillis=300000 you are instructing the connection pool to run through connections and evict and close idle ones every 5 minutes.

Answer 3

The removeAbandoned option is deprecated as of DBCP 1.2 (though still present in the 1.3 branch). Here's a non-official explanation.