main.handler_innodb fails in 5.5.11 as follows:
main.handler_innodb [ fail ]
Test ended at 2011-04-11 20:53:25
CURRENT_TEST: main.handler_innodb
mysqltest: In included file "./include/handler.inc":
included from ./include/handler.inc at line 781:
At line 780: query 'reap # Since last in this connection was a send' failed: 1031: Table storage engine for '#sql-16636_7' doesn't have this option
The result from queries just before the failure was:
< snip >
handler t1 close;
unlock tables;
drop table t1;
# Now test case which was reported originally but which no longer
# triggers execution path which has caused the problem.
create table t1 (a int, key(a));
insert into t1 values (1);
handler t1 open;
alter table t1 engine=memory;
Timeout in wait_condition.inc for select count(*) = 1 from information_schema.processlist
where state = "Waiting for table metadata lock" and
info = "alter table t1 engine=memory"
# Since S metadata lock was already acquired at HANDLER OPEN time
# and TL_READ lock requested by HANDLER READ is compatible with
# ALTER's TL_WRITE_ALLOW_READ the below statement should succeed
# without waiting. The old version of table should be used in it.
handler t1 read a next;
a
1
handler t1 close;
It's a regression caused by a bug in innodb_
The bug is that when removing/adding keys, the original table engine is checked to be InnoDB, rather than the target one. So if the table's engine is being changed from InnoDB to a different one, it still tries to drop/recreate keys, which in particular results in errors for the MEMORY engine:
mysql> create table t1(a int, key(a)) engine=innodb;
Query OK, 0 rows affected (0,02 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0,01 sec)
mysql> alter table t1 engine=memory;
ERROR 1031 (HY000): Table storage engine for '#sql-16659_1' doesn't have this option
The fix is to make sure the target table engine is InnoDB, rather than the original one.