What's the cause of (and treatment for) this java MySQL exception?

Question

I'm getting the following exception when executing the first preparedstatement after a period of inactivity:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 2,855,054 milliseconds ago. The last packet sent successfully to the server was 123 milliseconds ago. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3052) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2938) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3481) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2228)

This only shows up if my application hasn't communicated with MySQL recently. Subsequent queries execute normally. I suspect it's some kind of timeout issue, but the periods of inactivity are way below the 8 hour timeout for MySQL.

Any suggestions?

Answer 1

I'm getting the following exception when executing the first preparedstatement after a period of inactivity

This is just my guess - the MySQL connection is closed be default after some time of inactivity, since database connections are valuable assets so they should have been managed well by code.

Check the code regards the database connection control and you may find the answer there. Although by MySQL timeout setting the timeout value is 8 hours - it is still possible in your code (or in someone else's code?), the connection is to be closed after a certain time of inactivity.

Answer 2

Are you using a connection pool? If so, you can just turn on connection pool checking/monitoring so you'll be ensured that your client thread gets a working MySQL connection each time. Most pools have a way of specifying a low-effort piece of SQL to execute before a connection is borrowed or sometimes even in the background during idle periods (for better performance at the risk of some bad connections being issued to client threads).

Also, w.r.t. the time < 8h issue, does MySQL count idleness from the last packet received or the last significant event (a query, etc.)? I recall seeing in the MySQL driver code that there's a heartbeat signal implemented.