Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 832 insertions, 0 deletions

diff --git a/mysql-test/main/sp-lock.result b/mysql-test/main/sp-lock.result
new file mode 100644
index 00000000..e7ebc3ae
--- /dev/null
+++ b/mysql-test/main/sp-lock.result
@@ -0,0 +1,832 @@
+# 
+# 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.
+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;
+lock table v1 read;
+start transaction;
+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;
+commit;
+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;
+# 
+# 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
+#
+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 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
+#
+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
+#