As michaelpryor said, you have to change it for both the client and the daemon mysqld server.

His solution for the client command-line is good, but the ini files don’t always do the trick, depending on configuration.

So, open a terminal, type mysql to get a mysql prompt, and issue these commands:

set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

Keep the mysql prompt open, and run your command-line SQL execution on a second terminal..

This can be changed in your my.ini file (on Windows, located in \Program Files\MySQL\MySQL Server) under the server section, for example:

[mysqld]

max_allowed_packet = 10M

Re my.cnf on Mac OS X when using MySQL from the mysql.com dmg package distribution

By default, my.cnf is nowhere to be found.

You need to copy one of /usr/local/mysql/support-files/my*.cnf to /etc/my.cnf and restart mysqld. (Which you can do in the MySQL preference pane if you installed it.)

Use a max_allowed_packet variable issuing a command like

mysql --max_allowed_packet=32M -u root -p database < dump.sql

Slightly unrelated to your problem, so here’s one for Google.

If you didn’t mysqldump the SQL, it might be that your SQL is broken.

I just got this error by accidentally having an unclosed string literal in my code. Sloppy fingers happen.

That’s a fantastic error message to get for a runaway string, thanks for that MySQL!

On CENTOS 6 /etc/my.cnf , under [mysqld] section the correct syntax is:

[mysqld]
# added to avoid err "Got a packet bigger than 'max_allowed_packet' bytes"
#
net_buffer_length=1000000 
max_allowed_packet=1000000000
#

The fix is to increase the MySQL daemon’s max_allowed_packet. You can do this to a running daemon by logging in as Super and running the following commands.

# mysql -u admin -p

mysql> set global net_buffer_length=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> set global max_allowed_packet=1000000000;
Query OK, 0 rows affected (0.00 sec)

Then to import your dump:

gunzip < dump.sql.gz | mysql -u admin -p database

In etc/my.cnf try changing the max_allowed _packet and net_buffer_length to

max_allowed_packet=100000000
net_buffer_length=1000000 

if this is not working then try changing to

max_allowed_packet=100M
net_buffer_length=100K 

Set max_allowed_packet to the same (or more) than what it was when you dumped it with mysqldump. If you can’t do that, make the dump again with a smaller value.

That is, assuming you dumped it with mysqldump. If you used some other tool, you’re on your own.

I am working in a shared hosting environment and I have hosted a website based on Drupal. I cannot edit the my.ini file or my.conf file too.

So, I deleted all the tables which were related to Cache and hence I could resolve this issue. Still I am looking for a perfect solution / way to handle this problem.

Edit – Deleting the tables created problems for me, coz Drupal was expecting that these tables should be existing. So I emptied the contents of these tables which solved the problem.