9

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

Manidip Sengupta
  • 3,573
  • 5
  • 25
  • 27

2 Answers2

14

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)

bluish
  • 26,356
  • 27
  • 122
  • 180
Alain Pannetier
  • 9,315
  • 3
  • 41
  • 46
  • 1
    If I understand you right, DBCP keeps its connection in its pool but mySql server times it out, so when this connection is sent to the app, it is a closed connection. Makes sense, but is that my correct understanding? Another q: Is there any point in using validationQuery/testOnBorrow as you mentioned as well as the testWhileIdle and autoRecon... in the context.xml file? They go in that file, right? – Manidip Sengupta Feb 09 '11 at 22:23
  • 1. Your understanding is correct. All the solutions I indicated are complementary. Setting one up, does not prevent from setting the others. Belt and braces are better ;-) I'd implement all 3 solutions. testing while idle means less waiting time at 'borrow' time. Properties settings will reduce the inopportune disconnections. Yes they all go in the Resource part of your webapp's context.xml (later deployed by tomcat in $CATALINA_HOME/conf/Catalina/localhost/yourwebapp.xml). – Alain Pannetier Feb 09 '11 at 22:36
  • 1
    autoReconnect is not recommended - http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html – OrangeDog Feb 10 '11 at 11:29
0

DBCP is not meant for use in production, even the authors say that (see this presentation: http://www.infoq.com/presentations/Tuning-Tomcat-Mark-Thomas).

I suggest taking a look at C3P0: http://www.mchange.com/projects/c3p0/index.html

Spajus
  • 7,356
  • 2
  • 25
  • 26
  • 1
    This is not *that true*. There are threads about your comparison and the difference is not evident or clear. http://stackoverflow.com/questions/520585/connection-pooling-options-with-jdbc-dbcp-vs-c3p0 and http://stackoverflow.com/questions/490288/is-dbcp-apache-commons-database-connection-pooling-still-relevant – Alfabravo Feb 09 '11 at 22:21
  • That presentation talks about when to use BIO and NIO, depending on session lengths and concurrency requirements (We are low on both issues). Any pointer on why it shouldn't be used in production? Just high frequency of connection considerations? Btw, I did take a look at C3P0, looks very interesting, we might try it out if DBCP keeps giving us problems. Thank you for the info, - MS. – Manidip Sengupta Feb 09 '11 at 22:27
  • If I recall correctly there is a point in this presentation when the guy says "DBCP was never meant for use in production" or something like that. Yes, it's a needle in a hay stack, but I couldn't forget about that after I've heard it. – Spajus Feb 10 '11 at 05:37
  • 1
    C3P0 does not support JDBC4 features. Source : https://sourceforge.net/tracker/index.php?func=detail&aid=3483260&group_id=25357&atid=383690 – fdaugan Jan 08 '13 at 08:52