MDEV-20945: BACKUP UNLOCK + FTWRL assertion failure

MDEV-20945: BACKUP UNLOCK + FTWRL assertion failure | SIGSEGV in I_P_List
from MDL_context::release_lock on INSERT w/ BACKUP LOCK (on optimized
builds) | Assertion `ticket->m_duration == MDL_EXPLICIT' failed

BACKUP LOCK behavior is modified so it won't be used wrong:
- BACKUP LOCK should commit any active transactions.
- BACKUP LOCK should not be allowed in stored procedures.
- When BACKUP LOCK is active, don't allow any DDL's for that connection.
- FTWRL is forbidden on the same connection while BACKUP LOCK is active.

Reviewed-by: monty@mariadb.com

4 files changed, 241 insertions, 0 deletions

diff --git a/mysql-test/main/backup_locks.result b/mysql-test/main/backup_locks.result
index a3a66937cf9..f1c02677b7a 100644
--- a/mysql-test/main/backup_locks.result
+++ b/mysql-test/main/backup_locks.result
@@ -39,6 +39,108 @@ MDL_INTENTION_EXCLUSIVE	Schema metadata lock	test
 select * from t1;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 backup unlock;
+#
+# 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 con1;
 connection default;
 disconnect con1;
diff --git a/mysql-test/main/backup_locks.test b/mysql-test/main/backup_locks.test
index 21b67100506..ed7d3875d1d 100644
--- a/mysql-test/main/backup_locks.test
+++ b/mysql-test/main/backup_locks.test
@@ -43,6 +43,125 @@ SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.me
 --error ER_LOCK_DEADLOCK
 select * from t1;
 backup unlock;
+
+--echo #
+--echo # BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures.
+--echo #
+delimiter |;
+--error ER_SP_BADSTATEMENT
+CREATE PROCEDURE p_BACKUP_LOCK()
+BEGIN
+  BACKUP LOCK;
+END|
+--error ER_SP_BADSTATEMENT
+CREATE PROCEDURE p_BACKUP_UNLOCK()
+BEGIN
+  BACKUP UNLOCK;
+END|
+delimiter ;|
+
+--echo #
+--echo # BACKUP STAGE doesn't work when a BACKUP LOCK is active.
+--echo #
+CREATE TABLE t1 (a INT);
+BACKUP LOCK t1;
+--error ER_CANT_UPDATE_WITH_READLOCK
+BACKUP STAGE START;
+BACKUP UNLOCK;
+DROP TABLE t1;
+
+--echo #
+--echo # FLUSH TABLES WITH READ LOCK is not allowed when BACKUP LOCK is active.
+--echo #
+CREATE TABLE t1 (a INT);
+BACKUP LOCK t1;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+FLUSH TABLES t1 WITH READ LOCK;
+BACKUP UNLOCK;
+
+BACKUP LOCK t1;
+FLUSH TABLES WITH READ LOCK;
+BACKUP UNLOCK;
+UNLOCK TABLES;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-20945 BACKUP UNLOCK assertion failures.
+--echo #
+
+--echo # Scenario 1.
+CREATE TABLE t1 (a INT);
+BACKUP LOCK t1;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+FLUSH TABLE t1 WITH READ LOCK;
+UNLOCK TABLES;
+BACKUP UNLOCK;   # Shouldn't trigger an assertion.
+DROP TABLE t1;
+
+--echo # Scenario 2.
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (b INT);
+LOCK TABLES t2 AS a2 WRITE;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+BACKUP LOCK t1;
+UNLOCK TABLES;
+INSERT INTO t1 VALUES(0);
+--source include/restart_mysqld.inc
+DROP TABLE t1;
+DROP TABLE t2;
+
+--echo # Scenario 3.
+CREATE TEMPORARY TABLE t3 (c INT);
+BACKUP LOCK t1;  # Table `t1` doesn't exist.
+SET @@SESSION.profiling=ON;
+--error ER_CANT_UPDATE_WITH_READLOCK
+CREATE TABLE t1 (c INT);
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+LOCK TABLES t3 AS a1 READ, t1 AS a3 READ, t3 AS a5 READ LOCAL;
+UNLOCK TABLE;
+--source include/restart_mysqld.inc
+
+--echo # Scenario 4.
+CREATE TABLE t (c INT);
+BACKUP LOCK not_existing.t;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+LOCK TABLES t WRITE;
+UNLOCK TABLES;
+--source include/restart_mysqld.inc
+DROP TABLE t;
+
+--echo # Scenario 5.
+BACKUP LOCK t1;
+--error ER_CANT_UPDATE_WITH_READLOCK
+CREATE TABLE t2 (c1 TIME, c2 TIME, c3 DATE, KEY(c1, c2));
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+LOCK TABLE t2 READ;
+--source include/restart_mysqld.inc
+
+--echo # Scenario 6.
+BACKUP LOCK t;
+--error ER_CANT_UPDATE_WITH_READLOCK
+CREATE VIEW v AS SELECT 1;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+LOCK TABLES v READ;
+START TRANSACTION READ ONLY;
+BACKUP LOCK t;
+--source include/restart_mysqld.inc
+
+--echo # Scenario 7.
+SET SQL_MODE='';
+SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u;
+--error ER_CANT_UPDATE_WITH_READLOCK
+CREATE TABLE t (a INT) ENGINE=Aria;
+CREATE TEMPORARY TABLE IF NOT EXISTS s (c INT) ENGINE=Aria;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+LOCK TABLES s AS a READ LOCAL,t AS b WRITE;
+--let $q= `SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE`
+SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u;
+--source include/restart_mysqld.inc
+
+--echo #
+
 connection con1;
 --reap
 connection default;
diff --git a/mysql-test/main/implicit_commit.result b/mysql-test/main/implicit_commit.result
index b0cd0b75e8d..911aec085d8 100644
--- a/mysql-test/main/implicit_commit.result
+++ b/mysql-test/main/implicit_commit.result
@@ -1035,6 +1035,16 @@ YES
 # SQLCOM_BACKUP
 #
 #
+# SQLCOM_BACKUP_LOCK
+#
+INSERT INTO db1.trans VALUES (1);
+BACKUP LOCK t1;
+ROLLBACK;
+BACKUP UNLOCK;
+CALL db1.test_if_commit();
+IMPLICIT COMMIT
+YES
+#
 # SQLCOM_SHOW_ARCHIVE
 #
 #
diff --git a/mysql-test/main/implicit_commit.test b/mysql-test/main/implicit_commit.test
index bcc43865395..09f2e2e921e 100644
--- a/mysql-test/main/implicit_commit.test
+++ b/mysql-test/main/implicit_commit.test
@@ -1115,6 +1115,16 @@ source include/implicit_commit_helper.inc;
 #source include/implicit_commit_helper.inc;
 
 --echo #
+--echo # SQLCOM_BACKUP_LOCK
+--echo #
+
+INSERT INTO db1.trans VALUES (1);
+BACKUP LOCK t1;
+ROLLBACK;
+BACKUP UNLOCK;
+CALL db1.test_if_commit();
+
+--echo #
 --echo # SQLCOM_SHOW_ARCHIVE
 --echo #