Communications link failure Last packet sent to the server was 4 ms ago

Question

First of all, i know this question is being asked many times but for me the reproduction scenario is little different i guess(well may be).

For mysql health check, we do a simple select query (select 1 from dual). Once the mysql server go idle for a while (around 1 minute) and we try health check, we get this issue..

Communications link failure\n\nLast packet sent to the server was 4 ms ago

"message": "Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.", "stack": ["com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2431)", "com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2882)", "com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2871)", "com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3414)", "com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)", "com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)", "com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)", "com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)", "com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:734)", "org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)",

and immediately if we try once again, we get successful connection and everything works fine. So its very sporadic in nature.

I have seen this stackoverflow post com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

So out of all this possibilities.. I will answer few of those-

IP address or hostname in JDBC URL is wrong. -NO(it works on second attempt after communication failure)
Hostname in JDBC URL is not recognized by local DNS server. -No (same reason as step 1)
Port number is missing or wrong in JDBC URL. -No (same reason as step 1)
DB server is down. -NO-(server is up and running)
DB server doesn't accept TCP/IP connections. -No (same reason as step 1)
DB server has run out of connections. -No(I have increased number of connections to 1000)
Something in between Java and DB is blocking connections, e.g. a firewall or proxy.

Yes. Proxy config as follows:

retries                 3
timeout http-request    10s
timeout queue           1m
timeout connect         10s
timeout client          1m
timeout server          1m
timeout http-keep-alive 10s
timeout check           10s
maxconn                 3000

few of my etc/my.conf details are as follows--

connect_timeout  = 10
wait_timeout     = 28800
bind-address = 0.0.0.0
max_connections = 1000

For creating connections, I am using commons dbcp datasource (org.apache.commons.dbcp.BasicDataSource)

BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("url");
dataSource.setUsername("username");
dataSource.setPassword("pwd");
dataSource.getConnection();

Please help me out finding what could go wrong.. is it proxy config that causing the issue or anything else??? Help will be much appreciated.

I don't see the point of removing the mysql tag, as MySQL's fingerprints are all over the stack trace. — user207421
Hai @EJP, thanks for your time. I removed mysql because database server I am working on is Percona server which is in a way nothing but mysql, just to be precise i have removed mysql tag and added percona. — RIPAN
You are using a MySQL JDBC driver, and that is where the exception is coming from. — user207421

user207421 user207421 · Accepted Answer · 2016-07-29T10:17:06

IP address or hostname in JDBC URL is wrong. -NO(it works on second attempt after communication failure)

No, because you got as far as executeStatement(), which means you already got a connection and a PreparedStatement.

Hostname in JDBC URL is not recognized by local DNS server. -No (same reason as step 1)

Correct.

Port number is missing or wrong in JDBC URL. -No (same reason as step 1)

Correct.

DB server is down. -NO-(server is up and running)

No for same reason as (1), (2), (3).

DB server doesn't accept TCP/IP connections. -No (same reason as step 1)

Correct.

DB server has run out of connections. -No(I have increased number of connections to 1000)

No for same reason as (1), (2), (3), (4), (5).

Something in between Java and DB is blocking connections, e.g. a firewall or proxy.

No, for same reason as (1), (2), (3), (4), (5), (6).

You have got a communications failure after establishing a connection. That rules out everything you've mentioned here. What's left is (a) transient conditions at the server or in the network, (b) connection pool idiosyncracies at the client, (c) some catastrophically wrong command being submitted by the JDBC driver, etc. Most likely (a).

Communications link failure Last packet sent to the server was 4 ms ago

1 Answers