MySQL 'Got a packet bigger than max_allowed_packet bytes': Raising It Once Is Not Enough

Forty minutes into restoring a 6GB dump, the import dies. You read the error, find the answer in ninety seconds, set max_allowed_packet = 512M in my.cnf, restart MySQL, and start the import again. Forty minutes later it dies on the same table.

So you set it to 1G. Same failure. Now you are reading forum threads from 2011 and wondering whether the dump file is corrupt.

It is not corrupt. max_allowed_packet is not one setting. It is at least three independent settings with three different defaults, living in three different config sections, and you have been raising one of them. Worse, the tool that produced the dump has its own value which is not the server's, and it silently ignores anything you put under [mysqld].

The exact errors

Three wordings, three different origins, and telling them apart is most of the diagnosis.

From the server, refusing an oversized statement:

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

From mysqldump, choking while reading a row out of the source server. Note the missing "a", which is how you know it came from the client library and not the server:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `tabFile` at row: 18421

And the one that sends people chasing crashes and network faults for an afternoon:

ERROR 2006 (HY000) at line 4429: MySQL server has gone away

That third one is the same bug wearing a disguise. When a client sends a packet larger than the server accepts, the server does not politely reply. It logs the error and closes the connection. From the client's point of view the server vanished mid-sentence. If you see 2006 during a large import, assume packet size first and network second.

Why one change is never enough

MySQL's client/server protocol moves data in packets. A single SQL statement, a single row of a result set, and a single parameter sent through the C API each have to fit inside one packet. The buffer starts small, at net_buffer_length, and grows on demand up to max_allowed_packet. That limit exists deliberately, to stop a malformed or hostile packet from making the server allocate unbounded memory.

The critical detail is that both ends enforce their own limit independently. The server has a value. Every client program has a value. A packet has to be acceptable to whoever is receiving it, and during a dump-and-restore cycle there are three separate transfers, each with its own pair of limits.

The defaults nobody memorises

Where	Default	Notes
Server (mysqld), MySQL 8.x	64MB (67108864)	Minimum 1024, maximum 1GB, rounded down to a multiple of 1024
mysql client	16MB	Overrides the client library default
mysqldump	24MB (25165824)	The docs note this value is specific to mysqldump
Client library built-in	1GB	Individual programs override it, and most do
Protocol ceiling	1GB	Not raisable. This is the wall.

Two things fall out of this table. First, on modern MySQL the server is rarely the tightest limit, which is exactly why raising it there so often changes nothing. Second, MariaDB ships different defaults and has changed them across versions, so if you are on MariaDB, do not trust this table, check with SELECT @@global.max_allowed_packet; and use the number you get.

Fixing it properly

Step 1: Find out what you actually have

SELECT @@global.max_allowed_packet AS server_global,
       @@session.max_allowed_packet AS this_connection;

+---------------+-----------------+
| server_global | this_connection |
+---------------+-----------------+
|      67108864 |        16777216 |
+---------------+-----------------+

That output is the whole article in one query. The server allows 64MB. Your mysql session negotiated 16MB. The statement that failed was somewhere in between, and every hour you spend editing [mysqld] is wasted.

Step 2: Raise the server value, correctly

For an immediate change with no restart:

SET GLOBAL max_allowed_packet = 536870912;

Two traps here. SET SESSION max_allowed_packet is rejected, because the session value is read-only for clients. And SET GLOBAL does not change your current connection: the session inherited its value at connect time. Disconnect and reconnect, then re-run the query in Step 1 to confirm.

To make it permanent, put it in the server section and restart:

[mysqld]
max_allowed_packet = 512M

sudo systemctl restart mysql

On a managed service such as RDS, Cloud SQL or a hosted MariaDB, there is no my.cnf you can edit. Change the parameter group or the provider's equivalent, and expect some of them to require a restart before it applies.

Step 3: Raise it on the client running the import

This is the step that fixes most cases:

mysql --max-allowed-packet=512M -u root -p mydb < dump.sql

The command-line flag uses hyphens, the system variable uses underscores. Both are correct in their own context, and mixing them up produces an unhelpful "unknown variable" error.

Step 4: Put it in the section the tool actually reads

This is the trap that generates the most confused forum posts. mysqldump reads options from the [mysqldump] and [client] groups. It does not read [mysqld]. Neither does the mysql client. So a config file like this fixes the server and nothing else:

[mysqld]
max_allowed_packet = 512M

What you want is this:

[mysqld]
max_allowed_packet = 512M

[mysqldump]
max_allowed_packet = 512M

[mysql]
max_allowed_packet = 512M

Using [client] instead of the last two applies the value to every client program at once, which is convenient and occasionally surprising. I prefer naming the tools explicitly.

