Got an error reading communication packets mysql bitrix

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.

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

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 support engineer walkthrough for pt-stalk

White Paper

• MySQL Performance Analysis 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 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 5 Causes of Poor Database Performance

MySQL is the second famous database in the world according to the DB Engine website behind Oracle. What makes MySQL famous is probably because it is a very fast, reliable and flexible Database Management System. MySQL is also one of the supported databases in ClusterControl. You could easily deploy, scale, monitor and do a lot of things with ClusterControl.

Today we are not going to talk about any of those, but we will discuss one of the common errors for MySQL and possible troubleshooting tips. When working with tickets, a lot of time when we check the error reports or logs, we saw this line  “Got an error reading communication packet” quite frequently. We think it would be beneficial if we write a blog related to this error not only for our customers but also for other readers. Let’s wait no further, it’s time to dive more!

MySQL Client/Server Protocol

First of all, we need to understand the way MySQL communicate between client and server. Both client and server are using MySQL protocol which is implemented by Connectors, MySQL Proxy and also the communication between master and slave replication servers. MySQL protocol supports the features like transparent encryption via SSL, transparent compression, a connection phase and as well as a command phase.

Both integers and strings are the basic data types that are used throughout the MySQL protocol. Whenever MySQL client and server wants to communicate to each other or sending the data, it will divide the data into packets with a maximum size of 16MB and also will prepend a packet header to every chunk. Inside each packet, there will be a payload which is where the data types (integers/strings) play their parts.

Considering the CLIENT_PROTOCOL_41 is enabled, for almost every command that the client sends to the server, the server will reply any of the following packets as a response:

How To Diagnose The Problems

Typically, there are two types of connection problems which is communication errors or aborted connections. Whenever any of these connection problems happen, the following sources of information are the good starting point for the troubleshooting and analysis:

• The error log

• The general query log

• The Aborted_xxx and Connection_errors_xxx status variables

• The host cache

Connection Errors And Possible Reasons

In the event of any connection errors happen and depending on the errors, it will increment the status counter for either Aborted_clients or Aborted_connects in the status variables. As taken from the MySQL documentation, Aborted_clients means the number of connections that were aborted because the client died without closing the connection properly. As for Aborted_connects, it means the number of failed attempts to connect to the MySQL server.

If you start the MySQL server with the –log-warnings option, chances are you probably would see the example of the following message in your error log. As you noticed, the message clearly said it relates to the abort connection, hence Aborted_connects status counter will be incremented in the status variable:

[Warning] Aborted connection 154669 to db: ‘wordpress’ user: ‘wpuser’ host: ‘hostname’ (Got an error reading communication packets)

Normally, unsuccessful connection attempts could happen due to the following reasons. When you noticed this, it possibly indicates that an unauthorized person is about to breach the database and you might want to look at it the soonest possible:

• A client has no privileges to access the database.

• A wrong credential has been used.

• A connection packet that has incorrect information.

• Due to the limit reached for connect_timeout to connect.

The status variable for Aborted_clients will be incremented by the server should a client manage to connect but get disconnected or terminated in an improper way. In addition to that, the server also will log an Aborted connection message to the error log. For this type of error, commonly it could be due to the following reason:

• The client does not close the connection properly before exiting (does not call mysql_close ()).

• The client has exceeded wait_timeout or interactive_timeout seconds.

• The client program or application suddenly ended in the middle of data transfer.

Besides the reasons earlier, other likely reasons for both aborted connections and aborted clients issues could be related to any of the following:

• TCP/IP configuration messed up.

• The variable value is too small for max_allowed_packet.

• Insufficient memory allocation for queries.

• Faulty hardware like ethernets, switches, cables, etc.

• Issues with the thread library.

• Duplex syndrome issue whereby the transfer goes in burst-pause-burst-pause mode (if you use ethernet protocol with Linux, both half and full duplex).

How To Fix MySQL Communication Errors

Now that we learned a lot of potentialities that caused MySQL connection errors. Based on our experience, most of the time this issue is related to the firewall or network issues. It is also fair to say that it is not easy to diagnose this kind of issue. Nonetheless, the following solution might be helpful for you in solving this error:

• If your application is relying on the wait_timeout to close the connection, it’s worth changing the application logic so that it’s properly closed at the end of any operation.

• Making sure the value for max_allowed_packet is within the acceptable range so that the client does not receive any error related to the “packet too large”.

• For connection delay issues that could be due to the DNS, it’s worth checking if you have skip-name-resolve enabled.

• If you are using a PHP application or any other programming, the best is to make sure it does not abort the connections which are typically set at max_execution_time.

• If you noticed a lot of TIME_WAIT notifications from netstat, it’s worth confirming that the connections are well managed on the application end.

• If you are using Linux and suspect the issue is due to the networking, it’s best to check the networking interface by using ifconfig-a command and examine the output on the MySQL server for any error.

• For ClusterControl users, you could enable Audit Log from the Cluster -> Security -> Audit Log. By enabling this feature, it could assist you to narrow down finding which query is the culprit.

• Networking tools like tcpdump and Wireshark could be useful in identifying potential network issues, timeouts and resources issues for MySQL.

• Regularly check on the hardware by making sure there are no faulty devices especially for ethernets, hubs, switches, cables etc. It’s worth replacing the faulty appliance to make sure the connection is good all the time.

Conclusion

There are a lot of reasons that could perhaps lead to the MySQL connection packet issues. Whenever this issue occurs, it will definitely affect the business and day to day operations. Even though this type of issue is not easy to diagnose and most of the time it is due to the network or firewall, it’s worth taking into consideration all the steps that have been suggested previously in order to fix the issue. We really hope this blog post could help you in some way especially when you face this problem.

Subscribe to get our best and freshest content