MySQL Bugs: #17118: Waiting for table after finishing altering the table

Bug #17118	Waiting for table after finishing altering the table
Submitted:	4 Feb 2006 14:17	Modified:	10 Feb 2006 16:13
Reporter:	Ady Wicaksono	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15	OS:	Linux (RedHat Linux 9 Kernel 2.4 SMP)
Assigned to:		CPU Architecture:	Any

Description:
Dear All

I have a t_incoming_sms table, since it format is redundant not compact
i try to alter this table using

ALTER TABLE t_incoming_sms type=innodb

The problem is, since inserting application is still hit me, they start to go to Waiting for table state.... however after i finish the alter, all thread is still on waiting for table ... 

--- SQL for t_incoming_sms ---
CREATE TABLE `t_incoming_sms` (
  `in_sms_id` int(11) NOT NULL auto_increment,
  `in_sms_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `in_sms_sender` varchar(20) NOT NULL default '',
  `in_sms_msg` varchar(161) NOT NULL default '',
  `in_sms_operator` varchar(11) NOT NULL default '',
  `in_sms_message_id` varchar(22) NOT NULL default '',
  PRIMARY KEY  (`in_sms_id`),
  KEY `t_incoming_sms_message_id_idx` (`in_sms_message_id`(5)),
  KEY `t_incoming_sms_message_sender_idx` (`in_sms_sender`(8)),
  KEY `t_in_sms01` (`in_sms_time`),
  KEY `t_in_sms02` (`in_sms_msg`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--- SQL for t_incoming_sms ---

---TRANSACTION 0 0, not started, process no 31519, OS thread id 3056530880
MySQL thread id 3436, query id 1177356 10.1.20.10 root Waiting for table
INSERT INTO sms_telkomsel.t_incoming_sms (in_sms_time, in_sms_sender, in_sms_msg, in_sms_operator, in_sms_message_id) values ('2006-02-04 20:39:42','628128146377','Boy xdragons','TELKOMSEL','9d4c97b5X')
---TRANSACTION 0 0, not started, process no 31519, OS thread id 2987389632
MySQL thread id 3434, query id 1177290 10.1.20.10 root Waiting for table
INSERT INTO sms_telkomsel.t_incoming_sms (in_sms_time, in_sms_sender, in_sms_msg, in_sms_operator, in_sms_message_id) values ('2006-02-04 20:39:41','6281364548971','BOY GSTAR','TELKOMSEL','9c4c78f5X')
---TRANSACTION 0 0, not started, process no 31519, OS thread id 3037656512
MySQL thread id 3433, query id 1177240 10.1.20.10 root Waiting for table
INSERT INTO sms_telkomsel.t_incoming_sms (in_sms_time, in_sms_sender, in_sms_msg, in_sms_operator, in_sms_message_id) values ('2006-02-04 20:39:40','6281310616586','BOY JFC','TELKOMSEL','9b4c7ee5V')
---TRANSACTION 0 0, not started, process no 31519, OS thread id 3045455424
MySQL thread id 3432, query id 1177189 10.1.20.10 root Waiting for table
INSERT INTO sms_telkomsel.t_incoming_sms (in_sms_time, in_sms_sender, in_sms_msg, in_sms_operator, in_sms_message_id) values ('2006-02-04 20:39:39','6281322381755','GM B','TELKOMSEL','9a7077f5I')

How to repeat:
Try to create 3 MySQL client session

1. First session, execute this SQL

mysql> create table test (i int(11) not null auto_increment) type=innodb;

mysql> delimiter //

mysql> CREATE PROCEDURE insertMany () BEGIn  declare p1 int;  set p1=0;   label1: LOOP   set p1 = p1+1;   if p1 < 300000 then     insert into test values (p1);     iterate label1;   end if;   leave label1;   end loop label1; end; //

mysql> delimiter ;
mysql> call insertMany();
Query OK, 1 row affected (1 min 15.45 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|   299999 |
+----------+
1 row in set (0.00 sec)

WAIT, put this SQL on the mysql> cursor but don't enter 

alter table test type=innodb;

2. Second session, put 

insert into test values (1000000);

and execute alter table test type=innodb; and move quickly to second session
and run insert into test values (1000000);

insert into test values (1000000) will waiting until alter finished, but after the alter finished..... it still waiting :((

3. Third session, see the processlist.... insert is still waiting

| 4587 | root      | localhost:34400  | dbname     | Query   |   44 | Waiting for table | insert into test values (100000)                                                                     |

Thank you for a detailed problem report. Sorry, but I was not able to repeat the problem you described on 5.0.19-BK on Linux. I was forced to correct your test case slightly, though:

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `i` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I was forced to add primary key for auto_increment column. You can not define column as auto_increment without adding apropriate index, really.

INSERT statement waited for a while:

mysql> insert into test values(10000000);
Query OK, 1 row affected (3.10 sec)

But it finished immediately after alter table from the first session:

mysql> alter table test engine=innodb;
Query OK, 299999 rows affected (3.92 sec)
Records: 299999  Duplicates: 0  Warnings: 0

So, please, try to use newer version, 5.0.18, and reopen this report if it still gives you wait after altering table, withouth other sessions, and with index in place.