MDEV-29060 main.view_debug fix

MDEV-28567 counted two execution sequences, there is third one which
executes ALTER VIEW before f() is created.

The more appropriate place for this test case is lock_sync.test

4 files changed, 143 insertions, 151 deletions

diff --git a/mysql-test/main/lock_sync.result b/mysql-test/main/lock_sync.result
index 25b97460238..4013d96edec 100644
--- a/mysql-test/main/lock_sync.result
+++ b/mysql-test/main/lock_sync.result
@@ -878,3 +878,29 @@ SET DEBUG_SYNC="RESET";
 disconnect con1;
 disconnect con2;
 DROP TABLES t1, t2;
+#
+# MDEV-28567 Assertion `0' in open_tables upon function-related operation
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (b INT);
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET b = 0;
+CREATE TRIGGER tr2 BEFORE INSERT ON t2 FOR EACH ROW UPDATE t1 SET a = 6;
+CREATE VIEW v1 AS SELECT * FROM t1;
+SET AUTOCOMMIT=OFF;
+SELECT * FROM t1;
+a
+DROP TRIGGER tr1;
+INSERT INTO t2 SELECT * FROM t2;
+SELECT f() FROM t2;
+ERROR 42000: FUNCTION test.f does not exist
+set debug_sync= 'after_open_table_mdl_shared signal s1';
+ALTER VIEW v1 AS SELECT f() FROM t1;
+CREATE FUNCTION f() RETURNS INT RETURN 1;
+set debug_sync= 'now wait_for s1';
+SELECT * FROM ( SELECT * FROM v1 ) sq;
+a
+COMMIT;
+DROP VIEW v1;
+DROP FUNCTION f;
+DROP TABLE t1, t2;
+set debug_sync= 'reset';
diff --git a/mysql-test/main/lock_sync.test b/mysql-test/main/lock_sync.test
index b2cb0bc058c..9a81a1b2f4a 100644
--- a/mysql-test/main/lock_sync.test
+++ b/mysql-test/main/lock_sync.test
@@ -1103,3 +1103,120 @@ DROP TABLES t1, t2;
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc
+
+--echo #
+--echo # MDEV-28567 Assertion `0' in open_tables upon function-related operation
+--echo #
+# To get MDL trace run this case like this:
+# mtr --mysqld=--debug=d,mdl,query:i:o,/tmp/mdl.log ...
+# Cleanup trace like this:
+# sed -i -re '/(mysql|performance_schema|sys|mtr)\// d; /MDL_BACKUP_|MDL_INTENTION_/ d; /\/(t2|tr1|tr2)/ d' /tmp/mdl.log
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (b INT);
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET b = 0;
+CREATE TRIGGER tr2 BEFORE INSERT ON t2 FOR EACH ROW UPDATE t1 SET a = 6;
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SET AUTOCOMMIT=OFF;
+SELECT * FROM t1;
+# T@6
+# Seized:   test/t1 (MDL_SHARED_READ)
+
+--connect (con1,localhost,root,,test)
+--send
+  DROP TRIGGER tr1;
+# T@7
+# Seized:   test/t1 (MDL_SHARED_NO_WRITE)
+# Waiting: test/t1 (MDL_EXCLUSIVE)
+# Waiting: test/t1 (MDL_SHARED_WRITE)
+# Deadlock: test/t1 (MDL_SHARED_WRITE)
+
+--connection default
+--error 0, ER_LOCK_DEADLOCK
+INSERT INTO t2 SELECT * FROM t2;
+# T@6
+# Released: test/t1 (MDL_SHARED_READ)
+# T@7
+# Acquired: test/t1 (MDL_EXCLUSIVE) (good)
+--error ER_SP_DOES_NOT_EXIST
+SELECT f() FROM t2;
+# T@6
+# Seized:   test/f (MDL_SHARED)
+# T@7
+# Released: test/t1 (MDL_EXCLUSIVE)
+# Good1: continue T@6 below
+# Bad1: continue T@8 below
+
+# Now we hold test/f, the below code creates concurrent
+# waiting of 3 threads for test/f which leads to deadlock (Bad)
+
+# To achive Good comment out 'now wait_for s1' below and run multiple times.
+
+--connect (con2,localhost,root,,test)
+set debug_sync= 'after_open_table_mdl_shared signal s1';
+--send
+  ALTER VIEW v1 AS SELECT f() FROM t1;
+# T@8
+# Good2: Waiting: test/v1 (MDL_EXCLUSIVE)
+# Good2-3: continue T@7 below
+# Good5: Acquired: test/v1 (MDL_EXCLUSIVE)
+# Good5: Seized: test/v1 (MDL_EXCLUSIVE)
+# Good5-6: continue T@7 below
+# Good7: Seized:   test/t1 (MDL_SHARED_READ)
+# Good7: Waiting: test/f (MDL_SHARED)
+# Good7-8: continue T@7 below
+# Good9: Acquired: test/f (MDL_SHARED)
+# Good9: Released: test/f (MDL_SHARED)
+# Good9: Released: test/t1 (MDL_SHARED_READ)
+# Good9: Released: test/v1 (MDL_EXCLUSIVE)
+# Good9: command finished without error
+# Bad1: Seized: test/v1 (MDL_EXCLUSIVE)
+# Bad1: Seized: test/v1 (MDL_EXCLUSIVE)
+# Bad1: Seized: test/t1 (MDL_SHARED_READ)
+# Bad1-2: continue T@6 below
+# Bad4: Waiting: test/f (MDL_SHARED)
+# Bad4: Deadlock: test/f (MDL_SHARED)
+# Bad4: command finished with error
+
+--connection con1
+--reap
+--send
+CREATE FUNCTION f() RETURNS INT RETURN 1;
+# T@7
+# Good3: Waiting: test/f (MDL_EXCLUSIVE)
+# Good3-4: continue T@6 below
+# Good6: Acquired: test/f (MDL_EXCLUSIVE)
+# Good6-7: continue T@8 above
+# Good8: Released: test/f (MDL_EXCLUSIVE)
+# Good8-9: continue T@8 above
+# Bad3: Waiting: test/f (MDL_EXCLUSIVE)
+# Bad3-4: continue T@8 above
+
+--connection default
+set debug_sync= 'now wait_for s1';
+SELECT * FROM ( SELECT * FROM v1 ) sq;
+# T@6
+# Good1: Seized:   test/v1 (MDL_SHARED_READ)
+# Good1-2: continue T@8 above
+# Good4: Seized:   test/t1 (MDL_SHARED_READ)
+# Bad2: Waiting: test/v1 (MDL_SHARED_READ)
+# Bad2-3: continue T@7 above
+
+# Cleanup
+COMMIT;
+# Good4: Released: test/t1 (MDL_SHARED_READ)
+# Good4: Released: test/v1 (MDL_SHARED_READ)
+# Good4: Released: test/f (MDL_SHARED)
+# Good4-5: continue T@8 above
+
+--connection con2
+--error 0, ER_SP_DOES_NOT_EXIST
+--reap
+--disconnect con1
+--disconnect con2
+--connection default
+DROP VIEW v1;
+DROP FUNCTION f;
+DROP TABLE t1, t2;
+set debug_sync= 'reset';
diff --git a/mysql-test/main/view_debug.result b/mysql-test/main/view_debug.result
index 591beba5ef3..2aeba2e1c65 100644
--- a/mysql-test/main/view_debug.result
+++ b/mysql-test/main/view_debug.result
@@ -26,38 +26,3 @@ disconnect con2;
 connection default;
 drop procedure proc;
 drop view v1,v2;
