When running a long or complex query on MySQL you might encounter the error below:
Error 2013: Lost connection to MySQL server during query
This error is often faced when running mysqldump on a large database.
In order to fix this error, you will have to update the default MySQL query timeout limits as explained below.
MySQL lost connection errors occur when the client cannot maintain a stable connection to the database server.
The reasons might be varied – network issues, server overloads, configuration limits, or even improper client operations can lead to this error.
Pinpointing the exact cause is the first step toward a solution.
MySQL servers have a default setting that closes idle connections after a certain period. If your application has connections that are idle for longer than this period, increasing the 'wait_timeout' and 'interactive_timeout' variables file can help.
Before updating the timeouts, let's check their current values.
SHOW SESSION VARIABLES LIKE 'wait_timeout'; SHOW SESSION VARIABLES LIKE 'interactive_timeout';
wait_timeout : The number of seconds the server waits for activity on a noninteractive connection before closing it.
interactive_timeout: The number of seconds the server waits for activity on an interactive connection before closing it.
For each the default value is 28800 (seconds) so 8 hours.
Update MySQL timeout via a query
If your queries require more time than the default values returned in the previous step, you can update them as follow:
SET @@GLOBAL.wait_timeout=57600; SET @@GLOBAL.interactive_timeout=57600
In this example, we've doubled the timout to 16 hours.
Update MySQL timeout by updating my.cnf
[mysqld] wait_timeout = 57600 interactive_timeout = 57600
A 'MySQL server has gone away' error can be the result of a packet being too large for the current 'max_allowed_packet' setting. Increase this value in your MySQL configuration file:
Sometimes, the cause is as simple as a poorly optimized query or a table that needs repair. Use the 'OPTIMIZE TABLE' command for defragmenting a table, and ensure your queries are efficient and not causing timeouts.
Resource exhaustion on the server – such as running out of memory or hitting CPU limits – can cause the server to drop connections. Monitor your server's resources and upgrade if necessary.
If your server's 'max_connections' setting is too low, new client connections may be refused. Increasing this limit can help, but be cautious of the resources available on your server.
[mysqld] max_connections = 500
The server logs can indicate whether the MySQL server itself is terminating connections due to errors. Checking the error logs can give you specific insights.
tail -f /var/log/mysql/error.log
Lost connections in MySQL are a common issue but one that can often be resolved with careful configuration and server management.
By following the troubleshooting steps outlined above, you can ensure your MySQL server maintains stable connections, thereby providing a reliable database service to your applications.
Remember to back up your database before making any changes and test your adjustments in a controlled environment whenever possible.
Remember that keeping your MySQL server healthy is an ongoing process.
PostgreSQL, renowned for its robustness and flexibility, is a widely-used open-source database management system. One of its strengths lies…
Server-Side Encryption with Customer-Provided Keys (SSE-C) offers a secure method to store sensitive data in cloud storage services like…
Every savvy computer user knows a data backup is like insurance. You don’t really think about it until you end up needing it. This is no…
Free 7-day trial. No credit card required.
Have a question? Need help getting started?
Get in touch via chat or at [email protected]