1# This test should work in embedded server after mysqltest is fixed
2-- source include/not_embedded.inc
3
4--echo Tests of synchronization of stored procedure execution.
5
6--source include/have_debug_sync.inc
7
8# Save the initial number of concurrent sessions.
9--source include/count_sessions.inc
10
11# Clean up resources used in this test case.
12--disable_warnings
13SET DEBUG_SYNC= 'RESET';
14--enable_warnings
15
16--echo #
17--echo # Bug #30977 Concurrent statement using stored function and
18--echo #            DROP FUNCTION breaks SBR
19--echo #
20--echo # A stored routine could change after dispatch_command()
21--echo # but before a MDL lock is taken. This must be noticed and the
22--echo # sp cache flushed so the correct version can be loaded.
23--echo #
24
25connect (con2, localhost, root);
26
27connection default;
28CREATE FUNCTION f1() RETURNS INT RETURN 1;
29--echo # Get f1 cached
30SELECT f1();
31--echo # Then start executing it again...
32SET DEBUG_SYNC= 'before_execute_sql_command SIGNAL before WAIT_FOR changed';
33--echo # Sending:
34--send SELECT f1()
35
36connection con2;
37SET DEBUG_SYNC= 'now WAIT_FOR before';
38--echo # ... but before f1 is locked, change it.
39DROP FUNCTION f1;
40CREATE FUNCTION f1() RETURNS INT RETURN 2;
41SET DEBUG_SYNC= 'now SIGNAL changed';
42
43--echo # We should now get '2' and not '1'.
44connection default;
45--echo # Reaping: SELECT f1()
46--reap
47
48disconnect con2;
49DROP FUNCTION f1;
50SET DEBUG_SYNC= 'RESET';
51
52--echo #
53--echo # Field translation items must be cleared in case of back-offs
54--echo # for queries that use Information Schema tables. Otherwise
55--echo # memory allocated in fix_fields() for views may end up referring
56--echo # to freed memory.
57--echo #
58
59--disable_warnings
60DROP FUNCTION IF EXISTS f1;
61--enable_warnings
62
63connect (con2, localhost, root);
64connect (con3, localhost, root);
65
66connection default;
67CREATE FUNCTION f1() RETURNS INT RETURN 0;
68
69connection con2;
70SET DEBUG_SYNC= 'after_wait_locked_pname SIGNAL locked WAIT_FOR issued';
71--echo # con2 will now have an x-lock on f1
72--echo # Sending:
73--send ALTER FUNCTION f1 COMMENT 'comment'
74
75connection default;
76SET DEBUG_SYNC= 'now WAIT_FOR locked';
77--disable_result_log
78--echo # This query will block due to the x-lock on f1 and back-off
79--send SHOW OPEN TABLES WHERE f1()=0
80
81connection con3;
82let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
83  WHERE state= 'Waiting for stored function metadata lock'
84  AND info='SHOW OPEN TABLES WHERE f1()=0';
85--source include/wait_condition.inc
86--echo # Check that the IS query is blocked before releasing the x-lock
87SET DEBUG_SYNC= 'now SIGNAL issued';
88
89connection default;
90--echo # Reaping: ALTER FUNCTION f1 COMMENT 'comment'
91--reap
92--enable_result_log
93DROP FUNCTION f1;
94SET DEBUG_SYNC= 'RESET';
95disconnect con2;
96disconnect con3;
97
98
99--echo #
100--echo # Bug #48246 assert in close_thread_table
101--echo #
102
103CREATE TABLE t0 (b INTEGER);
104CREATE TABLE t1 (a INTEGER);
105CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1;
106CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1, t0;
107
108INSERT INTO t0 VALUES(1);
109INSERT INTO t1 VALUES(1), (2);
110
111connect (con2, localhost, root);
112CALL p1();
113
114SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked_t1 WAIT_FOR go_for_t0';
115--echo # This call used to cause an assertion. MDL deadlock with upcoming
116--echo # LOCK TABLES statement will cause back-off and retry.
117--echo # A variable indicating if a prelocking list exists, used to be not
118--echo # reset properly causing an eventual assert.
119--echo # Sending:
120--send CALL p1()
121
122connection default;
123SET DEBUG_SYNC= 'now WAIT_FOR locked_t1';
124--echo # Issue LOCK TABLES statement which will enter in MDL deadlock
125--echo # with CALL statement and as result will cause it to perform
126--echo # back-off and retry.
127SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL go_for_t0';
128LOCK TABLES t0 WRITE, t1 WRITE;
129UNLOCK TABLES;
130
131connection con2;
132--echo # Reaping: CALL p1()
133--reap;
134
135connection default;
136disconnect con2;
137DROP PROCEDURE p1;
138DROP FUNCTION f1;
139DROP TABLES t0, t1;
140
141
142--echo #
143--echo # test for bug#11756013
144--echo #
145--disable_warnings
146DROP SCHEMA IF EXISTS s1;
147--enable_warnings
148CREATE SCHEMA s1;
149CREATE PROCEDURE s1.p1() BEGIN END;
150
151connect (con3, localhost, root);
152SET DEBUG_SYNC='before_db_dir_check SIGNAL check_db WAIT_FOR dropped_schema';
153--send CALL s1.p1
154
155connection default;
156SET DEBUG_SYNC='now WAIT_FOR check_db';
157DROP SCHEMA s1;
158SET DEBUG_SYNC='now SIGNAL dropped_schema';
159
160connection con3;
161--error ER_BAD_DB_ERROR
162--reap
163connection default;
164disconnect con3;
165
166SET DEBUG_SYNC = 'RESET';
167
168# Check that all connections opened by test cases in this file are really
169# gone so execution of other tests won't be affected by their presence.
170--source include/wait_until_count_sessions.inc
171