MySQL

MySQL "Lost Connection to MySQL Server During Query" on Large Packet Size

"Lost connection to MySQL server during query" when transferring large data — big BLOB/TEXT values, bulk inserts, large result sets — very often means the data exceeds max_allowed_packet, a server-side (and client-side) limit on the maximum size of a single communication packet between client and server.

The Problem

A query involving unusually large data fails partway through:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Smaller, similar queries work fine, and the failure correlates specifically with the size of the data being inserted, updated, or retrieved, rather than happening consistently regardless of data size.

Why It Happens

max_allowed_packet limits the maximum size of a single packet (essentially, a single query or a single row of data) that can be sent between the client and MySQL server — this exists as a safety measure against a client accidentally (or maliciously) sending an enormous amount of data in one go, but its default value is often too conservative for legitimate use cases involving large content. Common triggers:

  • Inserting or updating a row containing a large TEXT, BLOB, or JSON value that exceeds the current packet size limit
  • A bulk insert statement combining many rows into a single large INSERT statement, where the combined size of all rows together exceeds the limit, even though no individual row alone would
  • Retrieving a result set containing an unusually large row or an unusually wide result (many columns, or columns with large content), exceeding the limit on the response side rather than the request side
  • A mismatch between client-side and server-side max_allowed_packet settings — both need to be sufficiently large, since either one being too small can trigger this error

The Fix

First, check the current server-side setting:

SHOW VARIABLES LIKE 'max_allowed_packet';

Increase it to accommodate your actual largest expected data size, with reasonable headroom:

SET GLOBAL max_allowed_packet = 67108864; -- 64MB

This change via SET GLOBAL takes effect immediately for new connections but doesn't persist across a server restart — for a permanent change, also add it to your MySQL configuration file:

# my.cnf
[mysqld]
max_allowed_packet = 64M

Confirm the client side is also configured with an adequate limit, since a client-side default that's smaller than the server's setting can still trigger this error even after raising the server-side value:

# my.cnf
[mysql]
max_allowed_packet = 64M

[client]
max_allowed_packet = 64M

For application code connecting via a specific library or driver, check whether that library has its own separate packet size configuration that also needs adjusting — some database drivers set their own conservative defaults independent of the MySQL configuration files entirely:

# Example: mysql command-line client with explicit flag
mysql --max_allowed_packet=64M -u user -p database < large_import.sql

For bulk inserts specifically, consider whether breaking a very large combined INSERT statement into smaller batches is a better long-term approach than continuing to raise max_allowed_packet indefinitely as data grows — smaller, more frequent inserts are generally more robust and easier to reason about than relying on an ever-increasing packet size limit to accommodate growing bulk operations:

-- Instead of one massive INSERT with thousands of rows,
-- batch into smaller groups, e.g. 500 rows per statement
INSERT INTO orders (customer_id, total) VALUES (1, 99.90), (2, 45.00), ...; -- up to 500 rows

Still Not Working?

If you've raised max_allowed_packet on both client and server and the error persists, check whether a proxy, load balancer, or connection pooler sitting between your application and MySQL has its own separate packet or message size limit — tools like ProxySQL or certain cloud database proxy layers can impose their own limits independent of MySQL's own configuration, and increasing MySQL's setting alone doesn't help if an intermediate layer in the connection path enforces a smaller limit of its own. Reviewing that specific intermediary's own configuration documentation is necessary in that case, since the fix requires adjusting a setting outside of MySQL itself entirely.

You might also like