[fix]-MySQL Error 1153 - Got a packet bigger than max_allowed_packet bytes
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)
- 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
