Punca dan penyelesaian untuk Pelayan MySQL "Sambungan terputus - bertahan betul-betul 60 saat" masalah
Pertanyaan MySQL dahulu berjalan seperti biasa, tetapi kini ia tamat masa selepas 60 saat dan melemparkan ralat, walaupun pertanyaan berjalan perlahan, tetapi sebagai sebahagian daripada kerja malam, ini bukan satu isu (jadi tolong jangan cadangkan mengoptimumkan pertanyaan).
Saya dapat menghasilkan semula ralat dengan menjalankan "pilih SLEEP(120);" daripada PHP seperti yang ditunjukkan di bawah. Walau bagaimanapun, menjalankan pernyataan yang sama daripada klien MySQL berjaya (mengembalikan 0). Saya cuba melaraskan tunggu_masa tamat (ditetapkan kepada 28800) tetapi tidak berjaya. Saya juga memulakan semula pelayan pangkalan data dan mesin itu sendiri.
Isu sentiasa tamat tepat pada 60 saat, yang membuatkan saya fikir ia mungkin isu persediaan dan bukannya isu sumber yang terhad.
Persekitaran berjalan saya adalah seperti berikut:
Berikut ialah kod ujian saya, output dan keputusan 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
Atas ialah kandungan terperinci Mengapa Pelayan MySQL Saya Tamat Masa Tepat 60 Saat Selepas Menyambung?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!