MySQL Bugs: #55314: all connection threads are locked and the server is hanging

Bug #55314	all connection threads are locked and the server is hanging
Submitted:	16 Jul 2010 9:00	Modified:	4 Sep 2010 12:04
Reporter:	steven S	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.1.34	OS:	Linux (2.6.16.60 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	myisam locked

Description:
We have about 52 tables on one server, every table have the same structure, something like 

| Table1 | CREATE TABLE `Table1` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `fid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `pos` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 

there are table2, table3,table4... etc.

all the tables use the myisam engine.

now we got a problem, that is the server hang occasionally

the result of show proceslist are something like this:

19863   u1 a:1 a Query   1215    Locked  SELECT id,fid,pos FROM Table1 WHERE id=12312 AND type=6
19864   u1 a:2 a Query   1219    update  REPLACE INTO Table1(id,type,fid,pos) VALUES (493291545,23,16,670)

it seems that an update query locked the table and all other select queries are locked

we have about 80 concurrent connections, when the last the server is hanged, two connections re running the update query on two tables, and other connections are running select queries on these two tables, so these connections get locked.

it seems that the update query never returns.

the error log of mysql shows nothing but some warrings about innodb plugin, but as I mentioned above , all the tables are using myisam, the info from the error log are like:

100629 23:36:03 mysqld_safe mysqld from pid file /usr/local/mysql/data/58.pid ended
100629 23:36:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
100629 23:36:05  InnoDB: Started; log sequence number 0 46409
100629 23:36:05 [Note] Event Scheduler: Loaded 0 events 
100629 23:36:05 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.1.34'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
100629 23:37:01 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

100629 23:37:01 [Note] Event Scheduler: Purging the queue. 0 events
100629 23:37:01  InnoDB: Starting shutdown...
100629 23:37:03  InnoDB: Shutdown completed; log sequence number 0 46409
100629 23:37:03 [Warning] Forcing shutdown of 1 plugins
100629 23:37:03 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

100629 23:37:03 mysqld_safe mysqld from pid file /usr/local/mysql/data/.pid ended
100629 23:37:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
100629 23:37:05  InnoDB: Started; log sequence number 0 46409
100629 23:37:05 [Note] Event Scheduler: Loaded 0 events 
100629 23:37:05 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.1.34'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
100629 23:38:02 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

100629 23:38:02 [Note] Event Scheduler: Purging the queue. 0 events
100629 23:38:02  InnoDB: Starting shutdown...
100629 23:38:03  InnoDB: Shutdown completed; log sequence number 0 46409
100629 23:38:03 [Warning] Forcing shutdown of 1 plugins
100629 23:38:03 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

there are lots of these in the error log

as far as I know , the myisam engine shoule be lock-free, so how does this happen?

How to repeat:

I don't know how to repeat the problem.

Please, check if the same problem happens with a newer version, 5.1.48. If server still hangs, please, send your my.cnf file content.

hi Valeriy Kravchuk 

thank you for your reply.

I can't change the mysql to a newer version now because I don't have the authority.

The content of my.cnf is blow:

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
bind-address    = 172.27.36.90
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 1024M
max_allowed_packet = 1M
table_cache = 8198 
sort_buffer_size = 24M
read_buffer_size = 24M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 8
max_connect_errors = 10000
max_connections = 500
server-id       = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Please, send the entire results of SHOW FULL PROCESSLIST and 

free

Linux command when this hanging happens.

hi Valeriy Kravchuk,

our server hangs again, here is the info of memory usage:

MemTotal:      8171820 kB
MemFree:         67760 kB
Buffers:        133548 kB
Cached:        3101816 kB
SwapCached:      24424 kB
Active:        5442388 kB
Inactive:      2366208 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      8171820 kB
LowFree:         67760 kB
SwapTotal:     2104504 kB
SwapFree:      1675140 kB
Dirty:              64 kB
Writeback:           0 kB
AnonPages:     4570592 kB
Mapped:         424680 kB
Slab:           197516 kB
CommitLimit:   6190412 kB
Committed_AS: 10571192 kB
PageTables:      72772 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    263544 kB
VmallocChunk: 34359474675 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

the result of show processlist is the same as I　described before, one thread is executing an update query which seems never return ,  and all other threads are trun into an status of "Locked".

thank you very much

I still wonder why not to send entire SHOW FULL PROCESSLIST results, as private file if you want, for me to check...

Do you have any triggers defined for that table you run REPLACE on?

Please, send also the results of SHOW GLOBAL STATUS;

hi Valeriy Kravchuk,

I add the full result of show processlist and show status, only change some names.

We don't have triggers.

thank you very much.

there's one thing I forgot to mention, using the kill command in the mysql client can't kill the threads which are executing the update query, but using the "flush tables" command can make the server become normal.

show full processlist

Attachment: processlist.txt (text/plain), 12.22 KiB.

show global status

Attachment: status.txt (text/plain), 6.74 KiB.

Thank you for the feedback.

I can not repeat described behavior with generic test. Additionally version 5.1.34 is old and many bugs were fixed since.

Please try current version 5.1.49 and inform us if bug still exists in your environment.

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