389 lines
10 KiB
Plaintext
389 lines
10 KiB
Plaintext
|
# 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
|
||
|
|
source include/show_binlog_events.inc;
|
||
|
|
|
||
|
|
# 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;
|
||
|
|
source include/show_binlog_events.inc;
|
||
|
|
|
||
|
|
# 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;
|
||
|
|
source include/show_binlog_events.inc;
|
||
|
|
|
||
|
|
# 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;
|
||
|
|
source include/show_binlog_events.inc;
|
||
|
|
|
||
|
|
set session debug="-d,sleep_create_like_before_binlogging";
|