Fixing ‘Packages larger than “max_allowed_packet” are not allowed’ in MySQL
When inserting large objects into a new table in MySQL, you may encounter the error, Packages larger than “max_allowed_packet” are not allowed. When this happens, MySql.Data.dll throws an error due to the query result being larger than the limit set. This is usually due to large objects, which are often large TEXT or BLOB objects. In the case of TEXT, this object has character encoding, which makes it differ from ordinary BLOB large objects. We also find other variations such as MEDIUMTEXT, LONGTEXT, and LONGBLOB. For the sake of mentioning all types, we also can find TINYTEXT, which is a smaller object data type.
TEXT and BLOB can hold 64K (65535 bytes) worth of data, whereas LONGTEXT and LONGBLOB can hold up to 4GB of data. (11.8 Data Type Storage Requirements, 2004) The below table in diagram 1 displays all of the datatypes belonging to the TEXT and BLOB families and their sizes.
(11.1.3 String Type Overview)
How do you solve this issue?
1. Check max_allowed_packet size parameter
First, you must check the current size of the max_allowed_packet size.
You can do this by using the below command:
SHOW VARIABLES LIKE ‘max_allowed_packet’;
2. Check largest object size
The second step would be to check the size of the objects which are returned/used by the query. Note the largest of these objects.
3. Increase the max_allowed_packet parameter
There are two ways to update this value in order to allow larger objects in a query.
a) SET GLOBAL/SESSION limits
One way would be to set the length globally or for the session
To set the global parameter:
SET GLOBAL max_allowed_packet= 16777216
This value will only be valid for new sessions and will not be updated for the current session.
To set the session parameter:
SET SESSION max_allowed_packet= 16777216
This value will only valid for the current session and will not update for other sessions.
(Voo, 2014)
b) Change the value of max_allowed_packet my.cnf
Open the my.cnf file, search for the max_allowed_packet parameter. If it exists, simply change the value to the appropriate amount. If it does not exist, simply add the parameter.
For example,
max_allowed_packet=1G
Restart MySQL service
You can do this by running the following command:
service mysql restart
(B.5.2.10 Packet Too Large)
This should solve your problem. Let me know if this helped you in the comments below.
Works Cited
11.1.3 String Type Overview. (n.d.). Retrieved from MySQL: https://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html
11.8 Data Type Storage Requirements. (2004). Retrieved from MySQL: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
B.5.2.10 Packet Too Large. (n.d.). Retrieved from MySQL: https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
Voo, A. (2014, January). Packets larger than max_allowed_packet are not allowed. Retrieved from Github: https://github.com/MySqlBackupNET/MySqlBackup.Net/wiki/Packets-larger-than-max_allowed_packet-are-not-allowed
