Debian Codegraph @

mysql-test/suite/rpl/include/create_recursive_construct.inc

raw
1 # ==== Purpose ====
2 #
3 # Creates a stored routine, stored function, trigger, view, or
4 # prepared statement (commonly referred to as "recursive construct")
5 # that invokes a given unsafe statement.
6 #
7 # Then, it invokes the created recursive construct several times:
8 #
9 # - With SQL_LOG_BIN = 1 and binlog_format = STATEMENT, to verify
10 # that it gives a warning.
11 #
12 # - With SQL_LOG_BIN = 0 and binlog_format = STATEMENT, to verify that
13 # there is no warning and nothing is logged.
14 #
15 # - With SQL_LOG_BIN = 1 and binlog_format = MIXED, to verify that it
16 # writes row events to the binlog.
17 #
18 # - In some cases, the recursive construct can be invoked so that it
19 # has no side-effects but returns a value that may be
20 # nondeterministic. An example is a function that returns UUID().
21 # The function does not have side effects but its a return value
22 # that may differ on slave. Such statements are invoked so that
23 # the return value is discarded (e.g., SELECT func()), with
24 # SQL_LOG_BIN = 1 and binlog_format = STATEMENT. In this case, no
25 # warning should be given and nothing should be written to the
26 # binlog.
27 #
28 # This is an auxiliary file particularly targeted to being used by the
29 # test binlog_unsafe. In this context, the purpose is to check how
30 # warnings for unsafe statements are propagated in recursive
31 # constructs.
32 #
33 # The statement to invoke ("input") is described using mtr variables,
34 # and the resulting recursive construct ("output") is stored in mtr
35 # variables in a similar fashion. To create several levels of nested
36 # recursive constructs, source this file once, then copy the values of
37 # appropriate output variables to the input variables, and then source
38 # this file again.
39 #
40 #
41 # ==== Usage ====
42 #
43 # See binlog_unsafe for an example of how to use this file.
44 #
45 # let $CRC_ARG_level= <level>;
46 # let $CRC_ARG_type= <type>;
47 # let $CRC_ARG_stmt_sidef= <stmt>;
48 # let $CRC_ARG_value= <stmt>;
49 # let $CRC_ARG_sel_retval= <stmt>;
50 # let $CRC_ARG_sel_sidef= <stmt>;
51 # let $CRC_ARG_desc= <desc>;
52 # source suite/rpl/include/create_recursive_construct.inc;
53 # let $my_stmt_sidef= $CRC_RET_stmt_sidef;
54 # let $my_value= $CRC_RET_value;
55 # let $my_sel_sidef= $CRC_RET_sel_sidef;
56 # let $my_sel_retval= $CRC_RET_sel_retval;
57 # let $my_drop= $CRC_RET_drop;
58 # let $my_is_toplevel= $CRC_RET_top_is_toplevel;
59 # let $my_desc= $CRC_RET_desc;
60 #
61 # $CRC_ARG_* are used as input parameters (arguments) to this file:
62 #
63 # $CRC_ARG_level is the recursion depth: 1 for the innermost
64 # statement created, 2 for a statement that invokes a statement on
65 # level 1, etc.
66 #
67 # $CRC_ARG_type is an integer from 0 to 6, indicating what type of
68 # statement shall be created:
69 # 0 - Create a stored function where the return value depends on
70 # the value of the given statement.
71 # 1 - Create a stored function that invokes the given statement as
72 # a side-effect but may not return a value that depends on it.
73 # 2 - Create a stored routine that invokes the given statement.
74 # 3 - Create a trigger (on table trigger_table_$CRC_ARG_level) that
75 # invokes the given statement.
76 # 4 - Create a view that returns a value that depends on the value
77 # of the given statement.
78 # 5 - Create a view that invokes the given statement but may return
79 # a value that does not depend on it.
80 # 6 - Create a prepared statement that invokes the given statement.
81 #
82 # $CRC_ARG_stmt_sidef is the statement to invoke. It should be a
83 # statement that can be invoked on its own (not sub-statement),
84 # which causes something unsafe to be written to the binlog.
85 #
86 # $CRC_ARG_value is a sub-statement holding the value of the given
87 # statement. Can be empty if the given statement does not have a
88 # value. Typically, this is non-empty if the given statement is a
89 # function call or user variable, but not if it is a stored routine
90 # call, INSERT, SELECT, etc (because none of them has a value).
91 # $CRC_ARG_value is used only when $CRC_ARG_type=6.
92 #
93 # $CRC_ARG_sel_sidef is a SELECT sub-statement that invokes the
94 # statement as a side-effect, but returns a result set that may not
95 # depend on the statement. Can be empty if the statement cannot
96 # produce a result set from a SELECT. $CRC_ARG_sel_sidef is used
97 # only if $CRC_ARG_type=2
98 #
99 # $CRC_ARG_sel_retval is a SELECT sub-statement that does not have
100 # side-effects, but returns a result set that depends on the unsafe
101 # statement. Can be empty if the statement cannot be invoked from a
102 # SELECT. $CRC_ARG_sel_retval is used only if $CRC_ARG_type=3.
103 #
104 # $CRC_ARG_desc is a human-readable description of the statement to
105 # invoke.
106 #
107 # $CRC_RET_* are used as output parameters (return values) of this
108 # file:
109 #
110 # $CRC_RET_stmt_sidef is a statement invoking the resulting recursive
111 # construct.
112 #
113 # $CRC_RET_value is a sub-statement invoking the resulting recursive
114 # construct and returning the value of the recursive construct.
115 # This is the empty string if the resulting recursive construct does
116 # not have a value. In particular, this is non-empty only if
117 # $CRC_ARG_value=7.
118 #
119 # $CRC_RET_sel_sidef is a SELECT sub-statement that invokes the
120 # resulting recursive construct as a side-effect but where the
121 # result set may not depend on the recursive construct. This is the
122 # empty string if the recursive construct cannot be invoked from a
123 # SELECT. In particular, this is non-empty only if $CRC_ARG_value=6
124 # or $CRC_ARG_value=2.
125 #
126 # $CRC_RET_sel_retval is a SELECT sub-statement that does not have
127 # side-effects, but returns a result set depending on the unsafe
128 # statement. This is the empty string if the recursive construct
129 # cannot produce a result set from a SELECT. In particular, this is
130 # non-empty only if $CRC_ARG_value=7 or $CRC_ARG_value=3.
131 #
132 # $CRC_RET_drop is a statement that drops the created object. I.e.,
133 # it is one of 'DROP FUNCTION <func>', 'DROP PROCEDURE <proc>', etc.
134 #
135 # $CRC_RET_top_is_toplevel is 0 normally, or 1 if the resulting
136 # recursive construct can only be called from a top-level statement.
137 # In particular, this is 1 only when $CRC_ARG_value=1, because
138 # prepared statements cannot be invoked from other recursive
139 # constructs.
140 #
141 # $CRC_RET_desc is a text string that describes the invokation of
142 # the recursive construct in a human-readable fashion.
143 #
144 # Assumptions
145 #
146 # Before sourcing this file with $CRC_ARG_level=X, you need to
147 # create three tables: tX, taX and trigger_table_X. These are used
148 # as auxiliary tables.
149
150
151 #--echo debug: >>>>ENTER create_recursive_construct
152 #--echo debug: level=$CRC_ARG_level
153 #--echo debug: type=$CRC_ARG_type
154 #--echo debug: stmt_sidef=$CRC_ARG_stmt_sidef
155 #--echo debug: value=$CRC_ARG_value
156 #--echo debug: sel_retval=$CRC_ARG_sel_retval
157 #--echo debug: sel_sidef=$CRC_ARG_sel_sidef
158
159 --let $CRC_RET_stmt_sidef=
160 --let $CRC_RET_value=
161 --let $CRC_RET_sel_retval=
162 --let $CRC_RET_sel_sidef=
163 --let $CRC_RET_drop=
164 --let $CRC_RET_is_toplevel= 1
165 --let $CRC_RET_desc=
166 --let $CRC_name=
167 --let $CRC_create=
168
169 ######## func_retval ########
170 # if inside if in lieu of AND operand
171 if ($CRC_ARG_type == 0) {
172 if ($CRC_ARG_value) {
173 # It will be safe to call this function and discard the return
174 # value, but it will be unsafe to use return value (e.g., in
175 # INSERT...SELECT).
176 --let $CRC_name= func_retval_$CRC_ARG_level
177 --let $CRC_create= CREATE FUNCTION $CRC_name() RETURNS VARCHAR(100) BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); RETURN $CRC_ARG_value; END
178 --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_level VALUES ($CRC_name())
179 --let $CRC_RET_value= $CRC_name()
180 --let $CRC_RET_sel_sidef=
181 --let $CRC_RET_sel_retval= SELECT $CRC_name()
182 --let $CRC_RET_drop= DROP FUNCTION $CRC_name
183 --let $CRC_RET_is_toplevel= 0
184 --let $CRC_RET_desc= function $CRC_name returning value from $CRC_ARG_desc
185 }
186 }
187
188 ######## func_sidef ########
189 if ($CRC_ARG_type == 1) {
190 # It will be unsafe to call func even if you discard return value.
191 --let $CRC_name= func_sidef_$CRC_ARG_level
192 --let $CRC_create= CREATE FUNCTION $CRC_name() RETURNS VARCHAR(100) BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); $CRC_ARG_stmt_sidef; RETURN 0; END
193 --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_level SELECT $CRC_name()
194 --let $CRC_RET_value=
195 --let $CRC_RET_sel_retval=
196 --let $CRC_RET_sel_sidef= SELECT $CRC_name()
197 --let $CRC_RET_drop= DROP FUNCTION $CRC_name
198 --let $CRC_RET_is_toplevel= 0
199 --let $CRC_RET_desc= function $CRC_name invoking $CRC_ARG_desc
200 }
201
202 ######## proc ########
203 if ($CRC_ARG_type == 2) {
204 # It will be unsafe to call this procedure.
205 --let $CRC_name= proc_$CRC_ARG_level
206 --let $CRC_create= CREATE PROCEDURE $CRC_name() BEGIN $CRC_ARG_stmt_sidef; INSERT INTO ta$CRC_ARG_level VALUES (47); END
207 --let $CRC_RET_stmt_sidef= CALL $CRC_name()
208 --let $CRC_RET_value=
209 --let $CRC_RET_sel_retval=
210 --let $CRC_RET_sel_sidef=
211 --let $CRC_RET_drop= DROP PROCEDURE $CRC_name
212 --let $CRC_RET_is_toplevel= 0
213 --let $CRC_RET_desc= procedure $CRC_name invoking $CRC_ARG_desc
214 }
215
216 ######## trig ########
217 if ($CRC_ARG_type == 3) {
218 # It will be unsafe to invoke this trigger.
219 --let $CRC_name= trig_$CRC_ARG_level
220 --let $CRC_create= CREATE TRIGGER $CRC_name BEFORE INSERT ON trigger_table_$CRC_ARG_level FOR EACH ROW BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); $CRC_ARG_stmt_sidef; END
221 --let $CRC_RET_stmt_sidef= INSERT INTO trigger_table_$CRC_ARG_level VALUES (1)
222 --let $CRC_RET_value=
223 --let $CRC_RET_sel_retval=
224 --let $CRC_RET_sel_sidef=
225 --let $CRC_RET_drop= DROP TRIGGER $CRC_name
226 --let $CRC_RET_is_toplevel= 0
227 --let $CRC_RET_desc= trigger $CRC_name invoking $CRC_ARG_desc
228 }
229
230 ######## view_retval ########
231 if ($CRC_ARG_type == 4) {
232 if ($CRC_ARG_sel_retval) {
233 # It will be safe to select from this view if you discard the result
234 # set, but unsafe to use result set (e.g., in INSERT..SELECT).
235 --let $CRC_name= view_retval_$CRC_ARG_level
236 --let $CRC_create= CREATE VIEW $CRC_name AS $CRC_ARG_sel_retval
237 --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_LEVEL SELECT * FROM $CRC_name
238 --let $CRC_RET_value=
239 --let $CRC_RET_sel_retval= SELECT * FROM $CRC_name
240 --let $CRC_RET_sel_sidef=
241 --let $CRC_RET_drop= DROP VIEW $CRC_name
242 --let $CRC_RET_is_toplevel= 0
243 --let $CRC_RET_desc= view $CRC_name returning value from $CRC_ARG_desc
244 }
245 }
246
247 ######## view_sidef ########
248 if ($CRC_ARG_type == 5) {
249 if ($CRC_ARG_sel_sidef) {
250 # It will be unsafe to select from this view, even if you discard
251 # the return value.
252 --let $CRC_name= view_sidef_$CRC_ARG_level
253 --let $CRC_create= CREATE VIEW $CRC_name AS $CRC_ARG_sel_sidef
254 --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_level SELECT * FROM $CRC_name
255 --let $CRC_RET_value=
256 --let $CRC_RET_sel_retval=
257 --let $CRC_RET_sel_sidef= SELECT * FROM $CRC_name
258 --let $CRC_RET_drop= DROP VIEW $CRC_name
259 --let $CRC_RET_is_toplevel= 0
260 --let $CRC_RET_desc= view $CRC_name invoking $CRC_ARG_desc
261 }
262 }
263
264 ######## prep ########
265 if ($CRC_ARG_type == 6) {
266 # It will be unsafe to execute this prepared statement
267 --let $CRC_name= prep_$CRC_ARG_level
268 --let $CRC_create= PREPARE $CRC_name FROM "$CRC_ARG_stmt_sidef"
269 --let $CRC_RET_stmt_sidef= EXECUTE $CRC_name
270 --let $CRC_RET_value=
271 --let $CRC_RET_sel_retval=
272 --let $CRC_RET_sel_sidef=
273 --let $CRC_RET_drop= DROP PREPARE $CRC_name
274 --let $CRC_RET_is_toplevel= 1
275 --let $CRC_RET_desc= prepared statement $CRC_name invoking $CRC_ARG_desc
276 }
277
278 ######## no recursive construct: just return the given statement ########
279 if ($CRC_ARG_type == 7) {
280 # CRC_ARG_type=7 is a special case. We just set $CRC_RET_x =
281 # $CRC_ARG_x. This way, the $CRC_ARG_stmt gets executed directly
282 # (below). In binlog_unsafe.test, it is used to invoke the unsafe
283 # statement created in the outermost loop directly, without
284 # enclosing it in a recursive construct.
285 --let $CRC_RET_stmt_sidef= $CRC_ARG_stmt_sidef
286 --let $CRC_RET_value= $CRC_ARG_value
287 --let $CRC_RET_sel_retval= $CRC_ARG_sel_retval
288 --let $CRC_RET_sel_sidef= $CRC_ARG_sel_sidef
289 --let $CRC_RET_drop=
290 --let $CRC_RET_is_toplevel= 1
291 --let $CRC_RET_desc= $CRC_ARG_desc
292 }
293
294 ######## execute! ########
295 if ($CRC_RET_stmt_sidef) {
296 --echo
297 --echo Invoking $CRC_RET_desc.
298 if ($CRC_create) {
299 --disable_ps_protocol
300 --eval $CRC_create
301 --enable_ps_protocol
302 }
303
304 --echo * binlog_format = STATEMENT: expect $CRC_ARG_expected_number_of_warnings warnings.
305 --eval $CRC_RET_stmt_sidef
306 --let $n_warnings= `SHOW COUNT(*) WARNINGS`
307 if ($n_warnings != $CRC_ARG_expected_number_of_warnings) {
308 --echo ******** Failure! Expected $CRC_ARG_expected_number_of_warnings warnings, got $n_warnings warnings. ********
309 SHOW WARNINGS;
310 SHOW BINLOG EVENTS;
311 --die Wrong number of warnings.
312 }
313
314 # These queries are run without query log, to make result file more
315 # readable. Debug info is only printed if something abnormal
316 # happens.
317 --disable_query_log
318
319 --echo * SQL_LOG_BIN = 0: expect nothing logged and no warning.
320 SET SQL_LOG_BIN = 0;
321 RESET MASTER;
322 --eval $CRC_RET_stmt_sidef
323 --let $n_warnings= `SHOW COUNT(*) WARNINGS`
324 if ($n_warnings) {
325 --echo ******** Failure! Expected 0 warnings, got $n_warnings warnings. ********
326 SHOW WARNINGS;
327 SHOW BINLOG EVENTS;
328 --die Wrong number of warnings.
329 }
330 # There should be no events after format description, Gtid list, and binlog checkpoint.
331 --let $binlog_event= query_get_value(SHOW BINLOG EVENTS, Event_type, 4)
332 if ($binlog_event != No such row) {
333 --enable_query_log
334 --echo ******** Failure! Something was written to the binlog despite SQL_LOG_BIN=0 ********
335 SHOW BINLOG EVENTS;
336 --die Binlog not empty
337 }
338 SET SQL_LOG_BIN = 1;
339
340 --echo * binlog_format = MIXED: expect row events in binlog and no warning.
341 SET binlog_format = MIXED;
342 RESET MASTER;
343 --eval $CRC_RET_stmt_sidef
344 --let $n_warnings= `SHOW COUNT(*) WARNINGS`
345 if ($n_warnings) {
346 --echo ******** Failure! Expected 0 warnings, got $n_warnings warnings. ********
347 SHOW WARNINGS;
348 SHOW BINLOG EVENTS;
349 --die Warnings printed
350 }
351 --let $event_type= query_get_value(SHOW BINLOG EVENTS, Event_type, 6)
352 # The first event is format_description, the second is Gtid list, the
353 # third is Binlog_checkpoint, the fourth Gtid, and the fifth should be
354 # our Query for 'INSERT DELAYED' unsafe_type 3, which is safe after
355 # the fix of bug#54579.
356 if (`SELECT $unsafe_type = 3 AND '$event_type' != 'Query'`) {
357 --enable_query_log
358 --echo ******** Failure! Event number 5 was a '$event_type', not a 'Query'. ********
359 SHOW BINLOG EVENTS;
360 --die Wrong events in binlog.
361 }
362 # The first event is format_description, the second is Gtid list,
363 # the third is Binlog_checkpoint, the fourth is Query_event('BEGIN'),
364 # the fifth is Annotate_rows, and the sixth should be our Table_map
365 # for unsafe statement.
366 if (`SELECT $unsafe_type != 3 AND '$event_type' != 'Table_map'`) {
367 --enable_query_log
368 --echo ******** Failure! Event number 6 was a '$event_type', not a 'Table_map'. ********
369 SHOW BINLOG EVENTS;
370 --die Wrong events in binlog.
371 }
372 SET binlog_format = STATEMENT;
373
374 --enable_query_log
375 }
376
377 # Invoke created object, discarding the return value. This should not
378 # give any warning.
379 if ($CRC_RET_sel_retval) {
380 --echo * Invoke statement so that return value is dicarded: expect no warning.
381 --disable_result_log
382 --eval $CRC_RET_sel_retval
383 --enable_result_log
384
385 # Currently, due to a bug, we do get warnings here, so we don't
386 # fail. When the bug is fixed, we should execute the following.
387
388 #--let $n_warnings= `SHOW COUNT(*) WARNINGS`
389 #if ($n_warnings) {
390 # --enable_query_log
391 # --echo Failure! Expected 0 warnings, got $n_warnings warnings.
392 # SHOW WARNINGS;
393 # SHOW BINLOG EVENTS;
394 # --die Wrong number of warnings.
395 #}
396 }
397
398 #--echo debug: <<<<EXIT create_recursive_construct
399 #--echo debug: stmt_sidef=$CRC_RET_stmt_sidef
400 #--echo debug: value=$CRC_RET_value
401 #--echo debug: sel_retval=$CRC_RET_sel_retval
402 #--echo debug: sel_sidef=$CRC_RET_sel_sidef
403 #--echo debug: drop=$CRC_RET_drop
404 #--echo debug: is_toplevel=$CRC_RET_is_toplevel
405 #--echo debug: desc=$CRC_RET_desc