MySQL Bugs: #36910: Mysql Server has gone away

Bug #36910	Mysql Server has gone away
Submitted:	23 May 2008 10:46	Modified:	27 Sep 2008 10:07
Reporter:	Bijumon K N	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	away, gone

Description:
I have a select statement that on execution produces Mysql server has gone away error often. The select statement is 
   select coalesce(sum(length(ts_Query)),0) as 'SIZECNT' from uploaddata
This select statement gets the size of data in the ts_query column(a text data type), which informs the user the size of data he/she has to upload. Another  select statement that produces the "gone away" error is when selecting customer details( from a table with 50,000 records). 
    

What might be the problem?

My Application is Visual Basic 6.0, Connector - MyODBCv5 - 5.0.11, Mysql - 5.0.45

The my.ini entries are-
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MyApp/mysql/"
#Path to the database root
datadir="C:/Program Files/MyApp/mysql/data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=150
query_cache_size=50
table_cache=128
tmp_table_size=5M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=212K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=32M
innodb_log_file_size=15M
innodb_thread_concurrency=8
max_allowed_packet=50M

How to repeat:
Any select statement on text type data or any very large result set would produce the error.

The my.ini entries are-
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MyApp/mysql/"
#Path to the database root
datadir="C:/Program Files/MyApp/mysql/data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=150
query_cache_size=50
table_cache=128
tmp_table_size=5M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=212K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=32M
innodb_log_file_size=15M
innodb_thread_concurrency=8
max_allowed_packet=50M

Thank you for the report.

But version 5.0.45 is a bit old. Please try with current version 5.0.51a and if problem still exists please provide output of SHOW CREATE TABLE uploaddata and error log file (add option log-error to the configuration file).

Dear Sveta Smirnova,
    Thanks for the reply. Due to technical reason, we can't upgrade our server. 
So we have to find the reason for this. The Show table output is(NOTE: the table name is tstamp, not uploaddata) 

tstamp  CREATE TABLE `tstamp` (                                                               
          `ts_ID` bigint(20) unsigned NOT NULL,                                               
          `ts_Tmp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
          `ts_Query` text,                                                                    
          `Disp_ID` varchar(50) default NULL                                                  
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1        

We switched on the error and warning log option last week end. we found the following lines intersting....

080526 13:30:41 [Warning] Aborted connection 17 to db: 'mysql' user: 'root' host: 'localhost' (Got an error reading communication packets)
080526 20:20:17 [Warning] Aborted connection 32461 to db: 'mysql' user: 'root' host: 'localhost' (Got an error reading communication packets)
080526 22:24:32 [Warning] Aborted connection 40326 to db: 'mysql' user: 'root' host: 'localhost' (Got an error reading communication packets)
080526 22:35:52 [Warning] Aborted connection 41236 to db: 'mysql' user: 'root' host: 'localhost' (Got an error reading communication packets)

Also, I have started to feel that these lines in our my.ini

read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=212K

are too low. Do you think so?

Regards,
Bijumon

Well, I have these errors messages from one of my client's machine..

080527  7:32:56  InnoDB: Started; log sequence number 0 292294507
080527  7:32:56 [Note] C:\Program Files\Carego\mysql\bin\mysqld-nt.exe: ready for connections.
Version: '5.0.45-community-nt-log'  socket: ''  port: 3306  MySQL Community Edition (GPL)
080526  8:06:15 [Warning] Aborted connection 1024 to db: 'mysql' user: 'root' host: 'localhost' (Got an error reading communication packets)
080527  8:58:18 [Warning] Aborted connection 3 to db: 'carego' user: 'carego' host: 'localhost' (Got timeout reading communication packets)
080527  8:58:18 [Warning] Aborted connection 898 to db: 'carego' user: 'carego' host: 'localhost' (Got timeout reading communication packets)
080527 11:35:00 [Warning] Aborted connection 3633 to db: 'mysql' user: 'root' host: 'localhost' (Got an error reading communication packets)
080527 11:37:04 [Warning] Aborted connection 3720 to db: 'mysql' user: 'root' host: 'localhost' (Got an error reading communication packets)
080527 12:34:22 [Warning] Aborted connection 4613 to db: 'mysql' user: 'root' host: 'localhost' (Got an error reading communication packets)
080527 14:23:35 [Note] C:\Program Files\Carego\mysql\bin\mysqld-nt.exe: Normal shutdown

My application logged the "gone away" error at ...
27-05-08 8:58:21 AM  and 27-05-08 8:58:25 AM

That points to the errors
080527  8:58:18 [Warning] Aborted connection 3 to db: 'carego' user: 'carego' host: 'localhost' (Got timeout reading communication packets)
080527  8:58:18 [Warning] Aborted connection 898 to db: 'carego' user: 'carego' host: 'localhost' (Got timeout reading communication packets)

BTW, the "wait_timeout" is set at 28800

So, any opnions or conclusions?

regards,
Vishnu

Thank you for the feedback.

I can not repeat described behavior with test data.

Please try current version 5.0.67 and if problem still exists send output of:

select now();
select coalesce(sum(length(ts_Query)),0) as 'SIZECNT' from tstamp;
select now();

explain extended select coalesce(sum(length(ts_Query)),0) as 'SIZECNT' from tstamp;
show warnings;

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".