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

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

1 files changed, 905 insertions, 0 deletions

diff --git a/mysql-test/main/lock_sync.result b/mysql-test/main/lock_sync.result
new file mode 100644
index 00000000..2d7a175e
--- /dev/null
+++ b/mysql-test/main/lock_sync.result
@@ -0,0 +1,905 @@
+#
+# Test how we handle locking in various cases when
+# we read data from MyISAM tables.
+#
+# In this test we mostly check that the SQL-layer correctly
+# determines the type of thr_lock.c lock for a table being
+# read.
+# I.e. that it disallows concurrent inserts when the statement
+# is going to be written to the binary log and therefore
+# should be serialized, and allows concurrent inserts when
+# such serialization is not necessary (e.g. when 
+# the statement is not written to binary log).
+#
+# Force concurrent inserts to be performed even if the table
+# has gaps. This allows to simplify clean up in scripts
+# used below (instead of backing up table being inserted
+# into and then restoring it from backup at the end of the
+# script we can simply delete rows which were inserted).
+set @old_concurrent_insert= @@global.concurrent_insert;
+set @@global.concurrent_insert= 2;
+select @@global.concurrent_insert;
+@@global.concurrent_insert
+ALWAYS
+# Prepare playground by creating tables, views,
+# routines and triggers used in tests.
+connect  con1, localhost, root,,;
+connect  con2, localhost, root,,;
+connection default;
+drop table if exists t0, t1, t2, t3, t4, t5;
+drop view if exists v1, v2;
+drop procedure if exists p1;
+drop procedure if exists p2;
+drop procedure if exists p3;
+drop function if exists f1;
+drop function if exists f2;
+drop function if exists f3;
+drop function if exists f4;
+drop function if exists f5;
+drop function if exists f6;
+drop function if exists f7;
+drop function if exists f8;
+drop function if exists f9;
+drop function if exists f10;
+drop function if exists f11;
+drop function if exists f12;
+drop function if exists f13;
+drop function if exists f14;
+drop function if exists f15;
+drop function if exists f16;
+drop function if exists f17;
+create table t1 (i int primary key);
+insert into t1 values (1), (2), (3), (4), (5);
+create table t2 (j int primary key);
+insert into t2 values (1), (2), (3), (4), (5);
+create table t3 (k int primary key);
+insert into t3 values (1), (2), (3);
+create table t4 (l int primary key);
+insert into t4 values (1);
+create table t5 (l int primary key);
+insert into t5 values (1);
+create view v1 as select i from t1;
+create view v2 as select j from t2 where j in (select i from t1);
+create procedure p1(k int) insert into t2 values (k);
+create function f1() returns int
+begin
+declare j int;
+select i from t1 where i = 1 into j;
+return j;
+end|
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+create function f2() returns int
+begin
+declare k int;
+select i from t1 where i = 1 into k;
+insert into t2 values (k + 5);
+return 0;
+end|
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+create function f3() returns int
+begin
+return (select i from t1 where i = 3);
+end|
+create function f4() returns int
+begin
+if (select i from t1 where i = 3) then
+return 1;
+else
+return 0;
+end if;
+end|
+create function f5() returns int
+begin
+insert into t2 values ((select i from t1 where i = 1) + 5);
+return 0;
+end|
+create function f6() returns int
+begin
+declare k int;
+select i from v1 where i = 1 into k;
+return k;
+end|
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+create function f7() returns int
+begin
+declare k int;
+select j from v2 where j = 1 into k;
+return k;
+end|
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+create function f8() returns int
+begin
+declare k int;
+select i from v1 where i = 1 into k;
+insert into t2 values (k+5);
+return k;
+end|
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+create function f9() returns int
+begin
+update v2 set j=j+10 where j=1;
+return 1;
+end|
+create function f10() returns int
+begin
+return f1();
+end|
+create function f11() returns int
+begin
+declare k int;
+set k= f1();
+insert into t2 values (k+5);
+return k;
+end|
+create function f12(p int) returns int
+begin
+insert into t2 values (p);
+return p;
+end|
+create function f13(p int) returns int
+begin
+return p;
+end|
+create procedure p2(inout p int)
+begin
+select i from t1 where i = 1 into p;
+end|
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+create function f14() returns int
+begin
+declare k int;
+call p2(k);
+insert into t2 values (k+5);
+return k;
+end|
+create function f15() returns int
+begin
+declare k int;
+call p2(k);
+return k;
+end|
+create function f16() returns int
+begin
+create temporary table if not exists temp1 (a int);
+insert into temp1 select * from t1;
+drop temporary table temp1;
+return 1;
+end|
+create function f17() returns int
+begin
+declare j int;
+select i from t1 where i = 1 into j;
+call p3;
+return 1;
+end|
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+create procedure p3()
+begin
+create temporary table if not exists temp1 (a int);
+insert into temp1 select * from t1;
+drop temporary table temp1;
+end|
+create trigger t4_bi before insert on t4 for each row
+begin
+declare k int;
+select i from t1 where i=1 into k;
+set new.l= k+1;
+end|
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+create trigger t4_bu before update on t4 for each row
+begin
+if (select i from t1 where i=1) then
+set new.l= 2;
+end if;
+end|
+create trigger t4_bd before delete on t4 for each row
+begin
+if !(select i from v1 where i=1) then
+signal sqlstate '45000';
+end if;
+end|
+create trigger t5_bi before insert on t5 for each row
+begin
+set new.l= f1()+1;
+end|
+create trigger t5_bu before update on t5 for each row
+begin
+declare j int;
+call p2(j);
+set new.l= j + 1;
+end|
+#
+# Set common variables to be used by the scripts
+# called below.
+#
+connection con1;
+set @save_dbug=@@debug_dbug;
+set debug_dbug="+d,cache_sp_in_show_create";
+# Cache all functions used in the tests below so statements
+# calling them won't need to open and lock mysql.proc table
+# and we can assume that each statement locks its tables
+# once during its execution.
+show create procedure p1;
+show create procedure p2;
+show create procedure p3;
+show create function f1;
+show create function f2;
+show create function f3;
+show create function f4;
+show create function f5;
+show create function f6;
+show create function f7;
+show create function f8;
+show create function f9;
+show create function f10;
+show create function f11;
+show create function f12;
+show create function f13;
+show create function f14;
+show create function f15;
+show create function f16;
+show create function f17;
+set debug_dbug=@save_dbug;
+connection default;
+#
+# 1. Statements that read tables and do not use subqueries.
+#
+#
+# 1.1 Simple SELECT statement.
+#
+# No locks are necessary as this statement won't be written
+# to the binary log and thanks to how MyISAM works SELECT
+# will see version of the table prior to concurrent insert.
+connection default;
+Success: 'select * from t1' allows concurrent inserts into 't1'.
+#
+# 1.2 Multi-UPDATE statement.
+#
+# Has to take shared locks on rows in the table being read as this
+# statement will be written to the binary log and therefore should
+# be serialized with concurrent statements.
+connection default;
+Success: 'update t2, t1 set j= j - 1 where i = j' doesn't allow concurrent inserts into 't1'.
+#
+# 1.3 Multi-DELETE statement.
+#
+# The above is true for this statement as well.
+connection default;
+Success: 'delete t2 from t1, t2 where i = j' doesn't allow concurrent inserts into 't1'.
+#
+# 1.4 DESCRIBE statement.
+#
+# This statement does not really read data from the
+# target table and thus does not take any lock on it.
+# We check this for completeness of coverage.
+lock table t1 write;
+connection con1;
+# This statement should not be blocked.
+describe t1;
+connection default;
+unlock tables;
+#
+# 1.5 SHOW statements.
+# 
+# The above is true for SHOW statements as well.
+lock table t1 write;
+connection con1;
+# These statements should not be blocked.
+show keys from t1;
+connection default;
+unlock tables;
+#
+# 2. Statements which read tables through subqueries.
+#
+#
+# 2.1 CALL with a subquery.
+# 
+# A strong lock is not necessary as this statement is not
+# written to the binary log as a whole (it is written
+# statement-by-statement).
+connection default;
+Success: 'call p1((select i + 5 from t1 where i = 1))' allows concurrent inserts into 't1'.
+#
+# 2.2 CREATE TABLE with a subquery.
+#
+# Has to take a strong lock on the table being read as
+# this statement is written to the binary log and therefore
+# should be serialized with concurrent statements.
+connection default;
+Success: 'create table t0 select * from t1' doesn't allow concurrent inserts into 't1'.
+drop table t0;
+connection default;
+Success: 'create table t0 select j from t2 where j in (select i from t1)' doesn't allow concurrent inserts into 't1'.
+drop table t0;
+#
+# 2.3 DELETE with a subquery.
+#
+# The above is true for this statement as well.
+connection default;
+Success: 'delete from t2 where j in (select i from t1)' doesn't allow concurrent inserts into 't1'.
+#
+# 2.4 MULTI-DELETE with a subquery.
+#
+# Same is true for this statement as well.
+connection default;
+Success: 'delete t2 from t3, t2 where k = j and j in (select i from t1)' doesn't allow concurrent inserts into 't1'.
+#
+# 2.5 DO with a subquery.
+#
+# A strong lock is not necessary as it is not logged.
+connection default;
+Success: 'do (select i from t1 where i = 1)' allows concurrent inserts into 't1'.
+#
+# 2.6 INSERT with a subquery.
+#
+# Has to take a strong lock on the table being read as
+# this statement is written to the binary log and therefore
+# should be serialized with concurrent inserts.
+connection default;
+Success: 'insert into t2 select i+5 from t1' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'insert into t2 values ((select i+5 from t1 where i = 4))' doesn't allow concurrent inserts into 't1'.
+#
+# 2.7 LOAD DATA with a subquery.
+# 
+# The above is true for this statement as well.
+connection default;
+Success: 'load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1)' doesn't allow concurrent inserts into 't1'.
+#
+# 2.8 REPLACE with a subquery.
+# 
+# Same is true for this statement as well.
+# Suppress warnings for REPLACE ... SELECT
+connection default;
+Success: 'replace into t2 select i+5 from t1' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'replace into t2 values ((select i+5 from t1 where i = 4))' doesn't allow concurrent inserts into 't1'.
+#
+# 2.9 SELECT with a subquery.
+#
+# Strong locks are not necessary as this statement is not written
+# to the binary log and thanks to how MyISAM works this statement
+# sees a version of the table prior to the concurrent insert.
+connection default;
+Success: 'select * from t2 where j in (select i from t1)' allows concurrent inserts into 't1'.
+#
+# 2.10 SET with a subquery.
+#
+# The same is true for this statement as well.
+connection default;
+Success: 'set @a:= (select i from t1 where i = 1)' allows concurrent inserts into 't1'.
+#
+# 2.11 SHOW with a subquery.
+# 
+# And for this statement too.
+connection default;
+Success: 'show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1)' allows concurrent inserts into 't1'.
+connection default;
+Success: 'show columns from t2 where (select i from t1 where i = 1)' allows concurrent inserts into 't1'.
+#
+# 2.12 UPDATE with a subquery.
+#
+# Has to take a strong lock on the table being read as
+# this statement is written to the binary log and therefore
+# should be serialized with concurrent inserts.
+connection default;
+Success: 'update t2 set j= j-10 where j in (select i from t1)' doesn't allow concurrent inserts into 't1'.
+#
+# 2.13 MULTI-UPDATE with a subquery.
+#
+# Same is true for this statement as well.
+connection default;
+Success: 'update t2, t3 set j= j -10 where j=k and j in (select i from t1)' doesn't allow concurrent inserts into 't1'.
+#
+# 3. Statements which read tables through a view.
+#
+#
+# 3.1 SELECT statement which uses some table through a view.
+#
+# Since this statement is not written to the binary log and
+# an old version of the table is accessible thanks to how MyISAM
+# handles concurrent insert, no locking is necessary.
+connection default;
+Success: 'select * from v1' allows concurrent inserts into 't1'.
+connection default;
+Success: 'select * from v2' allows concurrent inserts into 't1'.
+connection default;
+Success: 'select * from t2 where j in (select i from v1)' allows concurrent inserts into 't1'.
+connection default;
+Success: 'select * from t3 where k in (select j from v2)' allows concurrent inserts into 't1'.
+#
+# 3.2 Statements which modify a table and use views.
+#
+# Since such statements are going to be written to the binary
+# log they need to be serialized against concurrent statements
+# and therefore should take strong locks on the data read.
+connection default;
+Success: 'update t2 set j= j-10 where j in (select i from v1)' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'update t3 set k= k-10 where k in (select j from v2)' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'update t2, v1 set j= j-10 where j = i' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'update v2 set j= j-10 where j = 3' doesn't allow concurrent inserts into 't1'.
+#
+# 4. Statements which read tables through stored functions.
+#
+#
+# 4.1 SELECT/SET with a stored function which does not 
+#     modify data and uses SELECT in its turn.
+#
+# There is no need to take strong locks on the table
+# being selected from in SF as the call to such function
+# won't get into the binary log.
+connection default;
+Success: 'select f1()' allows concurrent inserts into 't1'.
+connection default;
+Success: 'set @a:= f1()' allows concurrent inserts into 't1'.
+#
+# 4.2 INSERT (or other statement which modifies data) with
+#     a stored function which does not modify data and uses
+#     SELECT.
+#
+# Since such statement is written to the binary log it should
+# be serialized with concurrent statements affecting the data
+# it uses. Therefore it should take strong lock on the data
+# it reads.
+connection default;
+Success: 'insert into t2 values (f1() + 5)' doesn't allow concurrent inserts into 't1'.
+#
+# 4.3 SELECT/SET with a stored function which
+#     reads and modifies data.
+#
+# Since a call to such function is written to the binary log,
+# it should be serialized with concurrent statements affecting
+# the data it uses. Hence, a strong lock on the data read
+# should be taken.
+connection default;
+Success: 'select f2()' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'set @a:= f2()' doesn't allow concurrent inserts into 't1'.
+#
+# 4.4. SELECT/SET with a stored function which does not
+#      modify data and reads a table through subselect
+#      in a control construct.
+#
+# Call to this function won't get to the
+# binary log and thus no strong lock is needed.
+connection default;
+Success: 'select f3()' allows concurrent inserts into 't1'.
+connection default;
+Success: 'set @a:= f3()' allows concurrent inserts into 't1'.
+connection default;
+Success: 'select f4()' allows concurrent inserts into 't1'.
+connection default;
+Success: 'set @a:= f4()' allows concurrent inserts into 't1'.
+#
+# 4.5. INSERT (or other statement which modifies data) with
+#      a stored function which does not modify data and reads
+#      the table through a subselect in one of its control
+#      constructs.
+#
+# Since such statement is written to the binary log it should
+# be serialized with concurrent statements affecting data it
+# uses. Therefore it should take a strong lock on the data
+# it reads.
+connection default;
+Success: 'insert into t2 values (f3() + 5)' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'insert into t2 values (f4() + 6)' doesn't allow concurrent inserts into 't1'.
+#
+# 4.6 SELECT/SET which uses a stored function with
+#      DML which reads a table via a subquery.
+#
+# Since call to such function is written to the binary log
+# it should be serialized with concurrent statements.
+# Hence reads should take a strong lock.
+connection default;
+Success: 'select f5()' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'set @a:= f5()' doesn't allow concurrent inserts into 't1'.
+#
+# 4.7 SELECT/SET which uses a stored function which
+#     doesn't modify data and reads tables through
+#     a view.
+#
+# Calls to such functions won't get into
+# the binary log and thus don't need strong
+# locks.
+connection default;
+Success: 'select f6()' allows concurrent inserts into 't1'.
+connection default;
+Success: 'set @a:= f6()' allows concurrent inserts into 't1'.
+connection default;
+Success: 'select f7()' allows concurrent inserts into 't1'.
+connection default;
+Success: 'set @a:= f7()' allows concurrent inserts into 't1'.
+#
+# 4.8 INSERT which uses stored function which
+#     doesn't modify data and reads a table
+#     through a view.
+#
+# Since such statement is written to the binary log and
+# should be serialized with concurrent statements affecting
+# the data it uses. Therefore it should take a strong lock on
+# the table it reads.
+connection default;
+Success: 'insert into t3 values (f6() + 5)' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'insert into t3 values (f7() + 5)' doesn't allow concurrent inserts into 't1'.
+#
+# 4.9 SELECT which uses a stored function which
+#     modifies data and reads tables through a view.
+#
+# Since a call to such function is written to the binary log
+# it should be serialized with concurrent statements.
+# Hence, reads should take strong locks.
+connection default;
+Success: 'select f8()' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'select f9()' doesn't allow concurrent inserts into 't1'.
+#
+# 4.10 SELECT which uses a stored function which doesn't modify
+#      data and reads a table indirectly, by calling another
+#      function.
+#
+# Calls to such functions won't get into the binary
+# log and thus don't need to acquire strong locks.
+connection default;
+Success: 'select f10()' allows concurrent inserts into 't1'.
+#
+# 4.11 INSERT which uses a stored function which doesn't modify
+#      data and reads a table indirectly, by calling another
+#      function. 
+#
+# Since such statement is written to the binary log, it should
+# be serialized with concurrent statements affecting the data it
+# uses. Therefore it should take strong locks on data it reads.
+connection default;
+Success: 'insert into t2 values (f10() + 5)' doesn't allow concurrent inserts into 't1'.
+#
+# 4.12 SELECT which uses a stored function which modifies
+#      data and reads a table indirectly, by calling another
+#      function. 
+#
+# Since a call to such function is written to the binary log
+# it should be serialized from concurrent statements.
+# Hence, read should take a strong lock.
+connection default;
+Success: 'select f11()' doesn't allow concurrent inserts into 't1'.
+#
+# 4.13 SELECT that reads a table through a subquery passed
+#      as a parameter to a stored function which modifies
+#      data.
+#
+# Even though a call to this function is written to the
+# binary log, values of its parameters are written as literals.
+# So there is no need to acquire strong locks for tables used in
+# the subquery.
+connection default;
+Success: 'select f12((select i+10 from t1 where i=1))' allows concurrent inserts into 't1'.
+#
+# 4.14 INSERT that reads a table via a subquery passed
+#      as a parameter to a stored function which doesn't
+#      modify data.
+#
+# Since this statement is written to the binary log it should
+# be serialized with concurrent statements affecting the data it
+# uses. Therefore it should take strong locks on the data it reads.
+connection default;
+Success: 'insert into t2 values (f13((select i+10 from t1 where i=1)))' doesn't allow concurrent inserts into 't1'.
+#
+# 4.15 SELECT/SET with a stored function which 
+#      inserts data into a temporary table using
+#      SELECT on t1.
+#
+# Since this statement is written to the binary log it should
+# be serialized with concurrent statements affecting the data it
+# uses. Therefore it should take strong locks on the data it reads.
+connection default;
+Success: 'select f16()' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'set @a:= f16()' doesn't allow concurrent inserts into 't1'.
+#
+# 4.16 SELECT/SET with a stored function which call procedure
+#      which inserts data into a temporary table using
+#      SELECT on t1.
+#
+# Since this statement is written to the binary log it should
+# be serialized with concurrent statements affecting the data it
+# uses. Therefore it should take strong locks on the data it reads.
+connection default;
+Success: 'select f17()' doesn't allow concurrent inserts into 't1'.
+connection default;
+Success: 'set @a:= f17()' doesn't allow concurrent inserts into 't1'.
+#
+# 5. Statements that read tables through stored procedures.
+#
+#
+# 5.1 CALL statement which reads a table via SELECT.
+#
+# Since neither this statement nor its components are
+# written to the binary log, there is no need to take
+# strong locks on the data it reads.
+connection default;
+Success: 'call p2(@a)' allows concurrent inserts into 't1'.
+#
+# 5.2 Function that modifies data and uses CALL, 
+#     which reads a table through SELECT.
+#
+# Since a call to such function is written to the binary
+# log, it should be serialized with concurrent statements.
+# Hence, in this case reads should take strong locks on data.
+connection default;
+Success: 'select f14()' doesn't allow concurrent inserts into 't1'.
+#
+# 5.3 SELECT that calls a function that doesn't modify data and
+#     uses a CALL statement that reads a table via SELECT.
+#
+# Calls to such functions won't get into the binary
+# log and thus don't need to acquire strong locks.
+connection default;
+Success: 'select f15()' allows concurrent inserts into 't1'.
+#
+# 5.4 INSERT which calls function which doesn't modify data and
+#     uses CALL statement which reads table through SELECT.
+#
+# Since such statement is written to the binary log it should
+# be serialized with concurrent statements affecting data it
+# uses. Therefore it should take strong locks on data it reads.
+connection default;
+Success: 'insert into t2 values (f15()+5)' doesn't allow concurrent inserts into 't1'.
+#
+# 6. Statements that use triggers.
+#
+#
+# 6.1 Statement invoking a trigger that reads table via SELECT.
+#
+# Since this statement is written to the binary log it should
+# be serialized with concurrent statements affecting the data
+# it uses. Therefore, it should take strong locks on the data
+# it reads.
+connection default;
+Success: 'insert into t4 values (2)' doesn't allow concurrent inserts into 't1'.
+#
+# 6.2 Statement invoking a trigger that reads table through
+#     a subquery in a control construct.
+#
+# The above is true for this statement as well.
+connection default;
+Success: 'update t4 set l= 2 where l = 1' doesn't allow concurrent inserts into 't1'.
+#
+# 6.3 Statement invoking a trigger that reads a table through
+#     a view.
+#
+# And for this statement.
+connection default;
+Success: 'delete from t4 where l = 1' doesn't allow concurrent inserts into 't1'.
+#
+# 6.4 Statement invoking a trigger that reads a table through
+#     a stored function.
+#
+# And for this statement.
+connection default;
+Success: 'insert into t5 values (2)' doesn't allow concurrent inserts into 't1'.
+#
+# 6.5 Statement invoking a trigger that reads a table through
+#     stored procedure.
+#
+# And for this statement.
+connection default;
+Success: 'update t5 set l= 2 where l = 1' doesn't allow concurrent inserts into 't1'.
+# Clean-up.
+drop function f1;
+drop function f2;
+drop function f3;
+drop function f4;
+drop function f5;
+drop function f6;
+drop function f7;
+drop function f8;
+drop function f9;
+drop function f10;
+drop function f11;
+drop function f12;
+drop function f13;
+drop function f14;
+drop function f15;
+drop function f16;
+drop function f17;
+drop view v1, v2;
+drop procedure p1;
+drop procedure p2;
+drop procedure p3;
+drop table t1, t2, t3, t4, t5;
+disconnect con1;
+disconnect con2;
+set @@global.concurrent_insert= @old_concurrent_insert;
+#
+# Bug#50821 Deadlock between LOCK TABLES and ALTER TABLE
+#
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1(id INT);
+CREATE TABLE t2(id INT);
+connect  con2, localhost, root;
+START TRANSACTION;
+SELECT * FROM t1;
+id
+connection default;
+# Sending:
+ALTER TABLE t1 ADD COLUMN j INT;
+connection con2;
+# This used to cause a deadlock.
+INSERT INTO t2 SELECT * FROM t1;
+COMMIT;
+connection default;
+# Reaping ALTER TABLE t1 ADD COLUMN j INT
+DROP TABLE t1, t2;
+disconnect con2;
+#
+# Bug#51391 Deadlock involving events during rqg_info_schema test
+#
+CREATE EVENT e1 ON SCHEDULE EVERY 5 HOUR DO SELECT 1;
+Warnings:
+Warning	1105	Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
+CREATE EVENT e2 ON SCHEDULE EVERY 5 HOUR DO SELECT 2;
+Warnings:
+Warning	1105	Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
+connect con1, localhost, root;
+SET DEBUG_SYNC="before_lock_tables_takes_lock SIGNAL drop WAIT_FOR query";
+# Sending:
+DROP EVENT e1;;
+connection default;
+SET DEBUG_SYNC="now WAIT_FOR drop";
+SELECT name FROM mysql.event, INFORMATION_SCHEMA.GLOBAL_VARIABLES
+WHERE definer = VARIABLE_VALUE;
+name
+SET DEBUG_SYNC="now SIGNAL query";
+connection con1;
+# Reaping: DROP EVENT t1
+disconnect con1;
+connection default;
+DROP EVENT e2;
+SET DEBUG_SYNC="RESET";
+#
+# Bug#57130 crash in Item_field::print during SHOW CREATE TABLE or VIEW
+#
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+DROP FUNCTION IF EXISTS f1;
+CREATE TABLE t1(a INT);
+CREATE FUNCTION f1() RETURNS INTEGER RETURN 1;
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1() = 1;
+DROP FUNCTION f1;
+connect con2, localhost, root;
+connect  con1, localhost, root;
+SET DEBUG_SYNC= 'open_tables_after_open_and_process_table SIGNAL opened WAIT_FOR dropped EXECUTE 2';
+# Sending:
+SHOW CREATE VIEW v1;
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+SET DEBUG_SYNC= 'now SIGNAL dropped';
+SET DEBUG_SYNC= 'now WAIT_FOR opened';
+# Sending:
+FLUSH TABLES t1;
+connection default;
+# Waiting for FLUSH TABLES to be blocked.
+SET DEBUG_SYNC= 'now SIGNAL dropped';
+connection con1;
+# Reaping: SHOW CREATE VIEW v1
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` where `f1`() = 1	latin1	latin1_swedish_ci
+Warnings:
+Warning	1356	View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+connection con2;
+# Reaping: FLUSH TABLES
+connection default;
+SET DEBUG_SYNC= 'RESET';
+DROP VIEW v1;
+DROP TABLE t1;
+disconnect con1;
+disconnect con2;
+#
+# Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
+#
+set low_priority_updates=1;
+drop table if exists t1;
+drop table if exists t2;
+set debug_sync='RESET';
+create table t1 (a int, b int, unique key t1$a (a));
+create table t2 (j int, k int);
+set debug_sync='after_lock_tables_takes_lock SIGNAL parked WAIT_FOR go';
+# Sending:
+insert into t2 select * from t1;;
+connect  update,localhost,root,,;
+connection update;
+set debug_sync='now WAIT_FOR parked';
+set low_priority_updates=1;
+show variables like 'low_priority_updates';
+Variable_name	Value
+low_priority_updates	ON
+insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;;
+connect  select,localhost,root,,;
+select * from t1;
+a	b
+set debug_sync='now SIGNAL go';
+connection default;
+disconnect update;
+disconnect select;
+# Reaping INSERT SELECT
+drop tables t1, t2;
+set low_priority_updates=default;
+set debug_sync='RESET';
+#
+# Additional test coverage for LOCK TABLES ... READ LOCAL
+# for InnoDB tables.
+#
+# Check that we correctly handle deadlocks which can occur
+# during metadata lock upgrade which happens when one tries
+# to use LOCK TABLES ... READ LOCAL for InnoDB tables.
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+CREATE TABLE t2 (j INT) ENGINE=InnoDB;
+# Execute LOCK TABLE READ LOCK which will pause after acquiring
+# SR metadata lock and before upgrading it to SRO lock.
+SET DEBUG_SYNC="after_open_table_mdl_shared SIGNAL locked WAIT_FOR go";
+# Sending:
+LOCK TABLE t1 READ LOCAL;
+connect  con1, localhost, root;
+SET DEBUG_SYNC="now WAIT_FOR locked";
+# Execute RENAME TABLE which will try to acquire X lock.
+# Sending:
+RENAME TABLE t1 TO t3, t2 TO t1, t3 TO t2;
+connect  con2, localhost, root;
+# Wait until RENAME TABLE is blocked.
+# Resume LOCK TABLE statement. It should try to
+# upgrade SR lock to SRO lock which will create
+# deadlock due to presence of pending X lock.
+# Deadlock should be detected and LOCK TABLES should
+# release its MDL and retry opening of tables.
+SET DEBUG_SYNC="now SIGNAL go";
+connection con1;
+# RENAME TABLE should be able to complete. Reap it.
+connection default;
+# Reap LOCK TABLES.
+# Check that we see new version of table.
+SELECT * FROM t1;
+j
+UNLOCK TABLES;
+# Clean-up.
+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;
+COMMIT;
+DROP VIEW v1;
+DROP FUNCTION f;
+DROP TABLE t1, t2;
+set debug_sync= 'reset';