diff options
Diffstat (limited to 'mysql-test/r/sp-lock.result')
| -rw-r--r-- | mysql-test/r/sp-lock.result | 254 |
1 files changed, 135 insertions, 119 deletions
diff --git a/mysql-test/r/sp-lock.result b/mysql-test/r/sp-lock.result index 49ff0c7ba58..edc5bd641d3 100644 --- a/mysql-test/r/sp-lock.result +++ b/mysql-test/r/sp-lock.result @@ -129,11 +129,13 @@ drop temporary table t1; # connection, try to use the routine. # That should block on the pending exclusive lock. # -# Establish helper connections. +connect con1, localhost, root,,; +connect con2, localhost, root,,; +connect con3, localhost, root,,; # # Test DROP PROCEDURE. # -# --> connection default +connection default; create procedure p1() begin end; create function f1() returns int begin @@ -144,24 +146,24 @@ begin; select f1(); f1() 1 -# --> connection con1 +connection con1; # Sending 'drop procedure p1'... drop procedure p1; -# --> connection con2 +connection con2; # Waiting for 'drop procedure t1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); -# --> connection con3 +connection con3; # Waiting for 'select f1()' to get blocked by a pending MDL lock... -# --> connection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'drop procedure p1'... -# --> connection con2 +connection con2; # Reaping 'select f1()' ERROR 42000: PROCEDURE test.p1 does not exist -# --> connection default +connection default; # # Test CREATE PROCEDURE. # @@ -170,25 +172,26 @@ begin; select f1(); f1() 1 -# --> connection con1 +connection con1; # Sending 'create procedure p1'... create procedure p1() begin end; -# --> connection con2 +connection con2; # Waiting for 'create procedure t1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); -# --> connection con3 +connection con3; # Waiting for 'select f1()' to get blocked by a pending MDL lock... -# --> connection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'create procedure p1'... ERROR 42000: PROCEDURE p1 already exists -# --> connection con2 +connection con2; # Reaping 'select f1()' f1() 1 +connection default; # # Test ALTER PROCEDURE. # @@ -196,25 +199,25 @@ begin; select f1(); f1() 1 -# --> connection con1 +connection con1; # Sending 'alter procedure p1'... alter procedure p1 contains sql; -# --> connection con2 +connection con2; # Waiting for 'alter procedure t1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); -# --> connection con3 +connection con3; # Waiting for 'select f1()' to get blocked by a pending MDL lock... -# --> connection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'alter procedure p1'... -# --> connection con2 +connection con2; # Reaping 'select f1()' f1() 1 -# --> connection default +connection default; # # Test DROP FUNCTION. # @@ -222,24 +225,24 @@ begin; select f1(); f1() 1 -# --> connection con1 +connection con1; # Sending 'drop function f1'... drop function f1; -# --> connection con2 +connection con2; # Waiting for 'drop function f1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); -# --> connection con3 +connection con3; # Waiting for 'select f1()' to get blocked by a pending MDL lock... -# --> connection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'drop function f1'... -# --> connection con2 +connection con2; # Reaping 'select f1()' ERROR 42000: FUNCTION test.f1 does not exist -# --> connection default +connection default; # # Test CREATE FUNCTION. # @@ -248,26 +251,26 @@ begin; select f1(); f1() 1 -# --> connection con1 +connection con1; # Sending 'create function f1'... create function f1() returns int return 2; -# --> connection con2 +connection con2; # Waiting for 'create function f1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); -# --> connection con3 +connection con3; # Waiting for 'select f1()' to get blocked by a pending MDL lock... -# --> connection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'create function f1'... ERROR 42000: FUNCTION f1 already exists -# --> connection con2 +connection con2; # Reaping 'select f1()' f1() 1 -# --> connection default +connection default; # # Test ALTER FUNCTION. # @@ -275,25 +278,25 @@ begin; select f1(); f1() 1 -# --> connection con1 +connection con1; # Sending 'alter function f1'... alter function f1 contains sql; -# --> connection con2 +connection con2; # Waiting for 'alter function f1' to get blocked on MDL lock... # Demonstrate that there is a pending exclusive lock. # Sending 'select f1()'... select f1(); -# --> connection con3 +connection con3; # Waiting for 'select f1()' to get blocked by a pending MDL lock... -# --> connection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'alter function f1'... -# --> connection con2 +connection con2; # Reaping 'select f1()' f1() 1 -# --> connection default +connection default; drop function f1; drop procedure p1; # @@ -330,12 +333,12 @@ call p4(); prepare stmt from "call p4()"; execute stmt; execute stmt; -# --> connection con1 +connection con1; drop procedure p1; drop procedure p2; drop procedure p3; drop procedure p4; -# --> connection default +connection default; # This is to verify there was no implicit commit. rollback to savepoint sv; call p4(); @@ -356,16 +359,16 @@ create trigger t1_ai after insert on t1 for each row insert into t2 (a, b) values (new.a, f1()); begin; insert into t1 (a) values (1); -# --> connection con1 +connection con1; # Sending 'drop function f1' drop function f1; -# --> connection con2 +connection con2; # Waiting for 'drop function f1' to get blocked on MDL lock... -# --> connnection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'drop function f1'... -# --> connection default +connection default; # # A function is used from a view. # @@ -375,16 +378,16 @@ begin; select * from v1; a 1 -# --> connection con1 +connection con1; # Sending 'drop function f1' drop function f1; -# --> connection con2 +connection con2; # Waiting for 'drop function f1' to get blocked on MDL lock... -# --> connnection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'drop function f1'... -# --> connection default +connection default; # # A procedure is used from a function. # @@ -399,16 +402,16 @@ begin; select * from v1; a 3 -# --> connection con1 +connection con1; # Sending 'drop procedure p1' drop procedure p1; -# --> connection con2 +connection con2; # Waiting for 'drop procedure p1' to get blocked on MDL lock... -# --> connnection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'drop procedure p1'... -# --> connection default +connection default; # # Deep nesting: a function is used from a procedure used # from a function used from a view used in a trigger. @@ -420,16 +423,16 @@ create trigger t1_ai after insert on t1 for each row insert into t2 (a, b) values (new.a, (select max(a) from v1)); begin; insert into t1 (a) values (3); -# --> connection con1 +connection con1; # Sending 'drop function f2' drop function f2; -# --> connection con2 +connection con2; # Waiting for 'drop function f2' to get blocked on MDL lock... -# --> connnection default +connection default; commit; -# --> connection con1 +connection con1; # Reaping 'drop function f2'... -# --> connection default +connection default; drop view v1; drop function f1; drop procedure p1; @@ -475,23 +478,23 @@ savepoint sv; select f2(); f2() f2() -# --> connection con1 +connection con1; # Sending 'drop function f1'... drop function f1; -# --> connection con2 +connection con2; # Waiting for 'drop function f1' to get blocked on MDL lock... # Sending 'drop function f2'... drop function f2; -# --> connection default +connection default; # Waiting for 'drop function f2' to get blocked on MDL lock... rollback to savepoint sv; -# --> connection con2 +connection con2; # Reaping 'drop function f2'... -# --> connection default +connection default; unlock tables; -# --> connection con1 +connection con1; # Reaping 'drop function f1'... -# --> connection default +connection default; drop function f1; ERROR 42000: FUNCTION test.f1 does not exist drop function f2; @@ -526,31 +529,32 @@ begin select f1() into @var; execute stmt; end| -# --> connection con2 +connection con2; prepare stmt from "select f2()"; -# --> connection default +connection default; begin; select f1(); f1() 1 -# --> connection con1 +connection con1; # Sending 'alter function f1 ...'... alter function f1 comment "comment"; -# --> connection con2 +connection con2; # Waiting for 'alter function f1 ...' to get blocked on MDL lock... # Sending 'call p1()'... call p1(); +connection default; # Waiting for 'call p1()' to get blocked on MDL lock on f1... # Let 'alter function f1 ...' go through... commit; -# --> connection con1 +connection con1; # Reaping 'alter function f1 ...' -# --> connection con2 +connection con2; # Reaping 'call p1()'... f2() 1 deallocate prepare stmt; -# --> connection default +connection default; drop function f1; drop function f2; drop procedure p1; @@ -569,10 +573,10 @@ begin; select f1(); f1() 1 -# --> connection con1 +connection con1; # Sending 'alter function f1 ...'... alter function f1 comment "comment"; -# --> connection con2 +connection con2; # Waiting for 'alter function f1 ...' to get blocked on MDL lock... # # We just mention p1() in the body of f2() to make @@ -595,14 +599,15 @@ select f2() into @var; end| # Sending 'call p1()'... call p1(); +connection default; # Waiting for 'call p1()' to get blocked on MDL lock on f1... # Let 'alter function f1 ...' go through... commit; -# --> connection con1 +connection con1; # Reaping 'alter function f1 ...' -# --> connection con2 +connection con2; # Reaping 'call p1()'... -# --> connection default +connection default; drop function f1; drop function f2; drop procedure p1; @@ -626,14 +631,14 @@ close c; select f2() into @var; return res; end| -# --> connection con1 +connection con1; select get_lock("30977", 0); get_lock("30977", 0) 1 -# --> connection default +connection default; # Sending 'select f3()'... select f3(); -# --> connection con1 +connection con1; # Waiting for 'select f3()' to get blocked on the user level lock... # Do something to change the cache version. create function f4() returns int return 4; @@ -641,7 +646,7 @@ drop function f4; select release_lock("30977"); release_lock("30977") 1 -# --> connection default +connection default; # Reaping 'select f3()'... # Routine 'f2()' should exist and get executed successfully. f3() @@ -688,10 +693,13 @@ select @exec_count; drop procedure p1; drop function f1; set @@session.max_sp_recursion_depth=default; -# --> connection con1 -# --> connection con2 -# --> connection con3 -# --> connection default +connection con1; +disconnect con1; +connection con2; +disconnect con2; +connection con3; +disconnect con3; +connection default; # # SHOW CREATE PROCEDURE p1 called from p1, after p1 was altered # @@ -703,24 +711,26 @@ BEGIN SELECT get_lock("test", 10); SHOW CREATE PROCEDURE p1; END| -# Connection default +connect con2, localhost, root; +connect con3, localhost, root; +connection default; SELECT get_lock("test", 10); get_lock("test", 10) 1 -# Connection 2 +connection con2; # Will halt before executing SHOW CREATE PROCEDURE p1 # Sending: CALL p1(); -# Connection 3 +connection con3; # Alter p1 DROP PROCEDURE p1; CREATE PROCEDURE p1() BEGIN END; -# Connection default +connection default; # Resume CALL p1, now with new p1 SELECT release_lock("test"); release_lock("test") 1 -# Connection 2 +connection con2; # Reaping: CALL p1() get_lock("test", 10) 1 @@ -730,9 +740,11 @@ BEGIN SELECT get_lock("test", 10); SHOW CREATE PROCEDURE p1; END latin1 latin1_swedish_ci latin1_swedish_ci -# Connection 3 -# Connection 2 -# Connection default; +connection con3; +disconnect con3; +connection con2; +disconnect con2; +connection default; DROP PROCEDURE p1; # # Bug#57663 Concurrent statement using stored function and DROP DATABASE @@ -740,26 +752,28 @@ DROP PROCEDURE p1; # DROP DATABASE IF EXISTS db1; DROP FUNCTION IF EXISTS f1; +connect con1, localhost, root; +connect con2, localhost, root; # Test 1: Check that DROP DATABASE block if a function is used # by an active transaction. -# Connection default +connection default; CREATE DATABASE db1; CREATE FUNCTION db1.f1() RETURNS INTEGER RETURN 1; START TRANSACTION; SELECT db1.f1(); db1.f1() 1 -# Connection con1 +connection con1; # Sending: DROP DATABASE db1; -# Connection default +connection default; # Waiting for DROP DATABASE to be blocked by the lock on f1() COMMIT; -# Connection con1 +connection con1; # Reaping: DROP DATABASE db1 # Test 2: Check that DROP DATABASE blocks if a procedure is # used by an active transaction. -# Connection default +connection default; CREATE DATABASE db1; CREATE PROCEDURE db1.p1() BEGIN END; CREATE FUNCTION f1() RETURNS INTEGER @@ -771,17 +785,17 @@ START TRANSACTION; SELECT f1(); f1() 1 -# Connection con1 +connection con1; # Sending: DROP DATABASE db1; -# Connection default +connection default; # Waiting for DROP DATABASE to be blocked by the lock on p1() COMMIT; -# Connection con1 +connection con1; # Reaping: DROP DATABASE db1 # Test 3: Check that DROP DATABASE is not selected as a victim if a # deadlock is discovered with DML statements. -# Connection default +connection default; CREATE DATABASE db1; CREATE TABLE db1.t1 (a INT); CREATE FUNCTION db1.f1() RETURNS INTEGER RETURN 1; @@ -789,18 +803,18 @@ START TRANSACTION; SELECT db1.f1(); db1.f1() 1 -# Connection con1 +connection con1; # Sending: DROP DATABASE db1; -# Connection default +connection default; # Waiting for DROP DATABASE to be blocked by the lock on f1() SELECT * FROM db1.t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction COMMIT; -# Connection con1 +connection con1; # Reaping: DROP DATABASE db1 # Test 4: Check that active DROP DATABASE blocks stored routine DDL. -# Connection default +connection default; CREATE DATABASE db1; CREATE FUNCTION db1.f1() RETURNS INTEGER RETURN 1; CREATE FUNCTION db1.f2() RETURNS INTEGER RETURN 2; @@ -808,22 +822,24 @@ START TRANSACTION; SELECT db1.f2(); db1.f2() 2 -# Connection con1 +connection con1; # Sending: DROP DATABASE db1; -# Connection con2 +connection con2; # Waiting for DROP DATABASE to be blocked by the lock on f2() # Sending: ALTER FUNCTION db1.f1 COMMENT "test"; -# Connection default +connection default; # Waiting for ALTER FUNCTION to be blocked by the schema lock on db1 COMMIT; -# Connection con1 +connection con1; # Reaping: DROP DATABASE db1 -# Connection con2 +disconnect con1; +connection con2; # Reaping: ALTER FUNCTION f1 COMMENT 'test' ERROR 42000: FUNCTION db1.f1 does not exist -# Connection default +disconnect con2; +connection default; DROP FUNCTION f1; # # End of 5.5 tests |