summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/backup_lock.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/backup_lock.test
parentInitial commit. (diff)
downloadmariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz
mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/backup_lock.test')
-rw-r--r--mysql-test/main/backup_lock.test349
1 files changed, 349 insertions, 0 deletions
diff --git a/mysql-test/main/backup_lock.test b/mysql-test/main/backup_lock.test
new file mode 100644
index 00000000..5453e5b0
--- /dev/null
+++ b/mysql-test/main/backup_lock.test
@@ -0,0 +1,349 @@
+########################################################################
+# 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';
+
+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) engine=innodb;
+
+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
+
+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) 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;
+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;
+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;