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 # Testing which locks we get from all stages 11--echo # 12 13BACKUP STAGE START; 14SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 15BACKUP STAGE FLUSH; 16SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 17BACKUP STAGE BLOCK_DDL; 18SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 19BACKUP STAGE BLOCK_COMMIT; 20SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 21BACKUP STAGE END; 22SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 23 24--echo # 25--echo # testing BACKUP STAGE LOCK's 26--echo # 27 28# Following connections are used in a few of the following tests 29connect (con1,localhost,root,,); 30connect (con2,localhost,root,,); 31connection default; 32 33--echo # 34--echo # testing if BACKUP STAGE FLUSH causes deadlocks with ALTER TABLE 35--echo # 36 37create table t1 (a int) engine=innodb; 38 39start transaction; 40# Acquires MDL lock 41insert into t1 values (1); 42 43connection con1; 44# Waits on MDL 45--send alter table t1 add column (j int), algorithm copy 46 47connection con2; 48let $wait_condition= 49 select count(*) = 1 from information_schema.processlist 50 where state = "Waiting for table metadata lock"; 51--source include/wait_condition.inc 52backup stage start; 53backup stage flush; 54SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 55# 56# Do first test with max_statement_time, other tests later are done with 57# lock_wait_timeout. This is mostly to ensure that both methods works 58# 59--error ER_STATEMENT_TIMEOUT 60SET STATEMENT max_statement_time=1 FOR backup stage block_ddl; 61--send backup stage block_ddl 62 63connection default; 64let $wait_condition= 65 select count(*) = 1 from information_schema.processlist 66 where state = "Waiting for backup lock"; 67--source include/wait_condition.inc 68commit; 69# The following select works because alter table is waiting for DDL lock 70SELECT * FROM t1; 71--error ER_LOCK_WAIT_TIMEOUT 72SET STATEMENT lock_wait_timeout=0 FOR INSERT INTO t1 values (2); 73--send INSERT INTO t1 values (2,0); 74connection con2; 75--reap # BLOCK_DDL 76backup stage end; 77connection con1; 78--reap # ALTER TABLE 79connection default; 80--reap # INSERT 81select * from t1; 82drop table t1; 83 84--echo # Test with inline alter table, which doesn't block block_commit 85 86create table t1 (a int) engine=innodb; 87 88start transaction; 89# Acquires MDL lock 90insert into t1 values (1); 91 92connection con1; 93# Waits on MDL 94--send alter table t1 add column (j int) 95 96connection con2; 97let $wait_condition= 98 select count(*) = 1 from information_schema.processlist 99 where state = "Waiting for table metadata lock"; 100--source include/wait_condition.inc 101backup stage start; 102backup stage flush; 103SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 104backup stage block_ddl; 105backup stage block_commit; 106connection default; 107SELECT * FROM t1; 108--send commit 109connection con2; 110let $wait_condition= 111 select count(*) = 1 from information_schema.processlist 112 where state = "Waiting for backup lock"; 113backup stage end; 114connection con1; 115--reap # ALTER TABLE 116connection default; 117--reap # commit 118drop table t1; 119 120--echo # 121--echo # testing if BACKUP STAGE FLUSH causes deadlocks with DROP TABLE 122--echo # 123 124create table t1 (a int) engine=innodb; 125start transaction; 126# Acquires MDL lock 127insert into t1 values (1); 128 129connection con1; 130# Waits on MDL 131--error ER_LOCK_WAIT_TIMEOUT 132SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1; 133--send DROP TABLE t1 134 135connection con2; 136backup stage start; 137backup stage flush; 138let $wait_condition= 139 select count(*) = 1 from information_schema.processlist 140 where state = "Waiting for table metadata lock"; 141--source include/wait_condition.inc 142--error ER_LOCK_WAIT_TIMEOUT 143SET STATEMENT lock_wait_timeout=0 FOR SELECT * FROM t1; 144 145backup stage block_ddl; 146SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 147backup stage end; 148 149connection default; 150commit; 151connection con1; 152--reap # DROP TABLE 153connection default; 154 155--echo # 156--echo # Check if backup stage block_dll + concurrent drop table blocks select 157--echo # 158 159create table t1 (a int) engine=innodb; 160backup stage start; 161backup stage block_ddl; 162connection con1; 163--send DROP TABLE t1 164connection con2; 165let $wait_condition= 166 select count(*) = 1 from information_schema.processlist 167 where state = "Waiting for backup lock"; 168--source include/wait_condition.inc 169connection con2; 170SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 171# Check that select's are not blocked 172SELECT * FROM t1; 173connection default; 174backup stage end; 175connection con1; 176--reap 177connection default; 178 179--echo # 180--echo # Check if backup stage block_dll overrides ddl lock for drop table 181--echo # 182 183create table t1 (a int) engine=innodb; 184start transaction; 185# Acquires MDL lock 186insert into t1 values (1); 187 188connection con1; 189# Waits on MDL 190--error ER_LOCK_WAIT_TIMEOUT 191SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1; 192--send DROP TABLE t1 193 194connection con2; 195backup stage start; 196backup stage flush; 197backup stage block_ddl; 198connection default; 199commit; 200connection con2; 201backup stage end; 202connection con1; 203--reap # DROP TABLE 204connection default; 205 206--echo # 207--echo # Check if BACKUP STAGE BLOCK_COMMIT blocks commit 208--echo # 209 210create table t1 (a int) engine=innodb; 211start transaction; 212# Acquires MDL lock 213insert into t1 values (1); 214 215connection con1; 216backup stage start; 217backup stage block_commit; 218connection default; 219--send commit 220connection con1; 221let $wait_condition= 222 select count(*) = 1 from information_schema.processlist 223 where state = "Waiting for backup lock"; 224backup stage end; 225connection default; 226--reap # commit 227select * from t1; 228drop table t1; 229 230# 231# End of tests using con1 and con2 232# 233disconnect con1; 234disconnect con2; 235 236--echo # 237--echo # Test backup stage and flush tables 238--echo # 239 240BACKUP STAGE START ; 241BACKUP STAGE BLOCK_DDL ; 242FLUSH TABLES; 243CREATE TEMPORARY TABLE t12345678_tmp (col1 INT); 244drop table t12345678_tmp; 245BACKUP STAGE END; 246 247--echo # 248--echo # Test BACKUP STAGES with lock timeouts 249--echo # 250 251SET GLOBAL lock_wait_timeout=0; 252CREATE TABLE t_permanent_innodb (col1 INT) ENGINE = InnoDB; 253CREATE TABLE t_permanent_myisam (col1 INT) ENGINE = MyISAM; 254CREATE TABLE t_permanent_aria (col1 INT) ENGINE = Aria transactional=1; 255CREATE TABLE t_permanent_aria2 (col1 INT) ENGINE = Aria transactional=0; 256INSERT INTO t_permanent_innodb SET col1 = 1; 257INSERT INTO t_permanent_myisam SET col1 = 1; 258INSERT INTO t_permanent_aria SET col1 = 1; 259INSERT INTO t_permanent_aria2 SET col1 = 1; 260 261CREATE TABLE t_con1_innodb (col1 INT) ENGINE = InnoDB; 262CREATE TABLE t_con1_myisam (col1 INT) ENGINE = MyISAM; 263 264--connect(con1,localhost,root,,) 265 266--connection default 267BACKUP STAGE START; 268BACKUP STAGE FLUSH; 269BACKUP STAGE BLOCK_DDL; 270BACKUP STAGE BLOCK_COMMIT; 271 272--connection con1 273SET AUTOCOMMIT = 1; 274 275# These should work as values are not changed 276UPDATE t_permanent_aria SET col1 = 1; 277--error ER_LOCK_WAIT_TIMEOUT 278UPDATE t_permanent_innodb SET col1 = 1; 279 280--error ER_LOCK_WAIT_TIMEOUT 281UPDATE t_permanent_innodb SET col1 = 8; 282--error ER_LOCK_WAIT_TIMEOUT 283UPDATE t_permanent_myisam SET col1 = 8; 284--error ER_LOCK_WAIT_TIMEOUT 285UPDATE t_permanent_aria SET col1 = 8; 286--error ER_LOCK_WAIT_TIMEOUT 287UPDATE t_permanent_aria2 SET col1 = 8; 288 289select * from t_permanent_innodb; 290select * from t_permanent_myisam; 291select * from t_permanent_aria; 292select * from t_permanent_aria2; 293 294SET AUTOCOMMIT = 0; 295UPDATE t_permanent_innodb SET col1 = 9; 296UPDATE t_permanent_aria SET col1 = 9; 297--error ER_LOCK_WAIT_TIMEOUT 298UPDATE t_permanent_myisam SET col1 = 9; 299--error ER_LOCK_WAIT_TIMEOUT 300UPDATE t_permanent_aria2 SET col1 = 9; 301 302--error ER_LOCK_WAIT_TIMEOUT 303DROP TABLE t_con1_innodb; 304 305--error ER_LOCK_WAIT_TIMEOUT 306DROP TABLE t_con1_myisam; 307 308--connection default 309BACKUP STAGE END; 310 311select * from t_permanent_innodb; 312select * from t_permanent_myisam; 313select * from t_permanent_aria; 314select * from t_permanent_aria2; 315 316DROP TABLE t_permanent_myisam, t_permanent_innodb, t_permanent_aria, t_permanent_aria2; 317DROP TABLE t_con1_innodb, t_con1_myisam; 318--disconnect con1 319set global lock_wait_timeout=default; 320