MySQL Server 出现“连接断开 - 正好持续 60 秒”问题的原因及解决方法
MySQL 查询在过去可以正常运行，但现在却在 60 秒后超时并抛出错误，即使该查询运行速度很慢，但作为夜间作业的一部分，这不是问题（因此，请不要建议优化查询）。
我可以通过从 PHP 运行“select SLEEP(120);”来重现该错误，如下所示。但是，从 MySQL 客户端运行相同语句却成功（返回 0）。我尝试调整 wait_timeout（设置为 28800），但没有成功。我还重新启动了数据库服务器和机器本身。
该问题总是会在恰好 60 秒时超时，这让我认为这可能是设置问题，而不是受限资源问题。
我的运行环境如下：
以下是我的测试代码、输出和 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
以上是为什么我的 MySQL 服务器在连接后正好 60 秒就超时？的详细内容。更多信息请关注PHP中文网其他相关文章！
