Bug #26508 Client connection is closed when executing query
Submitted: 20 Feb 2007 18:05 Modified: 23 May 2007 10:15
Reporter: David Crimmins Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S1 (Critical)
Version:5.0.22-pro-nt-log OS:Microsoft Windows (windows server 2000)
Assigned to: CPU Architecture:Any

[20 Feb 2007 18:05] David Crimmins
Description:
Connection is lost while executing a large insert query (approx 512K). Problem was first seen using C api. But can be duplicated using msql client. We have only experienced this on one of our customers servers and have been unable to replicate it in house. Customers server details:
OS Name 		Microsoft Windows 2000 Server
Version			5.0.2195 Service Pack 4 Build 2195
Processor		x86 Family 15 Model 3 Stepping 4GenuineIntel 3GHz
Processor		x86 Family 15 Model 3 Stepping 4GenuineIntel 3GHz
Physical Memory		1GB

show global variables output:
Variable_name	Value
auto_increment_increment	1
auto_increment_offset	1
automatic_sp_privileges	ON
back_log	100
basedir	D:\\EntuitySoftware\\database\\
binlog_cache_size	32768
bulk_insert_buffer_size	8388608
character_set_client	latin1
character_set_connection	latin1
character_set_database	latin1
character_set_filesystem	binary
character_set_results	latin1
character_set_server	latin1
character_set_system	utf8
character_sets_dir	D:\\EntuitySoftware\\database\\share\\charsets\\
collation_connection	latin1_swedish_ci
collation_database	latin1_swedish_ci
collation_server	latin1_swedish_ci
completion_type	0
concurrent_insert	1
connect_timeout	5
datadir	D:\\EntuitySoftware\\database\\data\\
date_format	%Y-%m-%d
datetime_format	%Y-%m-%d %H:%i:%s
default_week_format	0
delay_key_write	ALL
delayed_insert_limit	500
delayed_insert_timeout	300
delayed_queue_size	10000
div_precision_increment	4
engine_condition_pushdown	OFF
expire_logs_days	0
flush	OFF
flush_time	1800
ft_boolean_syntax	+ -><()~*:""&|
ft_max_word_len	84
ft_min_word_len	4
ft_query_expansion_limit	20
ft_stopword_file	(built-in)
group_concat_max_len	1024
have_archive	YES
have_bdb	NO
have_blackhole_engine	NO
have_compress	YES
have_crypt	NO
have_csv	NO
have_example_engine	NO
have_federated_engine	NO
have_geometry	YES
have_innodb	DISABLED
have_isam	NO
have_ndbcluster	NO
have_openssl	DISABLED
have_query_cache	YES
have_raid	NO
have_rtree_keys	YES
have_symlink	YES
init_connect	
init_file	
init_slave	
innodb_additional_mem_pool_size	1048576
innodb_autoextend_increment	8
innodb_buffer_pool_awe_mem_mb	0
innodb_buffer_pool_size	8388608
innodb_checksums	ON
innodb_commit_concurrency	0
innodb_concurrency_tickets	500
innodb_data_file_path	
innodb_data_home_dir	
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_arch_dir	
innodb_log_archive	OFF
innodb_log_buffer_size	1048576
innodb_log_file_size	5242880
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_support_xa	ON
innodb_sync_spin_loops	20
innodb_table_locks	ON
innodb_thread_concurrency	8
innodb_thread_sleep_delay	10000
interactive_timeout	28800
join_buffer_size	131072
key_buffer_size	201326592
key_cache_age_threshold	300
key_cache_block_size	1024
key_cache_division_limit	100
language	D:\\EntuitySoftware\\database\\share\\english\\
large_files_support	ON
large_page_size	0
large_pages	OFF
license	Commercial
local_infile	ON
log	OFF
log_bin	OFF
log_bin_trust_function_creators	OFF
log_error	D:\\EntuitySoftware\\log\\mysqld.error.log
log_slave_updates	OFF
log_slow_queries	ON
log_warnings	1
long_query_time	15
low_priority_updates	OFF
lower_case_file_system	ON
lower_case_table_names	2
max_allowed_packet	1048576
max_binlog_cache_size	4294967295
max_binlog_size	1073741824
max_connect_errors	999999999
max_connections	400
max_delayed_threads	100
max_error_count	64
max_heap_table_size	16777216
max_insert_delayed_threads	100
max_join_size	4294967295
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
multi_range_count	256
myisam_data_pointer_size	6
myisam_max_sort_file_size	2147483647
myisam_recover_options	FORCE
myisam_repair_threads	1
myisam_sort_buffer_size	8388608
myisam_stats_method	nulls_unequal
named_pipe	OFF
net_buffer_length	16384
net_read_timeout	30
net_retry_count	10
net_write_timeout	60
new	OFF
old_passwords	OFF
open_files_limit	2010
optimizer_prune_level	1
optimizer_search_depth	62
pid_file	D:/EntuitySoftware/log/mysqld.pid
prepared_stmt_count	0
port	3306
preload_buffer_size	32768
protocol_version	10
query_alloc_block_size	8192
query_cache_limit	1048576
query_cache_min_res_unit	4096
query_cache_size	0
query_cache_type	ON
query_cache_wlock_invalidate	OFF
query_prealloc_size	8192
range_alloc_block_size	2048
read_buffer_size	131072
read_only	OFF
read_rnd_buffer_size	262144
relay_log_purge	ON
relay_log_space_limit	0
rpl_recovery_rank	0
secure_auth	OFF
shared_memory	OFF
shared_memory_base_name	MYSQL
server_id	0
skip_external_locking	ON
skip_networking	OFF
skip_show_database	OFF
slave_compressed_protocol	OFF
slave_load_tmpdir	D:\\EntuitySoftware\\database\\data\\temp\\
slave_net_timeout	3600
slave_skip_errors	OFF
slave_transaction_retries	10
slow_launch_time	2
sort_buffer_size	2097144
sql_mode	
sql_notes	ON
sql_warnings	ON
storage_engine	MyISAM
sync_binlog	0
sync_frm	ON
system_time_zone	India Standard Time
table_cache	500
table_lock_wait_timeout	50
table_type	MyISAM
thread_cache_size	0
thread_stack	196608
time_format	%H:%i:%s
time_zone	SYSTEM
timed_mutexes	OFF
tmp_table_size	33554432
tmpdir	D:/EntuitySoftware/database/data/temp
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
tx_isolation	REPEATABLE-READ
updatable_views_with_limit	YES
version	5.0.22-pro-nt-log
version_comment	MySQL Pro (Commercial)
version_compile_machine	ia32
version_compile_os	Win32
wait_timeout	86400

