Connection Pool: MySQL connection closed exception

The Problem

While using a MySQL connection pool in Java, I received a MySQL Connection Closed Exception (see below).
The problem was that the JDBC driver was not compatible to the MySQL server version. I have used version 5.05a for both JDBC and MySQL and it solved the problem
The problem is that the connection stayed idle longer than the MySQL wait_timeout (MySQL Server System Variables).

A (Simple) Solution:
1) Set the property minIdle to 0 (this is the default). That way no connection can stay idle for more than DEFAULT_MIN_EVICTABLE_IDLE_TIME_MILLIS (the minimum amount of time an object may sit idle in the pool).
2) Set the JDBC properties: autoReconnect=true and autoReconnectForPool=true

See for example: How to set up a MySQL connection pool in Java

The MySQL Connection Closed Exception

ERROR ... com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
No operations allowed after connection closed.Connection was implicitly closed
due to underlying exception/error:
 
** BEGIN NESTED EXCEPTION **
 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: The last packet successfully received from the server was36797 seconds
ago. The last packet sent successfully to the server was 36797 seconds 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.
 
STACKTRACE:
 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was36797 seconds ago.The last packet sent successfully to the server was 36797 seconds 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.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        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:3246)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1917)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
        ...
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:3227)
        ... 12 more
 
** END NESTED EXCEPTION **
MySQL Quiz
Leave a Reply

*