How to Fix MySQL Error 1153: Packet Size Exceeds 'max_allowed_packet' Limit?

Troubleshooting MySQL Error 1153: Packet Size Exceeds 'max_allowed_packet' Limit

Database imports can fail with the error "Got a packet bigger than 'max_allowed_packet' bytes" when significant data is encountered, particularly attachments. Here's a detailed guide to resolve this error:

Identifying Affected Components:

• The error can occur due to insufficient packet size limits for both the client (from where the import is executed) and the MySQL daemon (mysqld) handling the import.

Making Changes for the Client:

• Specify the '--max_allowed_packet' option on the command line while running the import command:

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

Updating Server Settings:

• Edit the MySQL configuration file (usually located at /etc/mysql/my.cnf or /etc/mysql/my.ini) and add the following under the [mysqld] section:

max_allowed_packet=100M

Modifying Server Variables:

• If you prefer not to edit configuration files, you can manually set the global server variables involved:

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

Additional Considerations:

• Use large values for 'net_buffer_length' and 'max_allowed_packet' to accommodate significant data.
• Ensure that both the client and server settings for 'max_allowed_packet' are properly set to allow for larger packets during the import.
• If the error persists, consider increasing system memory (RAM) to handle the larger packet sizes.

The above is the detailed content of How to Fix MySQL Error 1153: Packet Size Exceeds 'max_allowed_packet' Limit?. For more information, please follow other related articles on the PHP Chinese website!