How to repeat:

mysql> create database daves;
Query OK, 1 row affected (0.01 sec)

mysql> use daves;
Database changed
mysql> CREATE TABLE `dstransform` (
    ->  `dsTransformId` int(10) unsigned NOT NULL auto_increment,
    ->   `dsName` char(64) NOT NULL default '',
    ->   `dsDescr` char(128) NOT NULL default '',
    ->   `dsClientData` blob,
    ->   `dsInputTypeId` int(10) unsigned NOT NULL,
    ->   `dsOutputTypeId` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY  (`dsTransformId`),
    ->   UNIQUE KEY `dsName` (`dsName`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> source ..\..\install\transform.sql
ERROR 2006 (HY000): MySQL server has gone awaymysql> source ..\..\install\transform.sql
ERROR 2006 (HY000): MySQL server has gone away
[20 Feb 2007 18:09] David Crimmins
single insert statement

Attachment: transform.zip (application/x-zip-compressed, text), 176.09 KiB.

[21 Mar 2007 13:19] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.36/5.0.37, and inform about the results.
[13 Apr 2007 12:16] Shane Bester
David, you've given us the wrong create table statement for the data.
please send us 

SHOW CREATE TABLE DSALPHA.dsTransformData\G

after trying on the latest version of mysql.
[16 Apr 2007 10:07] David Crimmins
Sorry for the error.

The origninal problem occured during a trial at a customers site. We no longer have access so cannot try a later version of the database. We are unable to replicate the problem on any other computer.

The following output is from 5.0.22-pro-nt-log
 
mysql> SHOW CREATE TABLE DSALPHA.dsTransformData\G
*************************** 1. row ***************************
       Table: dsTransformData
Create Table: CREATE TABLE `dsTransformData` (
  `dsTransformId` int(10) unsigned NOT NULL,
  `dsInputData` blob NOT NULL,
  `dsOutputData` blob,
  PRIMARY KEY  (`dsTransformId`,`dsInputData`(128))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[23 May 2007 10:15] Valeriy Kravchuk
As even reporter can not repeat this problem any more, I think, "Can't repeat" is a more appropriate status for this bug report.