1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
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
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
178
179
180
181
182
183
184
185
186
187
188
|
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.
#
# 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 2
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';
# Connection default
# We should now get '2' and not '1'.
# Reaping: SELECT f1()
f1()
2
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;
# 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';
#
# 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);
# Connection 2
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 2
# Reaping: CALL p1()
COUNT(f1(a))
2
# Connection default
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP TABLES t0, t1;
# Case 1: Test case to verify MDL locking from concurrent SELECT and
# DROP FUNCTION operation with case & access insensitive routine
# name.
CREATE FUNCTION mIxEdCaSe() RETURNS INT RETURN 1;
SET DEBUG_SYNC='after_shared_lock_pname SIGNAL locked WAIT_FOR continue';
SELECT mIxEdCaSé();
# At this point we have a shared lock on 'mIxEdCaSé'
CONNECT con1, localhost, root;
SET DEBUG_SYNC='now WAIT_FOR locked';
DROP FUNCTION mixedcase;;
# Without fix the shared lock does not prevent us from dropping the SF
# since the case used in this statement is different. This statement
# takes exclusive metadata lock on 'mixedcase', not 'mIxEdCaSe'.
# Drop function mIxEdCaSe will be blocked properly.
CONNECT con2, localhost, root;
SET DEBUG_SYNC='now SIGNAL continue';
connection default;
mIxEdCaSé()
1
connection con1;
# Case 2: Test case to verify MDL locking from concurrent SHOW FUNCTION
# and DROP FUNCTION operation with case and accent insensitive
# function name.
connection default;
CREATE FUNCTION mIxEdCaSe() RETURNS INT RETURN 1;
SET DEBUG_SYNC='after_acquiring_mdl_lock_on_routine SIGNAL locked WAIT_FOR continue';
DROP FUNCTION mIxEdCaSé;;
# At this point we have a exclusive lock on 'mIxEdCaSé'
connection con1;
SET DEBUG_SYNC='now WAIT_FOR locked';
SHOW CREATE FUNCTION MiXéDcAsE;
# This statement request for shared lock and it is blocked till DROP
# statement releases lock.
connection con2;
SET DEBUG_SYNC='now SIGNAL continue';
connection default;
connection con1;
ERROR 42000: FUNCTION MiXéDcAsE does not exist
# Case 3: Test case to verify MDL locking from concurrent DDL operations
# with case and accent insensitive function name.
connection default;
CREATE FUNCTION mIxEdCaSe() RETURNS INT RETURN 1;
SET DEBUG_SYNC='after_acquiring_mdl_lock_on_routine SIGNAL locked WAIT_FOR continue';
DROP FUNCTION miXEDCAsé;
# At this point we have a exclusive lock on 'miXEDCAsé'
connection con1;
SET DEBUG_SYNC='now WAIT_FOR locked';
ALTER FUNCTION mixedcase COMMENT "comment_string";;
# This statement request for exclusive lock and it is blocked till DROP
# statement releases lock.
connection con2;
SET DEBUG_SYNC='now SIGNAL continue';
connection default;
connection con1;
ERROR 42000: FUNCTION test.mixedcase does not exist
# Case 4: Test case to verify MDL locking from concurrent SHOW PROCEDURE
# and DROP PROCEDURE operation with case and accent insensitive
# procedure name.
connection default;
CREATE PROCEDURE mIxEdCaSe() BEGIN END;
SET DEBUG_SYNC='after_acquiring_mdl_lock_on_routine SIGNAL locked WAIT_FOR continue';
DROP PROCEDURE mIxEdCaSé;;
# At this point we have a exclusive lock on 'mIxEdCaSé'
connection con1;
SET DEBUG_SYNC='now WAIT_FOR locked';
SHOW CREATE PROCEDURE MiXéDcAsE;
# This statement request for shared lock and it is blocked till DROP
# statement releases lock.
connection con2;
SET DEBUG_SYNC='now SIGNAL continue';
connection default;
connection con1;
ERROR 42000: PROCEDURE MiXéDcAsE does not exist
# Case 5: Test case to verify MDL locking from concurrent DDL operations
# with case & accent insensitive procedure name.
connection default;
CREATE PROCEDURE mIxEdCaSe() BEGIN END;
SET DEBUG_SYNC='after_acquiring_mdl_lock_on_routine SIGNAL locked WAIT_FOR continue';
DROP PROCEDURE miXEDCAsé;
# At this point we have a exclusive lock on 'miXEDCAsé'
connection con1;
SET DEBUG_SYNC='now WAIT_FOR locked';
ALTER PROCEDURE mixedcase COMMENT "comment_string";;
# This statement request for exclusive lock and it is blocked till DROP
# statement releases lock.
connection con2;
SET DEBUG_SYNC='now SIGNAL continue';
connection default;
connection con1;
ERROR 42000: PROCEDURE test.mixedcase does not exist
connection default;
SET DEBUG_SYNC='RESET';
disconnect con1;
disconnect con2;
|
