1 files changed, 119 insertions, 0 deletions
diff --git a/mysql-test/main/sp_sync.result b/mysql-test/main/sp_sync.result
new file mode 100644
index 00000000000..d6c170712a7
--- /dev/null
+++ b/mysql-test/main/sp_sync.result
@@ -0,0 +1,119 @@
+Tests of synchronization of stored procedure execution.
+SET DEBUG_SYNC= 'RESET';
+#
+# Bug #30977 Concurrent statement using stored function and
+#            DROP FUNCTION breaks SBR
+#
+# A stored routine could change after dispatch_command()
+# but before a MDL lock is taken. This must be noticed and the
+# sp cache flushed so the correct version can be loaded.
+#
+connect  con2, localhost, root;
+connection default;
+CREATE FUNCTION f1() RETURNS INT RETURN 1;
+# Get f1 cached
+SELECT f1();
+f1()
+1
+# Then start executing it again...
+SET DEBUG_SYNC= 'before_execute_sql_command SIGNAL before WAIT_FOR changed';
+# Sending:
+SELECT f1();
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR before';
+# ... but before f1 is locked, change it.
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS INT RETURN 2;
+SET DEBUG_SYNC= 'now SIGNAL changed';
+# We should now get '2' and not '1'.
+connection default;
+# Reaping: SELECT f1()
+f1()
+2
+disconnect con2;
+DROP FUNCTION f1;
+SET DEBUG_SYNC= 'RESET';
+#
+# Field translation items must be cleared in case of back-offs
+# for queries that use Information Schema tables. Otherwise 
+# memory allocated in fix_fields() for views may end up referring
+# to freed memory.
+#
+DROP FUNCTION IF EXISTS f1;
+connect  con2, localhost, root;
+connect  con3, localhost, root;
+connection default;
+CREATE FUNCTION f1() RETURNS INT RETURN 0;
+connection con2;
+SET DEBUG_SYNC= 'after_wait_locked_pname SIGNAL locked WAIT_FOR issued';
+# con2 will now have an x-lock on f1
+# Sending:
+ALTER FUNCTION f1 COMMENT 'comment';
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+# This query will block due to the x-lock on f1 and back-off
+SHOW OPEN TABLES WHERE f1()=0;
+connection con3;
+# Check that the IS query is blocked before releasing the x-lock
+SET DEBUG_SYNC= 'now SIGNAL issued';
+connection default;
+# Reaping: ALTER FUNCTION f1 COMMENT 'comment'
+DROP FUNCTION f1;
+SET DEBUG_SYNC= 'RESET';
+disconnect con2;
+disconnect con3;
+#
+# Bug #48246 assert in close_thread_table
+#
+CREATE TABLE t0 (b INTEGER);
+CREATE TABLE t1 (a INTEGER);
+CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1;
+CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1, t0;
+INSERT INTO t0 VALUES(1);
+INSERT INTO t1 VALUES(1), (2);
+connect  con2, localhost, root;
+CALL p1();
+COUNT(f1(a))
+2
+SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked_t1 WAIT_FOR go_for_t0';
+# This call used to cause an assertion. MDL deadlock with upcoming
+# LOCK TABLES statement will cause back-off and retry.
+# A variable indicating if a prelocking list exists, used to be not
+# reset properly causing an eventual assert.
+# Sending:
+CALL p1();
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR locked_t1';
+# Issue LOCK TABLES statement which will enter in MDL deadlock
+# with CALL statement and as result will cause it to perform
+# back-off and retry.
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL go_for_t0';
+LOCK TABLES t0 WRITE, t1 WRITE;
+UNLOCK TABLES;
+connection con2;
+# Reaping: CALL p1()
+COUNT(f1(a))
+2
+connection default;
+disconnect con2;
+DROP PROCEDURE p1;
+DROP FUNCTION f1;
+DROP TABLES t0, t1;
+#
+# test for bug#11756013
+#
+DROP SCHEMA IF EXISTS s1;
+CREATE SCHEMA s1;
+CREATE PROCEDURE s1.p1() BEGIN END;
+connect  con3, localhost, root;
+SET DEBUG_SYNC='before_db_dir_check SIGNAL check_db WAIT_FOR dropped_schema';
+CALL s1.p1;
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR check_db';
+DROP SCHEMA s1;
+SET DEBUG_SYNC='now SIGNAL dropped_schema';
+connection con3;
+ERROR 42000: Unknown database 's1'
+connection default;
+disconnect con3;
+SET DEBUG_SYNC = 'RESET';