diff options
Diffstat (limited to 'mysql-test/r/sp-lock.result')
-rw-r--r-- | mysql-test/r/sp-lock.result | 846 |
1 files changed, 0 insertions, 846 deletions
diff --git a/mysql-test/r/sp-lock.result b/mysql-test/r/sp-lock.result deleted file mode 100644 index acf951f6676..00000000000 --- a/mysql-test/r/sp-lock.result +++ /dev/null @@ -1,846 +0,0 @@ -# -# Test coverage for changes performed by the fix -# for Bug#30977 "Concurrent statement using stored function -# and DROP FUNCTION breaks SBR. -# -# -# 1) Verify that the preceding transaction is -# (implicitly) committed before CREATE/ALTER/DROP -# PROCEDURE. Note, that this is already tested -# in implicit_commit.test, but here we use an alternative -# approach. -# -# Start a transaction, create a savepoint, -# then call a DDL operation on a procedure, and then check -# that the savepoint is no longer present. -drop table if exists t1; -drop procedure if exists p1; -drop procedure if exists p2; -drop procedure if exists p3; -drop procedure if exists p4; -drop function if exists f1; -create table t1 (a int); -# -# Test 'CREATE PROCEDURE'. -# -begin; -savepoint sv; -create procedure p1() begin end; -rollback to savepoint sv; -ERROR 42000: SAVEPOINT sv does not exist -# -# Test 'ALTER PROCEDURE'. -# -begin; -savepoint sv; -alter procedure p1 comment 'changed comment'; -rollback to savepoint sv; -ERROR 42000: SAVEPOINT sv does not exist -# -# Test 'DROP PROCEDURE'. -# -begin; -savepoint sv; -drop procedure p1; -rollback to savepoint sv; -ERROR 42000: SAVEPOINT sv does not exist -# -# Test 'CREATE FUNCTION'. -# -begin; -savepoint sv; -create function f1() returns int return 1; -rollback to savepoint sv; -ERROR 42000: SAVEPOINT sv does not exist -# -# Test 'ALTER FUNCTION'. -# -begin; -savepoint sv; -alter function f1 comment 'new comment'; -rollback to savepoint sv; -ERROR 42000: SAVEPOINT sv does not exist -# -# Test 'DROP FUNCTION'. -# -begin; -savepoint sv; -drop function f1; -rollback to savepoint sv; -ERROR 42000: SAVEPOINT sv does not exist -# -# 2) Verify that procedure DDL operations fail -# under lock tables. -# -# Auxiliary routines to test ALTER. -create procedure p1() begin end; -create function f1() returns int return 1; -lock table t1 write; -create procedure p2() begin end; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -alter procedure p1 comment 'changed comment'; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -drop procedure p1; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -create function f2() returns int return 1; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -alter function f1 comment 'changed comment'; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -lock table t1 read; -create procedure p2() begin end; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -alter procedure p1 comment 'changed comment'; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -drop procedure p1; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -create function f2() returns int return 1; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -alter function f1 comment 'changed comment'; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -unlock tables; -# -# Even if we locked a temporary table. -# Todo: this is a restriction we could possibly lift. -# -drop table t1; -create temporary table t1 (a int); -lock table t1 read; -create procedure p2() begin end; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -alter procedure p1 comment 'changed comment'; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -drop procedure p1; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -create function f2() returns int return 1; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -alter function f1 comment 'changed comment'; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction -unlock tables; -drop function f1; -drop procedure p1; -drop temporary table t1; -# -# 3) Verify that CREATE/ALTER/DROP routine grab an -# exclusive lock. -# -# For that, start a transaction, use a routine. In a concurrent -# connection, try to drop or alter the routine. It should place -# a pending or exclusive lock and block. In another concurrnet -# connection, try to use the routine. -# That should block on the pending exclusive lock. -# -connect con1, localhost, root,,; -connect con2, localhost, root,,; -connect con3, localhost, root,,; -# -# Test DROP PROCEDURE. -# -connection default; -create procedure p1() begin end; -create function f1() returns int -begin -call p1(); -return 1; -end| -begin; -select f1(); -f1() -1 -connection con1; -# Sending 'drop procedure p1'... -drop procedure p1; -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; -# Waiting for 'select f1()' to get blocked by a pending MDL lock... -connection default; -commit; -connection con1; -# Reaping 'drop procedure p1'... -connection con2; -# Reaping 'select f1()' -ERROR 42000: PROCEDURE test.p1 does not exist -connection default; -# -# Test CREATE PROCEDURE. -# -create procedure p1() begin end; -begin; -select f1(); -f1() -1 -connection con1; -# Sending 'create procedure p1'... -create procedure p1() begin end; -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; -# Waiting for 'select f1()' to get blocked by a pending MDL lock... -connection default; -commit; -connection con1; -# Reaping 'create procedure p1'... -ERROR 42000: PROCEDURE p1 already exists -connection con2; -# Reaping 'select f1()' -f1() -1 -connection default; -# -# Test ALTER PROCEDURE. -# -begin; -select f1(); -f1() -1 -connection con1; -# Sending 'alter procedure p1'... -alter procedure p1 contains sql; -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; -# Waiting for 'select f1()' to get blocked by a pending MDL lock... -connection default; -commit; -connection con1; -# Reaping 'alter procedure p1'... -connection con2; -# Reaping 'select f1()' -f1() -1 -connection default; -# -# Test DROP FUNCTION. -# -begin; -select f1(); -f1() -1 -connection con1; -# Sending 'drop function f1'... -drop function f1; -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; -# Waiting for 'select f1()' to get blocked by a pending MDL lock... -connection default; -commit; -connection con1; -# Reaping 'drop function f1'... -connection con2; -# Reaping 'select f1()' -ERROR 42000: FUNCTION test.f1 does not exist -connection default; -# -# Test CREATE FUNCTION. -# -create function f1() returns int return 1; -begin; -select f1(); -f1() -1 -connection con1; -# Sending 'create function f1'... -create function f1() returns int return 2; -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; -# Waiting for 'select f1()' to get blocked by a pending MDL lock... -connection default; -commit; -connection con1; -# Reaping 'create function f1'... -ERROR 42000: FUNCTION f1 already exists -connection con2; -# Reaping 'select f1()' -f1() -1 -connection default; -# -# Test ALTER FUNCTION. -# -begin; -select f1(); -f1() -1 -connection con1; -# Sending 'alter function f1'... -alter function f1 contains sql; -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; -# Waiting for 'select f1()' to get blocked by a pending MDL lock... -connection default; -commit; -connection con1; -# Reaping 'alter function f1'... -connection con2; -# Reaping 'select f1()' -f1() -1 -connection default; -drop function f1; -drop procedure p1; -# -# 4) MDL lock should not be taken for -# unrolled CALL statements. -# The primary goal of metadata locks is a consistent binary log. -# When a call statement is unrolled, it doesn't get to the -# binary log, instead the statements that are contained -# in the procedure body do. This can nest to any level. -# -create procedure p1() begin end; -create procedure p2() begin end; -create table t1 (a int); -create procedure p3() -begin -call p1(); -call p1(); -call p2(); -end| -create procedure p4() -begin -call p1(); -call p1(); -call p2(); -call p2(); -call p3(); -end| -begin; -select * from t1; -a -savepoint sv; -call p4(); -# Prepared statement should not add any locks either. -prepare stmt from "call p4()"; -execute stmt; -execute stmt; -connection con1; -drop procedure p1; -drop procedure p2; -drop procedure p3; -drop procedure p4; -connection default; -# This is to verify there was no implicit commit. -rollback to savepoint sv; -call p4(); -ERROR 42000: PROCEDURE test.p4 does not exist -commit; -drop table t1; -# -# 5) Locks should be taken on routines -# used indirectly by views or triggers. -# -# -# A function is used from a trigger. -# -create function f1() returns int return 1; -create table t1 (a int); -create table t2 (a int, b int); -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; -# Sending 'drop function f1' -drop function f1; -connection con2; -# Waiting for 'drop function f1' to get blocked on MDL lock... -connection default; -commit; -connection con1; -# Reaping 'drop function f1'... -connection default; -# -# A function is used from a view. -# -create function f1() returns int return 1; -create view v1 as select f1() as a; -begin; -select * from v1; -a -1 -connection con1; -# Sending 'drop function f1' -drop function f1; -connection con2; -# Waiting for 'drop function f1' to get blocked on MDL lock... -connection default; -commit; -connection con1; -# Reaping 'drop function f1'... -connection default; -# -# A procedure is used from a function. -# -create function f1() returns int -begin -declare v_out int; -call p1(v_out); -return v_out; -end| -create procedure p1(out v_out int) set v_out=3; -begin; -select * from v1; -a -3 -connection con1; -# Sending 'drop procedure p1' -drop procedure p1; -connection con2; -# Waiting for 'drop procedure p1' to get blocked on MDL lock... -connection default; -commit; -connection con1; -# Reaping 'drop procedure p1'... -connection default; -# -# Deep nesting: a function is used from a procedure used -# from a function used from a view used in a trigger. -# -create function f2() returns int return 4; -create procedure p1(out v_out int) set v_out=f2(); -drop trigger t1_ai; -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; -# Sending 'drop function f2' -drop function f2; -connection con2; -# Waiting for 'drop function f2' to get blocked on MDL lock... -connection default; -commit; -connection con1; -# Reaping 'drop function f2'... -connection default; -drop view v1; -drop function f1; -drop procedure p1; -drop table t1, t2; -# -# 6) Check that ER_LOCK_DEADLOCK is reported if -# acquisition of a shared lock fails during a transaction or -# we need to back off to flush the sp cache. -# -# Sic: now this situation does not require a back off since we -# flush the cache on the fly. -# -create function f1() returns int return 7; -create table t1 (a int); -begin; -select * from t1; -a -select f1(); -f1() -7 -commit; -drop table t1; -drop function f1; -# -# 7) Demonstrate that under LOCK TABLES we accumulate locks -# on stored routines, and release metadata locks in -# ROLLBACK TO SAVEPOINT. That is done only for those stored -# routines that are not part of LOCK TABLES prelocking list. -# Those stored routines that are part of LOCK TABLES -# prelocking list are implicitly locked when entering -# LOCK TABLES, and ROLLBACK TO SAVEPOINT has no effect on -# them. -# -create function f1() returns varchar(20) return "f1()"; -create function f2() returns varchar(20) return "f2()"; -create view v1 as select f1() as a; -set @@session.autocommit=0; -lock table v1 read; -select * from v1; -a -f1() -savepoint sv; -select f2(); -f2() -f2() -connection con1; -# Sending 'drop function f1'... -drop function f1; -connection con2; -# Waiting for 'drop function f1' to get blocked on MDL lock... -# Sending 'drop function f2'... -drop function f2; -connection default; -# Waiting for 'drop function f2' to get blocked on MDL lock... -rollback to savepoint sv; -connection con2; -# Reaping 'drop function f2'... -connection default; -unlock tables; -connection con1; -# Reaping 'drop function f1'... -connection default; -drop function f1; -ERROR 42000: FUNCTION test.f1 does not exist -drop function f2; -ERROR 42000: FUNCTION test.f2 does not exist -drop view v1; -set @@session.autocommit=default; -# -# 8) Check the situation when we're preparing or executing a -# prepared statement, and as part of that try to flush the -# session sp cache. However, one of the procedures that -# needs a flush is in use. Verify that there is no infinite -# reprepare loop and no crash. -# -create function f1() returns int return 1; -# -# We just mention p1() in the body of f2() to make -# sure that p1() metadata is validated when validating -# 'select f2()'. -# Recursion is not allowed in stored functions, so -# an attempt to just invoke p1() from f2() which is in turn -# called from p1() would have given a run-time error. -# -create function f2() returns int -begin -if @var is null then -call p1(); -end if; -return 1; -end| -create procedure p1() -begin -select f1() into @var; -execute stmt; -end| -connection con2; -prepare stmt from "select f2()"; -connection default; -begin; -select f1(); -f1() -1 -connection con1; -# Sending 'alter function f1 ...'... -alter function f1 comment "comment"; -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; -# Reaping 'alter function f1 ...' -connection con2; -# Reaping 'call p1()'... -f2() -1 -deallocate prepare stmt; -connection default; -drop function f1; -drop function f2; -drop procedure p1; -# -# 9) Check the situation when a stored function is invoked -# from a stored procedure, and recursively invokes the -# stored procedure that is in use. But for the second -# invocation, a cache flush is requested. We can't -# flush the procedure that's in use, and are forced -# to use an old version. It is not a violation of -# consistency, since we unroll top-level calls. -# Just verify the code works. -# -create function f1() returns int return 1; -begin; -select f1(); -f1() -1 -connection con1; -# Sending 'alter function f1 ...'... -alter function f1 comment "comment"; -connection con2; -# Waiting for 'alter function f1 ...' to get blocked on MDL lock... -# -# We just mention p1() in the body of f2() to make -# sure that p1() is prelocked for f2(). -# Recursion is not allowed in stored functions, so -# an attempt to just invoke p1() from f2() which is in turn -# called from p1() would have given a run-time error. -# -create function f2() returns int -begin -if @var is null then -call p1(); -end if; -return 1; -end| -create procedure p1() -begin -select f1() into @var; -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; -# Reaping 'alter function f1 ...' -connection con2; -# Reaping 'call p1()'... -connection default; -drop function f1; -drop function f2; -drop procedure p1; -# -# 10) A select from information_schema.routines now -# flushes the stored routines caches. Test that this -# does not remove from the cache a stored routine -# that is already prelocked. -# -create function f1() returns int return get_lock("30977", 100000); -create function f2() returns int return 2; -create function f3() returns varchar(255) -begin -declare res varchar(255); -declare c cursor for select routine_name from -information_schema.routines where routine_name='f1'; -select f1() into @var; -open c; -fetch c into res; -close c; -select f2() into @var; -return res; -end| -connection con1; -select get_lock("30977", 0); -get_lock("30977", 0) -1 -connection default; -# Sending 'select f3()'... -select f3(); -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; -drop function f4; -select release_lock("30977"); -release_lock("30977") -1 -connection default; -# Reaping 'select f3()'... -# Routine 'f2()' should exist and get executed successfully. -f3() -f1 -select @var; -@var -2 -drop function f1; -drop function f2; -drop function f3; -# 11) Check the situation when the connection is flushing the -# SP cache which contains a procedure that is being executed. -# -# Function f1() calls p1(). Procedure p1() has a DROP -# VIEW statement, which, we know, invalidates the routines cache. -# During cache flush p1() must not be flushed since it's in -# use. -# -create function f1() returns int -begin -call p1(); -return 1; -end| -create procedure p1() -begin -create view v1 as select 1; -drop view v1; -select f1() into @var; -set @exec_count=@exec_count+1; -end| -set @exec_count=0; -call p1(); -ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine p1 -select @exec_count; -@exec_count -0 -set @@session.max_sp_recursion_depth=5; -set @exec_count=0; -call p1(); -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger -select @exec_count; -@exec_count -0 -drop procedure p1; -drop function f1; -set @@session.max_sp_recursion_depth=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 -# -# We are just covering the existing behaviour with tests. The -# results are not necessarily correct." -# -CREATE PROCEDURE p1() -BEGIN -SELECT get_lock("test", 10); -SHOW CREATE PROCEDURE p1; -END| -connect con2, localhost, root; -connect con3, localhost, root; -connection default; -SELECT get_lock("test", 10); -get_lock("test", 10) -1 -connection con2; -# Will halt before executing SHOW CREATE PROCEDURE p1 -# Sending: -CALL p1(); -connection con3; -# Alter p1 -DROP PROCEDURE p1; -CREATE PROCEDURE p1() BEGIN END; -connection default; -# Resume CALL p1, now with new p1 -SELECT release_lock("test"); -release_lock("test") -1 -connection con2; -# Reaping: CALL p1() -get_lock("test", 10) -1 -Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -p1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() -BEGIN -SELECT get_lock("test", 10); -SHOW CREATE PROCEDURE p1; -END latin1 latin1_swedish_ci latin1_swedish_ci -connection con3; -disconnect con3; -connection con2; -disconnect con2; -connection default; -DROP PROCEDURE p1; -# -# Bug#57663 Concurrent statement using stored function and DROP DATABASE -# breaks SBR -# -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; -CREATE DATABASE db1; -CREATE FUNCTION db1.f1() RETURNS INTEGER RETURN 1; -START TRANSACTION; -SELECT db1.f1(); -db1.f1() -1 -connection con1; -# Sending: -DROP DATABASE db1; -connection default; -# Waiting for DROP DATABASE to be blocked by the lock on f1() -COMMIT; -connection con1; -# Reaping: DROP DATABASE db1 -# Test 2: Check that DROP DATABASE blocks if a procedure is -# used by an active transaction. -connection default; -CREATE DATABASE db1; -CREATE PROCEDURE db1.p1() BEGIN END; -CREATE FUNCTION f1() RETURNS INTEGER -BEGIN -CALL db1.p1(); -RETURN 1; -END| -START TRANSACTION; -SELECT f1(); -f1() -1 -connection con1; -# Sending: -DROP DATABASE db1; -connection default; -# Waiting for DROP DATABASE to be blocked by the lock on p1() -COMMIT; -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; -CREATE DATABASE db1; -CREATE TABLE db1.t1 (a INT); -CREATE FUNCTION db1.f1() RETURNS INTEGER RETURN 1; -START TRANSACTION; -SELECT db1.f1(); -db1.f1() -1 -connection con1; -# Sending: -DROP DATABASE db1; -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; -# Reaping: DROP DATABASE db1 -# Test 4: Check that active DROP DATABASE blocks stored routine DDL. -connection default; -CREATE DATABASE db1; -CREATE FUNCTION db1.f1() RETURNS INTEGER RETURN 1; -CREATE FUNCTION db1.f2() RETURNS INTEGER RETURN 2; -START TRANSACTION; -SELECT db1.f2(); -db1.f2() -2 -connection con1; -# Sending: -DROP DATABASE db1; -connection con2; -# Waiting for DROP DATABASE to be blocked by the lock on f2() -# Sending: -ALTER FUNCTION db1.f1 COMMENT "test"; -connection default; -# Waiting for ALTER FUNCTION to be blocked by the schema lock on db1 -COMMIT; -connection con1; -# Reaping: DROP DATABASE db1 -disconnect con1; -connection con2; -# Reaping: ALTER FUNCTION f1 COMMENT 'test' -ERROR 42000: FUNCTION db1.f1 does not exist -disconnect con2; -connection default; -DROP FUNCTION f1; -# -# End of 5.5 tests -# |