jdbc4 CommunicationsException

Question

I have a machine running a java app talking to a mysql instance running on the same instance. the app uses jdbc4 drivers from mysql. I keep getting com.mysql.jdbc.exceptions.jdbc4.CommunicationsException at random times.

Here is the whole message.

Could not open JDBC Connection for transaction; nested exception is

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was25899 milliseconds ago.The last packet sent successfully to the server was 25899 milliseconds ago, which  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.

For mysql, the value of global 'wait_timeout' and 'interactive_timeout' is set to 3600 seconds and 'connect_timeout' is set to 60 secs. the wait timeout value is much higher than the 26 secs(25899 msecs). mentioned in the exception trace.

I use dbcp for connection pooling and here is spring bean config for the datasource.

   <bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource" >
          <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/db"/>
                <property name="username" value="xxx"/>
                <property name="password" value="xxx" />
                    <property name="poolPreparedStatements" value="false" />
            <property name="maxActive" value="3" />
            <property name="maxIdle" value="3" />
    </bean>

Any idea why this could be happening? Will using c3p0 solve the problem ?

Answer 1

Try setting up the Apache Commons DBCP correctly.

You need to set:

• validationQuery to SELECT 1+1
• testOnBorrow to true

That should fix the problem.

Answer 2

I have seen before that Windows machines which have been moved on the network have had trouble with connecting to themselves.

Is there any connectivity problems outside the JVM - i.e. mysql client connecting to the server, and timing out, etc?

Answer 3

I'd follow the advice in the exception. You should consider either:

1. expiring and/or testing connection validity before use in your application,
2. increasing the server configured values for client timeouts, or
3. using the Connector/J connection property 'autoReconnect=true' to avoid this problem. Try adding that to your connection URL (consult the docs for the exact syntax) and see if it helps.

I doubt that C3P0 will be that much better than the DBCP that you're already using. The exception is giving you some specific advice. You've tried #3. What about the other two?

I know how to ask WebLogic to check connections before using them. You should find out how to do the same with Tomcat.

Answer 4

Can you describe how your app is handling connection pooling? I doubt that autoReconnect=true in the JDBC driver would re-pool connections from your app. The app needs to reconnect when it loses a connection.