Error
Error Code:
1153
MariaDB Error 1153: Packet Too Large
Description
This error occurs when a client or the MariaDB server attempts to send a data packet that exceeds the configured `max_allowed_packet` size. It commonly arises during operations involving large data transfers, such as inserting large BLOBs, importing substantial datasets, or executing complex queries with extensive WHERE clauses.
Error Message
Got a packet bigger than 'max_allowed_packet' bytes
Known Causes
4 known causesLarge Data Operations
Attempting to insert, update, or select rows containing extensive data, such as large BLOBs, TEXT fields, or numerous columns, that exceed the server's packet limit.
Bulk Data Imports
Importing substantial datasets via `LOAD DATA INFILE` or similar bulk operations where the aggregated data size surpasses the `max_allowed_packet` setting.
Client-Side Packet Limit
The client application or connector attempting the operation has its own `max_allowed_packet` setting, or an effective limit, that is lower than the data being sent.
Server Configuration Too Low
The `max_allowed_packet` variable on the MariaDB server is configured to a value that is inadequate for the expected size of data packets.
Solutions
2 solutions available1. Increase max_allowed_packet easy
Allow larger data transfers
1
Check current limit
SHOW VARIABLES LIKE 'max_allowed_packet';2
Increase for current session
SET GLOBAL max_allowed_packet = 64*1024*1024; -- 64MB3
Make permanent in my.cnf
[mysqld]
max_allowed_packet = 64M4
Restart MariaDB
sudo systemctl restart mariadb2. Split Large Data medium
Break up big inserts into smaller batches
1
Insert in batches instead of one large statement
-- Instead of one INSERT with 10000 rows
-- Do multiple INSERTs with 100 rows each
INSERT INTO table VALUES (...), (...), ...; -- 100 rows
INSERT INTO table VALUES (...), (...), ...; -- next 100 rows2
For BLOB data, use smaller chunks or LOAD DATA
LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE mytable
FIELDS TERMINATED BY ',';