Why is my MySQL query timing out after 60 seconds, even though `wait_timeout` is much higher?

MySQL server has gone away - 60 seconds away from what was expected

This article discusses what caused the MySQL server to go through a query that lasted 120 seconds Issue that times out after 60 seconds and throws an error. Although the query was running fine, an issue in the PHP script caused this error.

Problem Symptoms

• Running query via PHP SELECT SLEEP(120); times out after 60 seconds.
• Running the same query from the MySQL client executes successfully.
• The timeout is a fixed 60 seconds each time, suggesting it may be a setup issue rather than a resource limit.

System Configuration

• Windows Server 2003
• MySql 5.1.36-community
• PHP 5.3

Analysis

After checking the SHOW VARIABLES output, we found that wait_timeout has been set to 28800 (480 minutes), but the error still exists. This shows that wait_timeout is not the problem.

Solution

The cause of the problem is the PHP option mysql.connect_timeout. This option is used not only for connection timeouts but also for waiting for the first response from the server. Since the query duration is set to 120 seconds and mysql.connect_timeout is set to 60 seconds by default, the server closes the connection after 60 seconds, causing the error.

Resolved this issue by increasing mysql.connect_timeout to at least 120 seconds. This is achieved with the following code:

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

This increases mysql.connect_timeout to 300 seconds, avoiding prematurely closing the connection while waiting for the server to respond.

The above is the detailed content of Why is my MySQL query timing out after 60 seconds, even though `wait_timeout` is much higher?. For more information, please follow other related articles on the PHP Chinese website!