1# 2# Test lock taken 3# 4BACKUP LOCK test.t1; 5SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 6LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 7MDL_SHARED_HIGH_PRIO Table metadata lock test t1 8BACKUP UNLOCK; 9SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 10LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 11BACKUP LOCK t1; 12SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 13LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 14MDL_SHARED_HIGH_PRIO Table metadata lock test t1 15BACKUP UNLOCK; 16BACKUP LOCK non_existing.t1; 17SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 18LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 19MDL_SHARED_HIGH_PRIO Table metadata lock non_existing t1 20BACKUP UNLOCK; 21# 22# Test that backup lock protects against ddl 23# 24connect con1,localhost,root,,; 25connection default; 26create table t1 (a int) engine=innodb; 27insert into t1 values (1); 28backup lock t1; 29select * from t1; 30a 311 32connection con1; 33drop table t1; 34connection default; 35SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 36LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 37MDL_SHARED_HIGH_PRIO Table metadata lock test t1 38MDL_INTENTION_EXCLUSIVE Schema metadata lock test 39select * from t1; 40ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 41backup unlock; 42connection con1; 43connection default; 44# 45# Check that BACKUP LOCK blocks some operations 46# 47create sequence seq1; 48create sequence seq2; 49backup lock seq1; 50connection con1; 51CREATE OR REPLACE SEQUENCE seq1 START -28; 52ERROR HY000: Sequence 'test.seq1' values are conflicting 53SET STATEMENT max_statement_time=10 FOR CREATE OR REPLACE SEQUENCE seq1 START 50; 54ERROR 70100: Query execution was interrupted (max_statement_time exceeded) 55SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 NOMAXVALUE; 56ERROR 70100: Query execution was interrupted (max_statement_time exceeded) 57SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 MAXVALUE 1000; 58ERROR 70100: Query execution was interrupted (max_statement_time exceeded) 59SET STATEMENT max_statement_time=10 for rename table seq2 to seq3, seq3 to seq1; 60ERROR 70100: Query execution was interrupted (max_statement_time exceeded) 61connection default; 62backup unlock; 63drop table seq1,seq2; 64# 65# BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures. 66# 67CREATE PROCEDURE p_BACKUP_LOCK() 68BEGIN 69BACKUP LOCK; 70END| 71ERROR 0A000: BACKUP LOCK is not allowed in stored procedures 72CREATE PROCEDURE p_BACKUP_UNLOCK() 73BEGIN 74BACKUP UNLOCK; 75END| 76ERROR 0A000: BACKUP UNLOCK is not allowed in stored procedures 77# 78# BACKUP STAGE doesn't work when a BACKUP LOCK is active. 79# 80CREATE TABLE t1 (a INT); 81BACKUP LOCK t1; 82BACKUP STAGE START; 83ERROR HY000: Can't execute the query because you have a conflicting read lock 84BACKUP UNLOCK; 85DROP TABLE t1; 86# 87# FLUSH TABLES WITH READ LOCK is not allowed when BACKUP LOCK is active. 88# 89CREATE TABLE t1 (a INT); 90BACKUP LOCK t1; 91FLUSH TABLES t1 WITH READ LOCK; 92ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 93BACKUP UNLOCK; 94BACKUP LOCK t1; 95FLUSH TABLES WITH READ LOCK; 96BACKUP UNLOCK; 97UNLOCK TABLES; 98DROP TABLE t1; 99# 100# MDEV-20945 BACKUP UNLOCK assertion failures. 101# 102# Scenario 1. 103CREATE TABLE t1 (a INT); 104BACKUP LOCK t1; 105FLUSH TABLE t1 WITH READ LOCK; 106ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 107UNLOCK TABLES; 108BACKUP UNLOCK; 109DROP TABLE t1; 110# Scenario 2. 111CREATE TABLE t1 (a INT); 112CREATE TABLE t2 (b INT); 113LOCK TABLES t2 AS a2 WRITE; 114BACKUP LOCK t1; 115ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 116UNLOCK TABLES; 117INSERT INTO t1 VALUES(0); 118# restart 119DROP TABLE t1; 120DROP TABLE t2; 121# Scenario 3. 122CREATE TEMPORARY TABLE t3 (c INT); 123BACKUP LOCK t1; 124SET @@SESSION.profiling=ON; 125CREATE TABLE t1 (c INT); 126ERROR HY000: Can't execute the query because you have a conflicting read lock 127LOCK TABLES t3 AS a1 READ, t1 AS a3 READ, t3 AS a5 READ LOCAL; 128ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 129UNLOCK TABLE; 130# restart 131# Scenario 4. 132CREATE TABLE t (c INT); 133BACKUP LOCK not_existing.t; 134LOCK TABLES t WRITE; 135ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 136UNLOCK TABLES; 137# restart 138DROP TABLE t; 139# Scenario 5. 140BACKUP LOCK t1; 141CREATE TABLE t2 (c1 TIME, c2 TIME, c3 DATE, KEY(c1, c2)); 142ERROR HY000: Can't execute the query because you have a conflicting read lock 143LOCK TABLE t2 READ; 144ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 145# restart 146# Scenario 6. 147BACKUP LOCK t; 148CREATE VIEW v AS SELECT 1; 149ERROR HY000: Can't execute the query because you have a conflicting read lock 150LOCK TABLES v READ; 151ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 152START TRANSACTION READ ONLY; 153BACKUP LOCK t; 154# restart 155# Scenario 7. 156SET SQL_MODE=''; 157SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u; 158CREATE TABLE t (a INT) ENGINE=Aria; 159ERROR HY000: Can't execute the query because you have a conflicting read lock 160CREATE TEMPORARY TABLE IF NOT EXISTS s (c INT) ENGINE=Aria; 161LOCK TABLES s AS a READ LOCAL,t AS b WRITE; 162ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 163SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u; 164# restart 165# 166connection default; 167disconnect con1; 168show tables; 169Tables_in_test 170# 171# MDEV-22879 SIGSEGV (or hang) in free/my_free from 172# _ma_end_block_record (on optimized builds) 173# 174SET STATEMENT max_statement_time=20 FOR BACKUP LOCK test.t1; 175CREATE TABLE IF NOT EXISTS t3 (c1 CHAR(1) BINARY,c2 SMALLINT(10),c3 NUMERIC(1,0), PRIMARY KEY(c1(1))) ENGINE=InnoDB; 176ERROR HY000: Can't execute the query because you have a conflicting read lock 177BACKUP UNLOCK; 178CREATE TABLE IF NOT EXISTS t3 (c1 CHAR(1) BINARY,c2 SMALLINT(10),c3 NUMERIC(1,0), PRIMARY KEY(c1(1))) ENGINE=InnoDB; 179SET STATEMENT max_statement_time=20 FOR BACKUP LOCK test.t1; 180LOCK TABLES t3 AS a2 WRITE, t3 AS a1 READ LOCAL; 181ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 182DROP TABLE t3; 183ERROR HY000: Can't execute the query because you have a conflicting read lock 184BACKUP UNLOCK; 185DROP TABLE t3; 186# 187# End of MariaDB 10.4 tests 188# 189