Debian Codegraph @

mysql-test/suite/binlog_nogtid/t/binlog_unsafe_stmt_capable_engine.test

raw
1 # ==== Background ====
2 #
3 # Some statements may execute differently on master and slave when
4 # logged in statement format. Such statements are called unsafe.
5 # Unsafe statements include:
6 #
7 # - statements using @@variables (with a small number of exceptions;
8 # see below);
9 # - statements using certain functions, e.g., UUID();
10 # - statements using LIMIT;
11 # - insert into two autoinc columns;
12 # - statements using UDF's.
13 # - statements reading from log tables in the mysql database.
14 # - INSERT ... SELECT ... ON DUPLICATE KEY UPDATE
15 # - REPLACE ... SELECT
16 # - CREATE TABLE [IGNORE/REPLACE] SELECT
17 # - INSERT IGNORE...SELECT
18 # - UPDATE IGNORE
19 # - INSERT... ON DUPLICATE KEY UPDATE on a table with two UNIQUE KEYS
20 #
21 # Note that statements that use stored functions, stored procedures,
22 # triggers, views, or prepared statements that invoke unsafe
23 # statements shall also be unsafe.
24 #
25 # Unsafeness of a statement shall have the following consequences:
26 #
27 # 1. If the binlogging is on, for any unsafe statement,
28 # - If binlog_format=STATEMENT, the statement shall give a warning.
29 # - If binlog_format=MIXED or binlog_format=ROW, the statement shall
30 # be logged in row format if the engine is capable of logging in row format.
31 #
32 # 2. If the binlogging is on, for any unsafe statement,
33 # - If binlog_format=STATEMENT, the statement shall give a warning.
34 # - If binlog_format=MIXED or binlog_format=ROW, the statement shall
35 # error out if the engine is only statement capable.
36 #
37 # 3. If binlogging is off or the statement is not logged (e.g. SELECT
38 # UUID()), no warning shall be issued and the statement shall not
39 # be logged.
40 #
41 # Moreover, when a sub-statement of a recursive construct (i.e.,
42 # stored function, stored procedure, trigger, view, or prepared
43 # statement) is unsafe and binlog_format=STATEMENT, then a warning
44 # shall be issued for every recursive construct. In effect, this
45 # creates a stack trace from the top-level statement to the unsafe
46 # statement.
47 #
48 # ==== Purpose ====
49 #
50 # Most of this test is copied from the test binlog_unsafe,
51 # but here we test the logging of unsafe statements when
52 # storage engine is only capable of logging in statement format.
53 #
54 # This test verifies that warning/error is generated when it should,
55 # according to the above rules when storage engine is only statement capable.
56 #
57 # All @@variables should be unsafe, with some exceptions. Therefore,
58 # this test also verifies that the exceptions do *not* generate a
59 # warning.
60 #
61 # ==== Method ====
62 #
63 # 1. Each type of statements listed above is executed.
64 #
65 # 2. Each unsafe statement is wrapped in each type of recursive
66 # construct (stored function, stored procedure, trigger, view, or
67 # prepared statement).
68 #
69 # 3. Each unsafe statement is wrapped in two levels of recursive
70 # constructs (function invoking trigger invoking UUID(), etc).
71 #
72 # We try to insert the variables that should not be unsafe into a
73 # table, and verify that *no* warning is issued.
74 #
75 # Execute a unsafe statement calling a trigger or stored function
76 # or neither when SQL_LOG_BIN is turned ON, a warning/error should be issued
77 # Execute a unsafe statement calling a trigger or stored function
78 # or neither when @@SQL_LOG_BIN is turned OFF,
79 # no warning/error is issued
80 #
81 # ==== References ====
82 #
83 # https://dev.mysql.com/doc/refman/5.6/en/binary-log-mixed.html
84 #
85 # ==== Related bugs ====
86 #
87 # Bug#28429993: IF THE STORAGE ENGINE SUPPORTS RBR, UNSAFE SQL STATEMENTES END UP IN BINLOG
88
89 --source include/have_udf.inc
90 --source include/have_debug.inc
91 --source include/have_log_bin.inc
92 --source include/have_binlog_format_statement.inc
93
94 # Thread stack overrun in debug mode on sparc
95 --source include/not_sparc_debug.inc
96
97
98 --disable_query_log
99 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
100 --enable_query_log
101
102 --echo #### Setup tables ####
103
104 # t0.a must be big enough to hold the result from
105 # CONCAT(@@hostname, @@hostname), which can be 2*255 characters wide.
106 CREATE TABLE t0 (a VARCHAR(510));
107 CREATE TABLE t1 (a CHAR(100));
108 CREATE TABLE t2 (a CHAR(100));
109 CREATE TABLE ta0 (a CHAR(100));
110 CREATE TABLE ta1 (a CHAR(100));
111 CREATE TABLE ta2 (a CHAR(100));
112 CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
113 CREATE TABLE data_table (a CHAR(100));
114 INSERT INTO data_table VALUES ('foo');
115 CREATE TABLE trigger_table_1 (a INT);
116 CREATE TABLE trigger_table_2 (a INT);
117 CREATE TABLE trigger_table_3 (a INT);
118 CREATE TABLE double_autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
119
120 # Make the storage engine log only in statement format.
121 SET @old_debug= @@global.debug;
122 SET GLOBAL debug='+d,make_stmt_only_engines';
123
124 --DELIMITER |
125 CREATE TRIGGER double_autoinc_trig
126 BEFORE INSERT ON double_autoinc_table FOR EACH ROW
127 BEGIN
128 INSERT INTO autoinc_table VALUES (NULL);
129 END|
130
131 CREATE FUNCTION multi_unsafe_func() RETURNS INT
132 BEGIN
133 INSERT INTO t0 VALUES(CONCAT(@@hostname, @@hostname));
134 INSERT INTO t0 VALUES(0);
135 INSERT INTO t0 VALUES(CONCAT(UUID(), @@hostname));
136 RETURN 1;
137 END|
138 --DELIMITER ;
139
140 --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
141 --eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"
142
143 # In each iteration of this loop, we select one method to make the
144 # statement unsafe.
145 --let $unsafe_type= 0
146 while ($unsafe_type < 7) {
147
148 --echo
149
150 if ($unsafe_type == 0) {
151 --echo ==== Testing UUID() unsafeness ====
152 --let $desc_0= unsafe UUID() function
153 --let $stmt_sidef_0= INSERT INTO t0 VALUES (UUID())
154 --let $value_0= UUID()
155 --let $sel_sidef_0=
156 --let $sel_retval_0= SELECT UUID()
157 --let $CRC_ARG_expected_number_of_warnings= 1
158 --let $CRC_ARG_expected_number_of_deprecation_warnings= 0
159 }
160
161 if ($unsafe_type == 1) {
162 --echo ==== Testing @@hostname unsafeness ====
163 --let $desc_0= unsafe @@hostname variable
164 --let $stmt_sidef_0= INSERT INTO t0 VALUES (@@hostname)
165 --let $value_0= @@hostname
166 --let $sel_sidef_0=
167 # $sel_retval is going to be used in views. Views cannot execute
168 # statements that refer to @@variables. Hence, we set $set_retval
169 # to empty instead of SELECT @@hostname.
170 --let $sel_retval_0=
171 --let $CRC_ARG_expected_number_of_warnings= 1
172 --let $CRC_ARG_expected_number_of_deprecation_warnings= 0
173 }
174
175 if ($unsafe_type == 2) {
176 --echo ==== Testing SELECT...LIMIT unsafeness ====
177 --let $desc_0= unsafe SELECT...LIMIT statement
178 --let $stmt_sidef_0= INSERT INTO t0 SELECT * FROM data_table LIMIT 1
179 --let $value_0=
180 --let $sel_sidef_0=
181 --let $sel_retval_0= SELECT * FROM data_table LIMIT 1
182 --let $CRC_ARG_expected_number_of_warnings= 1
183 --let $CRC_ARG_expected_number_of_deprecation_warnings= 0
184 }
185
186 if ($unsafe_type == 3) {
187 --echo ==== Testing unsafeness of insert of two autoinc values ====
188 --let $desc_0= unsafe update of two autoinc columns
189 --let $stmt_sidef_0= INSERT INTO double_autoinc_table VALUES (NULL)
190 --let $value_0=
191 --let $sel_sidef_0=
192 --let $sel_retval_0=
193 --let $CRC_ARG_expected_number_of_warnings= 1
194 --let $CRC_ARG_expected_number_of_deprecation_warnings= 0
195 }
196
197 if ($unsafe_type == 4) {
198 --echo ==== Testing unsafeness of UDF's ====
199 --let $desc_0= unsafe UDF
200 --let $stmt_sidef_0= INSERT INTO t0 VALUES (myfunc_int(10))
201 --let $value_0= myfunc_int(10)
202 --let $sel_sidef_0= SELECT myfunc_int(10)
203 --let $sel_retval_0=
204 --let $CRC_ARG_expected_number_of_warnings= 1
205 --let $CRC_ARG_expected_number_of_deprecation_warnings= 0
206 }
207
208 if ($unsafe_type == 5) {
209 --echo ==== Testing unsafeness of access to mysql.general_log ====
210 --let $desc_0= unsafe use of mysql.general_log
211 --let $stmt_sidef_0= INSERT INTO t0 SELECT COUNT(*) FROM mysql.general_log
212 --let $value_0=
213 --let $sel_sidef_0=
214 --let $sel_retval_0= SELECT COUNT(*) FROM mysql.general_log
215 --let $CRC_ARG_expected_number_of_warnings= 1
216 --let $CRC_ARG_expected_number_of_deprecation_warnings= 0
217 }
218
219 if ($unsafe_type == 6) {
220 --echo ==== Testing a statement that is unsafe several times ====
221 --let $desc_0= statement that is unsafe several times
222 --let $stmt_sidef_0= INSERT INTO ta0 VALUES (multi_unsafe_func())
223 --let $value_0=
224 --let $sel_sidef_0= SELECT multi_unsafe_func()
225 --let $sel_retval_0=
226 --let $CRC_ARG_expected_number_of_warnings= 2
227 --let $CRC_ARG_expected_number_of_deprecation_warnings= 0
228 }
229
230 # In each iteration of the following loop, we select one way to
231 # enclose the unsafe statement as a sub-statement of a recursive
232 # construct (i.e., a function, procedure, trigger, view, or prepared
233 # statement).
234 #
235 # In the last iteration, $call_type_1=7, we don't create a recursive
236 # construct. Instead, we just invoke the unsafe statement directly.
237
238 --let $call_type_1= 0
239 while ($call_type_1 < 8) {
240 #--echo debug: level 1, types $call_type_1 -> $unsafe_type
241 --let $CRC_ARG_level= 1
242 --let $CRC_ARG_type= $call_type_1
243 --let $CRC_ARG_stmt_sidef= $stmt_sidef_0
244 --let $CRC_ARG_value= $value_0
245 --let $CRC_ARG_sel_sidef= $sel_sidef_0
246 --let $CRC_ARG_sel_retval= $sel_retval_0
247 --let $CRC_ARG_desc= $desc_0
248 --source extra/rpl_tests/create_recursive_construct_stmt_capable_engine.inc
249 --let $stmt_sidef_1= $CRC_RET_stmt_sidef
250 --let $value_1= $CRC_RET_value
251 --let $sel_sidef_1= $CRC_RET_sel_sidef
252 --let $sel_retval_1= $CRC_RET_sel_retval
253 --let $is_toplevel_1= $CRC_RET_is_toplevel
254 --let $drop_1= $CRC_RET_drop
255 --let $desc_1= $CRC_RET_desc
256
257 # Some statements must be top-level statements, i.e., cannot be
258 # called as a sub-statement of any recursive construct. (One
259 # example is 'EXECUTE prepared_stmt'). When
260 # create_recursive_construct_stmt_capable_engine.inc creates a top-level statement, it
261 # sets $CRC_RET_is_toplevel=1.
262
263 if (!$is_toplevel_1) {
264
265 # In each iteration of this loop, we select one way to enclose
266 # the previous recursive construct in another recursive
267 # construct.
268
269 --let $call_type_2= 0
270 while ($call_type_2 < 7) {
271 #--echo debug: level 2, types $call_type_2 -> $call_type_1 -> $unsafe_type
272 --let $CRC_ARG_level= 2
273 --let $CRC_ARG_type= $call_type_2
274 --let $CRC_ARG_stmt_sidef= $stmt_sidef_1
275 --let $CRC_ARG_value= $value_1
276 --let $CRC_ARG_sel_sidef= $sel_sidef_1
277 --let $CRC_ARG_sel_retval= $sel_retval_1
278 --let $CRC_ARG_desc= $desc_1
279 --source extra/rpl_tests/create_recursive_construct_stmt_capable_engine.inc
280 --let $stmt_sidef_2= $CRC_RET_stmt_sidef
281 --let $value_2= $CRC_RET_value
282 --let $sel_sidef_2= $CRC_RET_sel_sidef
283 --let $sel_retval_2= $CRC_RET_sel_retval
284 --let $is_toplevel_2= $CRC_RET_is_toplevel
285 --let $drop_2= $CRC_RET_drop
286 --let $desc_2= $CRC_RET_desc
287
288 # Drop created object.
289 if ($drop_2) {
290 --eval $drop_2
291 }
292 --inc $call_type_2
293 } # while (call_type_2)
294 } # if (!is_toplevel_1)
295
296 # Drop created object.
297 if ($drop_1) {
298 --eval $drop_1
299 }
300 --inc $call_type_1
301 } # while (call_type_1)
302
303 --inc $unsafe_type
304 } # while (unsafe_type)
305
306 DROP TRIGGER double_autoinc_trig;
307 DROP TABLE t0, t1, t2, ta0, ta1, ta2,
308 autoinc_table, double_autoinc_table,
309 data_table,
310 trigger_table_1, trigger_table_2, trigger_table_3;
311 DROP FUNCTION myfunc_int;
312 DROP FUNCTION multi_unsafe_func;
313
314
315 --echo ==== Special system variables that should *not* be unsafe ====
316
317 CREATE TABLE t1 (a VARCHAR(1000));
318 CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
319
320 INSERT INTO t1 VALUES (@@session.auto_increment_increment);
321 INSERT INTO t1 VALUES (@@session.auto_increment_offset);
322 INSERT INTO t1 VALUES (@@session.character_set_client);
323 INSERT INTO t1 VALUES (@@session.character_set_connection);
324 INSERT INTO t1 VALUES (@@session.character_set_database);
325 INSERT INTO t1 VALUES (@@session.character_set_server);
326 INSERT INTO t1 VALUES (@@session.collation_connection);
327 INSERT INTO t1 VALUES (@@session.collation_database);
328 INSERT INTO t1 VALUES (@@session.collation_server);
329 INSERT INTO t1 VALUES (@@session.foreign_key_checks);
330 INSERT INTO t1 VALUES (@@session.identity);
331 INSERT INTO t1 VALUES (@@session.last_insert_id);
332 INSERT INTO t1 VALUES (@@session.lc_time_names);
333 INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
334 INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
335 INSERT INTO t1 VALUES (@@session.timestamp);
336 INSERT INTO t1 VALUES (@@session.time_zone);
337 INSERT INTO t1 VALUES (@@session.unique_checks);
338
339 SET @my_var= 4711;
340 INSERT INTO t1 VALUES (@my_var);
341
342 # using insert_id implicitly should be ok.
343 SET insert_id= 12;
344 INSERT INTO autoinc_table VALUES (NULL);
345
346 # See set_var.cc for explanation.
347 --echo The following variables *should* give a warning, despite they are replicated.
348 INSERT INTO t1 VALUES (@@session.sql_mode);
349 INSERT INTO t1 VALUES (@@session.insert_id);
350
351
352 DROP TABLE t1, autoinc_table;
353
354 # Clean up
355 SET GLOBAL debug= @old_debug;
356 --echo "End of tests"