How to reset the JDBC Connection Pool

Question

I have a problem where I get tomcat exceptions after I reset my MySQL database at the request of the user via a tomcat web app. I've tried to break this up into the setup, problem, and my analysis so far to help anyone trying to read this.

Setup

The reset basically consists of calling a bash script from the java code to:

• Removing the root mysql user password
• Loading in an old version of the database
• Running some scripts on it
• Restoring all the passwords

It is a user initiated procedure to usually restore the database to a previous state, but it is also be used to import a database from another system. Once everything is complete, the user then attempts to access a different portion of the web app (i.e. with the same session without logging out/logging back in) which performs a DB query to get some data.

Problem

Once the DB is queried by the tomcat application, there is an exception:

Dec 29, 2014 3:49:50 PM ERROR BasicSecurityRealm:216 - 
ERROR: ----- SQLException -----

Dec 29, 2014 3:49:50 PM  INFO BasicSecurityRealm:218 - Exceptioncom.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 234,810 milliseconds ago.  The last packet sent successfully to the server was 12 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
...
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2540)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2990)

Even if the user logs out and back in, I will see this exception. If I refresh the page, four times, the page will load a little bit more each time with some different exceptions (all variations of the above - CommunicationsException caused by "EOFException: Can not read response from server"). The final time, everything seems to be running normally.

The only thing that I can do to avoid these exceptions is to restart tomcat. I would like to avoid this because it will mean that the current user who is logged in will lose their session and have to wait for tomcat to restart before they can log back in. Forcing them to log out/back in might be an acceptable compromise, but that doesn't solve the problem anyway.

Analysis

From what I can tell, I think that the problem has to do with the JDBC connection pool. I am using JNDI data sources to access my database as follows:

server.xml:

  <GlobalNamingResources>
    <Resource name="jdbc/mydb"
              auth="Container"
              type="javax.sql.DataSource"
              maxActive="30" maxIdle="30" maxWait="2147483647"
              username="x" password="x"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mydb?autoReconnect=true"/>

web.xml:

<!-- Data source definitions -->
<resource-ref>
    <res-ref-name>jdbc/mydb</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

Java:

    // Get connection to specified database
    Context initCtx = new InitialContext();
    Context envCtx = (Context) initCtx.lookup("java:comp/env");
    DataSource ds = (DataSource) envCtx.lookup("jdbc/mydb");
    con = ds.getConnection();
    stmt = con.createStatement();
    rs = stmt.executeQuery("...");

I believe that the connection pool contains connections that are stale/dead. Whenever I get a connection with ds.getConnection, then it is getting one of these old connections. Attempts to use it will fail the first time and the connection is reset (notice that I am using autoReconnect=true, so the second time should (and does) work). However, the pool contains many (in my case, empirically 4 or 5) stale connections, so it takes a while before they are all properly reset. Once a connection is reset, everything behaves properly.

Solutions?

Since I use autoReconnect=true I could re-structure my code so that if I get an exception when I attempt a query, I can retry the query once. If it fails again, then I would know that there is really a problem. If it passes, then the connection was successfully re-established.

The problem with this is that there are queries EVERYWHERE in the code. Re-factoring them all would take a lot of time and testing, which I will do if necessary, but would like to avoid. Also, if the query is failing for other reasons, then it would be attempted twice before being reported. For long queries, this could have a significant user experience delay, but only in error conditions.

Another solution would be to force a reset/reconnect of all connections in the connection pool. I could do this either programmatically (i.e. from my java code when the invocation of the bash script completes) or from the bash script (e.g. with some type of command line utility). The problem is, I don't know how to do this, or if it is even possible.

I've found some documentation on Interceptors, but I am not sure if that would work for resetting a connection. I will continue to investigate.

Thanks everyone for your time and help!

Answer 1

You can test connection before getting from the pool

By default Tomcat <7 uses commond-dbcp for Tomcat >= 7 it's jdbc-pool

In both cases add next properties to connection pool configuration:

validationQuery=<TEST SQL>
testOnBorrow=true