diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/backup_locks.result | 188 |
1 files changed, 188 insertions, 0 deletions
diff --git a/mysql-test/main/backup_locks.result b/mysql-test/main/backup_locks.result new file mode 100644 index 00000000..31aed5f3 --- /dev/null +++ b/mysql-test/main/backup_locks.result @@ -0,0 +1,188 @@ +# +# Test lock taken +# +BACKUP LOCK test.t1; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info where table_name not like "innodb_%"; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_SHARED_HIGH_PRIO Table metadata lock test t1 +BACKUP UNLOCK; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info where table_name not like "innodb_%"; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +BACKUP LOCK t1; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info where table_name not like "innodb_%"; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_SHARED_HIGH_PRIO Table metadata lock test t1 +BACKUP UNLOCK; +BACKUP LOCK non_existing.t1; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info where table_name not like "innodb_%"; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_SHARED_HIGH_PRIO Table metadata lock non_existing t1 +BACKUP UNLOCK; +# +# Test that backup lock protects against ddl +# +connect con1,localhost,root,,; +connection default; +create table t1 (a int) engine=innodb; +insert into t1 values (1); +backup lock t1; +select * from t1; +a +1 +connection con1; +drop table t1; +connection default; +SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info where table_name not like "innodb_%"; +LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME +MDL_SHARED_HIGH_PRIO Table metadata lock test t1 +MDL_INTENTION_EXCLUSIVE Schema metadata lock test +select * from t1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +backup unlock; +connection con1; +connection default; +# +# Check that BACKUP LOCK blocks some operations +# +create sequence seq1; +create sequence seq2; +backup lock seq1; +connection con1; +CREATE OR REPLACE SEQUENCE seq1 START -28; +ERROR HY000: Sequence 'test.seq1' has out of range value for options +SET STATEMENT max_statement_time=10 FOR CREATE OR REPLACE SEQUENCE seq1 START 50; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 NOMAXVALUE; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 MAXVALUE 1000; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +SET STATEMENT max_statement_time=10 for rename table seq2 to seq3, seq3 to seq1; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +connection default; +backup unlock; +drop table seq1,seq2; +# +# BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures. +# +CREATE PROCEDURE p_BACKUP_LOCK() +BEGIN +BACKUP LOCK; +END| +ERROR 0A000: BACKUP LOCK is not allowed in stored procedures +CREATE PROCEDURE p_BACKUP_UNLOCK() +BEGIN +BACKUP UNLOCK; +END| +ERROR 0A000: BACKUP UNLOCK is not allowed in stored procedures +# +# BACKUP STAGE doesn't work when a BACKUP LOCK is active. +# +CREATE TABLE t1 (a INT); +BACKUP LOCK t1; +BACKUP STAGE START; +ERROR HY000: Can't execute the query because you have a conflicting read lock +BACKUP UNLOCK; +DROP TABLE t1; +# +# FLUSH TABLES WITH READ LOCK is not allowed when BACKUP LOCK is active. +# +CREATE TABLE t1 (a INT); +BACKUP LOCK t1; +FLUSH TABLES t1 WITH READ LOCK; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +BACKUP UNLOCK; +BACKUP LOCK t1; +FLUSH TABLES WITH READ LOCK; +BACKUP UNLOCK; +UNLOCK TABLES; +DROP TABLE t1; +# +# MDEV-20945 BACKUP UNLOCK assertion failures. +# +# Scenario 1. +CREATE TABLE t1 (a INT); +BACKUP LOCK t1; +FLUSH TABLE t1 WITH READ LOCK; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +UNLOCK TABLES; +BACKUP UNLOCK; +DROP TABLE t1; +# Scenario 2. +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +LOCK TABLES t2 AS a2 WRITE; +BACKUP LOCK t1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +UNLOCK TABLES; +INSERT INTO t1 VALUES(0); +# restart +DROP TABLE t1; +DROP TABLE t2; +# Scenario 3. +CREATE TEMPORARY TABLE t3 (c INT); +BACKUP LOCK t1; +SET @@SESSION.profiling=ON; +CREATE TABLE t1 (c INT); +ERROR HY000: Can't execute the query because you have a conflicting read lock +LOCK TABLES t3 AS a1 READ, t1 AS a3 READ, t3 AS a5 READ LOCAL; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +UNLOCK TABLE; +# restart +# Scenario 4. +CREATE TABLE t (c INT); +BACKUP LOCK not_existing.t; +LOCK TABLES t WRITE; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +UNLOCK TABLES; +# restart +DROP TABLE t; +# Scenario 5. +BACKUP LOCK t1; +CREATE TABLE t2 (c1 TIME, c2 TIME, c3 DATE, KEY(c1, c2)); +ERROR HY000: Can't execute the query because you have a conflicting read lock +LOCK TABLE t2 READ; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# restart +# Scenario 6. +BACKUP LOCK t; +CREATE VIEW v AS SELECT 1; +ERROR HY000: Can't execute the query because you have a conflicting read lock +LOCK TABLES v READ; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +START TRANSACTION READ ONLY; +BACKUP LOCK t; +# restart +# Scenario 7. +SET SQL_MODE=''; +SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u; +CREATE TABLE t (a INT) ENGINE=Aria; +ERROR HY000: Can't execute the query because you have a conflicting read lock +CREATE TEMPORARY TABLE IF NOT EXISTS s (c INT) ENGINE=Aria; +LOCK TABLES s AS a READ LOCAL,t AS b WRITE; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u; +# restart +# +connection default; +disconnect con1; +show tables; +Tables_in_test +# +# MDEV-22879 SIGSEGV (or hang) in free/my_free from +# _ma_end_block_record (on optimized builds) +# +SET STATEMENT max_statement_time=20 FOR BACKUP LOCK test.t1; +CREATE TABLE IF NOT EXISTS t3 (c1 CHAR(1) BINARY,c2 SMALLINT(10),c3 NUMERIC(1,0), PRIMARY KEY(c1(1))) ENGINE=InnoDB; +ERROR HY000: Can't execute the query because you have a conflicting read lock +BACKUP UNLOCK; +CREATE TABLE IF NOT EXISTS t3 (c1 CHAR(1) BINARY,c2 SMALLINT(10),c3 NUMERIC(1,0), PRIMARY KEY(c1(1))) ENGINE=InnoDB; +SET STATEMENT max_statement_time=20 FOR BACKUP LOCK test.t1; +LOCK TABLES t3 AS a2 WRITE, t3 AS a1 READ LOCAL; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +DROP TABLE t3; +ERROR HY000: Can't execute the query because you have a conflicting read lock +BACKUP UNLOCK; +DROP TABLE t3; +# +# End of MariaDB 10.4 tests +# |