spring + ibatis + mysql result in com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

Question

Hi all,

this is a very famous error, however after searching and broswing the web I didn't manage to find a stable and proper solution. We are running an application onto this environment: spring + ibatis + mysql all at their latest version.

Very randomly we get this error, and then after some time, which can be hours or days, the error seems to propagate to all connections, then it happen frequently.

Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 75,054,753 milliseconds ago.  The last packet sent successfully to the server was 75,054,754 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.

However just after this error I opened the mysql console and enter 'show processlist':

1151 root localhost:45416 Sleep 163 
1152 root localhost:45417 Sleep 157
1153 root localhost:45418 Sleep 163

Which seems to me a quite clear situation, so at a glance I cannot tell where this long and expired connection is.

Also as the message says I already try to apply all the suggestions. We are running a tomcat dbcp configured in this way:

driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://myurl?autoReconnect=true"
maxActive="40" maxIdle="20" validationQuery="...my select..."
testWhileIdle="true" timeBetweenEvictionRunsMillis="600000"

Could you please point out what can be the cause ? All the table use InnoDB engine.

thanks Leonardo

P.S. In addition, I have just given access to catalina.out, in which I found:

Caused by: java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3294)