Why is my MySQL connection timing out after 60 seconds from PHP, but not from the MySQL client?

MySQL server outage - exactly 60 seconds later

Problem description:

I recently found a SQL that was working fine before The query now times out after 60 seconds and throws an error. The query is slower, but it's run as part of a nightly job, so that's not a problem per se (so please don't suggest I optimize it).

I can consistently reproduce the error by running "select SLEEP(120);" from PHP as shown below. However, running the same statement from the MySQL client succeeds (returns 0). I tried adjusting wait_timeout (set to 28800) with no success. I also restarted the database server and the machine itself.

The fact that it always times out at 60 seconds makes me think this is probably a setup issue rather than a resource constraint issue.

My running environment is as follows:

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

The following is my test code, output and SHOW Result of VARIABLES:

Test code:

set_error_handler("sqlErrorHandler");
set_time_limit(12000);

$link = mysql_connect("$MYSQL_Host","$MYSQL_User","$MYSQL_Pass");
mysql_select_db($MYSQL_db, $link);

echo "mysql_ping = " . (mysql_ping($link) ? "LIVE" : "DEAD") . "<br><br>";

$sql = "SELECT SLEEP(120);";

$start = microtime(true);
mysql_query($sql, $link);

echo "**query done**<br>";
allDone();

function allDone(){
 global $start, $sql;

 $end = microtime(true);
 echo "sql : $sql<br>";
 echo "elapsed : " . ($end - $start) . "<br>";
 echo "<br>";
}

function sqlErrorHandler($errno, $errstr, $errfile, $errline){
 global $link;
 echo "Error : $errno<br>$errstr<br>";
 echo "mysql_ping : " . (mysql_ping($link) ? "LIVE" : "DEAD") . "<br>";
 echo "<br>";

 allDone();
}

Output:

mysql_ping = LIVE

Error : 2
mysql_query() [function.mysql-query]: MySQL server has gone away
mysql_ping : DEAD

sql : SELECT SLEEP(120);
elapsed : 60.051116943359

Error : 2
mysql_query() [function.mysql-query]: Error reading result set's header
mysql_ping : DEAD

sql : SELECT SLEEP(120);
elapsed : 60.0511469841

**query done**
sql : SELECT SLEEP(120);
elapsed : 60.051155090332

SHOW VARIABLES :

Variable_name=Value
auto_increment_increment=1
auto_increment_offset=1
autocommit=ON
automatic_sp_privileges=ON
back_log=50
basedir=C:\Program Files\MySQL\MySQL Server 5.1\
big_tables=OFF
binlog_cache_size=32768
binlog_format=STATEMENT
bulk_insert_buffer_size=8388608
character_set_client=utf8
character_set_connection=utf8
character_set_database=latin1
character_set_filesystem=binary
character_set_results=utf8
character_set_server=latin1
character_set_system=utf8
character_sets_dir=C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\
collation_connection=utf8_general_ci
collation_database=latin1_swedish_ci
collation_server=latin1_swedish_ci
completion_type=0
concurrent_insert=1
connect_timeout=10
datadir=C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\Data\
date_format=%Y-%m-%d
datetime_format=%Y-%m-%d %H:%i:%s
default_week_format=0
delay_key_write=ON
delayed_insert_limit=100
delayed_insert_timeout=300
delayed_queue_size=1000
div_precision_increment=4
engine_condition_pushdown=ON
error_count=0
event_scheduler=OFF
expire_logs_days=0
flush=OFF
flush_time=1800
foreign_key_checks=ON
ft_boolean_syntax=+ ->

The above is the detailed content of Why is my MySQL connection timing out after 60 seconds from PHP, but not from the MySQL client?. For more information, please follow other related articles on the PHP Chinese website!