WebApp (Tomcat-jdbc) Pooled DB connection throwing abandon exception

Question

I have been browsing SO for some time, and chewing my hat in the process, but cannot find an exact match to my problem.
For short, I am getting superb stack trace (org.apache.tomcat.jdbc.pool.ConnectionPool abandon) after 60 seconds of inactivity, which is a normal behavior for a couple of server side threads.
I am using Tomcat JDBC Connection Pooling (org.apache.tomcat.jdbc.pool.DataSource) directly
Stack Trace:

     Oct 29, 2012 8:55:50 PM org.apache.tomcat.jdbc.pool.ConnectionPool abandon     WARNING: Connection has been abandoned PooledConnection[com.mysql.jdbc.JDBC4Connection@1ad2916]:java.lang.Exception         at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:967)         at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:721)         at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:579)         at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:174)         at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:111)         at com.getsom.getConnection(DAO.java:1444)         at com.getsom.PreparedConnection.(PreparedConnection.java:48)         at com.getsom.Alarms.run(Alarms.java:492) 

My PoolProperties are configured as follows:

    PoolProperties pp = new PoolProperties();      pp.setUrl( someValidUrl);     pp.setDriverClassName("com.mysql.jdbc.Driver");     pp.setUsername( someUser);     pp.setPassword( somePassword);     pp.setJmxEnabled( true);     pp.setTestWhileIdle( true);     pp.setTestOnBorrow( true);     pp.setValidationQuery( "SELECT 1");     pp.setTestOnReturn( false);     pp.setValidationInterval(30000);     pp.setTimeBetweenEvictionRunsMillis(30000);     pp.setMaxActive(100);     pp.setInitialSize(10);     pp.setMaxWait(10000);     pp.setMinEvictableIdleTimeMillis(30000);     pp.setMinIdle(10);      pp.setLogAbandoned(true);     pp.setRemoveAbandoned(true);     pp.setRemoveAbandonedTimeout(60);     pp.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+       "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");          setPoolProperties(pp); 

I was hoping setValidationInterval(30000) would save me, since 30s is not much in a connection life cycle. Anyway the question is:
What am I missing to keep this connection alive forever?
A nice to know : Why am I timing out in the function that claimed the connection, although it was invoked 30 seconds earlier.

Answer 1

Even though I'm over 1 year late at coming by this page, yet I stumbled here cos I was experiencing similar problems and in need of a solution too. So I thought i'd share what eventually worked for me.

In my case, after finding and reading through this article >>> configuring-jdbc-pool-high-concurrency - I just added an interceptor like this to my pool configuration;

"org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer" 

so that the line (from your posted code above) where you do setJdbcInterceptors(...) should now look like the following;

p.setJdbcInterceptors(             "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"             + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"             + "org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"); 

Explanation - Quoting from the article, it says;

We want to make sure that when we detect that the connection is still being used, we reset the timeout timer, so that the connection wont be considered abandoned. We do this by inserting an interceptor.

Each time a statement is prepared or a query is executed, the timer will reset the abandon timer on the connection pool. This way... doing lots of queries and updates, will not timeout.

Bearing in mind you most likely have overcome the issue a long time ago, I still hope this helps anybody else having similar issues that bumps into this page, just like I did.

Cheers!