mysql-test/main/create-big.test

# 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 need the Debug Sync Facility.
--source include/have_debug_sync.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
# Save the initial number of concurrent sessions.
--source include/count_sessions.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
set debug_sync='RESET';

#
# 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 debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send create table t1 (j char(5));
connection addconroot2;
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "create table t1 (j char(5))";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--error ER_TABLE_EXISTS_ERROR
--reap
connection default;
show create table t1;
drop table t1;

# Concurrent CREATE TABLE ... SELECT
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send create table t1 select 'Test' as j;
connection addconroot2;
# Wait until the above CREATE TABLE t1 is blocked due to CREATE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "create table t1 select 'Test' as j";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--error ER_TABLE_EXISTS_ERROR
--reap
connection default;
show create table t1;
drop table t1;

# Concurrent CREATE TABLE LIKE
create table t3 (j char(5));
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send create table t1 like t3;
connection addconroot2;
# Wait until the above CREATE TABLE t1 is blocked due to CREATE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "create table t1 like t3";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--error ER_TABLE_EXISTS_ERROR
--reap
connection default;
show create table t1;
drop table t1;

# Concurrent RENAME TABLE
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send rename table t3 to t1;
connection addconroot2;
# Wait until the above RENAME TABLE is blocked due to CREATE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "rename table t3 to t1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--error ER_TABLE_EXISTS_ERROR
--reap
connection default;
show create table t1;
drop table t1;

# Concurrent ALTER TABLE RENAME
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send alter table t3 rename to t1
connection addconroot2;
# Wait until the above ALTER TABLE RENAME is blocked due to CREATE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "alter table t3 rename to t1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--error ER_TABLE_EXISTS_ERROR
--reap
connection default;
show create table t1;
drop table t1;

# Concurrent ALTER TABLE RENAME which also adds column
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send alter table t3 rename to t1, add k int
connection addconroot2;
# Wait until the above ALTER TABLE RENAME is blocked due to CREATE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "alter table t3 rename to t1, add k int";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--error ER_TABLE_EXISTS_ERROR
--reap
connection default;
show create table t1;
drop table t1,t3;

# What happens if other statement sneaks in after the table
# creation but before its opening ?
set debug_sync='create_table_select_before_open SIGNAL parked WAIT_FOR go';
connection default;

# Concurrent DROP TABLE
set debug_sync='create_table_select_before_open SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send drop table t1;
connection addconroot2;
# Wait until the above DROP TABLE is blocked due to CREATE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "drop table t1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;

# Concurrent RENAME TABLE
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send rename table t1 to t2;
connection addconroot2;
# Wait until the above RENAME TABLE is blocked due to CREATE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "rename table t1 to t2";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
drop table t2;

# Concurrent SELECT
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send select * from t1;
connection addconroot2;
# Wait until the above SELECT is blocked due to CREATE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "select * from t1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
drop table t1;

# Concurrent INSERT
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send insert into t1 values (2);
connection addconroot2;
# Wait until the above INSERT is blocked due to CREATE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "insert into t1 values (2)";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
select * from t1;
drop table t1;

# Concurrent CREATE TRIGGER
set @a:=0;
set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send create trigger t1_bi before insert on t1 for each row set @a:=1;
connection addconroot2;
# Wait until the above CREATE TRIGGER is blocked due to CREATE TABLE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "create trigger t1_bi before insert on t1 for each row set @a:=1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
select @a;
drop table t1;

# Okay, now the same tests for the potential gap between open and lock
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';

# Concurrent DROP TABLE
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send drop table t1;
connection addconroot2;
# Wait until the above DROP TABLE is blocked due to CREATE TABLE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "drop table t1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;

# Concurrent RENAME TABLE
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send rename table t1 to t2;
connection addconroot2;
# Wait until the above RENAME TABLE is blocked due to CREATE TABLE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "rename table t1 to t2";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
drop table t2;

# Concurrent SELECT
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send select * from t1;
connection addconroot2;
# Wait until the above SELECT is blocked due to CREATE TABLE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "select * from t1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
drop table t1;

# Concurrent INSERT
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send insert into t1 values (2);
connection addconroot2;
# Wait until the above INSERT INTO t1 is blocked due to CREATE TABLE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "insert into t1 values (2)";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
select * from t1;
drop table t1;

# Concurrent CREATE TRIGGER
set @a:=0;
set debug_sync='create_table_select_before_lock SIGNAL parked WAIT_FOR go';
--send create table t1 select 1 as i;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send create trigger t1_bi before insert on t1 for each row set @a:=1;
connection addconroot2;
# Wait until the above CREATE TRIGGER is blocked due to CREATE TABLE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "create trigger t1_bi before insert on t1 for each row set @a:=1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
select @a;
drop table t1;

# Concurrent DROP TABLE
set debug_sync='create_table_before_check_if_exists SIGNAL parked WAIT_FOR go';
--send create table if not exists t1 select 1 as i
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send drop table t1
connection addconroot2;
# Wait until the above DROP TABLE is blocked due to CREATE TABLE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "drop table t1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;

# Concurrent CREATE TRIGGER
create table t1 (i int);
set @a:=0;
set debug_sync='create_table_before_check_if_exists SIGNAL parked WAIT_FOR go';
--send create table if not exists t1 select 1 as i
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send create trigger t1_bi before insert on t1 for each row set @a:=1
connection addconroot2;
# Wait until the above DROP TABLE is blocked due to CREATE TABLE
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info like "create trigger%";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
select @a;
select * from t1;
drop table t1;

# 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
set debug_sync='RESET';

# What happens if some statements sneak in right after we have
# acquired locks and opened source table ?
create table t1 (i int);
set debug_sync='create_table_like_after_open SIGNAL parked WAIT_FOR go';
# Reset binlog to have clear start
reset master;
--send create table t2 like t1;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
# DML on source table should be allowed to run concurrently
insert into t1 values (1);
# And DDL should wait
--send drop table t1;
connection addconroot2;
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "drop table t1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
show create table t2;
drop table t2;
# Let us check that statements were executed/binlogged in correct order
source include/show_binlog_events.inc;

# Now check the gap between table creation and binlogging
create table t1 (i int);
set debug_sync='create_table_like_before_binlog SIGNAL parked WAIT_FOR go';
reset master;
--send create table t2 like t1;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send insert into t2 values (1);
connection addconroot2;
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "insert into t2 values (1)";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap

connection addconroot1;
--reap
connection default;
drop table t2;
set debug_sync='create_table_like_before_binlog SIGNAL parked WAIT_FOR go';
--send create table t2 like t1;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send drop table t2;
connection addconroot2;
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "drop table t2";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
set debug_sync='create_table_like_before_binlog SIGNAL parked WAIT_FOR go';
--send create table t2 like t1;
connection addconroot1;
set debug_sync='now WAIT_FOR parked';
--send drop table t1;
connection addconroot2;
let $wait_condition=
    select count(*) = 1 from information_schema.processlist
    where state = "Waiting for table metadata lock" and
          info = "drop table t1";
--source include/wait_condition.inc
set debug_sync='now SIGNAL go';
connection default;
--reap
connection addconroot1;
--reap
connection default;
drop table t2;
disconnect addconroot1;
disconnect addconroot2;
disconnect addconroot3;

set debug_sync='RESET';
source include/show_binlog_events.inc;
# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
--source include/wait_until_count_sessions.inc