Why Does My Long-Running MySQL Query Result in a \'MySQL Server Has Gone Away\' Error After 60 Seconds?

MySQL Server Has Gone Away - Unexpected Timeout Issue

When executing a long-running query (120 second sleep), users have encountered an issue where the process fails with the error message "MySQL server has gone away." This occurs after exactly 60 seconds. The problem persists on both Windows Server 2003 and MySQL 5.1.36-community.

Troubleshooting Steps

The user has tried various troubleshooting measures, including adjusting the wait_timeout setting to 28800 seconds and rebooting the database server and machine. Despite these efforts, the timeout error persists.

Possible Cause

The issue appears to be related to a setting rather than a resource shortage, as the timeouts consistently occur at the 60-second mark.

Solution

The problem stems from the PHP option mysql.connect_timeout. While its primary purpose is to specify the connect timeout, it also determines the time allowed for the first server response. To resolve the issue, the user needs to increase this timeout value as follows:

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

By setting these values to 300 seconds (5 minutes), the system waits for server responses for a longer duration before timing out. This allows the long-running query to complete successfully.

The above is the detailed content of Why Does My Long-Running MySQL Query Result in a \'MySQL Server Has Gone Away\' Error After 60 Seconds?. For more information, please follow other related articles on the PHP Chinese website!