The restore died at 03:40, sixty-one percent through a 40GB mysqldump, with ERROR 1153 (08S01) at line 1844207: Got a packet bigger than 'max_allowed_packet' bytes. The dump had taken the whole evening, the restore was the recovery path for a corrupted replica, and the failing row was a single support ticket with a 6MB screenshot attached as a LONGBLOB — written to the primary months earlier through an application whose connection had a large packet allowance, dumped fine because mysqldump only reads, and now un-loadable because the mysql client doing the restore was running with a smaller packet limit than the row. Nothing was corrupt; the dump was a faithful copy of data the toolchain could not ingest. We raised the flag, restarted the restore from the beginning, and lost another ninety minutes. max_allowed_packet is one of those settings nobody thinks about until it is the only thing between them and sleep, and its behavior has just enough client-side subtlety — different defaults per tool, a server-side ceiling, a replication variant with its own name — that guessing at it produces exactly this kind of night.
What does max_allowed_packet actually limit, and what are the real defaults?
It limits the size of a single network packet — which in practice means a single statement or a single row — and the defaults differ between the server and every client tool, which is the whole trap. On the server side, max_allowed_packet defaults to 4MB in MySQL 5.7 and 64MB since MySQL 8.0.3, with a hard maximum of 1GB; it is dynamic, but a running connection's session value is fixed from the global value at connect time, so raising it with SET GLOBAL does nothing for connections that already exist. On the client side, each tool carries its own compiled-in default that overrides nothing and surprises everyone: the mysql CLI defaults to 16MB and mysqldump to 24MB (and the client-side maximum is also 1GB). So a statement can be legal on the server, fail in the client, or the reverse. The packet being measured is not a TCP packet — it is the protocol-level message: for a multi-row INSERT, the entire statement; for a row with a 100MB BLOB, that row. Worth knowing about memory: the server does not pre-allocate max_allowed_packet per connection; the net buffer starts at net_buffer_length (default 16KB) and grows only as needed, so setting a generous ceiling is not a memory bomb — the lazy "just set 1G everywhere" instinct is wrong for other reasons, which we will get to.
-- the server side
SELECT @@global.max_allowed_packet, @@session.max_allowed_packet;
-- raise it live (affects only new connections)
SET GLOBAL max_allowed_packet = 256*1024*1024;
-- the client side is a flag, not a session variable —
-- this is what the restore needed:
-- mysql --max_allowed_packet=256M < dump.sql
-- mysqldump --max_allowed_packet=512M ... > dump.sql
-- what the failure looks like server-side in the error log:
-- [Note] Aborted connection 91 to db: 'app' user: 'loader'
-- (Got a packet bigger than 'max_allowed_packet' bytes)Why does a restore fail when the dump and the original writes succeeded?
Because the three legs of the journey — application write, dump, reload — each have their own effective packet limit, and the restore is usually the smallest one. The application's connector negotiated a connection under a server max_allowed_packet generous enough for the 6MB row. mysqldump never sends the row as a packet — it reads rows and writes text to a file, so its own limit only governs its read buffer, and even a modest default lets the dump complete and look healthy; nothing validates that the output can be replayed. The reload through the mysql client then hits its 16MB client default or the server's 4MB 5.7 default on the first oversized statement, and ERROR 1153 aborts the load wherever it happens to be — in my case at 61 percent, leaving a half-restored replica. The operational rule that came out of that night: every dump-and-restore runbook sets the flag explicitly on both ends, and the restore command's allowance must cover the largest row in the dataset, not the average one. If you use the logical dump tools compared in the mydumper versus mysqldump notes, the same rule applies to myloader — chunk-per-table restores fail the same way on the one table with fat BLOBs, they just fail faster and in parallel.
How does the packet limit stop a replica instead of erroring a query?
Replication has its own ceiling — slave_max_allowed_packet, default 1GB — and when a single binlog event exceeds it, the replica's SQL thread stops with a packet error rather than skipping the event, so the failure mode is a halted replica, not a rejected statement. The protection exists so a replica cannot be fed an event larger than it can buffer; the trigger in practice is a primary whose application wrote rows approaching that ceiling — think a batch job assembling hundred-megabyte JSON documents or a multi-row INSERT the size of a small table — which replicates as one event. The replica error log shows the SQL thread exiting with the packet-too-large message, SHOW REPLICA STATUS shows Last_SQL_Errno 1153-class text, and because the offending event is the next thing in the stream, restarting the thread just fails again at the same position. The fix path matters: raising slave_max_allowed_packet alone is not sufficient, because the replica's own max_allowed_packet must also admit the row when the event applies, so you raise both, resume, and then have the harder conversation about whether hundred-megabyte rows belong in the schema at all. If your replicas lag for other reasons too, the replication lag diagnosis flow separates a stopped thread from a merely-behind one in the first minute.
How do you size max_allowed_packet instead of blindly setting 1G?
Size it to roughly twice your largest legitimate single row or statement, verified from data rather than vibes, because the setting is a sanity boundary as much as an allowance. A ceiling that admits everything admits anything: an ORM that decides to build a 900MB INSERT is a bug report, and a packet limit that lets it through converts that bug into memory pressure on the server and a binlog event every replica must swallow — the binlog row image notes cover what oversized events do downstream of the binlog. Finding your real largest row is the unglamorous part: information_schema gives you AVG_ROW_LENGTH, not the max, so the honest approaches are checking the application for BLOB/TEXT columns and their actual payloads — a quick SELECT MAX(LENGTH(doc_payload)) per suspect table tells you the truth in seconds — and watching for the error itself, since 1153 names the boundary it hit. On a system whose largest legitimate payload is 20MB of document JSON, a 64MB server default plus explicit client flags in the backup runbook is a complete answer, and it keeps the boundary meaningful. Also check the errant paths: load tools, ETL jobs, and admin scripts each open their own connections, so a fixed server value with forgotten client flags is the same incident waiting on a different schedule.
What is the checklist that makes this a non-event?
Four items, all cheap. One: set max_allowed_packet in my.cnf to a deliberate value sized as above — on 8.0 the 64MB default is right for most workloads, on 5.7 raise it from 4MB before you learn why the hard way. Two: hard-code --max_allowed_packet on both the dump and the load in every backup/restore runbook, sized to the dataset's largest rows, and test the runbook end to end — an untested restore path with a default flag is how you meet ERROR 1153 at 03:40. Three: on replicas, confirm slave_max_allowed_packet exceeds the primary's max_allowed_packet, so no legal primary write can halt the SQL thread. Four: alert on the error text in the error log, because the first occurrence is almost always a new code path — a migration, a new attachment feature, an ETL change — and catching it at one failed statement beats catching it at a halted replica. None of this is glamorous, and that is the point: packet limits are the kind of boundary that should be set from measurement, written into runbooks, and never thought about again.
Where MonPG stands on MySQL
I build MonPG, so the honest line: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — error-log events like 1153 surfaced with the connection that caused them, replication thread stops distinguished from lag, and binlog event sizes trended before they hit a ceiling — are exactly what the MySQL work is designed to surface, so a packet ceiling shows up as a warning, not a failed restore. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side today, and the rest of these MySQL field notes live on the blog.