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

User · Answer

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 gt  set global net buffer length 1000000  Query OK  0 rows affected  0 00 sec   mysql gt  set global max allowed packet 1000000000  Query OK  0 rows affected  0 00 sec    Then to import your dump   gunzip  lt  dump sql gz   mysql -u admin -p database

User · Answer

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

User · Answer

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

User · Answer

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  lt  dump sql   Also  change the my cnf or my ini file 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

User · Answer

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

User · Answer

Use a max allowed packet variable issuing a command like  mysql --max allowed packet 32M  -u root -p database  lt  dump sql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   8 0 3   67108864 Default Value  MySQL  lt   8 0 2   4194304 Minimum Value   1024 Maximum Value   1073741824

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Use a max allowed packet variable issuing a command like  mysql --max allowed packet 32M  -u root -p database  lt  dump sql

User · Answer

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  gt   8 0 3   67108864 Default Value  MySQL  lt   8 0 2   4194304 Minimum Value   1024 Maximum Value   1073741824

User · Answer

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

User · Answer

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

User · Answer

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  lt  dump sql   Also  change the my cnf or my ini file 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

User · Answer

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

User · Answer

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 gt  set global net buffer length 1000000  Query OK  0 rows affected  0 00 sec   mysql gt  set global max allowed packet 1000000000  Query OK  0 rows affected  0 00 sec    Then to import your dump   gunzip  lt  dump sql gz   mysql -u admin -p database

User · Answer

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

User · Answer

Use a max allowed packet variable issuing a command like  mysql --max allowed packet 32M  -u root -p database  lt  dump sql

User · Answer

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

User · Answer

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

User · Answer

Use a max allowed packet variable issuing a command like  mysql --max allowed packet 32M  -u root -p database  lt  dump sql

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

Examples related to mysql

Examples related to max-allowed-packet