MySQL Bugs: #3899: ALTER table periodically hangs on Opteron, SuSe Linux

Bug #3899	ALTER table periodically hangs on Opteron, SuSe Linux
Submitted:	26 May 2004 21:13	Modified:	24 Jun 2004 13:51
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.2bk	OS:	Linux (Linux)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
This bugs repets itself on "Melody" host runing Dual Operon.

Periodically (something with 1/10 probability) ALTER TABLE (any) hangs with the following status in the processlist:

mysql> show processlist;
+----+----------+-----------+------+---------+------+-------+----------------------------------------------------------------------------------------------+
| Id | User     | Host      | db   | Command | Time | State | Info                                                                                         |
+----+----------+-----------+------+---------+------+-------+----------------------------------------------------------------------------------------------+
|  2 | pzaitsev | localhost | xx   | Query   |    0 | NULL  | show processlist                                                                             |
|  3 | pzaitsev | localhost | xx   | Query   |   67 | setup | alter table tbl drop key key1, add key(col1) |
+----+----------+-----------+------+---------+------+-------+----------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

VMSTAT shows:

 5  0  0  26428 222644  39196 154860   0   0     0    14  139   446  50   0  50
 3  0  0  26428 222644  39196 154860   0   0     0     0  143   432  50   0  50
 5  0  0  26428 222648  39196 154860   0   0     0     2  123   432  50   0  50
 3  0  0  26428 222648  39196 154860   0   0     0     2  122   434  50   0  50
 5  0  0  26428 222648  39196 154860   0   0     0     0  124   432  50   0  50
 3  0  0  26428 222632  39196 154864   0   0     0     0  124   434  50   0  50

So one CPU of 2 seems to be running in the dead loop.

100% of that CPU is consumed by MySQL process but what is Weird is:

pzaitsev@melody:~> strace -p 14458
--- SIGSTOP (Stopped (signal)) ---

So according to ptrace that process is stopped. 

I've build MySQL on this platform just by ./configure; make 

How to repeat:
See above

Something which might be relevant to this bug:

As you see error message contain different key name compared to originally passed, so it could be some sort of memory corruption happening 

mysql> alter table STUDENT drop key DMN_ID, add key(DMN_ID,STUD_ID);
ERROR 1091: Can't DROP 'STUD_ID'. Check that column/key exists
mysql> alter table STUDENT drop key DMN_ID;
ERROR 1091: Can't DROP '°â@'. Check that column/key exists

I have got the same prob on our dual xeon mysqlserver. The error occurs only if i tried before this to drop a non existing index:
ERROR 1091 (42000): Can't DROP 'bswtext'. Check that column/key exists
alter table add index...
The next drop index statement fails on my system.
Main Problem is that the system hangs and you have to kill the mysql Server with -SIGKILL.

See BUG#4255 for a simpler test case.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet@1.1959, 2004-06-23 11:40:42+04:00, sergefp@mysql.com
  Fix and test case for BUG#3899

Please disregard the previous comment. The fix hasn't been commited yet.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet@1.1958.1.1, 2004-06-23 11:40:42+04:00, sergefp@mysql.com
  Fix and test case for BUG#3899