Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A single statement or row is larger than max_allowed_packet (error 1153)

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

Short answer

Error 1153 means one packet sent to the server was larger than its max_allowed_packet limit, 16 MB by default on MariaDB. A big BLOB, a very long multi-row INSERT or a dump made with huge extended inserts causes it. Raise the limit on the server and, for tools, on the client, or make the statements smaller.

  • Applies to MariaDB 11.4 LTS, MySQL 8.4 LTS, mariadb and mariadb-dump 11.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the current limit on the server, in bytes, and compare it with the size of the statement or file you are sending.

    SHOW VARIABLES LIKE 'max_allowed_packet';
  2. 2Match what you see, then jump to its fix

    1. 1A single large BLOB, JSON value or long INSERT fails every timeThe server limit is smaller than one row or statementFix
    2. 2The server value is large but the same error appears from mariadb or mariadb-dumpThe client or tool has its own, smaller limitFix
    3. 3Restoring a dump fails on a line that holds thousands of rowsA dump file contains huge multi-row INSERT statementsFix

What does the error look like?

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

Cause 1 of 3

The server limit is smaller than one row or statement

1Check

The limit applies to one packet or one generated string. It should be at least as large as your largest BLOB.

SHOW VARIABLES LIKE 'max_allowed_packet';
SELECT MAX(LENGTH(payload)) FROM documents;

2Fix

Raise it at runtime for new connections, and write the same value in the option file so it survives a restart. The range is 1024 bytes up to 1 GB.

SET GLOBAL max_allowed_packet = 268435456;

# /etc/mysql/mariadb.conf.d/60-packet.cnf
[mysqld]
max_allowed_packet = 256M

3Verify

Reconnect, because the session value of a running connection does not change, then read the value again.

SHOW VARIABLES LIKE 'max_allowed_packet';

Confirmed by mariadb.com

Did this fix it?

Cause 2 of 3

The client or tool has its own, smaller limit

1Check

Clients negotiate their own maximum packet length; if you change it on the server, change it on the client too. The command-line client defaults to 16 MB and accepts up to 1 GB.

mariadb --help | grep -i max.allowed.packet

2Fix

Pass the option to the client, or put it in the client section of the option file.

mariadb --max-allowed-packet=256M -u root -p shop < dump.sql

# option file
[client]
max_allowed_packet = 256M

3Verify

The import runs past the statement that failed.

mariadb -u root -p -e "SELECT COUNT(*) FROM shop.documents"

Confirmed by mariadb.com

Did this fix it?

Cause 3 of 3

A dump file contains huge multi-row INSERT statements

1Check

mariadb-dump builds multi-row INSERT statements by default (extended insert). One statement can be larger than the receiving server's limit.

head -c 600 dump.sql | tail -c 300

2Fix

Dump again with a smaller buffer so statements stay below the receiving limit, or one row per statement.

mariadb-dump --net-buffer-length=1M shop > dump.sql
# or one row per statement (larger and slower to load)
mariadb-dump --skip-extended-insert shop > dump.sql

3Verify

The restore on the target server finishes without error 1153.

mariadb -u root -p shop < dump.sql

Confirmed by mariadb.com

Did this fix it?

How do I stop it coming back?

  • Keep large files in object storage and store only a path in the database.
  • Set max_allowed_packet to the same value on source and target servers before a migration.
  • Dump with a fixed --net-buffer-length so restores do not depend on the target's limit.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.