我最近发现之前运行正常的sql查询现在在60秒后超时并抛出错误.查询速度很慢,但作为夜间工作的一部分运行,因此本身并不是问题(所以请不要建议我对其进行优化).
我可以通过运行"select SLEEP(120);"来一致地重现错误.来自PHP,如下所示.但是,从MySQL客户端运行相同的语句是成功的(返回0).我已经尝试调整wait_timeout(设置为28800),但没有运气.我还重启了数据库服务器和机器本身.
事实上,它总是在60秒内超时,这表明它可能是一个环境而不是有限的资源问题.
我正在运行:
Windows Server 2003
MySql 5.1.36-community
PHP 5.3
下面是我的测试代码,输出和SHOW VARIABLES的结果
谢谢!
码:
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") . "
";
$sql = "SELECT SLEEP(120);";
$start = microtime(true);
mysql_query($sql, $link);
echo "**query done**
";
allDone();
function allDone(){
global $start, $sql;
$end = microtime(true);
echo "sql : $sql
";
echo "elapsed : " . ($end - $start) . "
";
echo "
";
}
function sqlErrorHandler($errno, $errstr, $errfile, $errline){
global $link;
echo "Error : $errno
$errstr
";
echo "mysql_ping : " . (mysql_ping($link) ? "LIVE" : "DEAD") . "
";
echo "
";
allDone();
}
输出:
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
显示变量:
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 log_slow_queries=OFF log_warnings=1 long_query_time=10.000000 low_priority_updates=OFF lower_case_file_system=ON lower_case_table_names=1 max_allowed_packet=1048576 max_binlog_cache_size=4294963200 max_binlog_size=1073741824 max_connect_errors=10 max_connections=800 max_delayed_threads=20 max_error_count=64 max_heap_table_size=16777216 max_insert_delayed_threads=20 max_join_size=18446744073709551615 max_length_for_sort_data=1024 max_prepared_stmt_count=16382 max_relay_log_size=0 max_seeks_for_key=4294967295 max_sort_length=1024 max_sp_recursion_depth=0 max_tmp_tables=32 max_user_connections=0 max_write_lock_count=4294967295 min_examined_row_limit=0 multi_range_count=256 myisam_data_pointer_size=6 myisam_max_sort_file_size=107374182400 myisam_recover_options=OFF myisam_repair_threads=1 myisam_sort_buffer_size=12582912 myisam_stats_method=nulls_unequal myisam_use_mmap=OFF named_pipe=OFF net_buffer_length=16384 net_read_timeout=30 net_retry_count=10 net_write_timeout=80 new=OFF old=OFF old_alter_table=OFF old_passwords=OFF open_files_limit=2048 optimizer_prune_level=1 optimizer_search_depth=62 optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on pid_file=C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\Data\\p1.pid plugin_dir=C:\\Program Files\\MySQL\\MySQL Server 5.1\\lib/plugin port=3306 preload_buffer_size=32768 profiling=OFF profiling_history_size=15 protocol_version=10 pseudo_thread_id=3230 query_alloc_block_size=8192 query_cache_limit=1048576 query_cache_min_res_unit=4096 query_cache_size=33554432 query_cache_type=ON query_cache_wlock_invalidate=OFF query_prealloc_size=8192 rand_seed1= rand_seed2= range_alloc_block_size=4096 read_buffer_size=65536 read_only=OFF read_rnd_buffer_size=262144 relay_log= relay_log_index= relay_log_info_file=relay-log.info relay_log_purge=ON relay_log_space_limit=0 report_host= report_password= report_port=3306 report_user= rpl_recovery_rank=0 secure_auth=OFF secure_file_priv= server_id=0 shared_memory=OFF shared_memory_base_name=MYSQL skip_external_locking=ON skip_networking=OFF skip_show_database=OFF slave_compressed_protocol=OFF slave_exec_mode=STRICT slave_load_tmpdir=C:\\WINDOWS\\TEMP slave_net_timeout=3600 slave_skip_errors=OFF slave_transaction_retries=10 slow_launch_time=2 slow_query_log=OFF slow_query_log_file=C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\Data\\p1-slow.log sort_buffer_size=262144 sql_auto_is_null=ON sql_big_selects=ON sql_big_tables=OFF sql_buffer_result=OFF sql_log_bin=ON sql_log_off=OFF sql_log_update=ON sql_low_priority_updates=OFF sql_max_join_size=18446744073709551615 sql_mode= sql_notes=ON sql_quote_show_create=ON sql_safe_updates=OFF sql_select_limit=18446744073709551615 sql_slave_skip_counter= sql_warnings=OFF ssl_ca= ssl_capath= ssl_cert= ssl_cipher= ssl_key= storage_engine=InnoDB sync_binlog=0 sync_frm=ON system_time_zone=Eastern Daylight Time table_definition_cache=256 table_lock_wait_timeout=50 table_open_cache=619 table_type=InnoDB thread_cache_size=38 thread_handling=one-thread-per-connection thread_stack=196608 time_format=%H:%i:%s time_zone=SYSTEM timed_mutexes=OFF timestamp=1256827484 tmp_table_size=16777216 tmpdir=C:\\WINDOWS\\TEMP transaction_alloc_block_size=8192 transaction_prealloc_size=4096 tx_isolation=REPEATABLE-READ unique_checks=ON updatable_views_with_limit=YES version=5.1.36-community version_comment=MySQL Community Server (GPL) version_compile_machine=ia32 version_compile_os=Win32 wait_timeout=28800 warning_count=0
reko_t.. 73
php选项mysql.connect_timeout就是这个原因.它不仅用于连接超时,还用于等待服务器的第一个答案.你可以像这样增加它:
ini_set('mysql.connect_timeout', 300);
ini_set('default_socket_timeout', 300);
是!添加ini_set('default_socket_timeout',300)就可以了.谢谢!! (4认同)
如果您通过套接字(例如localhost)连接,请尝试使用它:ini_set('default_socket_timeout',60); (3认同)
arlomedia.. 8
当我遇到这个问题时,它不是由wait_timeout(设置为默认的8小时)引起的,而是由带有大INSERT语句的max_allowed_packet引起的.从PHP更改max_allowed_packet没有任何效果,但是当我在/etc/my.cnf的mysqld部分更改它并重新启动MySQL服务器时,问题就消失了.
php选项mysql.connect_timeout就是这个原因.它不仅用于连接超时,还用于等待服务器的第一个答案.你可以像这样增加它:
ini_set('mysql.connect_timeout', 300);
ini_set('default_socket_timeout', 300);
当我遇到这个问题时,它不是由wait_timeout(设置为默认的8小时)引起的,而是由带有大INSERT语句的max_allowed_packet引起的.从PHP更改max_allowed_packet没有任何效果,但是当我在/etc/my.cnf的mysqld部分更改它并重新启动MySQL服务器时,问题就消失了.
有很多事情可以导致这种情况.我通读了这些内容并尝试了每一个
http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
多年来我一直为几家网络托管公司工作,一般来说,当我看到这一点时,它是服务器端的wait_timeout,虽然这似乎不是这里的情况.
如果您找到解决方案,我希望您发布它.我想知道.
这是我做的（但通常是MySQLi类）。
$link = mysql_connect("$MYSQL_Host","$MYSQL_User","$MYSQL_Pass");
mysql_select_db($MYSQL_db, $link);
// RUN REALLY LONG QUERY HERE
// Reconnect if needed
if( !mysql_ping($link) ) $link = mysql_connect("$MYSQL_Host","$MYSQL_User","$MYSQL_Pass", true);
// RUN ANOTHER QUERY
京公网安备 11010802040832号 | 京ICP备19059560号-6 