How to Resolve MySQL Error 1153: 'Got a packet bigger than max_allowed_packet bytes'?

Troubleshooting MySQL Error 1153: "Got a packet bigger than max_allowed_packet bytes"

Error 1153 in MySQL occurs when an attempt is made to transmit a data packet larger than the maximum allowed packet size. This error can arise during data import operations or other database processes that involve large data transfers.

Identifying the Source of the Error

As mentioned in the provided information, the error in this case is encountered during MySQL dump import, indicating that the size of the imported data may exceed the maximum allowed packet size. To resolve this issue, you need to increase the value of the max_allowed_packet parameter.

Modifying the "max_allowed_packet" Parameter

To change the max_allowed_packet value, you need to adjust both the client settings and the server configuration.

1. Client Settings:

Use the following command to modify the client settings:

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

2. Server Configuration:

• my.cnf/my.ini File:

  • Locate the my.cnf or my.ini file (usually in /etc/mysql/).

  • Under the mysqld section, set:

    max_allowed_packet=100M

• MySQL Console:

  • Connect to the MySQL server and execute the following commands:

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

The above is the detailed content of How to Resolve MySQL Error 1153: 'Got a packet bigger than max_allowed_packet bytes'?. For more information, please follow other related articles on the PHP Chinese website!