Debian Codegraph @

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