MySQL Error Reading Communication Packets

Question

In MySQL error logs, I see these quite few a warnings like these:

120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading communication packets)

Haven't noticed any loss of data per se, so I am wondering what this warning means, or what causes it, and if how one might address the issue causing these. This is on RHEL 6.1 and MySQL Enterprise 5.5.

Answer 1

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 !!!

Answer 2

Mostly by default max_connections will be 100. Try increasing the config parameter to max_connections=400.

After setting in my.cnf reboot the server, or you can set it dynamically :

set @@global.max_connections = 400;

Just try the above recommendation to avoid this warning messages,and also ensure your network has no packet drops.

Answer 3

I encountered this issue recently after moving from MySQL Enterprise 5.1.x to 5.7.x, without any significant code changes to the application the 'note' started to appear.

In my case the root cause for the 'note' appearing was the program exiting with connections still open. The circumstance for connections not being closed were a little more involved and not related to MySQL but ACE, threads and TSS.

Answer 4

I'm solving the problem. MySQL sources have a decision called "skip-name-resolve". Faxed it to My.cfg and the problem was fixed. This command disables the dns resolution process of incoming extensions. Most likely there was a problem with the dns this week, which is why we got our error. The system is currently running.

Answer 5

Not mentioned here so I am including another cause of this issue. In my case, while using the mysql command line client, the error was caused by a low value of 30 seconds of interactive_timeout:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_interactive_timeout
This will persist across sessions but not a server restart.

SET GLOBAL interactive_timeout=6000;

Answer 6

I got this same error by doing a query that returned multiple rows, then processing those rows using rows.Next() (in Golang), and exiting early because of an unrelated error without calling rows.Close(). The confusing part was that it worked the first few times, but eventually failed, indicating some resource (connections?) was being used up.

I was able to take advantage of Golang's defer statement, and just do

defer rows.Close()

before ever calling rows.Next(), but calling rows.Close() before every early exit from the rows.Next() loop works as well.

Answer 7

I came across the same issue with MariaDB 10.3.24.

It seems like the warning can occur for all of the other reasons mentioned here and then some.

In my case it was due to a record in one of the database tables which had an empty dictionary value for one of the fields.

+----+--------+
| id | config |
+----+--------+
|  3 | {}     |
+----+--------+

As a test I changed the {} to () and that stopped the message. Just in case it helps someone.

Answer 8

This my.ini line temporary solved my problem:

log_error_verbosity=1

Reference this link