Fix Lost Connection to MySQL Server During Query

This guide walks you through the most common causes of the “Lost connection to MySQL server during query� error and shows you step-by-step how to fix it. You’ll learn how to adjust MySQL’s timeout and packet settings, check your network stability, and optimize slow queries so large or long-running operations complete reliably.

What You’ll Need

• Access to the MySQL server’s configuration file (my.cnf on Linux/macOS, my.ini on Windows).
• Ability to restart the MySQL service (or at least reload its configuration).
• Command-line access to run MySQL client commands (mysql, mysqladmin) for testing.
• Basic familiarity with editing text files and running commands as an administrator.

Step-by-Step

1. Identify the exact scenario. The error usually appears in three situations: (a) during a very long-running query (like a huge SELECT or an import), (b) when transferring a large amount of data (e.g., a query that returns millions of rows), or (c) when the network connection drops mid-query. Knowing which one you’re dealing with helps you choose the right fix.
2. Check MySQL’s error log. Look in the data directory (often /var/log/mysql/ or C:\ProgramData\MySQL\MySQL Server 8.0\Data) for a file like hostname.err. Open it and search for “Lost connection� or “Aborted connection�. If you see “Aborted connection … Got an error reading communication packets�, it points to network or timeout issues. If you see “Out of memory� or “Sort aborted�, it’s a query resource problem.
3. Increase the net_read_timeout and net_write_timeout values. These control how long the server waits for data from a client and how long it waits for the client to read data. The default is 30 seconds, which is often too short for slow networks or large queries. Edit your MySQL config file and add (or change) these lines under the [mysqld] section:
   net_read_timeout = 300
net_write_timeout = 300
   This gives each side 5 minutes to finish sending/receiving data.
4. Raise max_allowed_packet. This setting limits the maximum size of a single packet or any string/blob value. The default is 64MB, but large queries (especially with big BLOBs or bulk inserts) can exceed it. Set it to a value larger than your largest expected query result, e.g., max_allowed_packet = 256M. Note: both the server and the client have this setting; if you use the MySQL client, you may also need to set it on the client side (e.g., mysql --max-allowed-packet=256M).
5. Increase wait_timeout and interactive_timeout. These control how long the server waits for a connection to become active before closing it. If you’re running a long query, the connection might be idle while the server processes it, and the server could kill it. Set both to a higher value, like 600 seconds (10 minutes), or longer if needed.
6. Optimize the query itself. If the query is slow because it’s doing a full table scan or sorting a massive result set, the timeouts won’t help. Use EXPLAIN to see the execution plan. Add appropriate indexes, rewrite the query to avoid unnecessary joins or subqueries, or break it into smaller chunks (e.g., process rows in batches using LIMIT).
7. Check network stability. If you’re connecting over a network (especially VPN or Wi-Fi), packet loss or intermittent drops can cause this error. Test with a simple query like SELECT 1 repeatedly. If it fails intermittently, it’s a network issue. Use a wired connection, improve Wi-Fi signal, or increase TCP keepalive settings (on Linux, set net.ipv4.tcp_keepalive_time to a lower value like 300).
8. Restart MySQL. After making changes to the config file, restart the MySQL service. On Linux: sudo systemctl restart mysql (or mysqld). On Windows: open Services (Win+R, type services.msc), find MySQL, and restart it.
9. Test the fix. Run the query that previously failed. If it still fails, check the error log again for new messages. If the error is gone, you’re done.

Common Mistakes

• Only increasing one timeout. Both net_read_timeout and net_write_timeout need to be increased; the error can come from either side.
• Setting max_allowed_packet too low. If you set it to 64M but your query returns 100MB of data, it will still fail. Set it to at least 1.5 times your largest expected result.
• Forgetting to restart MySQL. Changes to the config file don’t take effect until you restart the service.
• Ignoring the client-side settings. If you’re using a client library (like PHP’s PDO or Python’s MySQLdb), it may have its own timeout settings. For example, in PHP, set PDO::ATTR_TIMEOUT to a higher value.

Tips

• Use a dedicated tool for large imports/exports. Instead of running a huge query from a script, use mysqldump with the --max-allowed-packet option and the --quick flag to stream results.
• Monitor your MySQL server. Use SHOW GLOBAL STATUS LIKE 'Aborted_connects' to see if connections are being aborted. This can help you track whether the issue is network-related or query-related.
• Consider using persistent connections. If your application opens a new connection for each query, the overhead can lead to timeouts. Use a connection pool (like HikariCP for Java or SQLAlchemy’s pool for Python) to reuse connections.
• Increase innodb_buffer_pool_size. If the query is slow because of disk I/O, a larger buffer pool (up to 70-80% of available RAM) can speed it up, reducing the chance of timeouts.

Troubleshooting

If the error persists after adjusting timeouts:

• Check for firewall or proxy interference. Some firewalls drop connections that are idle for a certain period. If you have a firewall between your client and server, increase its idle timeout or add a rule to keep the connection alive.
• Test with a different client. If you’re using a GUI tool like MySQL Workbench or a programming language library, try the command-line client. If the command-line client works, the issue is in your client’s configuration.
• Look for “Out of memory� errors in the log. If the query is too large for the server’s memory, you may need to increase sort_buffer_size or join_buffer_size, but be careful: these are per-connection buffers, so setting them too high can exhaust memory.
• Check if the query is being killed by the lock_wait_timeout. If your query is waiting on a lock held by another transaction, it will time out after 50 seconds by default. Increase innodb_lock_wait_timeout if you expect long waits.

Frequently Asked Questions

What does “Lost connection to MySQL server during query� actually mean?

It means the TCP connection between your client and the MySQL server was interrupted while a query was being executed. This can happen because the server closed the connection (due to timeout or error), the network dropped, or the server crashed.

Why does this happen only with large queries?

Large queries take longer to execute and transfer more data, so they’re more likely to exceed the default timeouts or packet size limits. The server may also be under more memory pressure, leading to crashes.

Can I fix this without changing the MySQL config?

Sometimes. If the query is slow, you can optimize it (add indexes, reduce data scanned). If it’s a network issue, you can try a more stable connection. But if the query legitimately needs more time or data, you must adjust the server settings.

Is it safe to increase max_allowed_packet to a very high value?

Generally yes, but it can make the server more vulnerable to denial-of-service attacks if an attacker sends huge packets. Set it to a value that covers your legitimate needs but not excessively high (e.g., 256M is usually fine).

How do I set these values without restarting MySQL?

You can set some variables dynamically with SET GLOBAL net_read_timeout = 300; but they will revert to the config file values after a restart. For a permanent fix, edit the config file.

Why does the error happen after exactly 30 seconds?

That’s the default value of net_read_timeout and net_write_timeout. If your query takes longer than 30 seconds to start sending data, the server times out.

Related

• Can’t Hear Text Messages Through Car Stereo? Fix It
• Import HEIC into Lightroom: Full Guide
• Samsung vs Vizio TVs: Which Should You Buy?