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.

Cephalopod

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!

Whitelaw

This answer is my solution as well! Thank you guys! – Balk

Answer 2

Have you seen the information on the Tomcat website relating to PoolConnection. Perhaps what you need is to look at the property minEvictableIdleTimeMillis

To answer your question you are timing out because you are checking for idle & abandon connections every 30 seconds (see TimeBetweenEvictionRunsMillis) and since you are setting an evictable idle timeout at 30 seconds (see minEvictableIdleTimeMillis) then you end up with what you have. You have said that you are receiving this exception while idle, I suspect the exception is a result of closing idle connection as opposed to abandoning a connection. From my understanding abandoning a connection is used for timing out longer than expected queries (as opposed to idle connections).

Personally I would not want to have connections alive forever because they would be consuming resources (that is a connection to the db) unnecessarily. I would play around with my max connections, eviction runs and idle times to optimise for my own requirements. I guess you can set these values large enough to almost be forever! It does really depend on what you are doing though...

Sorry I couldn't be much more help here.

Stakhanovism

Yes, I have been reading this extensively. I believe this has do do with abandon more than eviction. My assumption was the ConnectionPool would keep these connections alive. – Cephalopod

Are you getting the error when you are executing a query? It would seem odd that you have a query running for longer than 60 seconds! – Stakhanovism

Nope. The thread in question is idle. Which is the problem since ConnectionPool assumes the connection is abandonned. – Cephalopod

If you are having a problem with 'abandoned' and not with 'idle' connections, try setting (resolved problem for me): spring.datasource.tomcat.removeAbandonedTimeout: 86400 #seconds – Coal

Answer 3

Just add below entry in tomcat7 conf/server.xml or in context.xml wherever your resource tag is present.

jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;
org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"

Marileemarilin

Answer 4

track 'removeAbandonedTimeout' in the configuration file. this should be max running query in the application. othewise it will close connection in the middle of execution

Barre

I have the same issue, and already have removeAbandonedTimeout set, and it doesnt help, unfortunately. – Mishandle

Can you explain how to "track" that resource attribute in Tomcat? – Kemppe

Warning: This is only true if either you only do one query on a connection and then close it again, or when the ResetAbandonedTimer filter is used. Otherwise batch operations could potentially run in a timeout. – Stipel

Answer 5

If you define your datasource in the context.xml of tomcat then you should add the ResetAbandonedTimer like below :

jdbcInterceptors="ConnectionState;StatementFinalizer;ResetAbandonedTimer"

After setting ResetAbandonedTimer, issue got resolved in my application, Requesting you to let me know is there any relation between ResetAbandonedTimer interceptor and removeAbandoned="true" removeAbandonedTimeout="60"

Cchaddie

Answer 6

The answers to this question were very helpful to me.

Though in my case, I already had the "ResetAbandonedTimer" JDBC Interceptor configured.

However, I had a query that ran for longer than the "removeAbandonedTimeout" that I had also configured. Once I increased the "removeAbandonedTimeout" the problem went away.

Telugu

Answer 7

Had a similar problem ie., tomcat was closing JDBC connection due to it becoming abandoned because a transaction was taking a long time.

Resolved it by realizing that abandoned and idle are different and by setting: spring.datasource.tomcat.removeAbandonedTimeout: 86400 #seconds

Orelee

Answer 8

I can see this thread is old, but I had a similar problem, and I eventually found the reason that caused it, so I would like to share if it can help someone:

I used the connection from a restful web-service. In the interface at the serverside handling request from the client, I had accidentially put 'throws IOException' in the method signature:

@GET
@Path("/databases")
@Produces(MediaType.APPLICATION_JSON)
public String getAllDatabases() throws IOException {

Albinus