1 files changed, 268 insertions, 0 deletions
diff --git a/mysql-test/t/create_select-big.test b/mysql-test/t/create_select-big.test
new file mode 100644
index 00000000000..3fa655c5501
--- /dev/null
+++ b/mysql-test/t/create_select-big.test
@@ -0,0 +1,268 @@
+# Tests for various aspects of CREATE TABLE ... SELECT implementation
+#
+# Note that we don't test general CREATE TABLE ... SELECT 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
+
+# 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.
+#
+# 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;