This error occurs when MySQL query results or packets exceed the configured max_allowed_packet limit. Fixing it requires increasing the max_allowed_packet setting on both the client and server, or optimizing queries to produce smaller result sets.
The "ERROR 1301: Result larger than max_allowed_packet" error indicates that the MySQL server or client has encountered a packet that exceeds the configured maximum packet size. A packet is any single SQL statement sent to the server, a single row returned to the client, or a binary log event during replication. The max_allowed_packet variable controls the maximum size of data that can be transmitted to or from MySQL. By default, this is set to 64MB in MySQL 8.0+ and 4MB in MySQL 5.7. When a query result, INSERT statement, or any other operation produces data larger than this limit, MySQL closes the connection and returns this error. This commonly occurs with JSON functions like JSON_ARRAY_APPEND(), string functions like REPEAT(), large BLOB/TEXT operations, or bulk imports of large files. The error typically manifests as "ERROR 1301 (HY000): Result of [function_name]() was larger than max_allowed_packet ([size]) - truncated".
First, determine what the current limit is set to on your MySQL server:
SHOW VARIABLES LIKE 'max_allowed_packet';This will show you the current value in bytes. For example, 67108864 = 64MB. Also check the client setting:
SHOW VARIABLES LIKE 'max_allowed_packet' WHERE Variable_name = 'max_allowed_packet';Keep in mind that both the client and server have their own max_allowed_packet settings. If either is too small, you'll encounter this error.
The most reliable solution is to permanently increase max_allowed_packet in your MySQL configuration file. This change persists across server restarts.
For Linux/Unix (my.cnf or mysqld.cnf):
# Find and edit the MySQL config file (usually one of):
# /etc/mysql/my.cnf
# /etc/mysql/mysql.conf.d/mysqld.cnf
# /etc/my.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnfAdd or modify the max_allowed_packet setting under the [mysqld] section:
[mysqld]
max_allowed_packet = 256MFor Windows (my.ini):
# Edit C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
# or the path specific to your MySQL version
[mysqld]
max_allowed_packet = 256MCommon safe values are 128M to 512M depending on your needs. MySQL only allocates the extra memory when needed, so setting a higher value is safe.
The client program (mysql, mysqldump, etc.) also has its own max_allowed_packet setting. Add it to the same configuration file:
[mysql]
max_allowed_packet = 256M
[mysqldump]
max_allowed_packet = 256M
[mysqld]
max_allowed_packet = 256MThis ensures both server and client can handle the larger packets.
After modifying the configuration file, restart MySQL to apply the changes:
# For Linux/Unix with systemd
sudo systemctl restart mysql
# Or using service command
sudo service mysql restart
# For macOS with Homebrew
brew services restart mysqlVerify the change was applied:
SHOW VARIABLES LIKE 'max_allowed_packet';The output should show your new value (e.g., 268435456 for 256M).
If you need a quick temporary fix without restarting, you can set the value dynamically. This change will be lost when the server restarts:
SET GLOBAL max_allowed_packet = 256 * 1024 * 1024;To verify it was set:
SHOW VARIABLES LIKE 'max_allowed_packet';For the client, use the --max-allowed-packet flag when connecting:
mysql --max_allowed_packet=256M -u root -p
mysqldump --max_allowed_packet=256M -u root -p database > backup.sqlIf you cannot increase max_allowed_packet further due to system constraints, consider optimizing your queries:
For large INSERT statements:
-- Instead of one large INSERT
INSERT INTO table VALUES (row1), (row2), ..., (row10000);
-- Split into batches
INSERT INTO table VALUES (row1), (row2), ..., (row100);
INSERT INTO table VALUES (row101), (row102), ..., (row200);For JSON operations:
-- Avoid building massive JSON in a single query
-- Instead, use application logic to build it incrementally
SELECT JSON_OBJECT('key', JSON_ARRAY_AGG(small_chunk)) FROM ...;For REPEAT() or string functions:
-- Break large string operations into multiple statements
-- or use application-level string building
SELECT REPEAT('x', 100000); -- Risky if close to limitReplication Considerations: If you're using MySQL replication, both the source and replica servers must have max_allowed_packet set large enough to handle the binary log events. If the setting is too small on the replica, the replication I/O thread will stop. Always set it to the same value or higher on both servers.
1GB Hard Limit: MySQL has a hard limit on max_allowed_packet of 1GB (1073741824 bytes). This is the theoretical maximum for a single packet, and LONGBLOB columns cannot store more than this amount regardless of the setting. If you need to store larger data, consider splitting it across multiple rows or columns.
Memory Usage: Increasing max_allowed_packet increases the amount of memory allocated for packet buffers. On systems with multiple concurrent connections, this can add up. Monitor your memory usage after increasing the value.
mysqldump Considerations: When using mysqldump to backup large databases, ensure the --max-allowed-packet value is large enough for your largest rows. Similarly, when restoring (via mysql client), set the client-side max_allowed_packet appropriately.
Common Function Errors: Functions like JSON_ARRAY_APPEND(), REPEAT(), and CONCAT_WS() can generate error messages like "ERROR 1301: Result of json_binary::serialize() was larger than max_allowed_packet - truncated". These indicate the function's output exceeded the limit, not necessarily a network packet issue.
Docker and Cloud Environments: If running MySQL in Docker, be aware that custom max_allowed_packet settings in my.cnf may not persist if the container is recreated. Use Docker environment variables or create a custom configuration file and mount it to the container.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL