MySQL Bugs: #27969: SELECT during DDL no longer possible (memory engine)

Bug #27969	SELECT during DDL no longer possible (memory engine)
Submitted:	20 Apr 2007 1:00	Modified:	6 Aug 2007 13:30
Reporter:	Arjen Lentz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Windows
Assigned to:	Sveta Smirnova	CPU Architecture:	Any
Tags:	ALTER TABLE, DDL, regression, SELECT

Description:
It used to be possible to issue SELECT statements during DDL, e.g. ALTER TABLE.
This shouldn't be a problem since the old table is still there and is just being copied.
(The manual also notes this as a capability.)

However, current versions of MySQL do not do this, and block all queries during ALTER TABLE.

How to repeat:
Start an ALTER TABLE in one session, then start a SELECT in other session.
It used to not block, now it does.

Thank you for the report.

I can not repeat described behaviour with current sources.

Please provide output of SHOW CREATE TABLE and ALTER TABLE statements for the problem table. Also indicate accurate version of MySQL server you use.

With 5.0 and memory engine statement alter table t1 add column foo int; doesn't block talbe t1;

So I really need in SHOW CREATE TABLE and ALTER statement.

Sveta: 
Josh Chamas has provided the following test case, which
he says is reproducible on Windows, but not on Linux.

"To reproduce, I run the below program and during the ALTER section, run:

  call mem_select_bench(5);

to see the execution time of the selects.  I would recommend having 1GB memory
on your system to run this test.

...

===============================
use test;

set session max_heap_table_size=1000000000;

drop table if exists t1;
create table t1 (
   c1 int default 0,
   index using btree (c1)
) engine=memory;

delimiter //
drop procedure if exists mem_select_bench //
create procedure mem_select_bench(num int)
begin
   declare start datetime;
   declare lasttime datetime;
   declare count int default 0;

   while count < num do
     select now(), t1.* from t1 limit 1;
     set count := count + 1;
   end while;
end;
//
delimiter ;

# call mem_select_bench(5);

insert into t1 values(1);
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;
insert into t1 select rand()*100000000 from t1;

alter table t1 engine=memory;
alter table t1 engine=memory;
alter table t1 engine=memory;
alter table t1 engine=memory;
alter table t1 engine=memory;
alter table t1 engine=memory;

Verified by Miguel on Windows as described.

On Linux and Mac OSX the bug is not repeatable.

Tim, it's a windows-specific problem.
Please, when you have time, investigate and assign to the right team.

Since this previously worked, I've added the regression tag.

Rereading this report, I see that the question
of whether this functionality ever worked with
the memory engine has not been answered. If
this is, indeed, a regression then it's important
to know which version is affected.

I'm asking that this be reverified to see if the
regression tag fits.

I wasn't able to repeat with latest 5.0.48 Windows server built from
source.

Miguel, have you asked Josh Chamas for info? He was the one who originally found this problem.
The issue just disappearing in a newer Windows build is curious, there must be something else going on.

I believe it might also affect a MySQL AB customer since Josh found this on an assignment.