sp_sync.test 4.33 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 5 6 7
--echo Tests of syncronization of stored procedure execution.

--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 90 91
--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
  WHERE state= 'Waiting for table' 
  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 111 112 113 114 115 116 117 118 119 120 121
disconnect con3;


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

CREATE TABLE t1 (a INTEGER);
CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1;
CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1;

INSERT INTO t1 VALUES(1), (2);

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

--echo # Connection default
122
connection default;
123 124 125
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR called';
--echo # Sending:
--send CREATE TABLE t1 (a INTEGER)
126

127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151
--echo # Connection 2
connection con2;
SET DEBUG_SYNC= 'now WAIT_FOR locked';
SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL called WAIT_FOR created';
--echo # This call used to cause an assertion. MDL locking conflict will
--echo # cause back-off and retry. A variable indicating if a prelocking list
--echo # exists, used to be not reset properly causing an eventual assert.
--echo # Sending:
--send CALL p1()

--echo # Connection default
connection default;
--echo # Reaping: CREATE TABLE t1 (a INTEGER)
--error ER_TABLE_EXISTS_ERROR
--reap
SET DEBUG_SYNC= 'now SIGNAL created';

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

--echo # Connection default
connection default;
disconnect con2;
152
DROP PROCEDURE p1;
153 154 155
DROP FUNCTION f1;
DROP TABLE t1;
SET DEBUG_SYNC= 'RESET';
156 157


158 159 160
# 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