When Your Database Chokes: A Complete Guide to Conquering MySQL's max_allowed_packet Limit

The error #1153 - Got a packet bigger than 'max_allowed_packet' bytes means your INSERT statement is too large for MySQL to handle in a single request. The solution is to increase the max_allowed_packet limit on both the server and the client side.

What is max_allowed_packet?

In MySQL, a "packet" can be a single SQL statement, a row of data sent to the client, or a binary log event . The max_allowed_packet variable sets an upper limit on the size of these messages . The server's default is 64MB, and the default for the MySQL command-line client (mysql) is 16MB . When a packet exceeds these limits, MySQL rejects it and returns the error you're seeing.

Solutions

You need to increase max_allowed_packet on both the server and client sides. There are two main approaches:

Option 1 (worked) : Temporary Change via SQL (Simple, Quick Test)

This is a quick way to test if a larger limit fixes your problem. The change is temporary and resets when the server restarts .

To increase the limit for all new connections (requires SUPER or SYSTEM_VARIABLES_ADMIN privilege) :

sql

SET GLOBAL max_allowed_packet = 128*1024*1024; -- Sets limit to 128 MB

To increase the limit just for your current connection :

sql

SET SESSION max_allowed_packet = 128*1024*1024; -- Sets limit to 128 MB for this session

Important: If you set the GLOBAL variable, you may need to reconnect for it to take effect in your current session .

Option 2: Permanent Change via Configuration File (Recommended)

This is the most reliable, permanent solution, especially for recurring large imports. You need to edit your MySQL server's configuration file .

1.     Locate the configuration file: Usually my.cnf on Linux/macOS or my.ini on Windows .

2.     Edit the file: Add or modify the max_allowed_packet line under the [mysqld] section to set a larger size .

ini

[mysqld]

max_allowed_packet=256M

3.     Restart the MySQL server for the change to take effect .

For Client-Side Tools

Remember that your client tool also has its own limit. If you're using the mysql command-line client, you can increase its limit when starting it :

bash

mysql --max_allowed_packet=256M -u your_username -p

Alternative Strategy

If increasing the packet size isn't feasible, break your large INSERT into smaller batches. Inserting fewer rows per statement is often more efficient and avoids this issue entirely. You can also consider using LOAD DATA INFILE for very large data imports, as it's generally more performant and bypasses the packet size limit for the SQL statement itself.