Find and fix MySQL issues faster with Percona Monitoring and Management

In this blog post, we’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’s discuss possible reasons for this error and how to remedy it.

Try Now: Get MySQL Database Management Services with Percona Managed Services

MySQL Communication Errors

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, 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, let’s identify what is causing this problem and how to remedy it.

Fixing MySQL Communication Errors

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 for aborted connection errors 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 of 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, errors happen fairly often so you should be able to collect the 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 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, watch the process using tcpdump, then check if an error occurred during transfer. Repeat this test a 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:
In addition to diagnosing communication failure errors, you also need to take into account faulty ethernets, hubs, switches, cables, and so forth which can cause this issue as well. You must replace the hardware itself to properly diagnose these issues.

More resources

Webinars

White Paper

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

Download: Top 6 Causes of Poor Database Performance

 

 

9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Aftab Khan

Another nice blog!

Just to add few cents, starting from MySQL 5.7.2 debugging connection has been made little easier:

a) You can get more information about the lost connections by starting mysqld with the “log_error_verbosity” system variable set to 3

b) The ID included in error log messages is that of the thread within mysqld responsible for writing the message. This will help identify which part of the server produced the message, and is consistent with general query log and slow query log messages which include the connection thread ID. Before MySQL 5.7.2, the ID in error log messages is that of the mysqld process ID.

0
ranvijay mehta

Thanks Inrfan for the valuable information.

0
Sligcm

Thanks for the valuable information.

0
Richard vK

I found lots of these going through my database, and spend a lot of time trying to adjust both application/apache/freeradius and mysql parameters, all to no avail.

It turned out the timeout setting in haproxy (which sits between my cluster and all applications) had a 15 second timeout, which was shorter than all other timeouts. This makes sense in hindsight: its expected that apache, freeradius etc would cleanly close their timed out or expired db connections, however if something in the middle (ie haproxy) kills it, it would not be a clean disconnect, and you would expect this type of error in the logs.

1
idrissa

Thanks for the information

1
dennis gao

Is there any way to let server not do the log for Aborted connection?

0
guest

Another trial reason I once met is innodb_buffer_pool_size set to 8 mb (when queries seemed to return much bigger data)

0
madhu

Hi All,

i facing this error “Got timeout/error reading communication packets.”
Aborted connection 357093 to db: ‘’user: ‘root’ host: ‘cloudsqlproxy~0.0.0.0’ (Got timeout/error reading communication packets)

I tried multiple options by increasing the max_allowed_packet value from 100MB to 1GB and adding new variables in mysql db.

max_allowed_packet from 100MB to 1GB(1073741824)
wait_timeout 86400 (wait_timeout with default value =28800(8hrs to 24hrs)
interactive_timeout 86400(default value =28800)
net_read_timeout 28800(default value =30)
net_write_timeout 28800(default value =60)
max_connections =400

Still the issue exists.

please help on this.

Thanks,
madhu Reddy

1
1karu5

this is the first time that read about the “TIME_WAIT” problem… my god !!! TIME_WAIT is normal !!! it informs about the correct CLOSE of a Network Connection. Maybe you are put a peace of fruit…

0