~drizzle-trunk/drizzle/development

# Tests for various concurrency-related aspects of CREATE TABLE ... SELECT

# and CREATE TABLE like implementation.

#

# Note that we don't test general CREATE TABLE ... SELECT/LIKE functionality

# here as it is already covered by create.test. We are more interested in

# extreme cases.

#

# This test takes rather long time so let us run it only in --big-test mode

--source include/big_test.inc

# We are using some debug-only features in this test

--source include/have_debug.inc

# Some of tests below also use binlog to check that statements are

# executed and logged in correct order

--source include/have_binlog_format_mixed_or_statement.inc

# Create auxilliary connections

connect (addconroot1, localhost, root,,);

connect (addconroot2, localhost, root,,);

connect (addconroot3, localhost, root,,);

connection default;

--disable_warnings

drop table if exists t1,t2,t3,t4,t5;

--enable_warnings

#

# Tests for concurrency problems in CREATE TABLE ... SELECT

#

# We introduce delays between various stages of table creation

# and check that other statements dealing with this table cannot

# interfere during those delays.

#

# What happens in situation when other statement messes with

# table to be created before it is created ?

# Concurrent CREATE TABLE

set session debug="+d,sleep_create_select_before_create";

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

--error ER_TABLE_EXISTS_ERROR

create table t1 (j char(5));

connection default;

--reap

show create table t1;

drop table t1;

# Concurrent CREATE TABLE ... SELECT

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

--error ER_TABLE_EXISTS_ERROR

create table t1 select "Test" as j;

connection default;

--reap

show create table t1;

drop table t1;

# Concurrent CREATE TABLE LIKE

create table t3 (j char(5));

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

--error ER_TABLE_EXISTS_ERROR

create table t1 like t3;

connection default;

--reap

show create table t1;

drop table t1;

# Concurrent RENAME TABLE

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

--error ER_TABLE_EXISTS_ERROR

rename table t3 to t1;

connection default;

--reap

show create table t1;

drop table t1;

# Concurrent ALTER TABLE RENAME

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

--error ER_TABLE_EXISTS_ERROR

alter table t3 rename to t1;

connection default;

--reap

show create table t1;

drop table t1;

# Concurrent ALTER TABLE RENAME which also adds column

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

--error ER_TABLE_EXISTS_ERROR

alter table t3 rename to t1, add k int;

connection default;

--reap

show create table t1;

drop table t1, t3;

# What happens if other statement sneaks in after the table

# creation but before its opening ?

set session debug="-d,sleep_create_select_before_create:+d,sleep_create_select_before_open";

# Concurrent DROP TABLE

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

drop table t1;

connection default;

--reap

# Concurrent RENAME TABLE

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

rename table t1 to t2;

connection default;

--reap

drop table t2;

# Concurrent SELECT

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

select * from t1;

connection default;

--reap

drop table t1;

# Concurrent INSERT

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

insert into t1 values (2);

connection default;

--reap

select * from t1;

drop table t1;

# Concurrent CREATE TRIGGER 

set @a:=0;

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

create trigger t1_bi before insert on t1 for each row set @a:=1;

connection default;

--reap

select @a;

drop table t1;

# Okay, now the same tests for the potential gap between open and lock

set session debug="-d,sleep_create_select_before_open:+d,sleep_create_select_before_lock";

# Concurrent DROP TABLE

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

drop table t1;

connection default;

--reap

# Concurrent RENAME TABLE

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

rename table t1 to t2;

connection default;

--reap

drop table t2;

# Concurrent SELECT

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

select * from t1;

connection default;

--reap

drop table t1;

# Concurrent INSERT

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

insert into t1 values (2);

connection default;

--reap

select * from t1;

drop table t1;

# Concurrent CREATE TRIGGER 

set @a:=0;

--send create table t1 select 1 as i;

connection addconroot1;

--sleep 2

create trigger t1_bi before insert on t1 for each row set @a:=1;

connection default;

--reap

select @a;

drop table t1;

# Some tests for case with existing table

set session debug="-d,sleep_create_select_before_lock:+d,sleep_create_select_before_check_if_exists";

create table t1 (i int);

# Concurrent DROP TABLE

--send create table if not exists t1 select 1 as i;

connection addconroot1;

--sleep 2

drop table t1;

connection default;

--reap

# Concurrent CREATE TRIGGER 

create table t1 (i int);

set @a:=0;

--send create table if not exists t1 select 1 as i;

connection addconroot1;

--sleep 2

create trigger t1_bi before insert on t1 for each row set @a:=1;

connection default;

--reap

select @a;

select * from t1;

drop table t1;

set session debug="-d,sleep_create_select_before_check_if_exists";

# Test for some details of CREATE TABLE ... SELECT implementation.

#

# We check that create placeholder is handled properly if we have

# to reopen tables in open_tables().

# This test heavily relies on current implementation of name-locking/

# table cache so it may stop working if it changes. OTOH it such problem

