Java+Tomcat, Dying databse 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=ture 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

Nice. I set the parameters in context.xml, and I am going to leave it sit for 24 hours.

If it does not work I will unaccept the answer. But it looks promising!

Thanks!

Answer 3

I do not know whether the above answer does basically the same thing, but some of our systems use the DB connection about once a week and I've seen that we provide a -Otimeout flag or something of that sort to mysql to set the connection timeout.

Answer 4

@ScArcher2:

Those parameters seem to have done the trick!!

Thanks,

Matt

Answer 5

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 6

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