mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
354 lines
8.6 KiB
Text
354 lines
8.6 KiB
Text
########################################################################
|
|
# Tests BACKUP STAGE locking
|
|
########################################################################
|
|
|
|
--source include/have_innodb.inc
|
|
--source include/have_metadata_lock_info.inc
|
|
--source include/not_embedded.inc
|
|
--source include/no_view_protocol.inc
|
|
|
|
|
|
--echo #
|
|
--echo # Testing which locks we get from all stages
|
|
--echo #
|
|
|
|
let $mdl= LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info
|
|
WHERE TABLE_NAME NOT LIKE 'innodb_%_stats';
|
|
|
|
--source ../suite/innodb/include/wait_all_purged.inc
|
|
|
|
BACKUP STAGE START;
|
|
eval SELECT $mdl;
|
|
BACKUP STAGE FLUSH;
|
|
eval SELECT $mdl;
|
|
BACKUP STAGE BLOCK_DDL;
|
|
eval SELECT $mdl;
|
|
BACKUP STAGE BLOCK_COMMIT;
|
|
eval SELECT $mdl;
|
|
BACKUP STAGE END;
|
|
eval SELECT $mdl;
|
|
|
|
--echo #
|
|
--echo # testing BACKUP STAGE LOCK's
|
|
--echo #
|
|
|
|
# Following connections are used in a few of the following tests
|
|
connect (con1,localhost,root,,);
|
|
connect (con2,localhost,root,,);
|
|
connection default;
|
|
|
|
--echo #
|
|
--echo # testing if BACKUP STAGE FLUSH causes deadlocks with ALTER TABLE
|
|
--echo #
|
|
|
|
create table t1 (a int) stats_persistent= 0, engine=innodb;
|
|
--source ../suite/innodb/include/wait_all_purged.inc
|
|
|
|
connection con2;
|
|
backup stage start;
|
|
connection default;
|
|
|
|
start transaction;
|
|
# Acquires MDL lock
|
|
insert into t1 values (1);
|
|
|
|
connection con1;
|
|
# Waits on MDL
|
|
--send alter table t1 add column (j int), algorithm copy, lock shared
|
|
|
|
connection con2;
|
|
let $wait_condition=
|
|
select count(*) = 1 from information_schema.processlist
|
|
where state = "Waiting for table metadata lock";
|
|
--source include/wait_condition.inc
|
|
backup stage flush;
|
|
eval SELECT $mdl;
|
|
#
|
|
# Do first test with max_statement_time, other tests later are done with
|
|
# lock_wait_timeout. This is mostly to ensure that both methods works
|
|
#
|
|
--error ER_STATEMENT_TIMEOUT
|
|
SET STATEMENT max_statement_time=1 FOR backup stage block_ddl;
|
|
--send backup stage block_ddl
|
|
|
|
connection default;
|
|
let $wait_condition=
|
|
select count(*) = 1 from information_schema.processlist
|
|
where state = "Waiting for backup lock";
|
|
--source include/wait_condition.inc
|
|
commit;
|
|
# The following select works because alter table is waiting for DDL lock
|
|
SELECT * FROM t1;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
SET STATEMENT lock_wait_timeout=0 FOR INSERT INTO t1 values (2);
|
|
--send INSERT INTO t1 values (2,0);
|
|
connection con2;
|
|
--reap # BLOCK_DDL
|
|
backup stage end;
|
|
connection con1;
|
|
--reap # ALTER TABLE
|
|
connection default;
|
|
--reap # INSERT
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
--echo # Test with inline alter table, which doesn't block block_commit
|
|
|
|
create table t1 (a int) engine=innodb;
|
|
|
|
start transaction;
|
|
# Acquires MDL lock
|
|
insert into t1 values (1);
|
|
|
|
connection con1;
|
|
# Waits on MDL
|
|
--send alter table t1 add column (j int)
|
|
|
|
connection con2;
|
|
let $wait_condition=
|
|
select count(*) = 1 from information_schema.processlist
|
|
where state = "Waiting for table metadata lock";
|
|
--source include/wait_condition.inc
|
|
backup stage start;
|
|
backup stage flush;
|
|
eval SELECT $mdl;
|
|
backup stage block_ddl;
|
|
backup stage block_commit;
|
|
connection default;
|
|
SELECT * FROM t1;
|
|
--send commit
|
|
connection con2;
|
|
let $wait_condition=
|
|
select count(*) = 1 from information_schema.processlist
|
|
where state = "Waiting for backup lock";
|
|
backup stage end;
|
|
connection con1;
|
|
--reap # ALTER TABLE
|
|
connection default;
|
|
--reap # commit
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # testing if BACKUP STAGE FLUSH causes deadlocks with DROP TABLE
|
|
--echo #
|
|
|
|
create table t1 (a int)stats_persistent=0, engine=innodb;
|
|
--source ../suite/innodb/include/wait_all_purged.inc
|
|
start transaction;
|
|
# Acquires MDL lock
|
|
insert into t1 values (1);
|
|
|
|
connection con1;
|
|
# Waits on MDL
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1;
|
|
--send DROP TABLE t1
|
|
|
|
connection con2;
|
|
backup stage start;
|
|
backup stage flush;
|
|
let $wait_condition=
|
|
select count(*) = 1 from information_schema.processlist
|
|
where state = "Waiting for table metadata lock";
|
|
--source include/wait_condition.inc
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
SET STATEMENT lock_wait_timeout=0 FOR SELECT * FROM t1;
|
|
|
|
backup stage block_ddl;
|
|
eval SELECT $mdl;
|
|
backup stage end;
|
|
|
|
connection default;
|
|
commit;
|
|
connection con1;
|
|
--reap # DROP TABLE
|
|
connection default;
|
|
|
|
--echo #
|
|
--echo # Check if backup stage block_dll + concurrent drop table blocks select
|
|
--echo #
|
|
|
|
create table t1 (a int) engine=innodb;
|
|
--source ../suite/innodb/include/wait_all_purged.inc
|
|
backup stage start;
|
|
backup stage block_ddl;
|
|
connection con1;
|
|
--send DROP TABLE t1
|
|
connection con2;
|
|
let $wait_condition=
|
|
select count(*) = 1 from information_schema.processlist
|
|
where state = "Waiting for backup lock";
|
|
--source include/wait_condition.inc
|
|
connection con2;
|
|
eval SELECT $mdl;
|
|
# Check that select's are not blocked
|
|
SELECT * FROM t1;
|
|
connection default;
|
|
backup stage end;
|
|
connection con1;
|
|
--reap
|
|
connection default;
|
|
|
|
--echo #
|
|
--echo # Check if backup stage block_dll overrides ddl lock for drop table
|
|
--echo #
|
|
|
|
create table t1 (a int) engine=innodb;
|
|
start transaction;
|
|
# Acquires MDL lock
|
|
insert into t1 values (1);
|
|
|
|
connection con1;
|
|
# Waits on MDL
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1;
|
|
--send DROP TABLE t1
|
|
|
|
connection con2;
|
|
backup stage start;
|
|
backup stage flush;
|
|
backup stage block_ddl;
|
|
connection default;
|
|
commit;
|
|
connection con2;
|
|
backup stage end;
|
|
connection con1;
|
|
--reap # DROP TABLE
|
|
connection default;
|
|
|
|
--echo #
|
|
--echo # Check if BACKUP STAGE BLOCK_COMMIT blocks commit
|
|
--echo #
|
|
|
|
create table t1 (a int) engine=innodb;
|
|
start transaction;
|
|
# Acquires MDL lock
|
|
insert into t1 values (1);
|
|
|
|
connection con1;
|
|
backup stage start;
|
|
backup stage block_commit;
|
|
connection default;
|
|
--send commit
|
|
connection con1;
|
|
let $wait_condition=
|
|
select count(*) = 1 from information_schema.processlist
|
|
where state = "Waiting for backup lock";
|
|
backup stage end;
|
|
connection default;
|
|
--reap # commit
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # Check if BACKUP STAGE BLOCK_DDL blocks create view
|
|
--echo #
|
|
|
|
create table t1 (a int) engine=innodb;
|
|
connection con1;
|
|
backup stage start;
|
|
backup stage block_ddl;
|
|
connection default;
|
|
--send create view v1 as select * from t1;
|
|
connection con1;
|
|
--sleep 2
|
|
select count(*) = 1 from information_schema.processlist;
|
|
let $wait_condition=
|
|
select count(*) = 1 from information_schema.processlist
|
|
where state = "Waiting for backup lock";
|
|
backup stage end;
|
|
connection default;
|
|
--reap # create
|
|
drop table t1;
|
|
drop view v1;
|
|
|
|
#
|
|
# End of tests using con1 and con2
|
|
#
|
|
disconnect con1;
|
|
disconnect con2;
|
|
|
|
--echo #
|
|
--echo # Test backup stage and flush tables
|
|
--echo #
|
|
|
|
BACKUP STAGE START ;
|
|
BACKUP STAGE BLOCK_DDL ;
|
|
FLUSH TABLES;
|
|
CREATE TEMPORARY TABLE t12345678_tmp (col1 INT);
|
|
drop table t12345678_tmp;
|
|
BACKUP STAGE END;
|
|
|
|
--echo #
|
|
--echo # Test BACKUP STAGES with lock timeouts
|
|
--echo #
|
|
|
|
SET GLOBAL lock_wait_timeout=0;
|
|
CREATE TABLE t_permanent_innodb (col1 INT) ENGINE = InnoDB;
|
|
CREATE TABLE t_permanent_myisam (col1 INT) ENGINE = MyISAM;
|
|
CREATE TABLE t_permanent_aria (col1 INT) ENGINE = Aria transactional=1;
|
|
CREATE TABLE t_permanent_aria2 (col1 INT) ENGINE = Aria transactional=0;
|
|
INSERT INTO t_permanent_innodb SET col1 = 1;
|
|
INSERT INTO t_permanent_myisam SET col1 = 1;
|
|
INSERT INTO t_permanent_aria SET col1 = 1;
|
|
INSERT INTO t_permanent_aria2 SET col1 = 1;
|
|
|
|
CREATE TABLE t_con1_innodb (col1 INT) ENGINE = InnoDB;
|
|
CREATE TABLE t_con1_myisam (col1 INT) ENGINE = MyISAM;
|
|
|
|
--connect(con1,localhost,root,,)
|
|
|
|
--connection default
|
|
BACKUP STAGE START;
|
|
BACKUP STAGE FLUSH;
|
|
BACKUP STAGE BLOCK_DDL;
|
|
BACKUP STAGE BLOCK_COMMIT;
|
|
|
|
--connection con1
|
|
SET AUTOCOMMIT = 1;
|
|
|
|
# These should work as values are not changed
|
|
UPDATE t_permanent_aria SET col1 = 1;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
UPDATE t_permanent_innodb SET col1 = 1;
|
|
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
UPDATE t_permanent_innodb SET col1 = 8;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
UPDATE t_permanent_myisam SET col1 = 8;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
UPDATE t_permanent_aria SET col1 = 8;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
UPDATE t_permanent_aria2 SET col1 = 8;
|
|
|
|
select * from t_permanent_innodb;
|
|
select * from t_permanent_myisam;
|
|
select * from t_permanent_aria;
|
|
select * from t_permanent_aria2;
|
|
|
|
SET AUTOCOMMIT = 0;
|
|
UPDATE t_permanent_innodb SET col1 = 9;
|
|
UPDATE t_permanent_aria SET col1 = 9;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
UPDATE t_permanent_myisam SET col1 = 9;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
UPDATE t_permanent_aria2 SET col1 = 9;
|
|
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
DROP TABLE t_con1_innodb;
|
|
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
DROP TABLE t_con1_myisam;
|
|
|
|
--connection default
|
|
BACKUP STAGE END;
|
|
|
|
select * from t_permanent_innodb;
|
|
select * from t_permanent_myisam;
|
|
select * from t_permanent_aria;
|
|
select * from t_permanent_aria2;
|
|
|
|
DROP TABLE t_permanent_myisam, t_permanent_innodb, t_permanent_aria, t_permanent_aria2;
|
|
DROP TABLE t_con1_innodb, t_con1_myisam;
|
|
--disconnect con1
|
|
set global lock_wait_timeout=default;
|