Tomcat Configuration using DBCP

Question

We are getting a CommunicationsException (from DBCP) after iding for a while (a few hours). The error message (in the Exception) is at the end of this question - but I dont see wait_timeout defined in any of the configuration files. (Where should we look? Somewhere out of the tomcat/conf directory?).

Secondly, as suggested by the Exception, where does one put the "Connector/J connection property 'autoReconnect=true'"? Here is the resource definition in the file conf/context.xml in tomcat set up:

<Resource name="jdbc/TomcatResourceName" auth="Container" type="javax.sql.DataSource"
           maxActive="100" maxIdle="30" maxWait="10000"
           removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
           username="xxxx" password="yyyy"
           driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://127.0.0.1:3306/dbname?autoReconnect=true"/>

Thirdly, why does the JVM wait till the call to executeQuery() to throw the Exception? If the connection has timed out, the getConnection method should throw the Exception, shouldn't it? This is the section of the source code I am talking about:

        try {
                conn = getConnection (true);
                stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
                rset = stmt.executeQuery (bQuery);
                while (rset.next()) {
                     ....

Finally, here are the 1st few lines of the Stack trace...

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 84,160,724 milliseconds ago.  The last packet sent successfully to the server was 84,160,848 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3291)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1938)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1451)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

These are the reasons some of us are thinking "forget dbcp, it may be so dependent on IDE configurations and under-the-hood magic that DriverManager.getConnection(...) may be more reliable". Any comments on that? Thank you for your insights, - MS

Answer 1

Since DBCP keeps returned mysql connections open for upcoming connection requests, they fall victims to the MySQL Server timeout.

DBCP has a number of features that can help (can be used starting with Tomcat 5.5 IIRC).

validationQuery="SELECT 1"
testOnBorrow="true"

The validation makes sure that a connection is valid before returning it to a webapp executing the 'borrow' method. The flag of course, enables this feature.

If the timeout (8 hours I believe) is elapsed and the connection is dead, then a new connection is tested (if there are none anymore, it is created) and provided to the webapp.

Other possible approaches:

1. use the testWhileIdle="true" DBCP in your resource settings to also check idle connections before an effective request is detected.

2. Use the 'connectionProperties' to harden your MySQL connection (e.g. autoReconnect/autoReconnectForPools=true)