17

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.

Wojciech Wirzbicki
  • 3,887
  • 6
  • 36
  • 59
Matt MacLean
  • 19,410
  • 7
  • 50
  • 53

4 Answers4

11

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
ScArcher2
  • 85,501
  • 44
  • 121
  • 160
  • 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! – Matt MacLean Aug 19 '08 at 14:25
4

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.

Sindri Traustason
  • 5,445
  • 6
  • 48
  • 66
  • What is the name of this property? I would like to double check it. "thread_pool_idle_timeout = 60" is all I see. – Kerem Mar 18 '15 at 01:32
  • @mass I have no idea. My answer above is 7 years old and I haven't much used Tomcat or Servlet Resource for a few years now. As far as I can tell timeBetweenEvictionRunsMillis is a property of the Resource tag in context.xml and thread_pool_idle_timeout is a .property file key. – Sindri Traustason Mar 18 '15 at 11:12
  • Right now as far as I know setting up timeBetweenEvictionRunsMillis still kicks off the connection sweeper for jdbc datasource pool. thread_pool_idle_timeout however was a MySQL parameter I was referring to. I doubt the current defaults are shorter than 8 hours but thank you for replying anyway. Seems like the accepted solution has worked for the OP, I shall give that a go. – Kerem Mar 18 '15 at 15:40
1

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

Ophir
  • 11
  • 1
0

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.

abyx
  • 69,862
  • 18
  • 95
  • 117