Skip to content

Commit f3985c6

Browse files
author
Sven Sandberg
committed
BUG#39934: Slave stops for engine that only support row-based logging
General overview: The logic for switching to row format when binlog_format=MIXED had numerous flaws. The underlying problem was the lack of a consistent architecture. General purpose of this changeset: This changeset introduces an architecture for switching to row format when binlog_format=MIXED. It enforces the architecture where it has to. It leaves some bugs to be fixed later. It adds extensive tests to verify that unsafe statements work as expected and that appropriate errors are produced by problems with the selection of binlog format. It was not practical to split this into smaller pieces of work. Problem 1: To determine the logging mode, the code has to take several parameters into account (namely: (1) the value of binlog_format; (2) the capabilities of the engines; (3) the type of the current statement: normal, unsafe, or row injection). These parameters may conflict in several ways, namely: - binlog_format=STATEMENT for a row injection - binlog_format=STATEMENT for an unsafe statement - binlog_format=STATEMENT for an engine only supporting row logging - binlog_format=ROW for an engine only supporting statement logging - statement is unsafe and engine does not support row logging - row injection in a table that does not support statement logging - statement modifies one table that does not support row logging and one that does not support statement logging Several of these conflicts were not detected, or were detected with an inappropriate error message. The problem of BUG#39934 was that no appropriate error message was written for the case when an engine only supporting row logging executed a row injection with binlog_format=ROW. However, all above cases must be handled. Fix 1: Introduce new error codes (sql/share/errmsg.txt). Ensure that all conditions are detected and handled in decide_logging_format() Problem 2: The binlog format shall be determined once per statement, in decide_logging_format(). It shall not be changed before or after that. Before decide_logging_format() is called, all information necessary to determine the logging format must be available. This principle ensures that all unsafe statements are handled in a consistent way. However, this principle is not followed: thd->set_current_stmt_binlog_row_based_if_mixed() is called in several places, including from code executing UPDATE..LIMIT, INSERT..SELECT..LIMIT, DELETE..LIMIT, INSERT DELAYED, and SET @@binlog_format. After Problem 1 was fixed, that caused inconsistencies where these unsafe statements would not print the appropriate warnings or errors for some of the conflicts. Fix 2: Remove calls to THD::set_current_stmt_binlog_row_based_if_mixed() from code executed after decide_logging_format(). Compensate by calling the set_current_stmt_unsafe() at parse time. This way, all unsafe statements are detected by decide_logging_format(). Problem 3: INSERT DELAYED is not unsafe: it is logged in statement format even if binlog_format=MIXED, and no warning is printed even if binlog_format=STATEMENT. This is BUG#45825. Fix 3: Made INSERT DELAYED set itself to unsafe at parse time. This allows decide_logging_format() to detect that a warning should be printed or the binlog_format changed. Problem 4: LIMIT clause were not marked as unsafe when executed inside stored functions/triggers/views/prepared statements. This is BUG#45785. Fix 4: Make statements containing the LIMIT clause marked as unsafe at parse time, instead of at execution time. This allows propagating unsafe-ness to the view.
1 parent 81b5a39 commit f3985c6

110 files changed

Lines changed: 4990 additions & 6385 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎mysql-test/extra/rpl_tests/create_recursive_construct.inc‎

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

‎mysql-test/extra/rpl_tests/rpl_foreign_key.test‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,6 @@ insert into t1 set b=1;
4747
insert into t2 set a=1, b=1;
4848

4949
set foreign_key_checks=0;
50-
set @@session.binlog_format=row;
5150
delete from t1;
5251

5352
--echo must sync w/o a problem (could not with the buggy code)

0 commit comments

Comments
 (0)