A single statement or row is larger than max_allowed_packet (error 1153)
ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytesShort 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.
Fix it fast
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';2Match what you see, then jump to its fix
- 1
A single large BLOB, JSON value or long INSERT fails every timeThe server limit is smaller than one row or statementFix - 2
The server value is large but the same error appears from mariadb or mariadb-dumpThe client or tool has its own, smaller limitFix - 3
Restoring a dump fails on a line that holds thousands of rowsA dump file contains huge multi-row INSERT statementsFix
- 1
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 = 256M3Verify
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
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.packet2Fix
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 = 256M3Verify
The import runs past the statement that failed.
mariadb -u root -p -e "SELECT COUNT(*) FROM shop.documents"Confirmed by mariadb.com
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 3002Fix
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.sql3Verify
The restore on the target server finishes without error 1153.
mariadb -u root -p shop < dump.sqlConfirmed by mariadb.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
The connection to MySQL dropped while a query was running (error 2013)
ERROR 2006 (HY000): MySQL server has gone away
Error 2013 means the client sent a query or was waiting for the answer and the connection ended.
4 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix