Friday, August 20, 2010

mysql error: Got a packet bigger than 'max_allowed_packet' bytes

I had recently been working on a project where I was getting db dumps (using mysqldump) - and having issues importing the db back into a fresh install of mysql:
% mysql -u [username] -p [database] < sqlDump.sql 
Enter password: 
ERROR 1153 (08S01) at line 1191: Got a packet bigger than 'max_allowed_packet' bytes
Which seemed related to blobs containing larger pdf files mostly (in my case at least). In trying to figure out how to get past this - seeing the default size for 'max_allowed_packet' was too low - in the daemon side (mysqld):
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|              1048576 | 
+----------------------+
To fix this, I used the configuration files (optional) - located on my local system (OS X - Snow Leopard) at: /usr/local/mysql/support-files/my-xxx.cnf. I copied one of these (the 'my-small.cnf' specifically) to /etc/my.cnf, and edited the file to increase the default for the server to 64M:
# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 64M
...
This will increase the limit (globally - since the file is located in /etc/my.cnf and not in ~/.my.cnf) for the server. This increased limit can then be seen here after a server restart (64*1024*1024):
mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|             67108864 | 
+----------------------+
After trying to re-import my file, I found out I also needed to have this via the client as well while importing, since setting the above limit did not seem to solve my issue:
%  mysql --max_allowed_packet=64M -u  [username] -p [database] < sqlDump.sql 

This finally worked in getting past the limitation I was hitting.

Maybe this will be a quick fix help to someone else running into this problem as well.

1 comment: