MySQL Bugs: #4255: ALTER TABLE after some invalid queries causes the server to hang

Bug #4255	ALTER TABLE after some invalid queries causes the server to hang
Submitted:	23 Jun 2004 2:56	Modified:	23 Jun 2004 9:18
Reporter:	Sergey Petrunya	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0-bk, 4.1-bk	OS:	Linux (linux, win32)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Issuing ALTER TABLE after some sequence of queries (see below) causes mysqld to hang forever, consuming 100% cpu.
If mysqld is killed during ALTER TABLE and then re-started, the table will be unouched, and re-running the same ALTER TABLE command will finish in less then one second. 

I've repeated the problem with MyISAM and InnoDB tables. 
The sample above contains queries which result in error. If I remove these statements, the problem disappears. 

How to repeat:
Run the below queries and see mysqld to hang on last 'alter table'
(I'll attach the table dump).

show databases;
show tables;
select * from t0 limit 10;
insert into t0 (key1) values (null);
alter table t0 add keyA int;
select count(*) from t0 limit 10;
select count(*) from t0 limit 10;
select * from t0 limit 10;
update t0 set keyA=key1;
update t0 set keyA=NULL where key1 <= 10;
select * from t0 limit 10;
alter table t0 add key (keyA);
explain select * from t0 where keyA is null or keya=4;
select max(A) from (select count(*) as A from t0 group by key1);
select max(A) from (select count(*) as A from t0 group by key1 as U );
select max(A) from (select count(*) as A from t0 group by key1 );
select max(A) from (select count(*) as A from t0  ) as U ;
select max(A) from (select count(*) as A from t0 group by key1 ) as U ;
(select count(*) as A from t0 group by key1 limit 10;
select count(*) as A from t0 group by key1 limit 10;
select count(*) as A from t0 group by key1 limit 10 order by A desc ;
select count(*) as A from t0 group by key1 order by A desc limit 10;
alter table t0 drop key(a);
alter table t0 drop key(key1);
alter table t0 drop key key1;
show create table t0;
alter table t0 drop key i1;

The source table dump

Attachment: t0.sql (text/plain), 38.73 KiB.

And this causes crash on a just-pulled 4.1.
Here is the stack trace: 
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 73738 (LWP 13034)]
mysql_alter_table(THD*, char*, char*, st_ha_create_information*, st_table_list*, List<create_field>&, List<Key>&, unsigned, st_o
rder*, enum_duplicates, st_alter_info*, bool) (thd=0x85a3640, new_db=0x85a7270 "test2", new_name=0x85ba6a0 "t0", 
    create_info=0x85a39cc, table_list=0x85ba6c8, fields=@0x85ba740, keys=@0x85a3904, order_num=140170165, order=0x85ad3b5, 
    handle_duplicates=140170165, alter_info=0x85a3ae4, do_send_ok=true) at sql_table.cc:2707
2707          if (drop->type == Alter_drop::COLUMN &&
(gdb) info locals 
drop = (class Alter_drop *) 0x79656b00
table = (st_table *) 0x85a89d8
new_table = (st_table *) 0x79656b00
error = 1096390368
tmp_name = "??YA??YA\234?YA?<\003\0\022\0\0\0@6Z\b?YA?\006\027\b.\016\0\0?YA?YA?YA?YA?YA?YA?YA?YA?\0\0?YAs?\002@"
old_name = "?=Z\b@6Z\b??YA??2\b\002\001\0\0??YA??YA??YA"
new_name_buff = "8\0\0\001?\237YAx\237YA<?\002@", '\0' <repeats 12 times>, "l;Z\b?\237YAC\004\0\0?YA:?\024\b\201\0\0\0?\237YA?\2
37YA?\237YA?\237YA7\0\0\0?\237YA?YA\220?\022B?\022B/?\002@?YAC\004#42000Can't DROP 'key1'; check that column/key exists\0+?\002@
\201?\aB\030?Z\b\200?\022B+?\002@D+\003@\030?Z\b\200?\022B4?YA<?\002@\220?\022B?\022BT?"...
new_alias_buff = "\200?\022B??]\b?\235YA????\\\236YA\034\236YA?\235YA?\2362\by\0\0\0?\235YA?\235YA?\235YA?\235YA?\235YA?\235YA\0
\0\0\0\0\0\0\0Ic=\bG\0\0\0\\\236YA0\006Z\b\034\236YA\024?YA\207?\035\b\027\001\0\0\030\236YA\024\236YA\020\236YA,\237YA\0\0\0\0\
f\236YA?{\002@\0\b\0\0\0\0\0\00\006Z\b\211\aZ\bl\t5\bD+\003@$\236YA??\002@\0\0\0\0\0\0\0\0<\0\0\0<\0\0\08\222Z\b<\0\0\0d\236YA\0
25\0272\bH\0\0\0T\236YA"...
table_name = 0x85ba6a0 "t0"
db = 0x85a7270 "test2"
new_alias = 0x85ba6a0 "t0"
alias = 0x85ba6a0 "t0"
index_file = '\0' <repeats 28 times>, "n?\024\b?\222Z\b\210?[\b\002\0\0\0\002\0\0\0?=Z\bl;Z\b?\233YAR?\024\bl;Z\b\210?[\b\002\0\
0\0<?\002@09M\b\0\0\0\0\002\0\0\r?=Z\b?=Z\bP?[\b\024\234YAk?\024\bl;Z\b\210?[\b\002\0\0\0\004\234YA\b\234YA\004\234YA\0\234YAi?3
\b?[\b?=Z\b\024\234YA?[\b?=Z\b?[\b\224\237YA\204g\024\b?=Z\bH\234YAD\234YA@\234YAD\234YA@\234YA<\234YA?YAT\234YAP\234YAL\234"...
data_file = "0f[\b?D5\b\213\0\0\0\0\0\0\0?\231YA?\231YA\234\231YA\0\0\0\0\0\0\0\0h?[\b?=Z\b?\211Z\b", '\0' <repeats 16 times>, "
`?[\b??[\b\002", '\0' <repeats 23 times>, "??J\b", '\0' <repeats 344 times>, "?\003", '\0' <repeats 14 times>, "'?\002@", '\0' <
repeats 28 times>, "?YA", '\0' <repeats 12 times>, "'?\002@"
copied = 1096395744
deleted = 0
next_insert_id = 602026837330863936
db_create_options = 2036689664
used_fields = 0
old_db_type = DB_TYPE_MYISAM
new_db_type = DB_TYPE_MYISAM
_db_func_ = 0x1 <Address 0x1 out of bounds>
_db_file_ = 0x4002ce2f "\201?\025]"
_db_level_ = 0
_db_framep_ = (char **) 0x0
drop_it = {<base_list_iterator> = {list = 0x85a3ae4, el = 0x85ad3b5, prev = 0x85ba740, current = 0x85ad3b5}, <No data fields>}
def_it = {<base_list_iterator> = {list = 0x85a3910, el = 0x84ca960, prev = 0x85a3910, current = 0x84ca960}, <No data fields>}
alter_it = {<base_list_iterator> = {list = 0x85a3af0, el = 0x84ca960, prev = 0x85a3af0, current = 0x84ca960}, <No data fields>}
create_list = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x85ba7d8, last = 0x85ba7d8, 
    elements = 1}, <No data fields>}
key_list = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x84ca960, last = 0x4159995c, 
    elements = 0}, <No data fields>}
def = (class create_field *) 0x85ba740
f_ptr = (class Field **) 0x85ad334
---Type <return> to continue, or q <return> to quit---   
field = (Field *) 0x85ad438
find_it = {<base_list_iterator> = {list = 0x4212b180, el = 0x85b6618, prev = 0x41599944, 
    current = 0x85b6618}, <No data fields>}
key_it = {<base_list_iterator> = {list = 0x4212b190, el = 0x4212a2d0, prev = 0x41599924, 
    current = 0x42074a46}, <No data fields>}
field_it = {<base_list_iterator> = {list = 0x85b6618, el = 0x4212b180, prev = 0x41599904, 
    current = 0x4002a13c}, <No data fields>}
key_parts = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x85b6618, last = 0x4212b180, 
    elements = 1073926699}, <No data fields>}
key_info = (st_key *) 0x85a6cd0
(gdb) bt 
#0  mysql_alter_table(THD*, char*, char*, st_ha_create_information*, st_table_list*, List<create_field>&, List<Key>&, unsigned, 
st_order*, enum_duplicates, st_alter_info*, bool) (thd=0x85a3640, new_db=0x85a7270 "test2", new_name=0x85ba6a0 "t0", 
    create_info=0x85a39cc, table_list=0x85ba6c8, fields=@0x85ba740, keys=@0x85a3904, order_num=140170165, order=0x85ad3b5, 
    handle_duplicates=140170165, alter_info=0x85a3ae4, do_send_ok=true) at sql_table.cc:2707
#1  0x0816d82f in mysql_execute_command(THD*) (thd=0x85a3640) at sql_parse.cc:2490
#2  0x081712db in mysql_parse(THD*, char*, unsigned) (thd=0x85a3640, inBuf=0x85a3650 "\001", length=26) at sql_parse.cc:4034
#3  0x0816ae2e in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x85a3640, 
    packet=0x85a9239 "alter table t0 drop key i1", packet_length=27) at sql_parse.cc:1457
#4  0x0816a78b in do_command(THD*) (thd=0x85a3640) at sql_parse.cc:1272
#5  0x08169c88 in handle_one_connection (arg=0x85ad3b5) at sql_parse.cc:1016
#6  0x40029941 in pthread_start_thread () from /lib/i686/libpthread.so.0
#7  0x40029a45 in pthread_start_thread_event () from /lib/i686/libpthread.so.0

updated version

Tested 5.0/4.1 on Windows XP. Below call stack for 4.1.3:

/sql/sql_table.cpp
--2705--
  while ((drop=drop_it++))
    {
      if (drop->type == Alter_drop::COLUMN &&
	  !my_strcasecmp(system_charset_info,field->field_name, drop->name))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
      {

>	mysqld.exe!mysql_alter_table(THD * thd=0x00f1dd60, char * new_db=0x00d9bc58, char * new_name=0x00f2b0e0, st_ha_create_information * create_info=0x00f1e120, st_table_list * table_list=0x00f2b108, List<create_field> & fields={...}, List<Key> & keys={...}, unsigned int order_num=0, st_order * order=0x00000000, enum_duplicates handle_duplicates=DUP_ERROR, st_alter_info * alter_info=0x00f1e260, int do_send_ok=1)  Line 2708 + 0x6	C++
 	mysqld.exe!mysql_execute_command(THD * thd=0x00f1dd60)  Line 2489 + 0x65	C++
 	mysqld.exe!mysql_parse(THD * thd=0x00f1dd60, char * inBuf=0x00f2b0a8, unsigned int length=26)  Line 4023 + 0x9	C++
 	mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x00f1dd60, char * packet=0x00f27041, unsigned int packet_length=27)  Line 1457 + 0x1d	C++
 	mysqld.exe!do_command(THD * thd=0x00f1dd60)  Line 1272 + 0x31	C++
 	mysqld.exe!handle_one_connection(void * arg=0x00f1dd60)  Line 1016 + 0x9	C++
 	mysqld.exe!pthread_start(void * param=0x00d9a7b0)  Line 63 + 0x7	C
 	mysqld.exe!_threadstart(void * ptd=0x00d9a090)  Line 173 + 0xd	C
 	kernel32.dll!77e6d33b()

A smaller test case: 

alter table t0 drop key no_such_key;
alter table t0 drop key i1;

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

The same issue as BUG#3899