MySQL “Got an Error Reading Communication Packet” Errors

June 23, 2021 admin No Comments สู่เส้นทางดิจิตอล

In Percona’s managed services, we often receive customer questions on communication failure errors—where customers are faced with intermittent “Got an error reading communication packets” messages. I thought this topic deserved blog post so we can discuss possible reasons for this error, and how to remedy this problem. I hope this will help readers on how to investigate and resolve this problem.

First of all, whenever a communication error occurs it increments the status counter for either Aborted_clients or Aborted_connects, which describe the number of connections that were aborted because the client died without closing the connection properly and the number of failed attempts to connect to MySQL server (respectively). The possible reasons for both errors are numerous (see the Aborted_clients increments or Aborted_connects increments sections in the MySQL manual).

In the case of log_warnings>1, MySQL also writes this information to the error log (shown below):

In this case, MySQL increments the status counter for Aborted_clients, which could mean:

  • The client connected successfully but terminated improperly (and may relate to not closing the connection properly)
  • The client slept for longer than the defined wait_timeout or interactive_timeout seconds (which ends up causing the connection to sleep for wait_timeout seconds and then the connection gets forcibly closed by the MySQL server)
  • The client terminated abnormally or exceeded the max_allowed_packet for queries

The above is not an all-inclusive list.Now, how to identify what causing this problem and how to remedy this problem.

How Do We Identify What Caused This Problem, and How Do We Fix It?

To be honest, aborted connection errors are not easy to diagnose. But in my experience, it’s related to network/firewall issues most of the time. We usually investigate those issues with the help of Percona toolkit scripts, i.e. pt-summary / pt-mysql-summary / pt-stalk. The outputs from those scripts can be very helpful.

Some of the reasons can be:

  • A high rate of connections sleeping inside MySQL for hundred of seconds is one of the symptoms that applications aren’t closing connections after doing work, and instead relying on the wait_timeout to close them. I strongly recommend changing the application logic to properly close connections at the end of an operation.
  • Check to make sure the value of max_allowed_packet is high enough, and that your clients are not receiving a “packet too large” message. This situation aborts the connection without properly closing it.
  • Another possibility is TIME_WAIT. I’ve noticed many TIME_WAIT notifications from the netstat, so I would recommend confirming the connections are well managed to close on the application side.
  • Make sure the transactions are committed (begin and commit) properly, so that once the application is “done” with the connection it is left in a clean state.
  • You should ensure that client applications do not abort connections. For example, if PHP has option max_execution_time set to 5 seconds, increasing connect_timeout would not help because PHP will kill the script. Other programming languages and environments can have similar safety options.
  • Another cause for delay in connections is DNS problems. Check if you have skip-name-resolve enabled, and if hosts are authenticated against their IP address instead their hostname.
  • One way to find out where your application is misbehaving is to add some logging to your code that will save the application actions along with the MySQL connection ID. With that, you can correlate it to the connection number from the error lines. Enable the Audit log plugin, which logs connections and query activity, and check the Percona Audit Log Plugin as soon as you hit a connection abort error. You can check for the audit log to identify which query is the culprit. If you can’t use the Audit plugin for some reason, you can consider using the MySQL general log – however, this can be risky on a loaded server. You should enable the general log for at least a few minutes. While it puts a heavy burden on the server, the errors tend to happen fairly often, so you should be able to collect the needed data before the log grows too large. I recommend enabling the general log with an -f tail, then disable the general log when you see the next warning in the log. Once you find the query from the aborted connection, identify which piece of your application issues that query and co-relate the queries with portions of your application.
  • Try increasing the net_read_timeout and net_write_timeout values for MySQL and see if that reduces the number of errors. net_read_timeout is rarely the problem unless you have an extremely poor network. Try tweaking those values, however, because in most cases a query is generated and sent as a single packet to the server, and applications can’t switch to doing something else while leaving the server with a partially received query. There is a very detailed blog post on this topic from our CEO, Peter Zaitsev.

Aborted connections happen because a connection was not closed properly. The server can’t cause aborted connections unless there is a networking problem between the server and the client (like the server is half duplex, and the client is full duplex)—but that is the network causing the problem, not the server. In any case, such problems should show up as errors on the networking interface. To be extra sure, check the ifconfig -a output on the MySQL server to check if there are errors.

Another way to troubleshoot this problem is via tcpdump. You can refer to this blog post on how to track down the source of aborted connections. Look for potential network issues, timeouts and resource issues with MySQL.

I found this blog post useful in explaining how to use tcpdump on busy hosts. It provides help for tracking down the TCP exchange sequence that led to the aborted connection, which can help you figure out why the connection broke.

For network issues, use a ping to calculate the round trip time (RTT) between machine where mysqld is located and the machine from where the application makes requests. Send a large file (1GB or more) to and from client and server machines, watch the process using tcpdump, then check if an error occurred during transfer. Repeat this test few times. I also found this from my colleague Marco Tusa useful: Effective way to check network connection.

One other idea I can think of is to capture the netstat -s output along with a timestamp after every N seconds (e.g., 10 seconds so you can relate  netstat -s output of BEFORE and AFTER an aborted connection error from the MySQL error log). With the aborted connection error timestamp, you can co-relate it with the netstat sample captured as per a timestamp of netstat, and watch which error counters increased under the TcpExt section of netstat -s.

Along with that, you should also check the network infrastructure sitting between the client and the server for proxies, load balancers, and firewalls that could be causing a problem.

Conclusion

I’ve tried to cover communication failure errors, and how to identify and fix the possible aborted connections. Take into account, faulty ethernets, hubs, switches, cables, and so forth can cause this issue as well. You must replace the hardware itself to properly diagnose these issues.