1# 2# Check backup and FTWRL 3# 4flush tables with read lock; 5backup stage start; 6ERROR HY000: Can't execute the query because you have a conflicting read lock 7unlock tables; 8backup stage start; 9flush tables with read lock; 10ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 11backup stage end; 12# 13# Check backup and FLUSH TABLES 14# 15flush tables; 16backup stage start; 17flush tables; 18backup stage end; 19# 20# Check BACKUP STAGE under lock tables 21# 22create table t1 (a int); 23lock table t1 write; 24backup stage start; 25ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 26backup stage end; 27ERROR HY000: You must start backup with "BACKUP STAGE START" 28unlock tables; 29lock table t1 read; 30backup stage start; 31ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 32backup stage end; 33ERROR HY000: You must start backup with "BACKUP STAGE START" 34unlock tables; 35# 36# Check lock tables under BACKUP STAGE 37# 38backup stage start; 39unlock tables; 40select lock_mode from information_schema.metadata_lock_info where thread_id>0; 41lock_mode 42MDL_BACKUP_START 43lock table t1 write; 44ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 45lock table t1 read; 46ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 47unlock tables; 48backup stage end; 49drop table t1; 50# 51# Check setting readonly under BACKUP STAGE 52# 53backup stage start; 54set @@global.read_only=1; 55ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 56backup stage end; 57# also make sure going back from read-only mode is not allowed 58set @@global.read_only=1; 59backup stage start; 60set @@global.read_only=0; 61ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 62backup stage end; 63set @@global.read_only=0; 64# 65# Check BACKUP STAGE under read_only 66# 67set @@global.read_only=1; 68backup stage start; 69backup stage end; 70set @@global.read_only=0; 71# 72# Check that we can't create tables during backup 73# 74backup stage start; 75create table t1 (a int); 76ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 77backup stage end; 78# also make sure we can't write to a table during backup 79create table t1(a INT); 80backup stage start; 81insert into t1 values(1); 82ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 83insert delayed into t1 values(1); 84ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 85update t1 set a=1; 86ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 87delete from t1; 88ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 89truncate table t1; 90ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 91drop table t1; 92ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 93backup stage end; 94drop table t1; 95# 96# BACKUP STAGE performs implicit commits 97# 98create table t1(a int) engine=InnoDB; 99begin; 100insert into t1 values(1); 101select lock_mode from information_schema.metadata_lock_info where thread_id>0; 102lock_mode 103MDL_SHARED_WRITE 104backup stage start; 105select lock_mode from information_schema.metadata_lock_info where thread_id>0; 106lock_mode 107MDL_BACKUP_START 108backup stage block_commit; 109commit; 110backup stage end; 111drop table t1; 112# Ensure that BACKUP STAGE ... does AUTOCOMMIT like most DDL. 113# Sideeffect: 114# Show the impact of not yet committed INSERT before sequence start 115# and ROLLBACK sliding through the sequence. 116CREATE TABLE t1 (col1 INT) ENGINE = InnoDB; 117SET AUTOCOMMIT = 0; 118INSERT INTO t1 SET col1 = 1; 119BACKUP STAGE START; 120ROLLBACK; 121BACKUP STAGE END; 122#---- 123INSERT INTO t1 SET col1 = 1; 124BACKUP STAGE START; 125BACKUP STAGE FLUSH; 126ROLLBACK; 127BACKUP STAGE END; 128#---- 129INSERT INTO t1 SET col1 = 1; 130BACKUP STAGE START; 131BACKUP STAGE FLUSH; 132BACKUP STAGE BLOCK_DDL; 133ROLLBACK; 134BACKUP STAGE END; 135#---- 136INSERT INTO t1 SET col1 = 1; 137BACKUP STAGE START; 138BACKUP STAGE FLUSH; 139BACKUP STAGE BLOCK_DDL; 140BACKUP STAGE BLOCK_COMMIT; 141ROLLBACK; 142BACKUP STAGE END; 143#---- 144INSERT INTO t1 SET col1 = 1; 145BACKUP STAGE START; 146BACKUP STAGE FLUSH; 147BACKUP STAGE BLOCK_DDL; 148BACKUP STAGE BLOCK_COMMIT; 149BACKUP STAGE END; 150ROLLBACK; 151SELECT COUNT(*) = 5 AS expect_1 FROM t1; 152expect_1 1531 154# Show the impact of not yet committed INSERT before sequence start 155# and a COMMIT sliding through the sequence. 156SET AUTOCOMMIT = 0; 157INSERT INTO t1 SET col1 = 1; 158BACKUP STAGE START; 159COMMIT; 160BACKUP STAGE END; 161#---- 162INSERT INTO t1 SET col1 = 1; 163BACKUP STAGE START; 164BACKUP STAGE FLUSH; 165COMMIT; 166BACKUP STAGE END; 167#---- 168INSERT INTO t1 SET col1 = 1; 169BACKUP STAGE START; 170BACKUP STAGE FLUSH; 171BACKUP STAGE BLOCK_DDL; 172COMMIT; 173BACKUP STAGE END; 174#---- 175INSERT INTO t1 SET col1 = 1; 176BACKUP STAGE START; 177BACKUP STAGE FLUSH; 178BACKUP STAGE BLOCK_DDL; 179BACKUP STAGE BLOCK_COMMIT; 180COMMIT; 181BACKUP STAGE END; 182#---- 183INSERT INTO t1 SET col1 = 1; 184BACKUP STAGE START; 185BACKUP STAGE FLUSH; 186BACKUP STAGE BLOCK_DDL; 187BACKUP STAGE BLOCK_COMMIT; 188BACKUP STAGE END; 189COMMIT; 190SELECT COUNT(*) = 10 AS expect_1 FROM t1; 191expect_1 1921 193DELETE FROM t1; 194COMMIT; 195drop table t1; 196# 197# CHECK: RO transaction under BACKUP STAGE is a potential deadlock 198# OTOH we most probably allow them under FTWRL as well 199# 200CREATE TABLE t1 (col1 INT) ENGINE = InnoDB; 201insert into t1 values (1); 202backup stage start; 203backup stage block_commit; 204begin; 205select * from t1; 206col1 2071 208select lock_mode from information_schema.metadata_lock_info where thread_id>0; 209lock_mode 210MDL_BACKUP_WAIT_COMMIT 211MDL_SHARED_READ 212backup stage end; 213select lock_mode from information_schema.metadata_lock_info where thread_id>0; 214lock_mode 215drop table t1; 216# 217# Check that handler are closed by backup stage block_ddl 218# 219create table t1 (a int, key a (a)); 220insert into t1 (a) values (1), (2), (3), (4), (5); 221handler t1 open; 222handler t1 read a prev; 223a 2245 225backup stage start; 226handler t1 read a prev; 227a 2284 229backup stage flush; 230backup stage block_ddl; 231handler t1 read a prev; 232a 2335 234backup stage block_commit; 235handler t1 read a prev; 236a 2374 238backup stage end; 239handler t1 close; 240drop table t1; 241# Show the fate and impact of some SELECT /HANDLER ... READ 242# sliding through the sequence. 243CREATE TABLE t1_innodb (col1 INT) ENGINE = InnoDB; 244INSERT INTO t1_innodb values (1),(2),(3); 245COMMIT; 246CREATE TABLE t1_myisam (col1 INT) ENGINE = MyISAM; 247INSERT INTO t1_myisam values (1),(2),(3); 248BACKUP STAGE START; 249SELECT COUNT(*) FROM t1_innodb; 250COUNT(*) 2513 252SELECT * FROM t1_innodb; 253col1 2541 2552 2563 257HANDLER t1_innodb OPEN; 258HANDLER t1_innodb READ FIRST; 259col1 2601 261HANDLER t1_innodb CLOSE; 262SELECT COUNT(*) FROM t1_myisam; 263COUNT(*) 2643 265HANDLER t1_myisam OPEN; 266HANDLER t1_myisam READ FIRST; 267col1 2681 269HANDLER t1_myisam CLOSE; 270BACKUP STAGE FLUSH; 271SELECT COUNT(*) FROM t1_innodb; 272COUNT(*) 2733 274HANDLER t1_innodb OPEN; 275HANDLER t1_innodb READ FIRST; 276col1 2771 278HANDLER t1_innodb CLOSE; 279SELECT COUNT(*) FROM t1_myisam; 280COUNT(*) 2813 282HANDLER t1_myisam OPEN; 283HANDLER t1_myisam READ FIRST; 284col1 2851 286HANDLER t1_myisam CLOSE; 287BACKUP STAGE BLOCK_DDL; 288SELECT COUNT(*) FROM t1_innodb; 289COUNT(*) 2903 291HANDLER t1_innodb OPEN; 292HANDLER t1_innodb READ FIRST; 293col1 2941 295HANDLER t1_innodb CLOSE; 296SELECT COUNT(*) FROM t1_myisam; 297COUNT(*) 2983 299HANDLER t1_myisam OPEN; 300HANDLER t1_myisam READ FIRST; 301col1 3021 303HANDLER t1_myisam CLOSE; 304BACKUP STAGE BLOCK_COMMIT; 305SELECT COUNT(*) FROM t1_innodb; 306COUNT(*) 3073 308HANDLER t1_innodb OPEN; 309HANDLER t1_innodb READ FIRST; 310col1 3111 312HANDLER t1_innodb CLOSE; 313SELECT COUNT(*) FROM t1_myisam; 314COUNT(*) 3153 316HANDLER t1_myisam OPEN; 317HANDLER t1_myisam READ FIRST; 318col1 3191 320HANDLER t1_myisam CLOSE; 321BACKUP STAGE END; 322drop table t1_innodb,t1_myisam; 323# Show the fate and impact of some SET GLOBAL tx_read_only = 1/0 324# sliding through the sequence. 325BACKUP STAGE START; 326SET GLOBAL tx_read_only = 1; 327SET GLOBAL tx_read_only = 0; 328BACKUP STAGE FLUSH; 329SET GLOBAL tx_read_only = 1; 330SET GLOBAL tx_read_only = 0; 331BACKUP STAGE BLOCK_DDL; 332SET GLOBAL tx_read_only = 1; 333SET GLOBAL tx_read_only = 0; 334BACKUP STAGE BLOCK_COMMIT; 335SET GLOBAL tx_read_only = 1; 336SET GLOBAL tx_read_only = 0; 337BACKUP STAGE END; 338# Show the fate and impact of some SET SESSION sql_log_bin = 0/1 339# sliding through the sequence. 340COMMIT; 341SET SESSION sql_log_bin = 1; 342BACKUP STAGE START; 343SET SESSION sql_log_bin = 0; 344SET SESSION sql_log_bin = 1; 345BACKUP STAGE FLUSH; 346SET SESSION sql_log_bin = 0; 347SET SESSION sql_log_bin = 1; 348BACKUP STAGE BLOCK_DDL; 349SET SESSION sql_log_bin = 0; 350SET SESSION sql_log_bin = 1; 351BACKUP STAGE BLOCK_COMMIT; 352SET SESSION sql_log_bin = 0; 353SET SESSION sql_log_bin = 1; 354BACKUP STAGE END; 355#---- 356SET SESSION sql_log_bin = 0; 357BACKUP STAGE START; 358SET SESSION sql_log_bin = 1; 359SET SESSION sql_log_bin = 0; 360BACKUP STAGE FLUSH; 361SET SESSION sql_log_bin = 1; 362SET SESSION sql_log_bin = 0; 363BACKUP STAGE BLOCK_DDL; 364SET SESSION sql_log_bin = 1; 365SET SESSION sql_log_bin = 0; 366BACKUP STAGE BLOCK_COMMIT; 367SET SESSION sql_log_bin = 1; 368SET SESSION sql_log_bin = 0; 369BACKUP STAGE END; 370SET SESSION sql_log_bin = 1; 371#----------------------------------------------------------------------- 372# BACKUP STAGE statements are not allowed in stored routines 373#----------------------------------------------------------------------- 374CREATE TABLE t1 (col1 INT); 375CREATE PROCEDURE p1() 376BEGIN 377BACKUP STAGE START; 378BACKUP STAGE FLUSH; 379BACKUP STAGE BLOCK_DDL; 380BACKUP STAGE BLOCK_COMMIT; 381BACKUP STAGE END; 382END| 383ERROR 0A000: BACKUP STAGE is not allowed in stored procedures 384CREATE FUNCTION f1 (s CHAR(20)) RETURNS INT DETERMINISTIC 385BEGIN 386BACKUP STAGE START; 387BACKUP STAGE FLUSH; 388BACKUP STAGE BLOCK_DDL; 389BACKUP STAGE BLOCK_COMMIT; 390BACKUP STAGE END; 391RETURN 1; 392END| 393ERROR 0A000: BACKUP STAGE is not allowed in stored procedures 394CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW 395BEGIN 396BACKUP STAGE START; 397BACKUP STAGE FLUSH; 398BACKUP STAGE BLOCK_DDL; 399BACKUP STAGE BLOCK_COMMIT; 400BACKUP STAGE END; 401END| 402ERROR 0A000: BACKUP STAGE is not allowed in stored procedures 403DROP TABLE t1; 404#----------------------------------------------------------------------- 405# Check BACKUP status variables 406#----------------------------------------------------------------------- 407SET SESSION lock_wait_timeout = 1; 408FLUSH STATUS; 409# Show how the status variable 'Com_backup' changes after BACKUP STAGE .. 410SHOW STATUS LIKE 'Com_backup'; 411Variable_name Value 412Com_backup 0 413BACKUP STAGE START; 414SHOW STATUS LIKE 'Com_backup'; 415Variable_name Value 416Com_backup 1 417BACKUP STAGE START; 418ERROR HY000: Backup stage 'START' is same or before current backup stage 'START' 419SHOW STATUS LIKE 'Com_backup'; 420Variable_name Value 421Com_backup 2 422BACKUP STAGE FLUSH; 423SHOW STATUS LIKE 'Com_backup'; 424Variable_name Value 425Com_backup 3 426BACKUP STAGE BLOCK_DDL; 427SHOW STATUS LIKE 'Com_backup'; 428Variable_name Value 429Com_backup 4 430BACKUP STAGE BLOCK_COMMIT; 431SHOW STATUS LIKE 'Com_backup'; 432Variable_name Value 433Com_backup 5 434BACKUP STAGE END; 435# In case the backup lock is taken by the current connection than 436# - DML modifying some permanent table is not allowed 437CREATE TABLE t1_innodb (col1 INT) ENGINE = InnoDB; 438CREATE TABLE t1_myisam (col1 INT) ENGINE = MyISAM; 439BACKUP STAGE START; 440SET AUTOCOMMIT = 0; 441INSERT INTO t1_innodb SET col1 = 1; 442ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 443SET AUTOCOMMIT = 1; 444INSERT INTO t1_innodb SET col1 = 1; 445ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 446INSERT INTO t1_myisam SET col1 = 1; 447ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 448# - DDL creating or renaming a permanent table or a procedure etc. 449# is not allowed. 450CREATE TABLE throw_away (col1 INT) ENGINE = InnoDB; 451ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 452RENAME TABLE t1_innodb To throw_away; 453ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 454CREATE PROCEDURE p1() SELECT 13; 455ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 456CREATE PROCEDURE p1() SELECT 13; 457ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 458BACKUP STAGE END; 459DROP TABLE t1_innodb; 460DROP TABLE t1_myisam; 461# 462# Creating and modifying TEMPORARY TABLES are allowed 463# 464BACKUP STAGE START; 465BACKUP STAGE BLOCK_DDL; 466CREATE TEMPORARY TABLE tmp (col1 INT); 467DROP TEMPORARY TABLE tmp; 468CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB; 469CREATE TEMPORARY TABLE t_temporary_myisam (col1 INT) ENGINE = MyISAM; 470# - DML modifying that temporary table is allowed. 471INSERT INTO t_temporary_innodb SET col1 = 1; 472SELECT COUNT(*) FROM t_temporary_innodb; 473COUNT(*) 4741 475INSERT INTO t_temporary_myisam SET col1 = 1; 476SELECT COUNT(*) FROM t_temporary_myisam; 477COUNT(*) 4781 479BACKUP STAGE END; 480# Show the fate and impact of some auto committed INSERT into temporary 481# table sliding through the sequence. 482SET AUTOCOMMIT = 1; 483BACKUP STAGE START; 484INSERT INTO t_temporary_innodb SET col1 = 1; 485INSERT INTO t_temporary_myisam SET col1 = 1; 486BACKUP STAGE FLUSH; 487INSERT INTO t_temporary_innodb SET col1 = 1; 488INSERT INTO t_temporary_myisam SET col1 = 1; 489BACKUP STAGE BLOCK_DDL; 490INSERT INTO t_temporary_innodb SET col1 = 1; 491INSERT INTO t_temporary_myisam SET col1 = 1; 492BACKUP STAGE BLOCK_COMMIT; 493INSERT INTO t_temporary_innodb SET col1 = 1; 494INSERT INTO t_temporary_myisam SET col1 = 1; 495BACKUP STAGE END; 496SELECT COUNT(*) FROM t_temporary_innodb; 497COUNT(*) 4985 499# Show the fate and impact of some DROP/CREATE TEMPORARY TABLE sliding 500# through the sequence. 501SET AUTOCOMMIT = 1; 502BACKUP STAGE START; 503DROP TEMPORARY TABLE t_temporary_innodb; 504CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB; 505BACKUP STAGE FLUSH; 506DROP TEMPORARY TABLE t_temporary_innodb; 507CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB; 508BACKUP STAGE BLOCK_DDL; 509DROP TEMPORARY TABLE t_temporary_innodb; 510CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB; 511BACKUP STAGE BLOCK_COMMIT; 512DROP TEMPORARY TABLE t_temporary_innodb; 513CREATE TEMPORARY TABLE t_temporary_innodb (col1 INT) ENGINE = InnoDB; 514BACKUP STAGE END; 515# Show that even more DDL on the temporary table is allowed. 516BACKUP STAGE START; 517TRUNCATE t_temporary_innodb; 518ALTER TABLE t_temporary_innodb ADD COLUMN col2 INT; 519ALTER TABLE t_temporary_innodb ADD KEY idx(col2); 520BACKUP STAGE END; 521CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, f INT); 522BACKUP STAGE START; 523FLUSH TABLE t1 FOR EXPORT; 524ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 525FLUSH TABLE t1 WITH READ LOCK; 526ERROR HY000: Can't execute the command as you have a BACKUP STAGE active 527BACKUP STAGE END; 528DROP TABLE t1; 529