-#
-# MDEV-28567 Assertion `0' in open_tables upon function-related operation
-#
-CREATE TABLE t1 (a INT);
-CREATE TABLE t2 (b INT);
-CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET b = 0;
-CREATE TRIGGER tr2 BEFORE INSERT ON t2 FOR EACH ROW UPDATE t1 SET a = 6;
-CREATE VIEW v1 AS SELECT * FROM t1;
-SET AUTOCOMMIT=OFF;
-SELECT * FROM t1;
-a
-connect  con1,localhost,root,,test;
-DROP TRIGGER tr1;
-connection default;
-INSERT INTO t2 SELECT * FROM t2;
-SELECT f() FROM t2;
-ERROR 42000: FUNCTION test.f does not exist
-connect  con2,localhost,root,,test;
-set debug_sync= 'after_open_table_mdl_shared signal s1';
-ALTER VIEW v1 AS SELECT f() FROM t1;
-connection con1;
-CREATE FUNCTION f() RETURNS INT RETURN 1;
-connection default;
-set debug_sync= 'now wait_for s1';
-SELECT * FROM ( SELECT * FROM v1 ) sq;
-a
-COMMIT;
-connection con2;
-disconnect con1;
-disconnect con2;
-connection default;
-DROP VIEW v1;
-DROP FUNCTION f;
-DROP TABLE t1, t2;
-set debug_sync= 'reset';
diff --git a/mysql-test/main/view_debug.test b/mysql-test/main/view_debug.test
index acbd9cc740f..35fc0c6e02f 100644
--- a/mysql-test/main/view_debug.test
+++ b/mysql-test/main/view_debug.test
@@ -36,119 +36,3 @@ SET DEBUG_SYNC= 'RESET';
 --connection default
 drop procedure proc;
 drop view v1,v2;
