Thursday, April 8, 2010

Mysql ERROR 1153: Got a packet bigger than 'max_allowed_packet' bytes

Personal opinion: Got this while loading a large dump file. Never got this before even for large files.
It seems it happens when you haven't committed for long(data to be committed grow larger in size than a variable called max_allowed_packet)


http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html: It says use --max_allowed_packet=50M while starting mysql.
http://forums.mysql.com/read.php?35,75794,261640#msg-261640: says server and client have their own copy of these variables. Hence the above solution doesn't work. (Certainly not for remote clients, don't know otherwise).


What worked for me:
$mysql 
mysql>set global --max_allowed_packet=52428800 //(50MB) 
mysql>exit 
$mysql mysql>show variables //check that the variable is set correctly 
mysql>exit 
$mysql -u root -p [dbname] < [path to dump file]


"$" is the shell prompt
"mysql>" is the mysql client prompt


That's it.


http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html: mysql server sys vars

No comments:

Post a Comment