Debian Codegraph @

mysql-test/suite/binlog/t/binlog_unsafe.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 DELAYED;
12 # - insert into two autoinc columns;
13 # - statements using UDF's.
14 # - statements reading from log tables in the mysql database.
15 # - INSERT ... SELECT ... ON DUPLICATE KEY UPDATE
16 # - REPLACE ... SELECT
17 # - CREATE TABLE [IGNORE/REPLACE] SELECT
18 # - INSERT IGNORE...SELECT
19 # - UPDATE IGNORE
20 # - INSERT... ON DUPLICATE KEY UPDATE on a table with two UNIQUE KEYS
21 #
22 # Note that statements that use stored functions, stored procedures,
23 # triggers, views, or prepared statements that invoke unsafe
24 # statements shall also be unsafe.
25 #
26 # Unsafeness of a statement shall have the following consequences:
27 #
28 # 1. If the binlogging is on and the unsafe statement is logged:
29 # - If binlog_format=STATEMENT, the statement shall give a warning.
30 # - If binlog_format=MIXED or binlog_format=ROW, the statement shall
31 # be logged in row format.
32 #
33 # 2. If binlogging is off or the statement is not logged (e.g. SELECT
34 # UUID()), no warning shall be issued and the statement shall not
35 # be logged.
36 #
37 # Moreover, when a sub-statement of a recursive construct (i.e.,
38 # stored function, stored procedure, trigger, view, or prepared
39 # statement) is unsafe and binlog_format=STATEMENT, then a warning
40 # shall be issued for every recursive construct. In effect, this
41 # creates a stack trace from the top-level statement to the unsafe
42 # statement.
43 #
44 #
45 # ==== Purpose ====
46 #
47 # This test verifies that a warning is generated when it should,
48 # according to the rules above.
49 #
50 # All @@variables should be unsafe, with some exceptions. Therefore,
51 # this test also verifies that the exceptions do *not* generate a
52 # warning.
53 #
54 #
55 # ==== Method ====
56 #
57 # 1. Each type of statements listed above is executed.
58 #
59 # 2. Each unsafe statement is wrapped in each type of recursive
60 # construct (stored function, stored procedure, trigger, view, or
61 # prepared statement).
62 #
63 # 3. Each unsafe statement is wrapped in two levels of recursive
64 # constructs (function invoking trigger invoking UUID(), etc).
65 #
66 # We try to insert the variables that should not be unsafe into a
67 # table, and verify that *no* warning is issued.
68 #
69 # Execute a unsafe statement calling a trigger or stored function
70 # or neither when SQL_LOG_BIN is turned ON, a warning/error should be issued
71 # Execute a unsafe statement calling a trigger or stored function
72 # or neither when @@SQL_LOG_BIN is turned OFF,
73 # no warning/error is issued
74 #
75 #
76 # ==== Related bugs and worklogs ====
77 #
78 # WL#3339: Issue warnings when statement-based replication may fail
79 # BUG#31168: @@hostname does not replicate
80 # BUG#34732: mysqlbinlog does not print default values for auto_increment variables
81 # BUG#34768: nondeterministic INSERT using LIMIT logged in stmt mode if binlog_format=mixed
82 # BUG#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0
83 # BUG#42640: mysqld crashes when unsafe statements are executed (STRICT_TRANS_TABLES mode)
84 # BUG#45825: INSERT DELAYED is not unsafe: logged in statement format
85 # BUG#45785: LIMIT in SP does not cause RBL if binlog_format=MIXED
86 # BUG#47995: Mark user functions as unsafe
87 # BUG#49222: Mark RAND() unsafe
88 # BUG#11758262: MARK INSERT...SEL...ON DUP KEY UPD,REPLACE...SEL,CREATE...[IGN|REPL] SEL
89 #
90 # ==== Related test cases ====
91 #
92 # rpl.rpl_variables verifies that variables which cannot be replicated
93 # safely in statement mode are replicated correctly in mixed or row
94 # mode.
95 #
96 # rpl.rpl_variables_stm tests the small subset of variables that
97 # actually can be replicated safely in statement mode.
98 #
99 --source include/have_udf.inc
100 --source include/have_log_bin.inc
101 --source include/have_binlog_format_statement.inc
102
103 --disable_query_log
104 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
105 --enable_query_log
106
107 --echo #### Setup tables ####
108
109 CREATE TABLE t0 (a CHAR(200));
110 CREATE TABLE t1 (a CHAR(200));
111 CREATE TABLE t2 (a CHAR(200));
112 CREATE TABLE t3 (a CHAR(200));
113 CREATE TABLE ta0 (a CHAR(200));
114 CREATE TABLE ta1 (a CHAR(200));
115 CREATE TABLE ta2 (a CHAR(200));
116 CREATE TABLE ta3 (a CHAR(200));
117 CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
118 CREATE TABLE data_table (a CHAR(200));
119 INSERT INTO data_table VALUES ('foo');
120 CREATE TABLE trigger_table_1 (a INT);
121 CREATE TABLE trigger_table_2 (a INT);
122 CREATE TABLE trigger_table_3 (a INT);
123 CREATE TABLE double_autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
124
125 --DELIMITER |
126 CREATE TRIGGER double_autoinc_trig
127 BEFORE INSERT ON double_autoinc_table FOR EACH ROW
128 BEGIN
129 INSERT INTO autoinc_table VALUES (NULL);
130 END|
131
132 CREATE FUNCTION multi_unsafe_func() RETURNS INT
133 BEGIN
134 INSERT INTO t0 VALUES(CONCAT(@@hostname, @@hostname));
135 INSERT INTO t0 VALUES(0);
136 INSERT INTO t0 VALUES(CONCAT(UUID(), @@hostname));
137 RETURN 1;
138 END|
139 --DELIMITER ;
140
141 --replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
142 --eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO"
143
144 # In each iteration of this loop, we select one method to make the
145 # statement unsafe.
146 --let $unsafe_type= 0
147 while ($unsafe_type < 9) {
148
149 --echo
150
151 if ($unsafe_type == 0) {
152 --echo ==== Testing UUID() unsafeness ====
153 --let $desc_0= unsafe UUID() function
154 --let $stmt_sidef_0= INSERT INTO t0 VALUES (UUID())
155 --let $value_0= UUID()
156 --let $sel_sidef_0=
157 --let $sel_retval_0= SELECT UUID()
158 --let $CRC_ARG_expected_number_of_warnings= 1
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 }
173
174 if ($unsafe_type == 2) {
175 --echo ==== Testing SELECT...LIMIT unsafeness ====
176 --let $desc_0= unsafe SELECT...LIMIT statement
177 --let $stmt_sidef_0= INSERT INTO t0 SELECT * FROM data_table LIMIT 1
178 --let $value_0=
179 --let $sel_sidef_0=
180 --let $sel_retval_0= SELECT * FROM data_table LIMIT 1
181 --let $CRC_ARG_expected_number_of_warnings= 1
182 }
183
184 if ($unsafe_type == 3) {
185 --echo ==== Testing INSERT DELAYED safeness after BUG#54579 is fixed ====
186 --let $desc_0= unsafe INSERT DELAYED statement
187 --let $stmt_sidef_0= INSERT DELAYED INTO t0 VALUES (1), (2)
188 --let $value_0=
189 --let $sel_sidef_0=
190 --let $sel_retval_0=
191 --let $CRC_ARG_expected_number_of_warnings= 0
192 }
193
194 if ($unsafe_type == 4) {
195 --echo ==== Testing unsafeness of insert of two autoinc values ====
196 --let $desc_0= unsafe update of two autoinc columns
197 --let $stmt_sidef_0= INSERT INTO double_autoinc_table VALUES (NULL)
198 --let $value_0=
199 --let $sel_sidef_0=
200 --let $sel_retval_0=
201 --let $CRC_ARG_expected_number_of_warnings= 1
202 }
203
204 if ($unsafe_type == 5) {
205 --echo ==== Testing unsafeness of UDF's ====
206 --let $desc_0= unsafe UDF
207 --let $stmt_sidef_0= INSERT INTO t0 VALUES (myfunc_int(10))
208 --let $value_0= myfunc_int(10)
209 --let $sel_sidef_0= SELECT myfunc_int(10)
210 --let $sel_retval_0=
211 --let $CRC_ARG_expected_number_of_warnings= 1
212 }
213
214 if ($unsafe_type == 6) {
215 --echo ==== Testing unsafeness of access to mysql.general_log ====
216 --let $desc_0= unsafe use of mysql.general_log
217 --let $stmt_sidef_0= INSERT INTO t0 SELECT COUNT(*) FROM mysql.general_log
218 --let $value_0=
219 --let $sel_sidef_0=
220 --let $sel_retval_0= SELECT COUNT(*) FROM mysql.general_log
221 --let $CRC_ARG_expected_number_of_warnings= 1
222 }
223
224 if ($unsafe_type == 7) {
225 --echo ==== Testing a statement that is unsafe in many ways ====
226 --let $desc_0= statement that is unsafe in many ways
227 # Concatenate three unsafe values, and then concatenate NULL to
228 # that so that the result is NULL and we instead use autoinc.
229 --let $stmt_sidef_0= INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1
230 --let $value_0=
231 --let $sel_sidef_0=
232 --let $sel_retval_0=
233 --let $CRC_ARG_expected_number_of_warnings= 7
234 }
235
236 if ($unsafe_type == 8) {
237 --echo ==== Testing a statement that is unsafe several times ====
238 --let $desc_0= statement that is unsafe several times
239 --let $stmt_sidef_0= INSERT INTO ta0 VALUES (multi_unsafe_func())
240 --let $value_0=
241 --let $sel_sidef_0= SELECT multi_unsafe_func()
242 --let $sel_retval_0=
243 --let $CRC_ARG_expected_number_of_warnings= 2
244 }
245
246 # In each iteration of the following loop, we select one way to
247 # enclose the unsafe statement as a sub-statement of a recursive
248 # construct (i.e., a function, procedure, trigger, view, or prepared
249 # statement).
250 #
251 # In the last iteration, $call_type_1=7, we don't create a recursive
252 # construct. Instead, we just invoke the unsafe statement directly.
253
254 --let $call_type_1= 0
255 while ($call_type_1 < 8) {
256 #--echo debug: level 1, types $call_type_1 -> $unsafe_type
257 --let $CRC_ARG_level= 1
258 --let $CRC_ARG_type= $call_type_1
259 --let $CRC_ARG_stmt_sidef= $stmt_sidef_0
260 --let $CRC_ARG_value= $value_0
261 --let $CRC_ARG_sel_sidef= $sel_sidef_0
262 --let $CRC_ARG_sel_retval= $sel_retval_0
263 --let $CRC_ARG_desc= $desc_0
264 --source suite/rpl/include/create_recursive_construct.inc
265 --let $stmt_sidef_1= $CRC_RET_stmt_sidef
266 --let $value_1= $CRC_RET_value
267 --let $sel_sidef_1= $CRC_RET_sel_sidef
268 --let $sel_retval_1= $CRC_RET_sel_retval
269 --let $is_toplevel_1= $CRC_RET_is_toplevel
270 --let $drop_1= $CRC_RET_drop
271 --let $desc_1= $CRC_RET_desc
272
273 # Some statements must be top-level statements, i.e., cannot be
274 # called as a sub-statement of any recursive construct. (One
275 # example is 'EXECUTE prepared_stmt'). When
276 # create_recursive_construct.inc creates a top-level statement, it
277 # sets $CRC_RET_is_toplevel=1.
278
279 if (!$is_toplevel_1) {
280
281 # In each iteration of this loop, we select one way to enclose
282 # the previous recursive construct in another recursive
283 # construct.
284
285 --let $call_type_2= 0
286 while ($call_type_2 < 7) {
287 #--echo debug: level 2, types $call_type_2 -> $call_type_1 -> $unsafe_type
288 --let $CRC_ARG_level= 2
289 --let $CRC_ARG_type= $call_type_2
290 --let $CRC_ARG_stmt_sidef= $stmt_sidef_1
291 --let $CRC_ARG_value= $value_1
292 --let $CRC_ARG_sel_sidef= $sel_sidef_1
293 --let $CRC_ARG_sel_retval= $sel_retval_1
294 --let $CRC_ARG_desc= $desc_1
295 --source suite/rpl/include/create_recursive_construct.inc
296 --let $stmt_sidef_2= $CRC_RET_stmt_sidef
297 --let $value_2= $CRC_RET_value
298 --let $sel_sidef_2= $CRC_RET_sel_sidef
299 --let $sel_retval_2= $CRC_RET_sel_retval
300 --let $is_toplevel_2= $CRC_RET_is_toplevel
301 --let $drop_2= $CRC_RET_drop
302 --let $desc_2= $CRC_RET_desc
303
304 if (!$is_toplevel_2) {
305
306 # Conditioned out since it makes result file really big.
307
308 if (0) {
309
310 # In each iteration of this loop, we select one way to enclose
311 # the previous recursive construct in another recursive
312 # construct.
313
314 --let $call_type_3= 0
315 while ($call_type_3 < 7) {
316 #--echo debug: level 3, types $call_type_2 -> $call_type_2 -> $call_type_1 -> $unsafe_type
317 --let $CRC_ARG_level= 3
318 --let $CRC_ARG_type= $call_type_3
319 --let $CRC_ARG_stmt_sidef= $stmt_sidef_2
320 --let $CRC_ARG_value= $value_2
321 --let $CRC_ARG_sel_sidef= $sel_sidef_2
322 --let $CRC_ARG_sel_retval= $sel_retval_2
323 --let $CRC_ARG_desc= $desc_2
324 --source suite/rpl/include/create_recursive_construct.inc
325
326 # Drop created object.
327 if ($drop_3) {
328 --eval $drop_3
329 }
330 --inc $call_type_3
331 } # while (call_type_3)
332 } # if (0)
333 } # if (!is_toplevel_2)
334
335 # Drop created object.
336 if ($drop_2) {
337 --eval $drop_2
338 }
339 --inc $call_type_2
340 } # while (call_type_2)
341 } # if (!is_toplevel_1)
342
343 # Drop created object.
344 if ($drop_1) {
345 --eval $drop_1
346 }
347 --inc $call_type_1
348 } # while (call_type_1)
349
350 --inc $unsafe_type
351 } # while (unsafe_type)
352
353 DROP TRIGGER double_autoinc_trig;
354 DROP TABLE t0, t1, t2, t3, ta0, ta1, ta2, ta3,
355 autoinc_table, double_autoinc_table,
356 data_table,
357 trigger_table_1, trigger_table_2, trigger_table_3;
358 DROP FUNCTION myfunc_int;
359 DROP FUNCTION multi_unsafe_func;
360
361
362 --echo ==== Special system variables that should *not* be unsafe ====
363
364 CREATE TABLE t1 (a VARCHAR(1000));
365 CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
366
367 INSERT INTO t1 VALUES (@@session.auto_increment_increment);
368 INSERT INTO t1 VALUES (@@session.auto_increment_offset);
369 INSERT INTO t1 VALUES (@@session.character_set_client);
370 INSERT INTO t1 VALUES (@@session.character_set_connection);
371 INSERT INTO t1 VALUES (@@session.character_set_database);
372 INSERT INTO t1 VALUES (@@session.character_set_server);
373 INSERT INTO t1 VALUES (@@session.collation_connection);
374 INSERT INTO t1 VALUES (@@session.collation_database);
375 INSERT INTO t1 VALUES (@@session.collation_server);
376 INSERT INTO t1 VALUES (@@session.foreign_key_checks);
377 INSERT INTO t1 VALUES (@@session.identity);
378 INSERT INTO t1 VALUES (@@session.last_insert_id);
379 INSERT INTO t1 VALUES (@@session.lc_time_names);
380 INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
381 INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
382 INSERT INTO t1 VALUES (@@session.timestamp);
383 INSERT INTO t1 VALUES (@@session.time_zone);
384 INSERT INTO t1 VALUES (@@session.unique_checks);
385
386 SET @my_var= 4711;
387 INSERT INTO t1 VALUES (@my_var);
388
389 # using insert_id implicitly should be ok.
390 SET insert_id= 12;
391 INSERT INTO autoinc_table VALUES (NULL);
392
393 # See set_var.cc for explanation.
394 --echo The following variables *should* give a warning, despite they are replicated.
395 INSERT INTO t1 VALUES (@@session.sql_mode);
396 INSERT INTO t1 VALUES (@@session.insert_id);
397
398
399 DROP TABLE t1, autoinc_table;
400
401
402 #
403 # BUG#34768 - nondeterministic INSERT using LIMIT logged in stmt mode if
404 # binlog_format=mixed
405 #
406 CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
407 INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
408 REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
409 UPDATE t1 SET a=1 LIMIT 1;
410 DELETE FROM t1 LIMIT 1;
411 delimiter |;
412 CREATE PROCEDURE p1()
413 BEGIN
414 INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
415 REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
416 UPDATE t1 SET a=1 LIMIT 1;
417 DELETE FROM t1 LIMIT 1;
418 END|
419 delimiter ;|
420 CALL p1();
421 DROP PROCEDURE p1;
422 DROP TABLE t1;
423
424 #
425 # Bug#42634: % character in query can cause mysqld signal 11 segfault
426 #
427
428 --disable_warnings
429 DROP TABLE IF EXISTS t1;
430 --enable_warnings
431
432 CREATE TABLE t1 (a VARCHAR(200), b VARCHAR(200));
433 INSERT INTO t1 VALUES ('a','b');
434 UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1;
435 DROP TABLE t1;
436
437 #
438 #For bug#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0
439 #
440
441 --disable_warnings
442 DROP TABLE IF EXISTS t1, t2;
443 --enable_warnings
444 CREATE TABLE t1(i INT PRIMARY KEY);
445 CREATE TABLE t2(i INT PRIMARY KEY);
446 CREATE TABLE t3(i INT, ch CHAR(50));
447
448 --echo "Should issue message Statement may not be safe to log in statement format."
449 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
450
451 DELIMITER |;
452 CREATE FUNCTION func6()
453 RETURNS INT
454 BEGIN
455 INSERT INTO t1 VALUES (10);
456 INSERT INTO t1 VALUES (11);
457 INSERT INTO t1 VALUES (12);
458 RETURN 0;
459 END|
460 DELIMITER ;|
461 --echo "Should issue message Statement may not be safe to log in statement format only once"
462 INSERT INTO t3 VALUES(func6(), UUID());
463
464 --echo "Check whether SET @@SQL_LOG_BIN = 0/1 doesn't work in substatements"
465 DELIMITER |;
466 CREATE FUNCTION fun_check_log_bin() RETURNS INT
467 BEGIN
468 SET @@SQL_LOG_BIN = 0;
469 INSERT INTO t1 VALUES(@@global.sync_binlog);
470 RETURN 200;
471 END|
472 DELIMITER ;|
473 --echo "One unsafe warning should be issued in the following statement"
474 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_SQL_LOG_BIN
475 SELECT fun_check_log_bin();
476 --echo "SQL_LOG_BIN should be ON still"
477 SHOW VARIABLES LIKE "SQL_LOG_BIN";
478
479 set @save_log_bin = @@SESSION.SQL_LOG_BIN;
480 set @@SESSION.SQL_LOG_BIN = 0;
481 --echo "Should NOT have any warning message issued in the following statements"
482 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
483 DROP TABLE t1,t2;
484
485 --echo "Should NOT have any warning message issued in the following func7() and trig"
486 CREATE TABLE t1 (a INT);
487 CREATE TABLE t2 (a TEXT);
488 CREATE TABLE trigger_table (a CHAR(7));
489 DELIMITER |;
490 CREATE FUNCTION func7()
491 RETURNS INT
492 BEGIN
493 INSERT INTO t1 VALUES (@@global.sync_binlog);
494 INSERT INTO t1 VALUES (@@session.insert_id);
495 INSERT INTO t2 SELECT UUID();
496 INSERT INTO t2 VALUES (@@session.sql_mode);
497 INSERT INTO t2 VALUES (@@global.init_slave);
498 RETURN 0;
499 END|
500 DELIMITER ;|
501 SHOW VARIABLES LIKE "SQL_LOG_BIN";
502 SELECT func7();
503
504 --echo ---- Insert from trigger ----
505
506 DELIMITER |;
507 CREATE TRIGGER trig
508 BEFORE INSERT ON trigger_table
509 FOR EACH ROW
510 BEGIN
511 INSERT INTO t1 VALUES (@@global.sync_binlog);
512 INSERT INTO t1 VALUES (@@session.insert_id);
513 INSERT INTO t1 VALUES (@@global.auto_increment_increment);
514 INSERT INTO t2 SELECT UUID();
515 INSERT INTO t2 VALUES (@@session.sql_mode);
516 INSERT INTO t2 VALUES (@@global.init_slave);
517 INSERT INTO t2 VALUES (@@hostname);
518 END|
519 DELIMITER ;|
520
521 INSERT INTO trigger_table VALUES ('bye.');
522
523 #clean up
524 DROP FUNCTION fun_check_log_bin;
525 DROP FUNCTION func6;
526 DROP FUNCTION func7;
527 DROP TRIGGER trig;
528 DROP TABLE t1, t2, t3, trigger_table;
529 set @@SESSION.SQL_LOG_BIN = @save_log_bin;
530
531 #
532 # For BUG#42640: mysqld crashes when unsafe statements are executed (STRICT_TRANS_TABLES mode)
533 #
534 SET @save_sql_mode = @@SESSION.SQL_MODE;
535 SET @@SESSION.SQL_MODE = STRICT_ALL_TABLES;
536
537 CREATE TABLE t1(i INT PRIMARY KEY);
538 CREATE TABLE t2(i INT PRIMARY KEY);
539
540 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
541 INSERT INTO t1 VALUES(@@global.sync_binlog);
542
543 UPDATE t1 SET i = 999 LIMIT 1;
544 DELETE FROM t1 LIMIT 1;
545
546 DROP TABLE t1, t2;
547 SET @@SESSION.SQL_MODE = @save_sql_mode;
548
549 #
550 # BUG#45825: INSERT DELAYED is not unsafe: logged in statement format
551 # BUG#45785: LIMIT in SP does not cause RBL if binlog_format=MIXED
552 #
553 SET @old_binlog_format = @@session.binlog_format;
554 SET binlog_format = MIXED;
555
556 CREATE TABLE t1 (a INT);
557 CREATE TABLE t2 (a INT);
558 INSERT INTO t2 VALUES (1), (2);
559
560 --DELIMITER |
561 CREATE PROCEDURE proc_insert_delayed ()
562 BEGIN
563 INSERT DELAYED INTO t1 VALUES (1), (2);
564 END|
565
566 CREATE FUNCTION func_limit ()
567 RETURNS INT
568 BEGIN
569 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
570 RETURN 1;
571 END|
572 --DELIMITER ;
573
574 RESET MASTER;
575 CALL proc_insert_delayed();
576 --disable_ps2_protocol
577 SELECT func_limit();
578 --enable_ps2_protocol
579 source include/show_binlog_events.inc;
580
581 SET @@session.binlog_format = @old_binlog_format;
582 DROP TABLE t1, t2;
583 DROP PROCEDURE proc_insert_delayed;
584 DROP FUNCTION func_limit;
585
586 #
587 # BUG#45827
588 # The test verifies if stmt that have more than one
589 # different tables to update with autoinc columns
590 # will produce unsafe warning
591 #
592
593 # Test case1: stmt that have more than one different tables
594 # to update with autoinc columns should produce
595 # unsafe warning when calling a function
596 CREATE TABLE t1 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
597 CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
598
599 # The purpose of this function is to insert into t1 so that the second
600 # column is auto_increment'ed.
601 DELIMITER |;
602 CREATE FUNCTION func_modify_t1 ()
603 RETURNS INT
604 BEGIN
605 INSERT INTO t1 SET a = 1;
606 RETURN 0;
607 END|
608 DELIMITER ;|
609 --echo # The following statement causes auto-incrementation
610 --echo # of both t1 and t2. It is logged in statement format,
611 --echo # so it should produce unsafe warning.
612 INSERT INTO t2 SET a = func_modify_t1();
613
614 SET SESSION binlog_format = MIXED;
615 --echo # Check if the statement is logged in row format.
616 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
617 INSERT INTO t2 SET a = func_modify_t1();
618 --source include/show_binlog_events.inc
619
620 # clean up
621 DROP TABLE t1,t2;
622 DROP FUNCTION func_modify_t1;
623 #
624 # Test case2: stmt that have more than one different tables
625 # to update with autoinc columns should produce
626 # unsafe warning when invoking a trigger
627 SET SESSION binlog_format = STATEMENT;
628 CREATE TABLE t1 (a INT);
629 CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
630 CREATE TABLE t3 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
631
632 # The purpose of this function is to insert into t1 so that the second
633 # column is auto_increment'ed.
634 delimiter |;
635 create trigger tri_modify_two_tables before insert on t1 for each row begin
636 insert into t2(a) values(new.a);
637 insert into t3(a) values(new.a);
638 end |
639 delimiter ;|
640 --echo # The following statement causes auto-incrementation
641 --echo # of both t2 and t3. It is logged in statement format,
642 --echo # so it should produce unsafe warning
643 INSERT INTO t1 SET a = 1;
644
645 SET SESSION binlog_format = MIXED;
646 --echo # Check if the statement is logged in row format.
647 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
648 INSERT INTO t1 SET a = 2;
649 --source include/show_binlog_events.inc
650
651 # clean up
652 DROP TABLE t1,t2,t3;
653
654 #
655 # BUG#47995: Mark user functions as unsafe
656 # BUG#49222: Mark RAND() unsafe
657 #
658 # Test that the system functions that are supposed to be marked unsafe
659 # generate a warning. Each INSERT statement below should generate a
660 # warning.
661 #
662 SET SESSION binlog_format = STATEMENT;
663
664 CREATE TABLE t1 (a VARBINARY(1000));
665 INSERT INTO t1 VALUES (CURRENT_USER()); #marked unsafe before BUG#47995
666 INSERT INTO t1 VALUES (FOUND_ROWS()); #marked unsafe before BUG#47995
667 INSERT INTO t1 VALUES (GET_LOCK('tmp', 1)); #marked unsafe in BUG#47995
668 INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp')); #marked unsafe in BUG#47995
669 INSERT INTO t1 VALUES (IS_USED_LOCK('tmp')); #marked unsafe in BUG#47995
670 INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe in BUG#39701
671 INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
672 INSERT INTO t1 VALUES (RELEASE_LOCK('tmp')); #marked unsafe in BUG#47995
673 INSERT INTO t1 VALUES (ROW_COUNT()); #marked unsafe before BUG#47995
674 INSERT INTO t1 VALUES (SESSION_USER()); #marked unsafe before BUG#47995
675 INSERT INTO t1 VALUES (SLEEP(1)); #marked unsafe in BUG#47995
676 INSERT INTO t1 VALUES (SYSDATE()); #marked unsafe in BUG#47995
677 INSERT INTO t1 VALUES (SYSTEM_USER()); #marked unsafe before BUG#47995
678 INSERT INTO t1 VALUES (USER()); #marked unsafe before BUG#47995
679 INSERT INTO t1 VALUES (UUID()); #marked unsafe before BUG#47995
680 INSERT INTO t1 VALUES (UUID_SHORT()); #marked unsafe before BUG#47995
681 INSERT INTO t1 VALUES (VERSION()); #marked unsafe in BUG#47995
682 INSERT INTO t1 VALUES (RAND()); #marked unsafe in BUG#49222
683 INSERT INTO t1 VALUES (RANDOM_BYTES(1000)); #marked unsafe in MDEV-25704
684 DELETE FROM t1;
685
686 # Since we replicate the TIMESTAMP variable, functions affected by the
687 # TIMESTAMP variable are safe to replicate. So we check that the
688 # following following functions that depend on the TIMESTAMP variable
689 # are not unsafe and don't generate a warning.
690
691 SET TIME_ZONE= '+03:00';
692 SET TIMESTAMP=1000000;
693 INSERT INTO t1 VALUES
694 (CURDATE()),
695 (CURRENT_DATE()),
696 (CURRENT_TIME()),
697 (CURRENT_TIMESTAMP()),
698 (CURTIME()),
699 (LOCALTIME()),
700 (LOCALTIMESTAMP()),
701 (NOW()),
702 (UNIX_TIMESTAMP()),
703 (UTC_DATE()),
704 (UTC_TIME()),
705 (UTC_TIMESTAMP());
706 SELECT * FROM t1;
707
708 DROP TABLE t1;
709 #
710 #BUG#11758262-50439: MARK INSERT...SEL...ON DUP KEY UPD,REPLACE..
711 #The following statement may be unsafe when logged in statement format.
712 #INSERT IGNORE...SELECT
713 #INSERT ... SELECT ... ON DUPLICATE KEY UPDATE
714 #REPLACE ... SELECT
715 #UPDATE IGNORE
716 #CREATE TABLE... IGNORE SELECT
717 #CREATE TABLE... REPLACE SELECT
718 #
719 ###BUG 11765650 - 58637: MARK UPDATES THAT DEPEND ON ORDER OF TWO KEYS UNSAFE
720 #INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY
721
722 #setup tables
723 CREATE TABLE filler_table (a INT, b INT);
724 INSERT INTO filler_table values (1,1),(1,2);
725 CREATE TABLE insert_table (a INT, b INT, PRIMARY KEY(a));
726 CREATE TABLE replace_table (a INT, b INT, PRIMARY KEY(a));
727 INSERT INTO replace_table values (1,1),(2,2);
728 CREATE TABLE update_table (a INT, b INT, PRIMARY KEY(a));
729 INSERT INTO update_table values (1,1),(2,2);
730 CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY);
731 INSERT INTO insert_2_keys values (1, 1);
732
733 #INSERT IGNORE... SELECT
734 INSERT IGNORE INTO insert_table SELECT * FROM filler_table;
735 TRUNCATE TABLE insert_table;
736 #INSERT ... SELECT ... ON DUPLICATE KEY UPDATE
737 INSERT INTO insert_table SELECT * FROM filler_table ON DUPLICATE KEY UPDATE a = 1;
738 TRUNCATE TABLE insert_table;
739 #REPLACE...SELECT
740 REPLACE INTO replace_table SELECT * FROM filler_table;
741 #UPDATE IGNORE
742 UPDATE IGNORE update_table SET a=2;
743 #CREATE TABLE [IGNORE/REPLACE] SELECT
744 CREATE TABLE create_ignore_test (a INT, b INT, PRIMARY KEY(b)) IGNORE SELECT * FROM filler_table;
745 CREATE TABLE create_replace_test (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table;
746 #temporary tables should not throw the warning.
747 CREATE TEMPORARY TABLE temp1 (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table;
748
749 #INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY
750 INSERT INTO insert_2_keys VALUES (1, 2)
751 ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10;
752
753 ###clean up
754 DROP TABLE filler_table;
755 DROP TABLE insert_table;
756 DROP TABLE update_table;
757 DROP TABLE replace_table;
758 DROP TABLE create_ignore_test;
759 DROP TABLE create_replace_test;
760 DROP TABLE insert_2_keys;
761
762 --echo "End of tests"