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.
This question is related to
mysql
max-allowed-packet
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.
Use a max_allowed_packet variable issuing a command like
mysql --max_allowed_packet=32M
-u root -p database < dump.sql
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..
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
#
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
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!
Use a max_allowed_packet variable issuing a command like
mysql --max_allowed_packet=32M
-u root -p database < dump.sql
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.
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.
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
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!
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
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.
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
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
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
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.)
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.
Use a max_allowed_packet variable issuing a command like
mysql --max_allowed_packet=32M
-u root -p database < dump.sql
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.
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
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.
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
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.
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
#
Use a max_allowed_packet variable issuing a command like
mysql --max_allowed_packet=32M
-u root -p database < dump.sql
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.)
Source: Stackoverflow.com