Skip navigation links
A.5.2.11 Communication Errors and Aborted Connections »
Section Navigation      [Toggle]

A.5.2.10. Packet too large

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit is 1GB.

When a MySQL client or the mysqld server receives 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 a Lost connection to MySQL server during query error if the communication packet is too large.

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

If you are using the mysql client program, its default max_allowed_packet variable is 16MB. That is also the maximum value before MySQL 4.0. To set a larger value from 4.0 on, start mysql like this: 

shell> mysql --max_allowed_packet=32M

That sets the packet size to 32MB.

The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this: 

shell> mysqld --max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead: 

shell> mysqld --set-variable=max_allowed_packet=16M

You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file: 

[mysqld]
max_allowed_packet=16M

Before MySQL 4.0, use this syntax instead: 

[mysqld]
set-variable = max_allowed_packet=16M

It's safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you don't run out of memory by using large packets accidentally.

You can also get strange problems with large packets if you are using large BLOB values but have not 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 restarting mysqld.


User Comments

Posted by anand shinde on July 29 2008 6:03pm[Delete] [Edit]

By default configuration
------------------------------------------------------------
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
------------------------------------------------------------

change "max_allowed_packet = 1M" to "max_allowed_packet = 32M"

Posted by David OBrien on August 28 2008 4:47pm[Delete] [Edit]

On windows when importing very large databases,
I've found it helps to NOT use extended insert statements.
The server would go away for no reason. Switching the exporter to use regular inserts solved this problem.

ie. unchecking "Create bulk INSERT statement(s)" in SQLyog or by checking No EXTENDED INSERTS in MySQL Administrator

Posted by Devin Butts on October 31 2008 4:41pm[Delete] [Edit]

RE: mysql server has gone away error 2006
RE: Packet-too-large
RE: Windows MySQL Administrator
RE: my.ini
RE: max_allowed_packet

I had tried without success at trying to update max_allowed_packet by editing the my.ini file in Windows Vista. Seemed like nothing would work, there was no effect.

At the MySQL> command prompt, you can type:
show variables like 'max_allowed_packet'
to see if the changes you made are working.

Then I found that the values of my.ini could be changed using the MySQL Administrator. This didn't work either. It could not stop the MySQL server and every time I would click apply changes, it would say it could not apply the changes, Error 1.

Finally, I found that since I'm using Vista (bad words should be placed here), I had to run MySQL Administrator as a Windows Administrator (right click, choose Run as Administrator). Then suddenly I had the power to stop the server, make the changes I wanted and sure enough, it worked.

To change max_allowed_packet, under Startup Variables, go to the networking tab. (Took me forever to find it.)

I navigated in Windows Explorer to C:\Program Files (x86)\MySQL\MySQL Tools for 5.0\, right clicked, MySQLAdministrator.exe, choose properties and set it so it would always run as an Administrator. Now I don't have to remember.

It should be noted that MySQL install puts files in both C:\Program Files (x86)\MySQL and in C:\Program Files\MySQL

using Vista 64-bit, MySQL Administrator 1.2.12, MySQL 5.0.67

Posted by mohamed badheeu on April 23 2009 9:58pm[Delete] [Edit]

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M


It helps me very much.
But I want add my esperience here.
In some servers mysql socket is in different location.
socket = /tmp/mysql.sock (if this location is different database error will come up)make sure you write your location correct.

Posted by ayaz pasha on March 25 2010 3:06pm[Delete] [Edit]

I copied the variables and pasted into my.ini file.

wait_timeout=28800
interactive_timeout = 28800
max_allowed_packet=64M
socket = /tmp/mysql.sock
skip-locking

Then I just restarted the MySQL service from Administrative Tools & everything worked fine(I was able to insert huge data).

Make sure you check if the parameter values are changed by typing in the command (ex.)show parameters like 'max_allowed_packet' from MySQL client(default).

Thanks

Ayaz

Posted by Jay ANDERSON on April 2 2010 9:23pm[Delete] [Edit]

Regarding checking the existing parameter value...

show variables like 'max_allowed_packet';

...worked for me.

Add your own comment.