MySQL Bugs: #24933: Lost connection to MySQL server during query with users have a lot connections

Bug #24933	Lost connection to MySQL server during query with users have a lot connections
Submitted:	9 Dec 2006 11:18	Modified:	15 Jan 2007 12:33
Reporter:	Toan Dang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.30, 4.1.21	OS:	Linux (Centos 4.3)
Assigned to:		CPU Architecture:	Any

Description:
For more than 1 weeks I’ve been trying to find a solution to the error I kept receiving when doing a basic query to MySQL through script shell. I connect to mysql through SSH on web server. If I use the user for web server connect to mysql database to excute some basic query database, for example: use database or select * from... it kept returning back to me “Error 2013: Lost connection to MySQL server during query” with some user. (I have about 200 user connect to web server, in my.cnf I declare max_user_connections=600, max_connection=600, and max_timeout=3600, max_allow_packet: 512M, wait_timeout=3600). But if I use root user to connect and query, there is no error occur.
On the mysqld.log, I got a lots of errors: /var/lib/mysqld: error: Sort aborted.
I try to create another user and use to connect, there is no error.
I grant all privileges to that user and increase net_read_timeout, net_write_timeout,.. but can not solve problem.

I use more than 1 databases for website.
On website, some time it can not get data from database on some tables and some time I get error: Lost connection to MySQL during query or Fata error, can not connect to database...

Right away I went to my PHP, MySQL and Apache logs to find more information but nothing. I came back with nothing more than the error message I already received. So as I usually do off I went to Google for some help. Neither were leading into any direction to a solution. Even after finding the MySQL documentation talking about the “Lost Connection” or “server has gone away” error I didn’t find a solution.

Is it MySQL bug with the user have a lot of connections? How can I fix that problem? I have spent a lot of time for this problem. Our website provide news online so this error is terrible.
Please help me urgent.
Thanks.
PS: I use Centos 4.3 and use MySQL rpm version 4.1.21 supported on Centos 4.3.
Dell server with 4GB RAM, Core 2 Dual 4 CPU 2.8Mhz.

How to repeat:
when do query command line about 5 seconds

Thank you for a problem report. Please, send your my.cnf file content and last part of error log (for the day when you have this problem), or entire error log. You can upload error log as compressed file.

This is my.cnf file of database, please see for more detail.

[mysqld]
skip-locking
skip-name-resolve
datadir=/var/lib/mysql
skip-innodb
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
query_cache_limit=16M
query_cache_size=64M
query_cache_type=1
max_user_connections=600
max_connections=600
interactive_timeout=3600
wait_timeout=3600
connect_timeout=3600
thread_cache_size=256
key_buffer=512M
join_buffer=8M
join_buffer_size=8M
max_allowed_packet=512M
table_cache=2048
record_buffer=16M
sort_buffer_size=256M
read_buffer_size=16M
myisam_sort_buffer_size=256M
read_rnd_buffer_size=16
max_connect_errors=100
thread_concurrency=16
long_query_time=2
#log_slow_queries=/var/log/mysqld_slow.log

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=8192

[mysqldump]
quick
max_allowed_packet=256M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M

[mysqlhotcopy]
interactive-timeout

======================= mysqld.log============
/var/log/mysqld.log:
0611209 11:53:33 [ERROR] /usr/libexec/mysqld: Sort aborted
0611209 11:53:34 [ERROR] /usr/libexec/mysqld: Sort aborted
0611209 11:53:56 [ERROR] /usr/libexec/mysqld: Sort aborted
0611209 11:53:56 [ERROR] /usr/libexec/mysqld: Sort aborted 
............... many lines sort aborted..........

=======Show Status========
Big abort connections

Thanks for your help.

These was the most importnat in your initial report, it seems:

"But if I use root user to connect and query, there is no error occur."

Please, read the manual, http://dev.mysql.com/doc/refman/4.1/en/too-many-connections.html. You just used all your connections (or have simple out of memory issue while sorting data). Send SHOW PROCESSLIST results got as root as soon as you'll see this problem again.

Mysql status & Process log

Attachment: status_process.txt (text/plain), 15.37 KiB.

Our company website have more than 10 modules and 6 databases. Use only one user for all connections. I increase max_user_connections=1000
max_connections=1000 but still can not solve the problems. I sure there are about 150 (max:300) user connections concurrent on website. 
Until now, I solve this problem by separate each module have 1 user connection. I create 10 user connection for 10 modules and now, losting data on the website no happen. But Sort aborted and Lost connection to MySQL during query are still happen a lots. 
But in the future, when one module have more connection, I think the problem will come back. I am not sure this is the best solution.
Please see the log file attach.
Thank for your help
Toan Dang

Is this prosesslist you attached really got while you have Lost connection/Sort aborted errors happening? Please, confirm.

File of processlist & status mysql

Attachment: status_process.txt (text/plain), 10.26 KiB.

Dear Mr.Valeriy Kravchuk
Sorry for reply late.
My company bought MySQL 5 Enterprise, I install on new server (Dell 6850, 16GB, 4 CPU Dual Core 2.8GH) but can not solve the problem.
Sometime, the problem must be occur.
The attach file is mysql processlist and status, please view it for more detail.
Thanks for all help.

Toan Dang

Hi Toan,

If you are a customer already, we have to discuss your problems in frames of support issue. Please, open a new one (you had to get email explaining how to do it) or just send your company name/contract number here (as private comment), and I'll create issue for you.

[SOLVED]
Same problem here too many "[ERROR] /usr/libexec/mysqld: Sort aborted" and application and mysql hangs. We use MySQL 5.0.51b
Our problem was not freeing result. I suggest you use trace mode to view query errors. If you use PHP with MySQL there is an option in php.in "mysql.trace_mode = Off" and you should change it to "On".
That helped us a lot to solve the problem. I think your configuration seems OK. I`m not a DBA and not very advanced in administration.
Hope that will help!
Greetings.