-
---echo #
---echo # MDEV-28567 Assertion `0' in open_tables upon function-related operation
---echo #
-# To get MDL trace run this case like this:
-# mtr --mysqld=--debug=d,mdl,query:i:o,/tmp/mdl.log ...
-# Cleanup trace like this:
-# sed -i -re '/(mysql|performance_schema|sys|mtr)\// d; /MDL_BACKUP_|MDL_INTENTION_/ d; /\/(t2|tr1|tr2)/ d' /tmp/mdl.log
-
-CREATE TABLE t1 (a INT);
-CREATE TABLE t2 (b INT);
-CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET b = 0;
-CREATE TRIGGER tr2 BEFORE INSERT ON t2 FOR EACH ROW UPDATE t1 SET a = 6;
-CREATE VIEW v1 AS SELECT * FROM t1;
-
-SET AUTOCOMMIT=OFF;
-SELECT * FROM t1;
-# T@6
-# Seized:   test/t1 (MDL_SHARED_READ)
-
---connect (con1,localhost,root,,test)
---send
-  DROP TRIGGER tr1;
-# T@7
-# Seized:   test/t1 (MDL_SHARED_NO_WRITE)
-# Waiting: test/t1 (MDL_EXCLUSIVE)
-# Waiting: test/t1 (MDL_SHARED_WRITE)
-# Deadlock: test/t1 (MDL_SHARED_WRITE)
-
---connection default
---error 0, ER_LOCK_DEADLOCK
-INSERT INTO t2 SELECT * FROM t2;
-# T@6
-# Released: test/t1 (MDL_SHARED_READ)
-# T@7
-# Acquired: test/t1 (MDL_EXCLUSIVE) (good)
---error ER_SP_DOES_NOT_EXIST
-SELECT f() FROM t2;
-# T@6
-# Seized:   test/f (MDL_SHARED)
-# T@7
-# Released: test/t1 (MDL_EXCLUSIVE)
-# Good1: continue T@6 below
-# Bad1: continue T@8 below
-
-# Now we hold test/f, the below code creates concurrent
-# waiting of 3 threads for test/f which leads to deadlock (Bad)
-
-# To achive Good comment out 'now wait_for s1' below and run multiple times.
-
---connect (con2,localhost,root,,test)
-set debug_sync= 'after_open_table_mdl_shared signal s1';
---send
-  ALTER VIEW v1 AS SELECT f() FROM t1;
-# T@8
-# Good2: Waiting: test/v1 (MDL_EXCLUSIVE)
-# Good2-3: continue T@7 below
-# Good5: Acquired: test/v1 (MDL_EXCLUSIVE)
-# Good5: Seized: test/v1 (MDL_EXCLUSIVE)
-# Good5-6: continue T@7 below
-# Good7: Seized:   test/t1 (MDL_SHARED_READ)
-# Good7: Waiting: test/f (MDL_SHARED)
-# Good7-8: continue T@7 below
-# Good9: Acquired: test/f (MDL_SHARED)
-# Good9: Released: test/f (MDL_SHARED)
-# Good9: Released: test/t1 (MDL_SHARED_READ)
-# Good9: Released: test/v1 (MDL_EXCLUSIVE)
-# Good9: command finished without error
-# Bad1: Seized: test/v1 (MDL_EXCLUSIVE)
-# Bad1: Seized: test/v1 (MDL_EXCLUSIVE)
-# Bad1: Seized: test/t1 (MDL_SHARED_READ)
-# Bad1-2: continue T@6 below
-# Bad4: Waiting: test/f (MDL_SHARED)
-# Bad4: Deadlock: test/f (MDL_SHARED)
-# Bad4: command finished with error
-
---connection con1
---reap
---send
-CREATE FUNCTION f() RETURNS INT RETURN 1;
-# T@7
-# Good3: Waiting: test/f (MDL_EXCLUSIVE)
-# Good3-4: continue T@6 below
-# Good6: Acquired: test/f (MDL_EXCLUSIVE)
-# Good6-7: continue T@8 above
-# Good8: Released: test/f (MDL_EXCLUSIVE)
-# Good8-9: continue T@8 above
-# Bad3: Waiting: test/f (MDL_EXCLUSIVE)
-# Bad3-4: continue T@8 above
-
---connection default
-set debug_sync= 'now wait_for s1';
-SELECT * FROM ( SELECT * FROM v1 ) sq;
-# T@6
-# Good1: Seized:   test/v1 (MDL_SHARED_READ)
-# Good1-2: continue T@8 above
-# Good4: Seized:   test/t1 (MDL_SHARED_READ)
-# Bad2: Waiting: test/v1 (MDL_SHARED_READ)
-# Bad2-3: continue T@7 above
-
-# Cleanup
-COMMIT;
-# Good4: Released: test/t1 (MDL_SHARED_READ)
-# Good4: Released: test/v1 (MDL_SHARED_READ)
-# Good4: Released: test/f (MDL_SHARED)
-# Good4-5: continue T@8 above
-
---connection con2
---reap
---disconnect con1
---disconnect con2
---connection default
-DROP VIEW v1;
-DROP FUNCTION f;
-DROP TABLE t1, t2;
-set debug_sync= 'reset';