JDBC Connection pool not reopening Connections in tomcat

Question

Hi, 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. If anyone can help then please help me as this has been a problem for a few months yet has just cropped up as urgent due to my software going live soon.
Thanks in Advance Dean Chester

Answer 1

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.

Answer 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.

Answer 3

With your configuration, it's not supposed to create another connection if it's idle. Try to add

  minIdle="3"

With this setting, DBCP will maintain 3 connections all time.

We see exactly the same behavior with one of lightly used servers. Due to the default connection timeout of 8 hours, we see no connections when we come in the morning. That's what we expected. However, sometimes we see stale connection and the first request will fail. To get around this issue, you need add following attributes,

testWhileIdle="true",
timeBetweenEvictionRunsMillis="60000"

Answer 4

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"