Mysql 5.7 Aborted Connection (Got an Error Reading Communication Packets)

In this blog post, nosotros'll discuss the possible reasons for MySQL "Got an error reading communication packet" errors and how to address them.

In Percona's managed services, we often receive customer questions on communication failure errors. So let'southward hash out possible reasons for this fault and how to remedy information technology.

MySQL Communication Errors

First of all, whenever a advice 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 (come across the Aborted_clients increments or Aborted_connects increments sections in the MySQL manual).

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

[ Warning ] Aborted connection 305628 to db : 'db' user : 'dbuser' host : 'hostname' ( Got an fault reading communication packets ) [ Warning ] Aborted connection 305627 to db : 'db' user : 'dbuser' host : 'hostname' ( Got an error reading advice packets )

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

• The client connected successfully simply terminated improperly (and may chronicle to not closing the connection properly)
• The client slept for longer than the divers 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 higher up is not an all-inclusive list. Now, permit's identify what is causing this problem and how to remedy it.

Fixing MySQL Advice Errors

To be honest, aborted connection errors are non like shooting fish in a barrel to diagnose. But in my experience, it's related to network/firewall problems nigh of the time. Nosotros usually investigate those issues with the assistance of Percona toolkit scripts, i.e. pt-summary / pt-mysql-summary / pt-stalk. The outputs from those scripts tin can be very helpful.

Some of the reasons for aborted connection errors can be:

• A high rate of connections sleeping within MySQL for hundred of seconds is 1 of the symptoms that applications aren't closing connections later on doing work, and instead relying on the wait_timeout  to close them. I strongly recommend irresolute the awarding 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 non receiving a "packet besides large" message. This situation aborts the connexion 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 "washed" with the connection information technology is left in a clean land.
• You should ensure that client applications practise not abort connections. For example, if PHP has option max_execution_time  set to 5 seconds, increasing connect_timeout would not assistance because PHP will kill the script. Other programming languages and environments tin have similar safe options.
• Another crusade for delay in connections is DNS problems. Check if you lot have skip-proper noun-resolve enabled and if hosts are authenticated confronting their IP address instead of their hostname.
• One style to find out where your awarding is misbehaving is to add some logging to your code that will save the application deportment along with the MySQL connection ID. With that, you can correlate it to the connection number from the mistake lines. Enable the Audit log plugin, which logs connections and query activity, and cheque the Percona Audit Log Plugin as soon every bit you hit a connexion abort error. You can cheque for the audit log to identify which query is the culprit. If you tin can't use the Inspect plugin for some reason, you tin consider using the MySQL full general log – all the same, this tin 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, errors happen fairly ofttimes so you should exist able to collect the data before the log grows too big. I recommend enabling the general log with an -f tail, and so disable the full general log when you lot see the next warning in the log. Once you detect the query from the aborted connection, identify which slice 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 yous have an extremely poor network. Endeavor tweaking those values, however, considering in almost cases a query is generated and sent equally a single package to the server, and applications tin can't switch to doing something else while leaving the server with a partially received query. There is a very detailed blog mail service 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 trouble, not the server. In whatsoever case, such issues should bear witness upward as errors on the networking interface. To exist extra sure, bank check the ifconfig -a   output on the MySQL server to check if there are errors.

Some other 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. Wait for potential network bug, timeouts and resource bug with MySQL.

I found this blog post useful in explaining how to employ tcpdump  on busy hosts. It provides assistance for tracking downwardly the TCP exchange sequence that led to the aborted connectedness, which tin can help you figure out why the connexion broke.

For network issues, employ a ping to summate the round trip time (RTT) betwixt a 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, sentry the process using tcpdump , then check if an mistake occurred during transfer. Repeat this exam a few times. I likewise plant this from my colleague Marco Tusa useful: Constructive style to cheque network connection.

1 other idea I can think of is to capture the netstat -southward  output forth with a timestamp after every Northward seconds (due east.m., 10 seconds so you can chronicle netstat -s  output of BEFORE and Later an aborted connection error from the MySQL error log). With the aborted connection fault timestamp, you can co-relate it with the netstat  sample captured as per a timestamp of netstat , and watch which error counters increased nether the TcpExt section of netstat -s .

Forth with that, y'all should besides check the network infrastructure sitting betwixt the customer and the server for proxies, load balancers, and firewalls that could exist causing a problem.

Determination:
In add-on to diagnosing communication failure errors, you also need to have into business relationship faulty ethernets, hubs, switches, cables, and and so forth which can cause this event as well. You must supercede the hardware itself to properly diagnose these issues.

More resource

Posts

• Measuring the impact of tcpdump on very busy hosts
• How to track down the source of Aborted_connects
• MySQL net_write_timeouts vs wait_timeout and protocol notes

Webinars

• Troubleshooting hardware resource usage
• A Percona back up engineer walkthrough for pt-stalk

White Newspaper

• MySQL Performance Assay with Percona Toolkit and TCP/IP Network Traffic

---

Correctly understanding the true cause of database performance problems allows for a quick and efficient resolution – yet enterprises ofttimes lack this crucial data. Without it, your solution could require more time and resources than necessary, or inefficiently address the issue. And contrary to popular conventionalities, the problem is non always the database itself!

Download: Top five Causes of Poor Database Operation

rosehision.blogspot.com

Source: https://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-communication-packet-errors/

Share this post