Comms failures part-way through queries

Hi all.

I’m getting some problems with comms failures that periodically happen part-way through some large queries on my Java-based client.

They seem to happen between 15 mins - 1 hour after starting the query. My net_read_timeout is set to 3600 and my net_write_timeout is set to 36000, so I don’t think I’m hitting these.

Here is a stack trace:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureThe last packet successfully received from the server was 12 milliseconds ago. The last packet sent successfully to the server was 1,667,496 milliseconds ago. at sun.reflect.GeneratedConstructorAccessor18.newInstance(Unknown Source) [na:na] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) [na:1.6.0_18] at java.lang.reflect.Constructor.newInstance(Unknown Source) [na:1.6.0_18] at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3055) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1416) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:418) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:397) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7171) [mysql-connector-java-5.1.13-bin.jar:na] at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207) [commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207) [commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207) [commons-dbcp-1.4.jar:1.4] at [SNIPPED] … 4 common frames omittedCaused by: java.io.EOFException: Can not read response from server. Expected to read 104 bytes, read 9 bytes before connection was unexpectedly lost. at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2502) [mysql-connector-java-5.1.13-bin.jar:na] at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3008) [mysql-connector-java-5.1.13-bin.jar:na] … 19 common frames omitted

The above error occurred about 25 mins after starting the query. This is a pretty large query, returning many millions of (sorted) results.

Any ideas?

Thanks.

Neil.

What does the logs on the MySQL server say?

What kind of infrastructure do you have between the client and the mysql server?

Do you have a firewall between them, or is a firewall installed on one of them (a pretty common and overlooked source of problems when you have strange network errors)?

Quote:

Good point, I’ll check the log and see if there’re anything there.

Quote:

The client and server are on the same machine, so there shouldn’t be any firewalls between them. As for a firewall being installed on the machine, I’m not sure (it’s the client’s machine) but I will check.

[quote title=neilf wrote on Tue, 21 December 2010 11:17]
Quote:

If that’s the case then any local firewall is probably not the problem.

What about CPU and/or RAM usage during the execution of this query?
Does it hit the roof on one or the either or does the system start to swap a lot?

Can the same query be executed directly afterwards and suddenly work or does some queries always fail?

And don’t forget to check the OS logs as well for any anomalies.