1 files changed, 392 insertions, 0 deletions
diff --git a/mysql-test/t/create-big.test b/mysql-test/t/create-big.test
new file mode 100644
index 00000000000..6cd6326cdb8
--- /dev/null
+++ b/mysql-test/t/create-big.test
@@ -0,0 +1,392 @@
+# 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";