Java “last packet successfully received from the server” errors

The “last packet successfully received from the server XXXXX milliseconds ago” errors are virtually always caused by stale connections. There are a number of possible solutions, most having to do with small code changes.

Here are things to try to alleviate the issue.  I apologize in advance if these have already been tried; I’m shooting for completeness.

1.  Add “?autoReconnect=true” to the JDBC URL, e.g. jdbc:mysql://10.10.10.10:3306/schemaName?autoReconnect=true

2.  If using c3p0 as a connection pool (Spring), try adding these to your datasource definition:

<property name=”testConnectionOnCheckin” value=”false” />
<property name=”testConnectionOnCheckout” value=”true” />

3.  If using DBCP as a connection pool, add these:

<property name=”validationQuery” value=”SELECT 1″ />
<property name=”testOnBorrow” value=”true” />

SELECT 1 is always a good choice because it requires minimal effort from the DBMS.

4.  In some cases the addition of this line is needed as well when defining the resource containers in Spring, but not always:

testWhileIdle=”true”

5.  Ensure you are using the very latest MySQL driver(s).

6.  JDBC drivers support setting session socket timeouts.  For the standard MySQL driver, it’s done like this (building on the example given in #1):

jdbc:mysql://10.10.10.10:3306/schemaName?autoReconnect=true&socketTimeout=60000

6.  MySQL also has a number of timeout variables that can be set either dynamically or within my.cnf.  The only one that really matters in this use case is “wait_timeout.”  It is *usually* set to 28800 seconds, or 8 hours.  However, there are situations where it may have been set much lower, as there are other problems that can crop up with a ridiculously high wait_timeout (such as running out of connections).  If there is a possibility of there being 12, 16, 24 hours between periods of activity in the application, then the wait_timeout for this instance could be raised by a factor of 1.5, 2, 3, etc.  Bear in mind, however, that the wait_timeout is a global variable; if it is set to 30 hours, then all other applications utilizing that MySQL instance for other schemas will have the same wait_timeout, which may not be desirable.

7.  This is an odd one, but I’ve seen it happen, especially since OSes starting coming pre-configured for IPv6.  If you are using localhost as your host definition, switch to 127.0.0.1.  This forces IPv4.

Tags

Related Posts

Share This