How to fix this java mysql exception: Communications link failure?

Question

Here is the log of this exception:

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

The last packet successfully received from the server was 1,409,240 milliseconds ago.  The last packet sent successfully to the server was 1,409,267 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:425)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
        at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2229)
        at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1989)
        at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3410)
        at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:470)
        at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3112)
        at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2341)
        ...
Caused by: java.io.EOFException: Can not read response from server. Expected to read 7 bytes, read 5 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3011)
        at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2212)

I know this exception is quite normal, and I've googled it and got a lot of solutions. However, none of those solutions fix my problem.
Well, it's just a simple java application, not a java web application, and I didn't use any connecting pool but simply used JDBC. My mysql version is 5.7.12. And the mysql is running on a windows server, while the java application is running on linux. I have checked the 'wait_timeout' for mysql and it's 28800, which is much larger than 1409240 ms. So the problem should probably not be caused by this issue. I've also checked the tcp connection wait time on my linux, it's 7200s, still much bigger than 1409240 ms. I also tried add '?autoReconnect=true' to the JDBC url, but it still made no difference. And I'm sure that there is nothing wrong with the accessibility of my server, cause the connection does work for several minutes before the problem occurs.
I've almost tried any thing I can do. However, the problem still persists. What should I do? Is there any posibility that the problem is caused by the windows firewall?

edit: This problem occurs when executing a SELECT query tries to select all data items from a table whose size is 10.9 G. Maybe this table is just too big so that the sentence ResultSet rs = stmt.executeQuery(sql); cannot be done. But I've checked the 'max_execution_time' variable of mysql, it's set to 0, indicating there is no restriction of execution time.

Answer 1

Welcome to TCP/IP. Lots of things can cause loss of a TCP connection, especially one that has been idle for a while. One such thing, as you mention, is a firewall. The connection can be knocked down by some network entity even if both the client and the server agree it should be kept alive. Connection loss likelihood goes up when client and server are not on the same local network.

Figuring out why means doing lots of packet monitoring at various places in the network. That can use up a lot of time and effort and not teach you much. Plus, learning to read wireshark output is a real task.

Most client-server programmers who need long-lasting connections use some kind of keepalive operation to avoid having the connection sit idle for too long. Keepalive operations send something and get something back. In your case you could do this the easy way by issuing a SELECT NOW() query (or some other round-trip no-op) once every minute or two while your client sits otherwise idle.

The best way to handle this kind of thing is to open the database connection when you need it, then close it when you're done. If you use the connection pooling feature you can open and close your pooled connection upon every query, and still avoid churning the physical connections. The JDBC connector and MySQL server code are optimized for this approach; it is probably the best way to go.

Answer 2

I had com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure reading more on the error message I realized it had to do with my SSL setting in the connection string. When you have SSL enabled in your connection string for MySQL.Please make sure your date and time are correct or in sync with the current date. If not the connection will be hit with javax.net.ssl.SSLHandshakeException resulting in The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. . prompting you to do something about your date. This is how I solved mine. I had to set my date and time correctly. Sometimes the error messages say a lot about the problem.