MySQL server is gone - exactly 60 seconds

Question

I recently discovered that an SQL query that worked before now shuts down after 60 seconds and throws an error. The request is slow, but it is performed as part of the night work, so the problem itself does not arise (therefore, please do not suggest me optimizing it).

I can reproduce the error in sequence by doing "select SLEEP (120)"; from PHP as shown below. However, the execution of the same statement from the MySQL client is successful (returns 0). I tried setting wait_timeout (set to 28800) but no luck. I also rebooted the db server and machine.

The fact that it always expires after exactly 60 seconds tells me that this is most likely to be an installation, and not a problem with limited resources.

I'm runing:
Windows Server 2003
MySql 5.1.36 community
PHP 5.3

Below is my test code, output and results from SHOW VARIABLES

Thank!

CODE:

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(); } 

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 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 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 

Answer 1

For this is the php option mysql.connect_timeout . It is used not only for connection timeout, but also for waiting for the first response from the server. You can increase it as follows:

 ini_set('mysql.connect_timeout', 300); ini_set('default_socket_timeout', 300); 

Answer 2

There are a whole bunch of things that can cause this. I read them and try each of them

http://dev.mysql.com/doc/refman/5.1/en/gone-away.html

I have been working on several web hosting companies over the years, and usually when I see this, this is wait_timeout on the server, although this does not seem to be the case.

If you find a solution, I hope you publish it. I'd like to know.

Answer 3

When I ran into this problem, it was not called wait_timeout (which is set to 8 hours by default), but max_allowed_packet with a large INSERT statement. Changing max_allowed_packet from PHP did not affect, but when I changed it in mysqld / etc / my.cnf and restarted the MySQL server, the problem disappeared.

Answer 4

This is what I do (but usually with the MySQLi class).

 $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 

Answer 5

In this case, it took me longer SQL-Wait-Timeout, try the following:

 mysql_query("SET @@session.wait_timeout=900", $link); 

before the first "normal" SQL query.

Answer 6

My case was a database corruption after a minor upgrade in mysql, basically 5.0.x to 5.1.x, with the database in miisam. Same rows for query: MySQL server has gone Error reading header of result set

After recovering and optimizing with mysqlcheck, it returned to normal without having to change the socket timeout.

Answer 7

I solved this problem with

 if( !mysql_ping($link) ) $link = mysql_connect("$MYSQL_Host","$MYSQL_User","$MYSQL_Pass", true); 

Answer 8

I noticed something that might be appropriate.

I had two scripts executing rather slow queries. One of them locked the table, and the other had to wait. The one that was expecting had default_socket_timeout = 300. In the end, he stopped working with the "MySQL server is gone." However, the mysql process list continued to show both queries: slow, still running, and the other blocked and waiting.

Thus, I do not think mysqld is the culprit. Something has changed in the php mysql client. It is entirely possible default_socket_timeout, which I now set to -1, to see that this will change something.

Answer 9

I am having problems restoring a database using mysqldumper (php program). I managed to get it working by changing the parameter "mssql.timeout" in php.ini. It was 60 by default, and I changed it to 300.

Answer 10

I have the same problem with mysqli. My solution is http://php.net/manual/ru/mysqli.configuration.php

 mysqli.reconnect = On 

Answer 11

In my experience, when this happens on bright requests, there is a way to solve the problem. It seems that when you start or restart mysql after apache this problem starts to appear, and the source of the problem is confused by open sockets in the php process. To solve this problem:

• Restart mysql service first

• Then restart apache service

Answer 12

In our case, the culprit was the global (not "local") MySQL variable " wait_timeout ".

Compare the results of the following queries:

 SHOW VARIABLES LIKE '%wait%'; 

to

 SHOW GLOBAL VARIABLES WHERE Variable_name LIKE '%wait%'; 

In our case, the first request showed a wait_timeout of 28800, but the second request showed a value of 10 (seconds).

We verified that changing the global variable fixed the problem. Here is a simple PHP script that reproduced our condition:

 <?php $db = mysqli_connect('host', 'user', 'password', 'database'); sleep(10); // number of seconds to sleep // MySQL server has gone away? $obj = mysqli_query($db, 'SELECT * FROM some_table'); $results = mysqli_fetch_object($obj); print_r($results); 

As soon as the wait time exceeds the global wait_timeout value, we get the error: "Warning: mysqli_query (): MySQL server has left."

To change the value, we had to edit the setting in our Amazon RDS dashboard.

Answer 13

Please check this link http://bugs.php.net/bug.php?id=45150 it looks like they switched to native MySQL support in PHP5.3 and it has some problems with working with IPV6. Try using "127.0.0.1" instead of "localhost"