asked on

MySQL server has gone away

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

SOLUTION

Paulo Pimenta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

viznu

ASKER

Couldn't zero in on the bug though, now we feel that it may be due to the connect_timeout issue. We are planning to update the my.ini and test it for a week.
Regards,
Vishnu

viznu

ASKER

The test didn't work out either, my upadted ini is

[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/******/mysql/"
#Path to the database root
datadir="C:/Program Files/*******/mysql/data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
connect_timeout=60
max_connections=150
query_cache_size=8M
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=16M
read_buffer_size=256K
read_rnd_buffer_size=512K
sort_buffer_size=512K
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
#Enter a name for the error log file. Otherwise a default name will be used.
log-error=c:/MysqlErlog.txt
#Enter a name for the slow query log. Otherwise a default name will be used.
log-slow-queries=C:/MySQLSlow.txt
#Log some not critical warnings to the log file.
log-warnings

Still getting the "Gone Away" error

ASKER CERTIFIED SOLUTION

viznu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial