Causes and solutions for MySQL Server "Connection dropped - lasts exactly 60 seconds" problem
MySQL queries used to run normally , but now it times out after 60 seconds and throws an error, even though the query is running slowly, but as part of a nightly job, this is not an issue (so please don't suggest optimizing the query).
I was able to 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 issue always times out at exactly 60 seconds, which makes me think it might be a setup issue rather than a constrained resource issue.
My running environment is as follows:
The following is my test code, output and results of SHOW VARIABLES:
<code class="php">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();
}</code>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=+ -><()~*:""&| ft_max_word_len=84 ft_min_word_len=4 ft_query_expansion_limit=20 ft_stopword_file=(built-in) general_log=OFF general_log_file=C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\Data\p1.log group_concat_max_len=1024 have_community_features=YES have_compress=YES have_crypt=NO have_csv=YES have_dynamic_loading=YES have_geometry=YES have_innodb=YES have_ndbcluster=NO have_openssl=DISABLED have_partitioning=YES have_query_cache=YES have_rtree_keys=YES have_ssl=DISABLED have_symlink=YES identity=0 ignore_builtin_innodb=OFF init_connect= init_file= init_slave= innodb_adaptive_hash_index=ON innodb_additional_mem_pool_size=2097152 innodb_autoextend_increment=8 innodb_autoinc_lock_mode=1 innodb_buffer_pool_size=96468992 innodb_checksums=ON innodb_commit_concurrency=0 innodb_concurrency_tickets=500 innodb_data_file_path=ibdata1:10M:autoextend innodb_data_home_dir=D:\MySQL Datafiles\ innodb_doublewrite=ON innodb_fast_shutdown=1 innodb_file_io_threads=4 innodb_file_per_table=OFF innodb_flush_log_at_trx_commit=1 innodb_flush_method= innodb_force_recovery=0 innodb_lock_wait_timeout=50 innodb_locks_unsafe_for_binlog=OFF innodb_log_buffer_size=1048576 innodb_log_file_size=19922944 innodb_log_files_in_group=2 innodb_log_group_home_dir=.\ innodb_max_dirty_pages_pct=90 innodb_max_purge_lag=0 innodb_mirrored_log_groups=1 innodb_open_files=300 innodb_rollback_on_timeout=OFF innodb_stats_on_metadata=ON innodb_support_xa=ON innodb_sync_spin_loops=20 innodb_table_locks=ON innodb_thread_concurrency=8 innodb_thread_sleep_delay=10000 innodb_use_legacy_cardinality_algorithm=ON insert_id=0 interactive_timeout=28800 join_buffer_size=131072 keep_files_on_create=OFF key_buffer_size=50331648 key_cache_age_threshold=300 key_cache_block_size=1024 key_cache_division_limit=100 language=C:\Program Files\MySQL\MySQL Server 5.1\share\english\ large_files_support=ON large_page_size=0 large_pages=OFF last_insert_id=0 lc_time_names=en_US license=GPL local_infile=ON log=OFF log_bin=OFF log_bin_trust_function_creators=OFF log_bin_trust_routine_creators=OFF log_error=C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\Data\p1.err log_output=FILE log_queries_not_using_indexes=OFF log_slave_updates=OFF
The above is the detailed content of Why Does My MySQL Server Time Out at Exactly 60 Seconds After Connecting?. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
