diff options
Diffstat (limited to 'mysql-test/main/backup_lock.result')
| -rw-r--r-- | mysql-test/main/backup_lock.result | 219 |
1 files changed, 219 insertions, 0 deletions
diff --git a/mysql-test/main/backup_lock.result b/mysql-test/main/backup_lock.result new file mode 100644 index 00000000000..95b2f520d90 --- /dev/null +++ b/mysql-test/main/backup_lock.result @@ -0,0 +1,219 @@ +# +# Testing which locks we get from all stages +# +BACKUP STAGE START; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_BACKUP_START Backup lock +BACKUP STAGE FLUSH; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_BACKUP_FLUSH Backup lock +BACKUP STAGE BLOCK_DDL; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_BACKUP_WAIT_DDL Backup lock +BACKUP STAGE BLOCK_COMMIT; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_BACKUP_WAIT_COMMIT Backup lock +BACKUP STAGE END; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +# +# testing BACKUP STAGE LOCK's +# +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection default; +# +# testing if BACKUP STAGE FLUSH causes deadlocks with ALTER TABLE +# +create table t1 (a int) engine=innodb; +start transaction; +insert into t1 values (1); +connection con1; +alter table t1 add column (j int), algorithm copy; +connection con2; +backup stage start; +backup stage flush; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_BACKUP_DDL Backup lock +MDL_BACKUP_FLUSH Backup lock +MDL_SHARED_WRITE Table metadata lock test t1 +MDL_SHARED_UPGRADABLE Table metadata lock test t1 +MDL_INTENTION_EXCLUSIVE Schema metadata lock test +SET STATEMENT max_statement_time=1 FOR backup stage block_ddl; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +backup stage block_ddl; +connection default; +commit; +SELECT * FROM t1; +a +1 +SET STATEMENT lock_wait_timeout=0 FOR INSERT INTO t1 values (2); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t1 values (2,0);; +connection con2; +backup stage end; +connection con1; +connection default; +select * from t1; +a j +1 NULL +2 0 +drop table t1; +# Test with inline alter table, which doesn't block block_commit +create table t1 (a int) engine=innodb; +start transaction; +insert into t1 values (1); +connection con1; +alter table t1 add column (j int); +connection con2; +backup stage start; +backup stage flush; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_BACKUP_ALTER_COPY Backup lock +MDL_BACKUP_FLUSH Backup lock +MDL_SHARED_WRITE Table metadata lock test t1 +MDL_SHARED_UPGRADABLE Table metadata lock test t1 +MDL_INTENTION_EXCLUSIVE Schema metadata lock test +backup stage block_ddl; +backup stage block_commit; +connection default; +SELECT * FROM t1; +a +1 +commit; +connection con2; +backup stage end; +connection con1; +connection default; +drop table t1; +# +# testing if BACKUP STAGE FLUSH causes deadlocks with DROP TABLE +# +create table t1 (a int) engine=innodb; +start transaction; +insert into t1 values (1); +connection con1; +SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +DROP TABLE t1; +connection con2; +backup stage start; +backup stage flush; +SET STATEMENT lock_wait_timeout=0 FOR SELECT * FROM t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +backup stage block_ddl; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_BACKUP_WAIT_DDL Backup lock +MDL_SHARED_WRITE Table metadata lock test t1 +MDL_INTENTION_EXCLUSIVE Schema metadata lock test +backup stage end; +connection default; +commit; +connection con1; +connection default; +# +# Check if backup stage block_dll + concurrent drop table blocks select +# +create table t1 (a int) engine=innodb; +backup stage start; +backup stage block_ddl; +connection con1; +DROP TABLE t1; +connection con2; +connection con2; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_BACKUP_WAIT_DDL Backup lock +SELECT * FROM t1; +a +connection default; +backup stage end; +connection con1; +connection default; +# +# Check if backup stage block_dll overrides ddl lock for drop table +# +create table t1 (a int) engine=innodb; +start transaction; +insert into t1 values (1); +connection con1; +SET STATEMENT lock_wait_timeout=0 FOR DROP TABLE t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +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; +connection default; +# +# Check if BACKUP STAGE BLOCK_COMMIT blocks commit +# +create table t1 (a int) engine=innodb; +start transaction; +insert into t1 values (1); +connection con1; +backup stage start; +backup stage block_commit; +connection default; +commit; +connection con1; +backup stage end; +connection default; +select * from t1; +a +1 +drop table t1; +disconnect con1; +disconnect con2; +# +# Test backup stage and flush tables +# +BACKUP STAGE START ; +BACKUP STAGE BLOCK_DDL ; +FLUSH TABLES; +CREATE TEMPORARY TABLE t12345678_tmp (col1 INT); +drop table t12345678_tmp; +BACKUP STAGE END; +# +# Test BACKUP STAGES with lock timeouts +# +SET GLOBAL lock_wait_timeout=0; +CREATE TABLE t_permanent_innodb (col1 INT) ENGINE = InnoDB; +CREATE TABLE t_permanent_myisam (col1 INT) ENGINE = MyISAM; +INSERT INTO t_permanent_innodb SET col1 = 1; +INSERT INTO t_permanent_myisam 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,,; +SET AUTOCOMMIT = 0; +connection default; +BACKUP STAGE START; +BACKUP STAGE FLUSH; +BACKUP STAGE BLOCK_DDL; +BACKUP STAGE BLOCK_COMMIT; +connection con1; +UPDATE t_permanent_innodb SET col1 = 8; +UPDATE t_permanent_myisam SET col1 = 8; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +DROP TABLE t_con1_innodb; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +DROP TABLE t_con1_myisam; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection default; +BACKUP STAGE END; +DROP TABLE t_permanent_myisam, t_permanent_innodb; +DROP TABLE t_con1_innodb, t_con1_myisam; +disconnect con1; +set global lock_wait_timeout=default; |