what is java.io.EOFException, Message: Can not read response from server. Expected to read 4 bytes, read 0 bytes
This question has been asked a couple of times in SO and many times in other sites. But I didn't get any satisfiable answer.
My problem:
I have a java web application which uses simple JDBC to connect to mysql database through Glassfish application server.
I have used connection pooling in glassfish server with the following configurations:
Initial Pool Size: 25
Maximum Pool Size: 100
Pool Resize Quantity: 2
Idle Timeout: 300 seconds
Max Wait Time: 60,000 milliseconds
The application has been deployed for last 3 months and it was running flawlessly too.
But from last 2 days the following error is coming at the time of login.
Partial StackTrace
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:
** BEGIN NESTED EXCEPTION **
com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.io.EOFException
MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
STACKTRACE:
java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2411)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2916)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1448)
............
............
my application traces....
What caused this error suddenly ? I have lost a lot of time for this.
EDIT : The problem even persists after restarting the server. As per DBA two of the important mysql server configurations are:
wait_timeout : 1800 seconds
connect_timeout : 10 seconds
NOTE : Other applications deployed in the same server connecting to the same database and using different pools are running smoothly.
EDIT-2 : After reading a lot of things and expecting some positive outcome I made these changes to my connection pool.
Max Wait Time : 0 (previously it was 60 seconds)
Connection Validation : Required
Validation Method : table
Table Name : Demo
Validate Atmost Once : 40 seconds
Creation Retry Attempts : 1
Retry Intervals : 5 seconds
Max Connection Usage : 5
And this worked as the application is running for 3 days consistently. But I got a very strange and interesting result of out this. While monitoring the connection pool, I found these figures:
NumConnAcquired : 44919 Count
NumConnReleased : 44919 Count
NumConnCreated : 9748 Count
NumConnDestroyed : 9793 Count
NumConnFailedValidation : 70 Count
NumConnFree : 161 Count
NumConnUsed : -136 Count
How can the NumConnFree become 161 as I have Maximum Pool Size = 100 ?
How can the NumConnUsed become -136, a negative number ?
How can the NumConnDestroyed > NumConnCreated ?
Answer
The connection has failed, possibly due to a firewall idle-timeout, etc. If you don't have your JDBC driver configured to reconnect on failure, then this error will not go away unless you open a new connection.
If you are using a database connection pool (you are using one, right?), then you probably want to enable it's connection-checking features like issuing a query to check to see if the connection is working before handing it back to the application. In Apache commons-dbcp, this is called the validationQuery and is often set to something simple like SELECT 1.
Since you are using MySQL, you ought to use a Connector/J-specific "ping" query that is lighter-weight than actually issuing a true SQL query and set your validation query to /* ping */ SELECT 1 (the ping part needs to be exact).