JDBC Connection pool not reopening connections in Tomcat

Question

I have set up Tomcat to use a connection pool yet after the MySQL timeout on connections the connections previously open in the pool are not opened. Here is what my context.xml file looks like:

<Resource name="jdbc/hpsgDB" auth="Container" type="javax.sql.DataSource"
           maxActive="5" maxIdle="3" maxWait="10000"
           username="uname" password="password" driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/hpsgdb?autoReconnect=true"/>

As you can see I have included autoReconnect as true yet it doesn't. I have checked the process on the database after 8 hours which is what the time out is set to.

Solution 1

Try adding a validation query attribute. This should have the effect of automatically closing and re-opening the connection after a timeout like this:

validationQuery="SELECT 1"

Solution 2

First, get rid of the autoReconnect property. You don't need this with a connection pool and may cause problems.

Second, ensure that you close all resources (Connection, Statement and ResultSet) in your JDBC code in the finally block.

I am not sure if this applies in your case, but a common misconception among starters is that they seem to think that you don't need to close those resources in case of a pooled connections. This is untrue. A pooled connection is a wrapper (decorator) around a connection which has a slightly changed close() method which roughly look like

public void close() throws SQLException {
    if (this.connection is still active) {
        do not close this.connection, but just return it to pool for reuse;
    } else {
        actually invoke this.connection.close();
    }
}

With other words, closing them frees up the pooled connection so that it can be put back in the pool for future reuse. If you acquire connections without closing them, then the pool will run out of connections sooner or later.

Solution 3

Since this is urgent and for production I suggest you have look at a decent connection pool such as c3p0. It's more robust and reliable and can handle timeouts better.

Comments

[Dean]

I got this solution on another forum aswell and already have done this.

[Lucas Smith]

validationQuery is not enough. Please read: leakfromjavaheap.blogspot.com/2013/11/…

[Nikhil Sahu]

testWhileIdle and test-on-borrow properties will use validationQuery