<t>I recently discovered that a sql query that was running fine earlier is now timing out after 60 seconds and throwing an error. The query is slow but runs as part of a nightly job so that's not a problem in itself (so please don't suggest I optimize it). <br/>
<br/>
I'm able to reproduce the error consistently by running "select SLEEP(120);" from PHP as shown below. However, running the same statement from a MySQL client is successful (returns 0). I've tried adjusting wait_timeout (set to 28800), but have had no luck. I've also rebooted both the db server and machine itself.<br/>
<br/>
The fact that it always times out at exactly 60 seconds suggests to me that it is likely to be a setting and not a limited resources issue.<br/>
<br/>
I'm running:<br/>
<br/>
Windows Server 2003<br/>
<br/>
MySql 5.1.36-community<br/>
<br/>
PHP 5.3<br/>
<br/>
Below are my test code, the output and the results from SHOW VARIABLES<br/>
<br/>
Thanks!<br/>
<br/>
CODE:<br/>
<br/>
set_error_handler("sqlErrorHandler");<br/>
set_time_limit(12000);<br/>
<br/>
$link = mysql_connect("$MYSQL_Host","$MYSQL_User","$MYSQL_Pass");<br/>
mysql_select_db($MYSQL_db, $link);<br/>
<br/>
echo "mysql_ping = " . (mysql_ping($link) ? "LIVE" : "DEAD") . "<br /><br />"; <br/>
<br/>
$sql = "SELECT SLEEP(120);";<br/>
<br/>
$start = microtime(true);<br/>
mysql_query($sql, $link);<br/>
<br/>
echo "**query done**<br />";<br/>
allDone();<br/>
<br/>
function allDone(){<br/>
global $start, $sql;<br/>
<br/>
$end = microtime(true);<br/>
echo "sql : $sql<br />";<br/>
echo "elapsed : " . ($end - $start) . "<br />";<br/>
echo "<br />";<br/>
}<br/>
<br/>
function sqlErrorHandler($errno, $errstr, $errfile, $errline){<br/>
global $link;<br/>
echo "Error : $errno<br />$errstr<br />";<br/>
echo "mysql_ping : " . (mysql_ping($link) ? "LIVE" : "DEAD") . "<br />"; <br/>
echo "<br />";<br/>
<br/>
allDone();<br/>
}<br/>
<br/>
```<br/>
<br/>
OUTPUT :<br/>
<br/>
```<br/>
mysql_ping = LIVE<br/>
<br/>
Error : 2<br/>
mysql_query() [function.mysql-query]: MySQL server has gone away<br/>
mysql_ping : DEAD<br/>
<br/>
sql : SELECT SLEEP(120);<br/>
elapsed : 60.051116943359<br/>
<br/>
Error : 2<br/>
mysql_query() [function.mysql-query]: Error reading result set's header<br/>
mysql_ping : DEAD<br/>
<br/>
sql : SELECT SLEEP(120);<br/>
elapsed : 60.0511469841<br/>
<br/>
**query done**<br/>
sql : SELECT SLEEP(120);<br/>
elapsed : 60.051155090332<br/>
<br/>
```<br/>
<br/>
SHOW VARIABLES:<br/>
<br/>
```<br/>
Variable_name=Value<br/>
auto_increment_increment=1<br/>
auto_increment_offset=1<br/>
autocommit=ON<br/>
automatic_sp_privileges=ON<br/>
back_log=50<br/>
basedir=C:\\Program Files\\MySQL\\MySQL Server 5.1\\<br/>
big_tables=OFF<br/>
binlog_cache_size=32768<br/>
binlog_format=STATEMENT<br/>
bulk_insert_buffer_size=8388608<br/>
character_set_client=utf8<br/>
character_set_connection=utf8<br/>
character_set_database=latin1<br/>
character_set_filesystem=binary<br/>
character_set_results=utf8<br/>
character_set_server=latin1<br/>
character_set_system=utf8<br/>
character_sets_dir=C:\\Program Files\\MySQL\\MySQL Server 5.1\\share\\charsets\\<br/>
collation_connection=utf8_general_ci<br/>
collation_database=latin1_swedish_ci<br/>
collation_server=latin1_swedish_ci<br/>
completion_type=0<br/>
concurrent_insert=1<br/>
connect_timeout=10<br/>
datadir=C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\Data\\<br/>
date_format=%Y-%m-%d<br/>
datetime_format=%Y-%m-%d %H:%i:%s<br/>
default_week_format=0<br/>
delay_key_write=ON<br/>
delayed_insert_limit=100<br/>
delayed_insert_timeout=300<br/>
delayed_queue_size=1000<br/>
div_precision_increment=4<br/>
engine_condition_pushdown=ON<br/>
error_count=0<br/>
event_scheduler=OFF<br/>
expire_logs_days=0<br/>
flush=OFF<br/>
flush_time=1800<br/>
foreign_key_checks=ON<br/>
ft_boolean_syntax=+ -><()~*:""&|<br/>
ft_max_word_len=84<br/>
ft_min_word_len=4<br/>
ft_query_expansion_limit=20<br/>
ft_stopword_file=(built-in)<br/>
general_log=OFF<br/>
general_log_file=C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\Data\\p1.log<br/>
group_concat_max_len=1024<br/>
have_community_features=YES<br/>
have_compress=YES<br/>
have_crypt=NO<br/>
have_csv=YES<br/>
have_dynamic_loading=YES<br/>
have_geometry=YES<br/>
have_innodb=YES<br/>
have_ndbcluster=NO<br/>
have_openssl=DISABLED<br/>
have_partitioning=YES<br/>
have_query_cache=YES<br/>
have_rtree_keys=YES<br/>
have_ssl=DISABLED<br/>
have_symlink=YES<br/>
identity=0<br/>
ignore_builtin_innodb=OFF<br/>
init_connect=<br/>
init_file=<br/>
init_slave=<br/>
innodb_adaptive_hash_index=ON<br/>
innodb_additional_mem_pool_size=2097152<br/>
innodb_autoextend_increment=8<br/>
innodb_autoinc_lock_mode=1<br/>
innodb_buffer_pool_size=96468992<br/>
innodb_checksums=ON<br/>
innodb_commit_concurrency=0<br/>
innodb_concurrency_tickets=500<br/>
innodb_data_file_path=ibdata1:10M:autoextend<br/>
innodb_data_home_dir=D:\\MySQL Datafiles\\<br/>
innodb_doublewrite=ON<br/>
innodb_fast_shutdown=1<br/>
innodb_file_io_threads=4<br/>
innodb_file_per_table=OFF<br/>
innodb_flush_log_at_trx_commit=1<br/>
innodb_flush_method=<br/>
innodb_force_recovery=0<br/>
innodb_lock_wait_timeout=50<br/>
innodb_locks_unsafe_for_binlog=OFF<br/>
innodb_log_buffer_size=1048576<br/>
innodb_log_file_size=19922944<br/>
innodb_log_files_in_group=2<br/>
innodb_log_group_home_dir=.\\<br/>
innodb_max_dirty_pages_pct=90<br/>
innodb_max_purge_lag=0<br/>
innodb_mirrored_log_groups=1<br/>
innodb_open_files=300<br/>
innodb_rollback_on_timeout=OFF<br/>
innodb_stats_on_metadata=ON<br/>
innodb_support_xa=ON<br/>
innodb_sync_spin_loops=20<br/>
innodb_table_locks=ON<br/>
innodb_thread_concurrency=8<br/>
innodb_thread_sleep_delay=10000<br/>
innodb_use_legacy_cardinality_algorithm=ON<br/>
insert_id=0<br/>
interactive_timeout=28800<br/>
join_buffer_size=131072<br/>
keep_files_on_create=OFF<br/>
key_buffer_size=50331648<br/>
key_cache_age_threshold=300<br/>
key_cache_block_size=1024<br/>
key_cache_division_limit=100<br/>
language=C:\\Program Files\\MySQL\\MySQL Server 5.1\\share\\english\\<br/>
large_files_support=ON<br/>
large_page_size=0<br/>
large_pages=OFF<br/>
last_insert_id=0<br/>
lc_time_names=en_US<br/>
license=GPL<br/>
local_infile=ON<br/>
log=OFF<br/>
log_bin=OFF<br/>
log_bin_trust_function_creators=OFF<br/>
log_bin_trust_routine_creators=OFF<br/>
log_error=C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\Data\\p1.err<br/>
log_output=FILE<br/>
log_queries_not_using_indexes=OFF<br/>
log_slave_updates=OFF<br/>
log_slow_queries=OFF<br/>
log_warnings=1<br/>
long_query_time=10.000000<br/>
low_priority_updates=OFF<br/>
lower_case_file_system=ON<br/>
lower_case_table_names=1<br/>
max_allowed_packet=1048576<br/>
max_binlog_cache_size=4294963200<br/>
max_binlog_size=1073741824<br/>
max_connect_errors=10<br/>
max_connections=800<br/>
max_delayed_threads=20<br/>
max_error_count=64<br/>
max_heap_table_size=16777216<br/>
max_insert_delayed_threads=20<br/>
max_join_size=18446744073709551615<br/>
max_length_for_sort_data=1024<br/>
max_prepared_stmt_count=16382<br/>
max_relay_log_size=0<br/>
max_seeks_for_key=4294967295<br/>
max_sort_length=1024<br/>
max_sp_recursion_depth=0<br/>
max_tmp_tables=32<br/>
max_user_connections=0<br/>
max_write_lock_count=4294967295<br/>
min_examined_row_limit=0<br/>
multi_range_count=256<br/>
myisam_data_pointer_size=6<br/>
myisam_max_sort_file_size=107374182400<br/>
myisam_recover_options=OFF<br/>
myisam_repair_threads=1<br/>
myisam_sort_buffer_size=12582912<br/>
myisam_stats_method=nulls_unequal<br/>
myisam_use_mmap=OFF<br/>
named_pipe=OFF<br/>
net_buffer_length=16384<br/>
net_read_timeout=30<br/>
net_retry_count=10<br/>
net_write_timeout=80<br/>
new=OFF<br/>
old=OFF<br/>
old_alter_table=OFF<br/>
old_passwords=OFF<br/>
open_files_limit=2048<br/>
optimizer_prune_level=1<br/>
optimizer_search_depth=62<br/>
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on<br/>
pid_file=C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\Data\\p1.pid<br/>
plugin_dir=C:\\Program Files\\MySQL\\MySQL Server 5.1\\lib/plugin<br/>
port=3306<br/>
preload_buffer_size=32768<br/>
profiling=OFF<br/>
profiling_history_size=15<br/>
protocol_version=10<br/>
pseudo_thread_id=3230<br/>
query_alloc_block_size=8192<br/>
query_cache_limit=1048576<br/>
query_cache_min_res_unit=4096<br/>
query_cache_size=33554432<br/>
query_cache_type=ON<br/>
query_cache_wlock_invalidate=OFF<br/>
query_prealloc_size=8192<br/>
rand_seed1=<br/>
rand_seed2=<br/>
range_alloc_block_size=4096<br/>
read_buffer_size=65536<br/>
read_only=OFF<br/>
read_rnd_buffer_size=262144<br/>
relay_log=<br/>
relay_log_index=<br/>
relay_log_info_file=relay-log.info<br/>
relay_log_purge=ON<br/>
relay_log_space_limit=0<br/>
report_host=<br/>
report_password=<br/>
report_port=3306<br/>
report_user=<br/>
rpl_recovery_rank=0<br/>
secure_auth=OFF<br/>
secure_file_priv=<br/>
server_id=0<br/>
shared_memory=OFF<br/>
shared_memory_base_name=MYSQL<br/>
skip_external_locking=ON<br/>
skip_networking=OFF<br/>
skip_show_database=OFF<br/>
slave_compressed_protocol=OFF<br/>
slave_exec_mode=STRICT<br/>
slave_load_tmpdir=C:\\WINDOWS\\TEMP<br/>
slave_net_timeout=3600<br/>
slave_skip_errors=OFF<br/>
slave_transaction_retries=10<br/>
slow_launch_time=2<br/>
slow_query_log=OFF<br/>
slow_query_log_file=C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\Data\\p1-slow.log<br/>
sort_buffer_size=262144<br/>
sql_auto_is_null=ON<br/>
sql_big_selects=ON<br/>
sql_big_tables=OFF<br/>
sql_buffer_result=OFF<br/>
sql_log_bin=ON<br/>
sql_log_off=OFF<br/>
sql_log_update=ON<br/>
sql_low_priority_updates=OFF<br/>
sql_max_join_size=18446744073709551615<br/>
sql_mode=<br/>
sql_notes=ON<br/>
sql_quote_show_create=ON<br/>
sql_safe_updates=OFF<br/>
sql_select_limit=18446744073709551615<br/>
sql_slave_skip_counter=<br/>
sql_warnings=OFF<br/>
ssl_ca=<br/>
ssl_capath=<br/>
ssl_cert=<br/>
ssl_cipher=<br/>
ssl_key=<br/>
storage_engine=InnoDB<br/>
sync_binlog=0<br/>
sync_frm=ON<br/>
system_time_zone=Eastern Daylight Time<br/>
table_definition_cache=256<br/>
table_lock_wait_timeout=50<br/>
table_open_cache=619<br/>
table_type=InnoDB<br/>
thread_cache_size=38<br/>
thread_handling=one-thread-per-connection<br/>
thread_stack=196608<br/>
time_format=%H:%i:%s<br/>
time_zone=SYSTEM<br/>
timed_mutexes=OFF<br/>
timestamp=1256827484<br/>
tmp_table_size=16777216<br/>
tmpdir=C:\\WINDOWS\\TEMP<br/>
transaction_alloc_block_size=8192<br/>
transaction_prealloc_size=4096<br/>
tx_isolation=REPEATABLE-READ<br/>
unique_checks=ON<br/>
updatable_views_with_limit=YES<br/>
version=5.1.36-community<br/>
version_comment=MySQL Community Server (GPL)<br/>
version_compile_machine=ia32<br/>
version_compile_os=Win32<br/>
wait_timeout=28800<br/>
warning_count=0<br/>
<br/>
```</t>
