One of the silent killers of MySQL Connections is the MySQL Packet.
First, let’s figure out what a MySQL Packet is.
According to the page 99 of «Understanding MySQL Internals» (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:
MySQL network communication code was
written under the assumption that
queries are always reasonably short,
and therefore can be sent to and
processed by the server in one chunk,
which is called a packet in MySQL
terminology. The server allocates the
memory for a temporary buffer to store
the packet, and it requests enough to
fit it entirely. This architecture
requires a precaution to avoid having
the server run out of memory—a cap
on the size of the packet, which this
option accomplishes.The code of interest in relation to
this option is found in
sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
particular attention to
net_realloc().This variable also limits the length
of a result of many string functons.
See sql/field.cc and
sql/intem_strfunc.cc for details.
Knowing this about MySQL Packets allows a Developer/DBA to size them up to accommodate multiple BLOBs inside one packet even if they are obnoxiously large. Definitely, a packet too small will cause problems for open connections in this respect.
According to the MySQL Documentation
-
You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server’s max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
-
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.
RECOMMENDATION
Try raising the max_allowed_packet to a much larger number, since the default is 1M. I would suggest about 10 times the largest TEXT or BLOB field you have in your current dataset.
To set the max_allowed_packet to 256M, you can add it to /etc/my.cnf or my.ini
[mysqld]
max_allowed_packet=256M
to cover future restarts of mysqld. To install the value now on the server, please run this:
SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;
Give it a Try !!!
I am getting the MySQL error
«Got an error reading communication packets»
in MySQL.err file and in my application side I am getting 2013 error (lost connection during query).
All the timeout values are (in seconds):
wait_timeout = 60
net_read_timeout = 30
connect_timeout = 30
How to resolve this?
JYelton
35.1k27 gold badges128 silver badges190 bronze badges
asked Mar 29, 2012 at 10:05
1
For what this is worth, this vague error is somewhat common with many possible culprits.
Often it is not a problem with MySql per say…but the system or calling program instead. eg Sometimes you have php memory limits set to low or the swap drive was never setup…that crashes PHP and MySQL is left confused as to what happened.
In my case, I didn’t have that, but I was trying to do a json_encode on a character string with non-utf8 characters which would fail silently and drag mysql down with it. On top of this OPCache in PHP7 seems to be seriously buggy and lacks proper error logging. I had to disable that as well…then magically all my problems with «communication packets» in /var/log/mysql/error.log went away.
Hope this helps someone…gave me a LOT of grief.
answered Feb 1, 2018 at 16:00
user2662680user2662680
6378 silver badges16 bronze badges
2
Another very weird corner case: check your RAM and your swap!
If you have not enough RAM and swap space, your operating system may decide to kill processes causing your exact error in case of MySQL (Got an error reading communication packets). This is a common situation in virtual machines where the provider often does not install any partition for the swap (and when often people does not pay enough RAM).
So, double-check with top or htop to verify if you have ~100% of your RAM in use or if you have not enough swap (or not swap space at all).
In that case, buy more RAM or search «how to install swapfile» in your distribution.
If that’s not the case, see the other answers. Cheers! 
answered Mar 29, 2021 at 7:32
blog
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:
|
OK_Packet |
This is the signal for every successful command. |
|
ERR_Packet |
The signal indicates an error for the packet. |
|
EOF_Packet |
This packet contains a warning or status flag. |
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
Join the DZone community and get the full member experience.
Join For Free
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):
[Warning] Aborted connection 305628 to db: 'db' user: 'dbuser' host: 'hostname' (Got an error reading communication packets)
[Warning] Aborted connection 305627 to db: 'db' user: 'dbuser' host: 'hostname' (Got an error reading communication packets) 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_timeoutto 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_packetis 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 manyTIME_WAITnotifications 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_timeoutis 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.
MySQL
Connection (dance)
application
Database
Published at DZone with permission of Muhammad Irfan, DZone MVB.
See the original article here.
Opinions expressed by DZone contributors are their own.
How do I resolve the «Got an error reading communication packets» error in Amazon RDS for MySQL or Aurora MySQL-Compatible?
Last updated: 2022-12-12
I received a «Got an error reading communication packets» error. I want to resolve this error in an Amazon Relational Database Service (Amazon RDS) for MySQL or Amazon Aurora MySQL-Compatible Edition DB instance.
Short description
If server-client connections are dropped, improperly closed, or are unsuccessful, then Amazon RDS increments the status counter for either aborted_clients or aborted_connects. The aborted_clients parameter describes the number of connections that were aborted because the client died without closing the connection properly. The aborted_connects parameter describes the number of failed attempts to connect to the MySQL server.
log_error_verbosity specifies the verbosity for handling events intended for the error log. If this parameter’s value exceeds a value of 2, then RDS for MySQL writes this information to your error log:
[Warning] Aborted connection xxx to db: '<database name>' user: '<user name>' host: '<host IP>' (Got an error reading communication packets)This warning is triggered whenever the status counter for the aborted_clients or aborted_connects metric values are incremented. Amazon RDS uses the database error logs to retrieve this information. For more information, see Communication errors and aborted connections on the MySQL server website.
Factors that can trigger the Aborted connection warning include but aren’t limited to:
- Client or driver incompatibility
- Firewalls or proxies, which can close any idle connections or block a connection.
- Improper closing of a client-server connection, resulting in a higher number of sleeping connections inside RDS for MySQL.
- A client application that improperly terminates a connection. If connections are left idle for a certain amount of time then MySQL forcibly closes these connections. You then see the Aborted connection message.
- Idle connections that exceed the wait_timeout or interactive_timeout thresholds.
- A client connection that exceeds the connect_timeout seconds threshold when obtaining a connection packet.
- Insufficient values for parameters like net_write_timeout and net_read_timeout.
- The max_allowed_packet parameter value is exceeded. If the value is too small or queries require more memory than is allocated for RDS for MySQL, this triggers the Aborted connection warning.
Resolution
If you receive an Aborted connection error in RDS for MySQL or Aurora MySQL-Compatible, be sure to review your MySQL parameter values. After you identify the root cause of your issue, update the parameter and test the new value, while monitoring the MySQL error logs.
As you troubleshoot the Aborted Connection error in Amazon RDS, consider these approaches:
- Check to see if you’re using the default values of an Amazon RDS parameter group. The default values for parameters related to connectivity timeouts might not be appropriate for your DB instance. For more information, see the Parameters related to connectivity timeout section of Best practices for configuring parameters for Amazon RDS for MySQL.
- Set a higher value for connect_timeout to see if this helps to reduce the occurrence of the Aborted connection error messages. This parameter specifies how long the MySQL server instance must wait (in seconds) before responding with a bad handshake.
- Modify interactive_timeout and wait_timeout. Applications that use connection pooling (like Java) must have timeouts that match the connection pool settings.
- Increase the value of max_allowed_packet if the instance must handle big queries. If a row has more data than the max_allowed_packet value for the client, then errors are reported. Increase this value if you are using large BLOB columns or long strings. For more information, see the max_allowed_packet section of Best practices for configuring parameters for Amazon RDS for MySQL.
- Increase the net_write_timeout and net_read_timeout values. Note: These values can be set to the same value as wait_timeout.
- Make sure that the RDS for MySQL or Aurora MySQL-Compatible connections are properly closed. Before exiting the database, make sure to call the mysql_close() function from the client application.
Troubleshooting tips
If you’re still receiving the Aborted connection error message, try the following troubleshooting tips:
- Turn on Performance Insights to retrieve the timeframe of the SQL statements that are causing the warning. For Aurora MySQL-Compatible, see Monitoring DB load with Performance Insights on Amazon Aurora.
- Turn on slow query logging on your Amazon RDS or Aurora MySQL DB instance. This captures and log any queries running for longer than long_query_time, which by default is 10 seconds. For more information, refer to How do I activate and monitor logs for an Amazon RDS for MySQL DB instance?
- Consider turning on VPC Flow Logs. VPC Flow Logs allows you to see from which side (client/server) that the connection is being closed from.
- Make sure that your application properly closed the connection from MySQL.
- Run the tcpdump command from the machine that’s running the client to test sample packet captures. For example:
sudo tcpdump -vvv --interface eth0 port 3306 -W 10 -C 100Did this article help?
Do you need billing or technical support?
AWS support for Internet Explorer ends on 07/31/2022. Supported browsers are Chrome, Firefox, Edge, and Safari.
Learn more »