Categories
The Error: Lost connection to MySQL server during query, is due to frequent disconnections or transaction taking too long. This at times, happens when large number of rows are sent/processed during the execution of the query.
This error could be due to the below reasons:
1. Network connectivity issue: If this error occurs frequently, first confirm the condition of your network connectivity. Most of the time this error could be due to the network connectivity, if not then proceed to the second step below, which checks the server for this error.
2. Server side setting: Another probable reason for this error could be processing of multiple rows as a part of the query. If you already know that the query will be processing millions of rows, then the timeout values should be increased so that it gives the query enough time for completion. The timeout values would be normally set to the default value; change it from the default to longer, sufficient for the data transfer to complete. Follow the below steps:
Location : Change these values in the MySQL configuration file at /etc/my.cnf file.
Values to modify:
1. net_read_timeout – This is the number of seconds to wait for more data from a connection before aborting the read. The default value will be 30sec, you can increase it to the desired value.
2. wait_timeout – This is the time in seconds the server waits for a connection to become active before closing it. If you modify the wait_timeout to 28800, it will wait for 8 hours.
3. max_allowed_packet – This is the maximum size in bytes of a packet or a generated string. You can increase this value to 128M or further to 256M
Example
net_read_timeout = 6000 (in seconds)
wait_timeout = 28800 (wait for 8hours)
max_allowed_packet = 128M (or further to 256M)
After this change restart MySQL using the below command
sudo /etc/init.d/mysql restart
This should fix the ‘Lost connection to MySQL server during query’ error. If you still face the issue, please have your server administrator check this further.
If you want all your KPI‘s or Business metrics monitored on a single dashboard, join our BETA.
