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

Wikitechy | 3185 Views | mysql | 01 Jun 2016

 

Scenario:

When importing the MySQL dump file, the following error occurs:

$ mysql foo < foo.sql 

ERROR 1153 (08S01) at line 96: Got a packet bigger than 'max_allowed_packet' bytes

error:

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

Reason for the error:

The main reason is that the MySQL dump is really huge and hence cannot be imported into the system.

Fix1:

1. Open your my.cnf file

For Debian/Ubuntu the file path is

/etc/mysql/my.cnf, 

For Fedora/RedHat/CentOS the file path is 

/etc/my.cnf – 

2. Find for the line with the word max_allowed_packet.

3. Increase the value little bit as double or thrice the existing value.

4. Now restart MySQL

For Debian/Ubuntu, the command to restart is 

/etc/init.d/mysql restart

For Fedora/RedHat/CentOS, the command is

/etc/init.d/mysqld restart

5. If the error still persists, increase the value again and restart the server again.

Fix 2:

For Windows web development environment:

1. Go to Wamp tray Icon 

Select MySql -> my.ini

2. Find the below lines of code:

[mysqld]

port=3306

explicit_defaults_for_timestamp = TRUE

3. Now add max_allowed_packet value here as follows:

[mysqld]

port=3306

max_allowed_packet = 16M

explicit_defaults_for_timestamp = TRUE

4. Restart MySQL service and check for the updated packet value as:

Select @@global.max_allowed_packet;

Fix 3:

On Linux start mysql prompt and key in the below commands to increase buffer length and packet size:

mysql> set global net_buffer_length=1000000;

mysql> set global max_allowed_packet=1000000000;

Now import the dump file with admin rights as:

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

if the above command does not work, use the below to set values for buffer length and packet size as:

max_allowed_packet=100M

net_buffer_length=100K 

Fix 4:

For Cent OS

1. Go to /etc/my.cnf , 

2. Enter the below commands under the [mysqld] section:

[mysqld]

# added to avoid err "Got a packet bigger than 'max_allowed_packet' bytes"

#

net_buffer_length=1000000 

max_allowed_packet=1000000000

#

Fix 5:

Enter the packet size in my.ini file as 

max_allowed_packet = 16M

If it is not working enter one of the below commands to increase packet size:

set-variable = max_allowed_packet = 32M (or)

set-variable = max_allowed_packet = 1000000000

Now restart the mysql server with the command /etc/init.d/mysql restart

Fixes are applicable to the following versions of MySql:

1. MySQL 3.23

2. MySQL 4.0

3. MySQL 4.1

4. MySQL 5.0

5. MySQL 5.7

Related Error Tags:

  • How to change max_allowed_packet size
  • unable to restore mysql database, getting ERROR 1153 (08S01)
  • mamp max_allowed_packet
  • mysql workbench max_allowed_packet
  • mysqldump max_allowed_packet
  • #1153 - got a packet bigger than 'max_allowed_packet' bytes phpmyadmin
  • #1153 - got a packet bigger than 'max_allowed_packet' bytes mamp
  • got a packet bigger than 'max_allowed_packet' bytes centos
  • got a packet bigger than 'max_allowed_packet' bytes xampp
  • mysql show max_allowed_packet




Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise