MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes

Question

I'm importing a MySQL dump and getting the following error.

$ mysql foo < foo.sql 
ERROR 1153 (08S01) at line 96: Got a packet bigger than 'max_allowed_packet' bytes

Apparently there are attachments in the database, which makes for very large inserts.

---

This is on my local machine, a Mac with MySQL 5 installed from the MySQL package.

Where do I change max_allowed_packet to be able to import the dump?

Is there anything else I should set?

Just running mysql --max_allowed_packet=32M … resulted in the same error.

Conciliatory

possible duplicate of How to change max_allowed_packet size – Shock

@Muleskinner, this question was posted 3 years before the one you mention and I'm pointing this out 4 years after your comment. :p – Acalia

Webyog.com Link is broken: 404 – Sensor

Here, a similar error, "Packet for query is to large (5526600 > 1048576).", was caused by a wrong password entry, when connecting to MySQL database using DataGrip 2016.1 by JetBrains, and got resolved when rectified (using the right password corresponding to the MySQL database user). – Pforzheim

Answer 1

You probably have to change it for both the client (you are running to do the import) AND the daemon mysqld that is running and accepting the import.

For the client, you can specify it on the command line:

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

Also, change the my.cnf or my.ini file (usually found in /etc/mysql/) under the mysqld section and set:

max_allowed_packet=100M

or you could run these commands in a MySQL console connected to that same server:

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

(Use a very large value for the packet size.)

Gyneco

I have a server with 16 GB of RAM, is it a bad idea to set max_allowed_packet to 100 MB? – Apomorphine

as an aside, to run those set commands your mysql user must have SUPER privileges or it will fail with ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation – Thorfinn

FYI - it helped me to solve a DIFFERENT error - "#2006 server has gone away" – Farming

I also got the "ERROR 2006: MySQL server has gone away". This was on a windows machine. On a Linux machine I got the "ERROR 1153: Got a packet bigger than 'max_allowed_packet' bytes" for the same dump file. Mysql versions are also different on the machines so I'm not really sure if it is related to the version or OS. – Hillyer

Be aware that using "set global" works until the next mysql service restart. – Putscher

This did the trick for me except that 100MB caused issues with InnoDB. I changed it to 10MB and it worked fine. – Subscapular

Skip "set global" and the final ";" when adding these values to the my.ini or my.cnf files. Ex: "net_buffer_length=1000000" in my.conf. – Charlatanry

On CentOS 5, my.cnf is located at /etc/my.cnf – Charlatanry

For those using PHPMyAdmin I was able to do this technique by starting on the home page of PHPMyAdmin, then clicking the variables tab, then you can search for 'packet' or 'buffer' to narrow down and edit from there. – Improvisatory

I've had the same problem and I spent many hours for it because I restore DB using MySQL Workbench which does not show error about packet size. When I use SQLYog, it shows something like package size is not enough. Changing that value to 500MB solves the problem. – Ullman

I had the same problem and in my case changing my.ini file on server was enough. Did not need to add --max_allowed_packets to the client call – Hearse

@Charlatanry On CentOS 7, inside the my.cnf that max_allowed_packet does not exist. I added it, saved the file, restarted mariadb and it didn't work. The problem actually was a wordpress plugin that produced that backup.sql file. Instead it was solved by making the backup through phpmyadmin – Sensor

The command-line option DID NOT work for me (MySQL 5.7 on windows), but setting the option in my.ini DID WORK. (So try changing it there, before you start hunting for other sources of the error) – Annalist

setting max_allowed_packet=100M in my.cnf was enough for me. – Kilpatrick

conf file locations for MariaDB: mariadb.com/kb/en/library/configuring-mariadb-with-option-files/… – Hygroscope

On Windows and using WAMP left click wamp icon and select mysql. then settings and change it there. It will auto restart everything – Remainderman

I had to add the lines to /etc/my.cnf file under [mysqld] section (CentOS 7, MariaDB 5.5) to make it work. – Cog

+1 @Cog Users of this answer must not forget [mysqld] otherwise you will get error: Found option without preceding group in config file: /etc/mysql/my.cnf at line:... Solution: https://mcmap.net/q/41849/-mysql-my-cnf-file-found-option-without-preceding-group – Exclusion

Answer 2

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..

Yogh

Solved the problem for me; the import I'm doing is a once-off, and I can't easily change the configuration. This worked great. :D – Lading

Answer 3

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

Mistreat

on a mac, file obviously located elsewhere. – Conciliatory

sure, but the configuration is still somewhere although I don't know the exact location – Mistreat

For me in Fedora 20 with MariaDB, placing that setting at the end of /etc/my.cnf.d/server.cnf did the trick. I had to restart the service of course... sudo nano systemctl restart mariadb.service – Lumberyard

The file would more likely be "my.cnf" and on nix systems, typically in /etc/ or /usr/local/etc. Once you edit, be sure to restart mysql server to apply change. – Tabulate

Answer 4

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

Monopode

What version of MySQL were you running this on? – Matador

This one works for my server. Thanks – Nonmaterial

Answer 5

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.)

Conciliatory

The default config in place for OSX appears to be my-medium.cnf, although the max_allowed_packet size is the same in my-large.cnf ... until you start changing things :) – Yaupon

In my case /usrl/local/mysql/my.cnf not worked until I copy it to /etc/my.cnf. – Sitsang

Answer 6

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 

Kuomintang

Answer 7

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
#

Accelerometer

Answer 8

I have resolved my issue by this query

SET GLOBAL max_allowed_packet=1073741824;

and check max_allowed_packet with this query

SHOW VARIABLES LIKE 'max_allowed_packet';

Bovill

Answer 9

Use a max_allowed_packet variable issuing a command like

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

Gavelkind

tried that, didn't work. whole dump in 272mb, tried with max higher than that. – Conciliatory

Answer 10

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!

Pleurodynia

I also got this error because of broken SQL. More specifically, my table has null constraints and my code was INSERTing null values. Instead of giving me an informative error, MySQL returned the max_allowed_packet error. If it helps for those in the future, I was INSERTing using the pandas API df.to_sql(...) – Amazing

Something similar happened to me (and I did use mysqldump. Fortunately my IDE highlighting caught it once I knew to look for the problem. Thank you. – Quotation

Answer 11

Error:

ERROR 1153 (08S01) at line 6772: Got a packet bigger than 'max_allowed_packet' bytes Operation failed with exitcode 1

QUERY:

SET GLOBAL max_allowed_packet=1073741824;
SHOW VARIABLES LIKE 'max_allowed_packet'; 

Max value:

Default Value (MySQL >= 8.0.3)  67108864
Default Value (MySQL <= 8.0.2)  4194304
Minimum Value   1024
Maximum Value   1073741824

News

Answer 12

Sometimes type setting:

max_allowed_packet = 16M

in my.ini is not working.

Try to determine the my.ini as follows:

set-variable = max_allowed_packet = 32M

or

set-variable = max_allowed_packet = 1000000000

Then restart the server:

/etc/init.d/mysql restart

Wideawake

Answer 13

It is a security risk to have max_allowed_packet at higher value, as an attacker can push bigger sized packets and crash the system.

So, Optimum Value of max_allowed_packet to be tuned and tested.

It is to better to change when required (using set global max_allowed_packet = xxx) than to have it as part of my.ini or my.conf.

Niggard

Answer 14

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.

Ingratitude

Answer 15

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.

Feldman