Step 5: Regenerate the dump if it was produced with a small limit

If the failure happened during mysqldump rather than during the restore, the dump you have is incomplete. Take it again:

mysqldump --max-allowed-packet=512M --single-transaction --quick \
  -u root -p mydb > dump.sql

--quick streams rows instead of buffering the whole result set in memory, which matters on a small VPS. --single-transaction gives you a consistent snapshot of InnoDB tables without locking writes, which matters if the source is live.

If a single row is genuinely enormous, you can also make the generated statements smaller:

mysqldump --skip-extended-insert --max-allowed-packet=512M -u root -p mydb > dump.sql

This writes one INSERT per row instead of packing many rows into one statement. It makes the file much larger and the import noticeably slower, so it is a targeted fix for a table with a few enormous rows, not a default. It also does nothing if the problem is one single row that is itself too big.

Verification

First confirm the settings actually took, from a fresh connection:

mysql --max-allowed-packet=512M -u root -p -e \
  "SELECT @@global.max_allowed_packet, @@session.max_allowed_packet;"

+-----------------------------+------------------------------+
| @@global.max_allowed_packet | @@session.max_allowed_packet |
+-----------------------------+------------------------------+
|                   536870912 |                    536870912 |
+-----------------------------+------------------------------+

Both numbers matter. If the session column is still 16777216 you passed the flag wrong or your option file is not being read; check with mysql --print-defaults.

Then find your actual largest row, so you know whether the new ceiling is high enough rather than hoping:

SELECT MAX(LENGTH(file_content)) AS biggest_bytes FROM tabFile;

Compare that number to your new limit. If the biggest value is 300MB and your limit is 512M, you have headroom. If it is 1.1GB, stop configuring and read the last section, because no setting will help.

Finally, run the real import and check the exit code rather than eyeballing the output:

mysql --max-allowed-packet=512M -u root -p mydb < dump.sql
echo "exit code: $?"

Zero means it finished. Then count rows in the largest table on both sides and compare. A restore you have not verified is not a restore, which is the argument I make at length in Restore First.

What people get wrong

"I already increased it, it must be something else." You increased one of six values. The single most common version of this is setting it under [mysqld] and expecting mysqldump to obey. It never reads that section.

"SET GLOBAL did not work." It worked. Your current connection kept the value it was given at connect time, and the session variable is read-only so you cannot change it in place. Reconnect.

"Set everything to 1G and forget about it." The limit is a memory safety valve. Each connection can grow its buffer up to that value, so on a box with 200 connections and 4GB of RAM, a 1GB ceiling is a promise you cannot keep. Set it a comfortable margin above your largest real row, not at the maximum. On a 2GB VPS, which is what a lot of small production systems in Nairobi and elsewhere actually run on, this is the difference between a slow import and the OOM killer, a subject I have covered in Killed by the Kernel.

"MySQL server has gone away means the server crashed." Check the error log before you believe that. During a large import it is almost always the server closing the connection after rejecting an oversized packet.

"Just split the dump file with a script." Splitting by line count breaks multi-row INSERT statements in the middle and produces a file that will not parse. If you want smaller statements, get them from mysqldump --skip-extended-insert, which produces valid SQL by construction.

When it is still broken

1. A single row is bigger than 1GB. The protocol ceiling is not configurable. The fix is schema-level: move large files out of the database and store a reference, or split the content across rows. Storing PDFs and images in a LONGBLOB is the usual road here, and the cure is object storage.
2. Timeouts, not packets. net_read_timeout, net_write_timeout and wait_timeout also produce "server has gone away". If the import dies at a consistent wall-clock time rather than a consistent table, look there.
3. Something is in the middle. ProxySQL, HAProxy, a load balancer or a Docker network can impose their own limits, and the error surfaces as a packet or connection error with the database blamed. Test by connecting directly to the database host, bypassing the proxy.
4. Cross-version dumps. A dump taken from an older major version can generate statements the newer target will not accept at its configured limits. Raise the limit on the target first, then, if it persists, dump with --skip-extended-insert so no statement is larger than a single row.
5. The import is just slow and you assumed it hung. Watch progress with pv dump.sql | mysql --max-allowed-packet=512M -u root -p mydb rather than staring at a still cursor. On a home connection or a modest VPS a multi-gigabyte restore taking an hour is normal, not a symptom.

If your dumps are consistently big enough to be hitting this, the real question is whether a nightly logical dump is still the right backup strategy at all. For anything past a few gigabytes, binlogs and point-in-time recovery are both faster to restore and less likely to fail at 90 percent, which I go through in bench backup Is Not a Backup Strategy.