A.2.9 Packet too large Error

When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.

In MySQL 3.23 the biggest possible packet is 16M (due to limits in the client/server protocol). In MySQL 4.0.1 and up, this is only limited by the amount on memory you have on your server (up to a theoretical maximum of 2G).

A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client.

When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. With some clients, you may also get Lost connection to MySQL server during query error if the communication packet is too big.

Note that both the client and the server has it's own max_allowed_packet variable. If you want to handle big packets, you have to increase this variable both in the client and in the server.

It's safe to increase this variable as memory is only allocated when needed; this variable is more a precaution to catch wrong packets between the client/server and also to ensure that you don't accidentally use big packets so that you run out of memory.

If you are using the mysql client, you may specify a bigger buffer by starting the client with mysql --set-variable=max_allowed_packet=8M. Other clients have different methods to set this variable. Please note that --set-variable is deprecated since MySQL 4.0, just use --max-allowed-packet=8M instead. You can use the option file to set max_allowed_packet to a larger size in mysqld. For example, if you are expecting to store the full length of a MEDIUMBLOB into a table, you'll need to start the server with the set-variable=max_allowed_packet=16M option.

You can also get strange problems with large packets if you are using big blobs, but you haven't given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the mysqld_safe script and restart mysqld.

User Comments

2006: MySQL server has gone away

Another way that this problem can manifest itself is when trying to load data greater than the prescribed size (say a 1.5MB file) and you get the error shown above. I can confirm that increasing the value in the my.ini file (Windows ME, mySQL 3.23.38) as described corrects the problem.

I had the same problem. I use mysqldump to create large .sql backups of my databases. Recently when I tried to restore one of these backups using the "mysql" command, I got this error:

"ERROR 1153 at line 42: Got a packet bigger than 'max_allowed_packet'"

It turned out that the SQL "INSERT" statements created by mysqldump were exceeding the "max_allowed_packet" limit on the target server. Since I didn't have administrative permissions, my initial solution was to write a Perl script that split up the SQL commands into smaller chunks.

Later, though, I realized that mysqldump also has a "--set-variable" option. If you use this to set a "max_allowed_packet" size matching the target server, then mysqldump will generate smaller commands. So the recommended solution I guess is to import the oversized SQL dump to a local server (where you can set a large "max_allowed_packet"), and then re-export it using mysqldump with the correct settings.

However, I fail to understand why the MySQL protocol cannot handle this gracefully, e.g. by automatically breaking up the "packets". End users should not have to waste their time learning about and accomodating low-level protocol issues.

Add your own comment.