diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/ps_ddl.test | 11 | ||||
| -rw-r--r-- | mysql-test/t/ps_ddl1.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/sp-error.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/sp-lock.test | 876 |
4 files changed, 883 insertions, 8 deletions
diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test index e00d63aaedc..6a2b49ac9b7 100644 --- a/mysql-test/t/ps_ddl.test +++ b/mysql-test/t/ps_ddl.test @@ -278,8 +278,6 @@ deallocate prepare stmt; --echo # Test 7-a: dependent PROCEDURE has changed --echo # ---echo # Note, this scenario is not supported, subject of Bug#12093 ---echo # create table t1 (a int); create trigger t1_ai after insert on t1 for each row @@ -291,11 +289,10 @@ execute stmt using @var; drop procedure p1; create procedure p1 (a int) begin end; set @var= 2; ---error ER_SP_DOES_NOT_EXIST execute stmt using @var; --echo # Cleanup drop procedure p1; -call p_verify_reprepare_count(0); +call p_verify_reprepare_count(1); --echo # Test 7-b: dependent FUNCTION has changed --echo # @@ -355,11 +352,13 @@ set @var=8; --echo # XXX: bug, the SQL statement in the trigger is still --echo # pointing at table 't3', since the view was expanded --echo # at first statement execution. +--echo # Since the view definition is inlined in the statement +--echo # at prepare, changing the view definition does not cause +--echo # repreparation. --echo # Repreparation of the main statement doesn't cause repreparation --echo # of trigger statements. ---error ER_NO_SUCH_TABLE execute stmt using @var; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); --echo # --echo # Sic: the insert went into t3, even though the view now --echo # points at t2. This is because neither the merged view diff --git a/mysql-test/t/ps_ddl1.test b/mysql-test/t/ps_ddl1.test index 379ed576b5f..0145d445a14 100644 --- a/mysql-test/t/ps_ddl1.test +++ b/mysql-test/t/ps_ddl1.test @@ -363,7 +363,7 @@ end| delimiter ;| --error ER_SP_DOES_NOT_EXIST execute stmt; -call p_verify_reprepare_count(1); +call p_verify_reprepare_count(0); --error ER_SP_DOES_NOT_EXIST execute stmt; call p_verify_reprepare_count(0); diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 4df1118cd56..c8b2595e23d 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -723,7 +723,7 @@ lock table t1 read| # This should fail since we forgot to lock mysql.proc for writing # explicitly, and we can't open mysql.proc for _writing_ if there # are locked tables. ---error 1100 +--error ER_LOCK_OR_ACTIVE_TRANSACTION alter procedure bug9566 comment 'Some comment'| unlock tables| # This should succeed diff --git a/mysql-test/t/sp-lock.test b/mysql-test/t/sp-lock.test new file mode 100644 index 00000000000..a90b85a59be --- /dev/null +++ b/mysql-test/t/sp-lock.test @@ -0,0 +1,876 @@ +# Copyright (C) 2009 Sun Microsystems, Inc +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +# +# Metadata lock handling for stored procedures and +# functions. +# +--echo # +--echo # Test coverage for changes performed by the fix +--echo # for Bug#30977 "Concurrent statement using stored function +--echo # and DROP FUNCTION breaks SBR. +--echo # + +--echo # +--echo # 1) Verify that the preceding transaction is +--echo # (implicitly) committed before CREATE/ALTER/DROP +--echo # PROCEDURE. Note, that this is already tested +--echo # in implicit_commit.test, but here we use an alternative +--echo # approach. +--echo # + +--echo # Start a transaction, create a savepoint, +--echo # then call a DDL operation on a procedure, and then check +--echo # that the savepoint is no longer present. + +--disable_warnings +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; +--enable_warnings +create table t1 (a int); +--echo # +--echo # Test 'CREATE PROCEDURE'. +--echo # +begin; +savepoint sv; +create procedure p1() begin end; +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint sv; +--echo # +--echo # Test 'ALTER PROCEDURE'. +--echo # +begin; +savepoint sv; +alter procedure p1 comment 'changed comment'; +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint sv; +--echo # +--echo # Test 'DROP PROCEDURE'. +--echo # +begin; +savepoint sv; +drop procedure p1; +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint sv; +--echo # +--echo # Test 'CREATE FUNCTION'. +--echo # +begin; +savepoint sv; +create function f1() returns int return 1; +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint sv; +--echo # +--echo # Test 'ALTER FUNCTION'. +--echo # +begin; +savepoint sv; +alter function f1 comment 'new comment'; +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint sv; +--echo # +--echo # Test 'DROP FUNCTION'. +--echo # +begin; +savepoint sv; +drop function f1; +--error ER_SP_DOES_NOT_EXIST +rollback to savepoint sv; + +--echo # +--echo # 2) Verify that procedure DDL operations fail +--echo # under lock tables. +--echo # +--echo # Auxiliary routines to test ALTER. +create procedure p1() begin end; +create function f1() returns int return 1; + +lock table t1 write; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create procedure p2() begin end; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter procedure p1 comment 'changed comment'; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop procedure p1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create function f2() returns int return 1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter function f1 comment 'changed comment'; +lock table t1 read; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create procedure p2() begin end; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter procedure p1 comment 'changed comment'; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop procedure p1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create function f2() returns int return 1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter function f1 comment 'changed comment'; +unlock tables; +--echo # +--echo # Even if we locked a temporary table. +--echo # Todo: this is a restriction we could possibly lift. +--echo # +drop table t1; +create temporary table t1 (a int); +lock table t1 read; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create procedure p2() begin end; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter procedure p1 comment 'changed comment'; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +drop procedure p1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +create function f2() returns int return 1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +alter function f1 comment 'changed comment'; +unlock tables; + +drop function f1; +drop procedure p1; +drop temporary table t1; + +--echo # +--echo # 3) Verify that CREATE/ALTER/DROP routine grab an +--echo # exclusive lock. +--echo # +--echo # For that, start a transaction, use a routine. In a concurrent +--echo # connection, try to drop or alter the routine. It should place +--echo # a pending or exlusive lock and block. In a concurrnet +--echo # connection, try to use the routine under LOCK TABLES. +--echo # That should yield ER_LOCK_DEADLOCK. +--echo # +--echo # Establish helper connections. +connect(con1, localhost, root,,); +connect(con2, localhost, root,,); + +--echo # +--echo # Test DROP PROCEDURE. +--echo # +--echo # --> connection default +connection default; +create table t1 (a int); +create procedure p1() begin end; +delimiter |; +create function f1() returns int +begin + call p1(); + return 1; +end| +delimiter ;| +begin; +select f1(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'drop procedure p1'... +--send drop procedure p1 +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'drop procedure t1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='drop procedure p1'; +--source include/wait_condition.inc +--echo # Demonstrate that there is a pending exclusive lock. +lock table t1 read; +--error ER_LOCK_DEADLOCK +select f1(); +unlock tables; +--echo # --> connection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'drop procedure p1'... +--reap +--echo # --> connection default +connection default; + +--echo # +--echo # Test CREATE PROCEDURE. +--echo # +create procedure p1() begin end; +begin; +select f1(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'create procedure p1'... +--send create procedure p1() begin end +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'create procedure t1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='create procedure p1() begin end'; +--source include/wait_condition.inc +--echo # Demonstrate that there is a pending exclusive lock. +lock table t1 read; +--error ER_LOCK_DEADLOCK +select f1(); +unlock tables; +--echo # --> connection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'create procedure p1'... +--error ER_SP_ALREADY_EXISTS +--reap +connection default; +--echo # +--echo # Test ALTER PROCEDURE. +begin; +select f1(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'alter procedure p1'... +--send alter procedure p1 contains sql +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'alter procedure t1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='alter procedure p1 contains sql'; +--source include/wait_condition.inc +--echo # Demonstrate that there is a pending exclusive lock. +lock table t1 read; +--error ER_LOCK_DEADLOCK +select f1(); +unlock tables; +--echo # --> connection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'alter procedure p1'... +--reap +--echo # --> connection default +connection default; + +--echo # +--echo # Test DROP FUNCTION. +--echo # +begin; +select f1(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'drop function f1'... +--send drop function f1 +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'drop function f1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='drop function f1'; +--source include/wait_condition.inc +--echo # Demonstrate that there is a pending exclusive lock. +lock table t1 read; +--error ER_LOCK_DEADLOCK +select f1(); +unlock tables; +--echo # --> connection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'drop function f1'... +--reap +--echo # --> connection default +connection default; + +--echo # +--echo # Test CREATE FUNCTION. +--echo # +create function f1() returns int return 1; +begin; +select f1(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'create function f1'... +--send create function f1() returns int return 2 +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'create function f1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='create function f1() returns int return 2'; +--source include/wait_condition.inc +--echo # Demonstrate that there is a pending exclusive lock. +lock table t1 read; +--error ER_LOCK_DEADLOCK +select f1(); +unlock tables; +--echo # --> connection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'create function f1'... +--error ER_SP_ALREADY_EXISTS +--reap +--echo # --> connection default +connection default; +--echo # +--echo # Test ALTER FUNCTION. +begin; +select f1(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'alter function f1'... +--send alter function f1 contains sql +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'alter function f1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='alter function f1 contains sql'; +--source include/wait_condition.inc +--echo # Demonstrate that there is a pending exclusive lock. +lock table t1 read; +--error ER_LOCK_DEADLOCK +select f1(); +unlock tables; +--echo # --> connection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'alter function f1'... +--reap +--echo # --> connection default +connection default; +drop function f1; +drop procedure p1; + +--echo # +--echo # 4) MDL lock should not be taken for +--echo # unrolled CALL statements. +--echo # The primary goal of metadata locks is a consistent binary log. +--echo # When a call statement is unrolled, it doesn't get to the +--echo # binary log, instead the statements that are contained +--echo # in the procedure body do. This can nest to any level. +--echo # +create procedure p1() begin end; +create procedure p2() begin end; +delimiter |; +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| +delimiter ;| +begin; +select * from t1; +savepoint sv; +call p4(); +--echo # Prepared statement should not add any locks either. +prepare stmt from "call p4()"; +execute stmt; +execute stmt; +--echo # --> connection con1 +connection con1; +drop procedure p1; +drop procedure p2; +drop procedure p3; +drop procedure p4; +--echo # --> connection default +connection default; +--echo # This is to verify there was no implicit commit. +rollback to savepoint sv; +--error ER_SP_DOES_NOT_EXIST +call p4(); +commit; +drop table t1; + +--echo # +--echo # 5) Locks should be taken on routines +--echo # used indirectly by views or triggers. +--echo # +--echo # +--echo # A function is used from a trigger. +--echo # +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); +--echo # --> connection con1 +connection con1; +--echo # Sending 'drop function f1' +--send drop function f1 +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'drop function f1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='drop function f1'; +--source include/wait_condition.inc +--echo # --> connnection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'drop function f1'... +--reap +--echo # --> connection default +connection default; +--echo # +--echo # A function is used from a view. +--echo # +create function f1() returns int return 1; +create view v1 as select f1() as a; +begin; +select * from v1; +--echo # --> connection con1 +connection con1; +--echo # Sending 'drop function f1' +--send drop function f1 +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'drop function f1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='drop function f1'; +--source include/wait_condition.inc +--echo # --> connnection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'drop function f1'... +--reap +--echo # --> connection default +connection default; +--echo # +--echo # A procedure is used from a function. +--echo # +delimiter |; +create function f1() returns int +begin + declare v_out int; + call p1(v_out); + return v_out; +end| +delimiter ;| +create procedure p1(out v_out int) set v_out=3; +begin; +select * from v1; +--echo # --> connection con1 +connection con1; +--echo # Sending 'drop procedure p1' +--send drop procedure p1 +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'drop procedure p1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='drop procedure p1'; +--source include/wait_condition.inc +--echo # --> connnection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'drop procedure p1'... +--reap +--echo # --> connection default +connection default; + +--echo # +--echo # Deep nesting: a function is used from a procedure used +--echo # from a function used from a view used in a trigger. +--echo # +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); +--echo # --> connection con1 +connection con1; +--echo # Sending 'drop function f2' +--send drop function f2 +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'drop function f2' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='drop function f2'; +--source include/wait_condition.inc +--echo # --> connnection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'drop function f2'... +--reap +--echo # --> connection default +connection default; + +drop view v1; +drop function f1; +drop procedure p1; +drop table t1, t2; + +--echo # +--echo # 6) Check that ER_LOCK_DEADLOCK is reported if +--echo # acquisition of a shared lock fails during a transaction or +--echo # we need to back off to flush the sp cache. +--echo # +--echo # a) A back off due to a lock conflict. +--echo # +create table t1 (a int); +create function f1() returns int return 6; +begin; +select f1(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'drop function f1'... +--send drop function f1 +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'drop function f1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='drop function f1'; +--source include/wait_condition.inc +begin; +select * from t1; +--error ER_LOCK_DEADLOCK +select f1(); +commit; +--echo # --> connection default +connection default; +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'drop function f1'... +--reap +--echo # --> connection default +connection default; +--echo # +--echo # b) A back off to flush the cache. +--echo # Sic: now this situation does not require a back off since we +--echo # flush the cache on the fly. +--echo # +create function f1() returns int return 7; +begin; +select * from t1; +# Used to have a back-off here, with optional ER_LOCK_DEADLOCK +#--error ER_LOCK_DEADLOCK +select f1(); +commit; +drop table t1; +drop function f1; + +--echo # +--echo # 7) Demonstrate that under LOCK TABLES we accumulate locks +--echo # on stored routines, and release metadata locks in +--echo # ROLLBACK TO SAVEPOINT. That is done only for those stored +--echo # routines that are not part of LOCK TABLES prelocking list. +--echo # Those stored routines that are part of LOCK TABLES +--echo # prelocking list are implicitly locked when entering +--echo # LOCK TABLES, and ROLLBACK TO SAVEPOINT has no effect on +--echo # them. +--echo # +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; +savepoint sv; +select f2(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'drop function f1'... +--send drop function f1 +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'drop function f1' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='drop function f1'; +--source include/wait_condition.inc +--echo # Sending 'drop function f2'... +--send drop function f2 +--echo # --> connection default +connection default; +--echo # Waitng for 'drop function f2' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='drop function f2'; +--source include/wait_condition.inc +rollback to savepoint sv; +--echo # --> connection con2 +connection con2; +--echo # Reaping 'drop function f2'... +--reap +--echo # --> connection default +connection default; +unlock tables; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'drop function f1'... +--reap +--echo # --> connection default +connection default; +--error ER_SP_DOES_NOT_EXIST +drop function f1; +--error ER_SP_DOES_NOT_EXIST +drop function f2; +drop view v1; +set @@session.autocommit=default; + +--echo # +--echo # 8) Check the situation when we're preparing or executing a +--echo # prepared statement, and as part of that try to flush the +--echo # session sp cache. However, one of the procedures that +--echo # needs a flush is in use. Verify that there is no infinite +--echo # reprepare loop and no crash. +--echo # +create function f1() returns int return 1; +delimiter |; +--echo # +--echo # We just mention p1() in the body of f2() to make +--echo # sure that p1() metadata is validated when validating +--echo # 'select f2()'. +--echo # Recursion is not allowed in stored functions, so +--echo # an attempt to just invoke p1() from f2() which is in turn +--echo # called from p1() would have given a run-time error. +--echo # +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| +delimiter ;| +--echo # --> connection con2 +connection con2; +prepare stmt from "select f2()"; +--echo # --> connection default +connection default; +begin; +select f1(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'alter function f1 ...'... +--send alter function f1 comment "comment" +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'alter function f1 ...' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info like 'alter function f1 comment%'; +--source include/wait_condition.inc +--echo # Sending 'call p1()'... +--send call p1() +connection default; +--echo # Waitng for 'call p1()' to get blocked on MDL lock on f1... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='select f1() into @var'; +--source include/wait_condition.inc +--echo # Let 'alter function f1 ...' go through... +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'alter function f1 ...' +--reap +--echo # --> connection con2 +connection con2; +--echo # Reaping 'call p1()'... +--reap +deallocate prepare stmt; +--echo # --> connection default +connection default; +drop function f1; +drop function f2; +drop procedure p1; + +--echo # +--echo # 9) Check the situation when a stored function is invoked +--echo # from a stored procedure, and recursively invokes the +--echo # stored procedure that is in use. But for the second +--echo # invocation, a cache flush is requested. We can't +--echo # flush the procedure that's in use, and are forced +--echo # to use an old version. It is not a violation of +--echo # consistency, since we unroll top-level calls. +--echo # Just verify the code works. +--echo # +create function f1() returns int return 1; +begin; +select f1(); +--echo # --> connection con1 +connection con1; +--echo # Sending 'alter function f1 ...'... +--send alter function f1 comment "comment" +--echo # --> connection con2 +connection con2; +--echo # Waitng for 'alter function f1 ...' to get blocked on MDL lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info like 'alter function f1 comment%'; +--source include/wait_condition.inc +delimiter |; +--echo # +--echo # We just mention p1() in the body of f2() to make +--echo # sure that p1() is prelocked for f2(). +--echo # Recursion is not allowed in stored functions, so +--echo # an attempt to just invoke p1() from f2() which is in turn +--echo # called from p1() would have given a run-time error. +--echo # +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| +delimiter ;| +--echo # Sending 'call p1()'... +--send call p1() +connection default; +--echo # Waitng for 'call p1()' to get blocked on MDL lock on f1... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='Waiting for table' and info='select f1() into @var'; +--source include/wait_condition.inc +--echo # Let 'alter function f1 ...' go through... +commit; +--echo # --> connection con1 +connection con1; +--echo # Reaping 'alter function f1 ...' +--reap +--echo # --> connection con2 +connection con2; +--echo # Reaping 'call p1()'... +--reap +--echo # --> connection default +connection default; +drop function f1; +drop function f2; +drop procedure p1; + +--echo # +--echo # 10) A select from information_schema.routines now +--echo # flushes the stored routines caches. Test that this +--echo # does not remove from the cache a stored routine +--echo # that is already prelocked. +--echo # +create function f1() returns int return get_lock("30977", 100000); +create function f2() returns int return 2; +delimiter |; +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| +delimiter ;| +--echo # --> connection con1 +connection con1; +select get_lock("30977", 0); +--echo # --> connection default +connection default; +--echo # Sending 'select f3()'... +--send select f3() +--echo # --> connection con1 +connection con1; +--echo # Waitng for 'select f3()' to get blocked on the user level lock... +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='User lock' and info='select f1() into @var'; +--source include/wait_condition.inc +--echo # Do something to change the cache version. +create function f4() returns int return 4; +drop function f4; +select release_lock("30977"); +--echo # --> connection default +connection default; +--echo # Reaping 'select f3()'... +--echo # Routine 'f2()' should exist and get executed successfully. +--reap +select @var; +drop function f1; +drop function f2; +drop function f3; + + +--echo # 11) Check the situation when the connection is flushing the +--echo # SP cache which contains a procedure that is being executed. +--echo # +--echo # Function f1() calls p1(). Procedure p1() has a DROP +--echo # VIEW statement, which, we know, invalidates the routines cache. +--echo # During cache flush p1() must not be flushed since it's in +--echo # use. +--echo # +delimiter |; +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| +delimiter ;| +set @exec_count=0; +--error ER_SP_RECURSION_LIMIT +call p1(); +select @exec_count; +set @@session.max_sp_recursion_depth=5; +set @exec_count=0; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +call p1(); +select @exec_count; +drop procedure p1; +drop function f1; +set @@session.max_sp_recursion_depth=default; + +--echo # --> connection con1 +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +--echo # --> connection con2 +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +--echo # --> connection default +connection default; +--echo # +--echo # End of 5.5 tests +--echo # |