Why does a MySQL query now time out after 60 seconds when it used to execute successfully?

MySQL Server 60-Second Timeout Issue

This article explores an issue where a MySQL query that was previously successful now times out after 60 seconds, displaying the error message "MySQL server has gone away." While the query itself is slow, it has always been part of a nightly job and has never posed a problem until recently.

Problem Symptoms

• The query stalls for exactly 60 seconds before timing out.
• The issue is reproducible by running the statement "SELECT SLEEP(120);" from PHP.
• The same statement executes successfully when run from a MySQL client.
• Adjusting the "wait_timeout" variable to 28800 seconds does not resolve the problem.

Possible Causes

• Limited resources, such as CPU or memory, are not a likely cause since the issue always times out at exactly 60 seconds.
• A potential cause could be an incorrect setting rather than a resource limitation.

Troubleshooting

The default PHP setting "mysql.connect_timeout" is found to be the root of the issue. This setting controls not only the connection timeout but also the waiting time for the server's initial response.

Solution

To increase the waiting time, the following PHP configuration can be used:

ini_set('mysql.connect_timeout', 300);
ini_set('default_socket_timeout', 300); 

This change will increase the waiting time to the specified value, enabling the query to complete without timing out.

The above is the detailed content of Why does a MySQL query now time out after 60 seconds when it used to execute successfully?. For more information, please follow other related articles on the PHP Chinese website!