1######################################################################## 2# Tests BACKUP STAGE locking 3######################################################################## 4 5--source include/have_innodb.inc 6--source include/have_metadata_lock_info.inc 7--source include/not_embedded.inc 8 9--echo # 10--echo # Test lock taken 11--echo # 12 13BACKUP LOCK test.t1; 14SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 15BACKUP UNLOCK; 16SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 17BACKUP LOCK t1; 18SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 19BACKUP UNLOCK; 20BACKUP LOCK non_existing.t1; 21SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 22BACKUP UNLOCK; 23 24--echo # 25--echo # Test that backup lock protects against ddl 26--echo # 27 28connect (con1,localhost,root,,); 29 30connection default; 31create table t1 (a int) engine=innodb; 32insert into t1 values (1); 33backup lock t1; 34select * from t1; 35connection con1; 36--send drop table t1 37connection default; 38let $wait_condition= 39 select count(*) = 1 from information_schema.processlist 40 where state = "Waiting for table metadata lock"; 41--source include/wait_condition.inc 42SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 43--error ER_LOCK_DEADLOCK 44select * from t1; 45backup unlock; 46connection con1; 47--reap 48connection default; 49 50--echo # 51--echo # Check that BACKUP LOCK blocks some operations 52--echo # 53 54# These test has to be done with timeouts as we want to ensure that the tables 55# doesn't change 56 57create sequence seq1; 58create sequence seq2; 59backup lock seq1; 60connection con1; 61--error ER_SEQUENCE_INVALID_DATA 62CREATE OR REPLACE SEQUENCE seq1 START -28; 63--error ER_STATEMENT_TIMEOUT 64SET STATEMENT max_statement_time=10 FOR CREATE OR REPLACE SEQUENCE seq1 START 50; 65--error ER_STATEMENT_TIMEOUT 66SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 NOMAXVALUE; 67--error ER_STATEMENT_TIMEOUT 68SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 MAXVALUE 1000; 69--error ER_STATEMENT_TIMEOUT 70SET STATEMENT max_statement_time=10 for rename table seq2 to seq3, seq3 to seq1; 71connection default; 72backup unlock; 73drop table seq1,seq2; 74 75--echo # 76--echo # BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures. 77--echo # 78 79delimiter |; 80--error ER_SP_BADSTATEMENT 81CREATE PROCEDURE p_BACKUP_LOCK() 82BEGIN 83 BACKUP LOCK; 84END| 85--error ER_SP_BADSTATEMENT 86CREATE PROCEDURE p_BACKUP_UNLOCK() 87BEGIN 88 BACKUP UNLOCK; 89END| 90delimiter ;| 91 92--echo # 93--echo # BACKUP STAGE doesn't work when a BACKUP LOCK is active. 94--echo # 95CREATE TABLE t1 (a INT); 96BACKUP LOCK t1; 97--error ER_CANT_UPDATE_WITH_READLOCK 98BACKUP STAGE START; 99BACKUP UNLOCK; 100DROP TABLE t1; 101 102--echo # 103--echo # FLUSH TABLES WITH READ LOCK is not allowed when BACKUP LOCK is active. 104--echo # 105CREATE TABLE t1 (a INT); 106BACKUP LOCK t1; 107--error ER_LOCK_OR_ACTIVE_TRANSACTION 108FLUSH TABLES t1 WITH READ LOCK; 109BACKUP UNLOCK; 110 111BACKUP LOCK t1; 112FLUSH TABLES WITH READ LOCK; 113BACKUP UNLOCK; 114UNLOCK TABLES; 115DROP TABLE t1; 116 117--echo # 118--echo # MDEV-20945 BACKUP UNLOCK assertion failures. 119--echo # 120 121--echo # Scenario 1. 122CREATE TABLE t1 (a INT); 123BACKUP LOCK t1; 124--error ER_LOCK_OR_ACTIVE_TRANSACTION 125FLUSH TABLE t1 WITH READ LOCK; 126UNLOCK TABLES; 127BACKUP UNLOCK; # Shouldn't trigger an assertion. 128DROP TABLE t1; 129 130--echo # Scenario 2. 131CREATE TABLE t1 (a INT); 132CREATE TABLE t2 (b INT); 133LOCK TABLES t2 AS a2 WRITE; 134--error ER_LOCK_OR_ACTIVE_TRANSACTION 135BACKUP LOCK t1; 136UNLOCK TABLES; 137INSERT INTO t1 VALUES(0); 138--source include/restart_mysqld.inc 139DROP TABLE t1; 140DROP TABLE t2; 141 142--echo # Scenario 3. 143CREATE TEMPORARY TABLE t3 (c INT); 144BACKUP LOCK t1; # Table `t1` doesn't exist. 145SET @@SESSION.profiling=ON; 146--error ER_CANT_UPDATE_WITH_READLOCK 147CREATE TABLE t1 (c INT); 148--error ER_LOCK_OR_ACTIVE_TRANSACTION 149LOCK TABLES t3 AS a1 READ, t1 AS a3 READ, t3 AS a5 READ LOCAL; 150UNLOCK TABLE; 151--source include/restart_mysqld.inc 152 153--echo # Scenario 4. 154CREATE TABLE t (c INT); 155BACKUP LOCK not_existing.t; 156--error ER_LOCK_OR_ACTIVE_TRANSACTION 157LOCK TABLES t WRITE; 158UNLOCK TABLES; 159--source include/restart_mysqld.inc 160DROP TABLE t; 161 162--echo # Scenario 5. 163BACKUP LOCK t1; 164--error ER_CANT_UPDATE_WITH_READLOCK 165CREATE TABLE t2 (c1 TIME, c2 TIME, c3 DATE, KEY(c1, c2)); 166--error ER_LOCK_OR_ACTIVE_TRANSACTION 167LOCK TABLE t2 READ; 168--source include/restart_mysqld.inc 169 170--echo # Scenario 6. 171BACKUP LOCK t; 172--error ER_CANT_UPDATE_WITH_READLOCK 173CREATE VIEW v AS SELECT 1; 174--error ER_LOCK_OR_ACTIVE_TRANSACTION 175LOCK TABLES v READ; 176START TRANSACTION READ ONLY; 177BACKUP LOCK t; 178--source include/restart_mysqld.inc 179 180--echo # Scenario 7. 181SET SQL_MODE=''; 182SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u; 183--error ER_CANT_UPDATE_WITH_READLOCK 184CREATE TABLE t (a INT) ENGINE=Aria; 185CREATE TEMPORARY TABLE IF NOT EXISTS s (c INT) ENGINE=Aria; 186--error ER_LOCK_OR_ACTIVE_TRANSACTION 187LOCK TABLES s AS a READ LOCAL,t AS b WRITE; 188--let $q= `SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE` 189SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u; 190--source include/restart_mysqld.inc 191 192--echo # 193 194connection default; 195disconnect con1; 196show tables; 197 198--echo # 199--echo # MDEV-22879 SIGSEGV (or hang) in free/my_free from 200--echo # _ma_end_block_record (on optimized builds) 201--echo # 202 203SET STATEMENT max_statement_time=20 FOR BACKUP LOCK test.t1; 204--error ER_CANT_UPDATE_WITH_READLOCK 205CREATE TABLE IF NOT EXISTS t3 (c1 CHAR(1) BINARY,c2 SMALLINT(10),c3 NUMERIC(1,0), PRIMARY KEY(c1(1))) ENGINE=InnoDB; 206BACKUP UNLOCK; 207 208CREATE TABLE IF NOT EXISTS t3 (c1 CHAR(1) BINARY,c2 SMALLINT(10),c3 NUMERIC(1,0), PRIMARY KEY(c1(1))) ENGINE=InnoDB; 209SET STATEMENT max_statement_time=20 FOR BACKUP LOCK test.t1; 210--error ER_LOCK_OR_ACTIVE_TRANSACTION 211LOCK TABLES t3 AS a2 WRITE, t3 AS a1 READ LOCAL; 212--error ER_CANT_UPDATE_WITH_READLOCK 213DROP TABLE t3; 214BACKUP UNLOCK; 215DROP TABLE t3; 216 217--echo # 218--echo # End of MariaDB 10.4 tests 219--echo # 220