ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

What it means

max_allowed_packet is the largest single message a client and server will pass each other: one SQL statement with all its values, or one row of a result. Error 1153 means the server received a statement bigger than its limit. It refuses it and closes the connection, so whatever you send next fails with a lost-connection error too.

The defaults:

Both ends have a limit. When the client’s is too small for a row coming back, the client reports it: ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes from MySQL’s client.

Different clients describe the closed connection differently. The MariaDB client didn’t show 1153 at all; it said “Server has gone away”, and the reason was only in the server’s error log.

Common causes

1. Restoring a dump made on a server with a bigger limit: mysqldump writes multi-row INSERT statements up to about 1 MB by default, but a single large BLOB or TEXT value makes its row’s statement as big as the value.
2. Storing files or images in the database: one INSERT with a 20 MB file is over MariaDB’s 16 MB default.
3. Bulk inserts built by an application, joining thousands of rows into one statement.
4. A limit lowered in the server’s configuration or a hosted database’s parameter group.

How to fix it

See the current limit

SELECT @@global.max_allowed_packet, @@session.max_allowed_packet;

Raise it on the server

For the running server (needs an administrator account), then in the configuration file so it survives a restart:

SET GLOBAL max_allowed_packet = 256 * 1024 * 1024;

[mysqld]
max_allowed_packet = 256M

Then reconnect: a session takes the value when it connects, and can’t change it itself (SET SESSION max_allowed_packet fails with error 1621, “SESSION variable 'max_allowed_packet' is read-only”). On a hosted database, change it in the provider’s parameter settings.

Raise it on the client too

mysql --max-allowed-packet=256M -h <host> -u <user> -p shop < dump.sql
mysqldump --max-allowed-packet=256M shop > dump.sql

Or send smaller statements

• Re-dump with smaller statements: --net-buffer-length=… caps the size of each multi-row INSERT, and --skip-extended-insert writes one row per statement (slower to restore).
• In application code, insert in batches of a few hundred rows rather than one huge statement.
• Keep large files in object storage and store their path or URL in the table.

When the result is too big

A function whose result would be bigger than the session’s limit returns NULL with a warning in a SELECT, and fails in an UPDATE or INSERT in strict mode:

Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
ERROR 1301 (HY000): Result of concat() was larger than max_allowed_packet (1048576) - truncated

Reproduce it

The shared test servers keep their defaults, so this used temporary MySQL 8.4.11 and MariaDB 11.4.13 containers started with --max-allowed-packet=1M, removed afterwards. A script with a 2 MB INSERT:

CREATE TABLE files (id int AUTO_INCREMENT PRIMARY KEY, body longtext);
INSERT INTO files (body) VALUES ('xxxx…');   -- 2,097,152 characters
SELECT 2;

The MySQL client, against either server:

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes
ERROR 2013 (HY000): Lost connection to MySQL server during query

The MariaDB client against MariaDB:

ERROR 2006 (HY000): Server has gone away
ERROR 2006 (HY000): Server has gone away

MariaDB’s error log gave the reason:

[Warning] Aborted connection 9 to db: 'shop' user: 'root' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)

On the shared MySQL 8.4 server (64 MB limit), reading a 2 MB value with the client’s limit set to 1 MB (mysql --max-allowed-packet=1M) failed on the client side; the MariaDB client said ERROR 2013 (HY000): Lost connection to server during query instead:

ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

In Inlet

When the server rejects a statement or closes the connection, Inlet shows the error and links to this page. To find the rows that are too big to move in one statement, sort by LENGTH(body) in the query editor; long text and binary values open in Inlet’s inspector.