Catching database resources leaks with Tomcat - howto

This it a solution of "Cannot get a connection, pool error Timeout waiting for idle object" error message. It guides how to find your resource leak.

1. Add the red properties to your context.xml in this way:
  <Resource name="jdbc/MyAppDS" auth="Container" type="javax.sql.DataSource"
            maxActive="10" maxIdle="10" maxWait="10000" defaultAutoCommit="false"
            username="user_dev" password="passssss" driverClassName="com.mysql.jdbc.Driver"
            removeAbandoned="true" removeAbandonedTimeout="25" logAbandoned="true"
            url="jdbc:mysql://my-mysqlserver.com:3306/databasename_dev?autoReconnect=true&amp;characterEncoding=UTF-8"/>

2. Reproduce your problem to full the active connection. In the above configuration, I have limited them to 10, to reproduce the problem sooner.

3. Watch the stdout output for log of the abandoned connection. It is something like this:
org.apache.tomcat.dbcp.dbcp.AbandonedTrace$AbandonedObjectException: DBCP object created 2012-05-03 15:51:35 by the following code was never closed:
    at org.apache.tomcat.dbcp.dbcp.AbandonedTrace.setStackTrace(AbandonedTrace.java:139)
    at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:81)
    at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at com.yourcompany.getDataSource(XXX.java:55)
...
Caused by: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
        at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:104)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)           
        ... 19 more
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
        at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:825)
        at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
        ... 23 more

3.1 If you do not have stdout.log file in Windows, check the link.
4. Correct the XXX.java file as you close the resource after its usage. It could not be easier!

Get console output in Tomcat

You need stdout.log & stderr.log in Tomcat in Windows? Here is a guide how to get it.

1. If you use Apache as Windows service, the default behavior is the files to be created and filled. It it is not so, rightclick on taskbar service icon -> Configure -> Logging tab and check the values of 2 redirect dropdowns. They must be set to auto. The files are placed in %CATALINA_HOME%/logs folder.

2. If you use Tomcat not as windows service. I could not find the way of doing this. I redirected the output of the calling java in catalina.bat file but with no success. If you find a way of doing this, please post a comment below.