Symptoms & Diagnosis
A MySQL connection timeout occurs when the client or server terminates a connection because no data was exchanged within a specific timeframe. This often manifests as the dreaded “Error 2006: MySQL server has gone away” or “Error 2013: Lost connection to MySQL server during query.”
To diagnose the root cause, you must first identify if the issue is network-related or configuration-based. Start by checking the MySQL error logs, usually found in /var/log/mysql/error.log or /var/lib/mysql/hostname.err.
Frequent “Aborted connection” messages in the log suggest that the server is killing idle processes or the client is timing out before the server can respond to a complex query.
Troubleshooting Guide
1. Adjust Global Timeout Variables
The primary culprit is often the wait_timeout variable. This defines how many seconds the server waits for activity on a non-interactive connection before closing it. For web applications, the default is often too low or too high depending on the traffic pattern.
# Check current timeout settings in MySQL
mysql -u root -p -e "SHOW VARIABLES LIKE '%timeout%';"To apply a fix, update your my.cnf or my.ini file under the [mysqld] header. Increasing these values prevents the server from dropping idle connections too aggressively.
[mysqld]
wait_timeout = 28800
interactive_timeout = 28800
connect_timeout = 602. Resolve Packet Size Issues
If your connection drops specifically during large data transfers or bulk inserts, the max_allowed_packet size might be the bottleneck. If a packet is larger than this value, MySQL assumes something is wrong and closes the connection.
[mysqld]
max_allowed_packet = 64M3. Configuration Reference Table
Use the following table to understand which variables impact your specific timeout scenario:
| Variable | Purpose | Recommended Start |
|---|---|---|
| wait_timeout | Seconds to wait for activity on idle connections. | 28800 |
| interactive_timeout | Seconds to wait for activity on interactive shells. | 28800 |
| net_read_timeout | Seconds to wait for more data from a connection. | 60 |
| connect_timeout | Seconds the mysqld server waits for a connect packet. | 30 |
Prevention
The most effective way to prevent future timeouts is to implement connection pooling at the application level. Connection pools manage a cache of database connections, ensuring they stay alive and are reused efficiently rather than being opened and closed repeatedly.
Additionally, ensure your application handles “gone away” exceptions gracefully by implementing a retry mechanism. If the application detects a dropped connection, it should attempt to re-establish the link before failing the user request.
Finally, keep an eye on network latency. If your database and application are in different regions, network instability can trigger connect_timeout errors regardless of your MySQL configuration settings.