# will serve as warning that such changes should not be done lightly.

create table t2 (a int);

create table t4 (b int);

connection addconroot2;

lock table t4 write;

select 1;

connection addconroot1;

# Create placeholder/name-lock for t3

--send create table t3 as select * from t4;

--sleep 2

connection default;

# This statement creates placeholder for t1, then opens t2,

# then meets name-lock for t3 and then reopens all tables

--send create table t1 select * from t2, t3;

--sleep 2

connection addconroot2;

unlock tables;

connection addconroot1;

--reap

connection default;

--reap

select * from t1;

show create table t1;

drop table t1, t3;

# Now similar test which proves that we really temporarily

# remove placeholder when we reopen tables.

connection addconroot2;

lock table t4 read;

select 1;

connection addconroot1;

# Create name-lock for t3 

--send rename table t4 to t3;

--sleep 2

connection default;

# This statement creates placeholder for t1, then opens t2,

# then meets name-lock for t3 and then reopens all tables

--send create table if not exists t1 select 1 as i from t2, t3;

--sleep 2

connection addconroot3;

# We should be able to take name-lock on table t1 as we should not have

# open placeholder for it at this point (otherwise it is possible to

# come-up with situation which will lead to deadlock, e.g. think of

# concurrent CREATE TABLE t1 SELECT * FROM t2 and RENAME TABLE t2 TO t1)

create table t5 (j int);

# This statement takes name-lock on t1 and therefore proves 

# that there is no active open placeholder for it.

rename table t5 to t1;

connection addconroot2;

unlock tables;

connection addconroot1;

--reap

connection default;

--reap

select * from t1;

show create table t1;

drop table t1, t2, t3;

# Tests for possible concurrency issues with CREATE TABLE ... LIKE

#

# Bug #18950 "create table like does not obtain LOCK_open"

# Bug #23667 "CREATE TABLE LIKE is not isolated from alteration by other

#             connections"

#

# Again the idea of this test is that we introduce artificial delays on

# various stages of table creation and check that concurrent statements

# for tables from CREATE TABLE ... LIKE are not interfering.

--disable_warnings

drop table if exists t1,t2;

--enable_warnings

# What happens if some statements sneak in right after we have

# opened source table ?

create table t1 (i int);

set session debug="+d,sleep_create_like_before_check_if_exists";

# Reset binlog to have clear start

reset master;

--send create table t2 like t1;

connection addconroot1;

--sleep 2

# DML on source table should be allowed to run concurrently

insert into t1 values (1);

# And DDL should wait

drop table t1;

connection default;

--reap

show create table t2;

drop table t2;

# Let us check that statements were executed/binlogged in correct order

--replace_column 2 # 5 #

show binlog events in 'master-bin.000001' from 106;

# Now let us check the gap between check for target table

# existance and copying of .frm file. 

create table t1 (i int);

set session debug="-d,sleep_create_like_before_check_if_exists:+d,sleep_create_like_before_copy";

# It should be impossible to create target table concurrently

--send create table t2 like t1;

connection addconroot1;

--sleep 2

create table if not exists t2 (j int);

connection default;

--reap

show create table t2;

drop table t2;

# And concurrent DDL on the source table should be still disallowed 

reset master;

--send create table t2 like t1;

connection addconroot1;

--sleep 2

drop table t1;

connection default;

--reap

drop table t2;

--replace_column 2 # 5 #

show binlog events in 'master-bin.000001' from 106;

# And now he gap between copying of .frm file and ha_create_table() call. 

create table t1 (i int);

set session debug="-d,sleep_create_like_before_copy:+d,sleep_create_like_before_ha_create";

# Both DML and DDL on target table should wait till operation completes

reset master;

--send create table t2 like t1;

connection addconroot1;

--sleep 2

insert into t2 values (1);

connection default;

--reap

drop table t2;

--send create table t2 like t1;

connection addconroot1;

--sleep 2

drop table t2;

connection default;

--reap

# Concurrent DDL on the source table still waits 

--send create table t2 like t1;

connection addconroot1;

--sleep 2

drop table t1;

connection default;

--reap

drop table t2;

--replace_column 2 # 5 #

show binlog events in 'master-bin.000001' from 106;

# Finally we check the gap between ha_create_table() and binlogging

create table t1 (i int);

set session debug="-d,sleep_create_like_before_ha_create:+d,sleep_create_like_before_binlogging";

reset master;

--send create table t2 like t1;

connection addconroot1;

--sleep 2

insert into t2 values (1);

connection default;

--reap

drop table t2;

--send create table t2 like t1;

connection addconroot1;

--sleep 2

drop table t2;

connection default;

--reap

--send create table t2 like t1;

connection addconroot1;

--sleep 2

drop table t1;

connection default;

--reap

drop table t2;

--replace_column 2 # 5 #

show binlog events in 'master-bin.000001' from 106;

set session debug="-d,sleep_create_like_before_binlogging";