sp_sync.test 4.88 KB
Newer Older
Georgi Kodinov's avatar
Georgi Kodinov committed
1 2 3
# This test should work in embedded server after mysqltest is fixed
-- source include/not_embedded.inc

4
--echo Tests of synchronization of stored procedure execution.
5 6 7

--source include/have_debug_sync.inc

8 9 10 11 12 13 14 15 16 17 18
# Save the initial number of concurrent sessions.
--source include/count_sessions.inc

# Clean up resources used in this test case.
--disable_warnings
SET DEBUG_SYNC= 'RESET';
--enable_warnings

--echo #
--echo # Bug #30977 Concurrent statement using stored function and
--echo #            DROP FUNCTION breaks SBR
19
--echo #
20 21 22
--echo # A stored routine could change after dispatch_command()
--echo # but before a MDL lock is taken. This must be noticed and the
--echo # sp cache flushed so the correct version can be loaded.
23 24
--echo #

25
connect (con2, localhost, root);
26

27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
--echo # Connection default
connection default;
CREATE FUNCTION f1() RETURNS INT RETURN 1;
--echo # Get f1 cached
SELECT f1();
--echo # Then start executing it again...
SET DEBUG_SYNC= 'before_execute_sql_command SIGNAL before WAIT_FOR changed';
--echo # Sending:
--send SELECT f1()

--echo # Connection 2
connection con2;
SET DEBUG_SYNC= 'now WAIT_FOR before';
--echo # ... but before f1 is locked, change it.
DROP FUNCTION f1;
CREATE FUNCTION f1() RETURNS INT RETURN 2;
SET DEBUG_SYNC= 'now SIGNAL changed';

--echo # Connection default
--echo # We should now get '2' and not '1'.
connection default;
--echo # Reaping: SELECT f1()
--reap

disconnect con2;
DROP FUNCTION f1;
SET DEBUG_SYNC= 'RESET';
54

55 56 57 58 59 60
--echo #
--echo # Field translation items must be cleared in case of back-offs
--echo # for queries that use Information Schema tables. Otherwise 
--echo # memory allocated in fix_fields() for views may end up referring
--echo # to freed memory.
--echo #
61

62 63 64
--disable_warnings
DROP FUNCTION IF EXISTS f1;
--enable_warnings
65

66 67
connect (con2, localhost, root);
connect (con3, localhost, root);
68

69 70 71
--echo # Connection default
connection default;
CREATE FUNCTION f1() RETURNS INT RETURN 0;
72

73
--echo # Connection con2
74
connection con2;
75 76 77 78
SET DEBUG_SYNC= 'after_wait_locked_pname SIGNAL locked WAIT_FOR issued';
--echo # con2 will now have an x-lock on f1
--echo # Sending:
--send ALTER FUNCTION f1 COMMENT 'comment'
79

80 81 82 83 84 85 86 87 88 89
--echo # Connection default
connection default;
SET DEBUG_SYNC= 'now WAIT_FOR locked';
--disable_result_log
--echo # This query will block due to the x-lock on f1 and back-off
--send SHOW OPEN TABLES WHERE f1()=0

--echo # Connection con3
connection con3;
let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
90
  WHERE state= 'Waiting for stored function metadata lock' 
91
  AND info='SHOW OPEN TABLES WHERE f1()=0';
92
--source include/wait_condition.inc
93 94
--echo # Check that the IS query is blocked before releasing the x-lock
SET DEBUG_SYNC= 'now SIGNAL issued';
95

96 97 98
--echo # Connection default
connection default;
--echo # Reaping: ALTER FUNCTION f1 COMMENT 'comment'
99
--reap
100 101 102
--enable_result_log
DROP FUNCTION f1;
SET DEBUG_SYNC= 'RESET';
103
disconnect con2;
104 105 106 107 108 109 110
disconnect con3;


--echo #
--echo # Bug #48246 assert in close_thread_table
--echo #

111
CREATE TABLE t0 (b INTEGER);
112 113
CREATE TABLE t1 (a INTEGER);
CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1;
114
CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1, t0;
115

116
INSERT INTO t0 VALUES(1);
117 118 119 120 121 122
INSERT INTO t1 VALUES(1), (2);

--echo # Connection 2
connect (con2, localhost, root);
CALL p1();

123 124 125 126 127
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked_t1 WAIT_FOR go_for_t0';
--echo # This call used to cause an assertion. MDL deadlock with upcoming
--echo # LOCK TABLES statement will cause back-off and retry.
--echo # A variable indicating if a prelocking list exists, used to be not
--echo # reset properly causing an eventual assert.
128 129 130 131 132
--echo # Sending:
--send CALL p1()

--echo # Connection default
connection default;
133 134 135 136 137 138 139
SET DEBUG_SYNC= 'now WAIT_FOR locked_t1';
--echo # Issue LOCK TABLES statement which will enter in MDL deadlock
--echo # with CALL statement and as result will cause it to perform
--echo # back-off and retry.
SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL go_for_t0';
LOCK TABLES t0 WRITE, t1 WRITE;
UNLOCK TABLES;
140 141 142 143 144 145 146 147 148

--echo # Connection 2
connection con2;
--echo # Reaping: CALL p1()
--reap;

--echo # Connection default
connection default;
disconnect con2;
149
DROP PROCEDURE p1;
150
DROP FUNCTION f1;
151
DROP TABLES t0, t1;
152 153


154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177
--echo #
--echo # test for bug#11756013
--echo #
--disable_warnings
DROP SCHEMA IF EXISTS s1;
--enable_warnings
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';
--send 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 ER_BAD_DB_ERROR
--reap
connection default;
disconnect con3;

178 179
SET DEBUG_SYNC = 'RESET';

180 181 182
# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
--source include/wait_until_count_sessions.inc