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 104call 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 109CREATE TABLE t0 (a CHAR(200)); 110CREATE TABLE t1 (a CHAR(200)); 111CREATE TABLE t2 (a CHAR(200)); 112CREATE TABLE t3 (a CHAR(200)); 113CREATE TABLE ta0 (a CHAR(200)); 114CREATE TABLE ta1 (a CHAR(200)); 115CREATE TABLE ta2 (a CHAR(200)); 116CREATE TABLE ta3 (a CHAR(200)); 117CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT); 118CREATE TABLE data_table (a CHAR(200)); 119INSERT INTO data_table VALUES ('foo'); 120CREATE TABLE trigger_table_1 (a INT); 121CREATE TABLE trigger_table_2 (a INT); 122CREATE TABLE trigger_table_3 (a INT); 123CREATE TABLE double_autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT); 124 125--DELIMITER | 126CREATE TRIGGER double_autoinc_trig 127BEFORE INSERT ON double_autoinc_table FOR EACH ROW 128BEGIN 129 INSERT INTO autoinc_table VALUES (NULL); 130END| 131 132CREATE FUNCTION multi_unsafe_func() RETURNS INT 133BEGIN 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; 138END| 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 147while ($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 353DROP TRIGGER double_autoinc_trig; 354DROP 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; 358DROP FUNCTION myfunc_int; 359DROP FUNCTION multi_unsafe_func; 360 361 362--echo ==== Special system variables that should *not* be unsafe ==== 363 364CREATE TABLE t1 (a VARCHAR(1000)); 365CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT); 366 367INSERT INTO t1 VALUES (@@session.auto_increment_increment); 368INSERT INTO t1 VALUES (@@session.auto_increment_offset); 369INSERT INTO t1 VALUES (@@session.character_set_client); 370INSERT INTO t1 VALUES (@@session.character_set_connection); 371INSERT INTO t1 VALUES (@@session.character_set_database); 372INSERT INTO t1 VALUES (@@session.character_set_server); 373INSERT INTO t1 VALUES (@@session.collation_connection); 374INSERT INTO t1 VALUES (@@session.collation_database); 375INSERT INTO t1 VALUES (@@session.collation_server); 376INSERT INTO t1 VALUES (@@session.foreign_key_checks); 377INSERT INTO t1 VALUES (@@session.identity); 378INSERT INTO t1 VALUES (@@session.last_insert_id); 379INSERT INTO t1 VALUES (@@session.lc_time_names); 380INSERT INTO t1 VALUES (@@session.pseudo_thread_id); 381INSERT INTO t1 VALUES (@@session.sql_auto_is_null); 382INSERT INTO t1 VALUES (@@session.timestamp); 383INSERT INTO t1 VALUES (@@session.time_zone); 384INSERT INTO t1 VALUES (@@session.unique_checks); 385 386SET @my_var= 4711; 387INSERT INTO t1 VALUES (@my_var); 388 389# using insert_id implicitly should be ok. 390SET insert_id= 12; 391INSERT 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. 395INSERT INTO t1 VALUES (@@session.sql_mode); 396INSERT INTO t1 VALUES (@@session.insert_id); 397 398 399DROP TABLE t1, autoinc_table; 400 401 402# 403# BUG#34768 - nondeterministic INSERT using LIMIT logged in stmt mode if 404# binlog_format=mixed 405# 406CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b)); 407INSERT INTO t1 SELECT * FROM t1 LIMIT 1; 408REPLACE INTO t1 SELECT * FROM t1 LIMIT 1; 409UPDATE t1 SET a=1 LIMIT 1; 410DELETE FROM t1 LIMIT 1; 411delimiter |; 412CREATE PROCEDURE p1() 413BEGIN 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; 418END| 419delimiter ;| 420CALL p1(); 421DROP PROCEDURE p1; 422DROP TABLE t1; 423 424# 425# Bug#42634: % character in query can cause mysqld signal 11 segfault 426# 427 428--disable_warnings 429DROP TABLE IF EXISTS t1; 430--enable_warnings 431 432CREATE TABLE t1 (a VARCHAR(200), b VARCHAR(200)); 433INSERT INTO t1 VALUES ('a','b'); 434UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1; 435DROP 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 442DROP TABLE IF EXISTS t1, t2; 443--enable_warnings 444CREATE TABLE t1(i INT PRIMARY KEY); 445CREATE TABLE t2(i INT PRIMARY KEY); 446CREATE TABLE t3(i INT, ch CHAR(50)); 447 448--echo "Should issue message Statement may not be safe to log in statement format." 449INSERT INTO t1 SELECT * FROM t2 LIMIT 1; 450 451DELIMITER |; 452CREATE FUNCTION func6() 453RETURNS INT 454BEGIN 455 INSERT INTO t1 VALUES (10); 456 INSERT INTO t1 VALUES (11); 457 INSERT INTO t1 VALUES (12); 458 RETURN 0; 459END| 460DELIMITER ;| 461--echo "Should issue message Statement may not be safe to log in statement format only once" 462INSERT INTO t3 VALUES(func6(), UUID()); 463 464--echo "Check whether SET @@SQL_LOG_BIN = 0/1 doesn't work in substatements" 465DELIMITER |; 466CREATE FUNCTION fun_check_log_bin() RETURNS INT 467BEGIN 468 SET @@SQL_LOG_BIN = 0; 469 INSERT INTO t1 VALUES(@@global.sync_binlog); 470 RETURN 200; 471END| 472DELIMITER ;| 473--echo "One unsafe warning should be issued in the following statement" 474--error ER_STORED_FUNCTION_PREVENTS_SWITCH_SQL_LOG_BIN 475SELECT fun_check_log_bin(); 476--echo "SQL_LOG_BIN should be ON still" 477SHOW VARIABLES LIKE "SQL_LOG_BIN"; 478 479set @save_log_bin = @@SESSION.SQL_LOG_BIN; 480set @@SESSION.SQL_LOG_BIN = 0; 481--echo "Should NOT have any warning message issued in the following statements" 482INSERT INTO t1 SELECT * FROM t2 LIMIT 1; 483DROP TABLE t1,t2; 484 485--echo "Should NOT have any warning message issued in the following func7() and trig" 486CREATE TABLE t1 (a INT); 487CREATE TABLE t2 (a TEXT); 488CREATE TABLE trigger_table (a CHAR(7)); 489DELIMITER |; 490CREATE FUNCTION func7() 491RETURNS INT 492BEGIN 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; 499END| 500DELIMITER ;| 501SHOW VARIABLES LIKE "SQL_LOG_BIN"; 502SELECT func7(); 503 504--echo ---- Insert from trigger ---- 505 506DELIMITER |; 507CREATE TRIGGER trig 508BEFORE INSERT ON trigger_table 509FOR EACH ROW 510BEGIN 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); 518END| 519DELIMITER ;| 520 521INSERT INTO trigger_table VALUES ('bye.'); 522 523#clean up 524DROP FUNCTION fun_check_log_bin; 525DROP FUNCTION func6; 526DROP FUNCTION func7; 527DROP TRIGGER trig; 528DROP TABLE t1, t2, t3, trigger_table; 529set @@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# 534SET @save_sql_mode = @@SESSION.SQL_MODE; 535SET @@SESSION.SQL_MODE = STRICT_ALL_TABLES; 536 537CREATE TABLE t1(i INT PRIMARY KEY); 538CREATE TABLE t2(i INT PRIMARY KEY); 539 540INSERT INTO t1 SELECT * FROM t2 LIMIT 1; 541INSERT INTO t1 VALUES(@@global.sync_binlog); 542 543UPDATE t1 SET i = 999 LIMIT 1; 544DELETE FROM t1 LIMIT 1; 545 546DROP TABLE t1, t2; 547SET @@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# 553SET @old_binlog_format = @@session.binlog_format; 554SET binlog_format = MIXED; 555 556CREATE TABLE t1 (a INT); 557CREATE TABLE t2 (a INT); 558INSERT INTO t2 VALUES (1), (2); 559 560--DELIMITER | 561CREATE PROCEDURE proc_insert_delayed () 562BEGIN 563 INSERT DELAYED INTO t1 VALUES (1), (2); 564END| 565 566CREATE FUNCTION func_limit () 567RETURNS INT 568BEGIN 569 INSERT INTO t1 SELECT * FROM t2 LIMIT 1; 570 RETURN 1; 571END| 572--DELIMITER ; 573 574RESET MASTER; 575CALL proc_insert_delayed(); 576SELECT func_limit(); 577source include/show_binlog_events.inc; 578 579SET @@session.binlog_format = @old_binlog_format; 580DROP TABLE t1, t2; 581DROP PROCEDURE proc_insert_delayed; 582DROP FUNCTION func_limit; 583 584# 585# BUG#45827 586# The test verifies if stmt that have more than one 587# different tables to update with autoinc columns 588# will produce unsafe warning 589# 590 591# Test case1: stmt that have more than one different tables 592# to update with autoinc columns should produce 593# unsafe warning when calling a function 594CREATE TABLE t1 (a INT, b INT PRIMARY KEY AUTO_INCREMENT); 595CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT); 596 597# The purpose of this function is to insert into t1 so that the second 598# column is auto_increment'ed. 599DELIMITER |; 600CREATE FUNCTION func_modify_t1 () 601RETURNS INT 602BEGIN 603 INSERT INTO t1 SET a = 1; 604 RETURN 0; 605END| 606DELIMITER ;| 607--echo # The following statement causes auto-incrementation 608--echo # of both t1 and t2. It is logged in statement format, 609--echo # so it should produce unsafe warning. 610INSERT INTO t2 SET a = func_modify_t1(); 611 612SET SESSION binlog_format = MIXED; 613--echo # Check if the statement is logged in row format. 614let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 615INSERT INTO t2 SET a = func_modify_t1(); 616--source include/show_binlog_events.inc 617 618# clean up 619DROP TABLE t1,t2; 620DROP FUNCTION func_modify_t1; 621# 622# Test case2: stmt that have more than one different tables 623# to update with autoinc columns should produce 624# unsafe warning when invoking a trigger 625SET SESSION binlog_format = STATEMENT; 626CREATE TABLE t1 (a INT); 627CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT); 628CREATE TABLE t3 (a INT, b INT PRIMARY KEY AUTO_INCREMENT); 629 630# The purpose of this function is to insert into t1 so that the second 631# column is auto_increment'ed. 632delimiter |; 633create trigger tri_modify_two_tables before insert on t1 for each row begin 634 insert into t2(a) values(new.a); 635 insert into t3(a) values(new.a); 636end | 637delimiter ;| 638--echo # The following statement causes auto-incrementation 639--echo # of both t2 and t3. It is logged in statement format, 640--echo # so it should produce unsafe warning 641INSERT INTO t1 SET a = 1; 642 643SET SESSION binlog_format = MIXED; 644--echo # Check if the statement is logged in row format. 645let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); 646INSERT INTO t1 SET a = 2; 647--source include/show_binlog_events.inc 648 649# clean up 650DROP TABLE t1,t2,t3; 651 652# 653# BUG#47995: Mark user functions as unsafe 654# BUG#49222: Mare RAND() unsafe 655# 656# Test that the system functions that are supposed to be marked unsafe 657# generate a warning. Each INSERT statement below should generate a 658# warning. 659# 660SET SESSION binlog_format = STATEMENT; 661 662CREATE TABLE t1 (a VARCHAR(1000)); 663INSERT INTO t1 VALUES (CURRENT_USER()); #marked unsafe before BUG#47995 664INSERT INTO t1 VALUES (FOUND_ROWS()); #marked unsafe before BUG#47995 665INSERT INTO t1 VALUES (GET_LOCK('tmp', 1)); #marked unsafe in BUG#47995 666INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp')); #marked unsafe in BUG#47995 667INSERT INTO t1 VALUES (IS_USED_LOCK('tmp')); #marked unsafe in BUG#47995 668INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe in BUG#39701 669INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1)); 670INSERT INTO t1 VALUES (RELEASE_LOCK('tmp')); #marked unsafe in BUG#47995 671INSERT INTO t1 VALUES (ROW_COUNT()); #marked unsafe before BUG#47995 672INSERT INTO t1 VALUES (SESSION_USER()); #marked unsafe before BUG#47995 673INSERT INTO t1 VALUES (SLEEP(1)); #marked unsafe in BUG#47995 674INSERT INTO t1 VALUES (SYSDATE()); #marked unsafe in BUG#47995 675INSERT INTO t1 VALUES (SYSTEM_USER()); #marked unsafe before BUG#47995 676INSERT INTO t1 VALUES (USER()); #marked unsafe before BUG#47995 677INSERT INTO t1 VALUES (UUID()); #marked unsafe before BUG#47995 678INSERT INTO t1 VALUES (UUID_SHORT()); #marked unsafe before BUG#47995 679INSERT INTO t1 VALUES (VERSION()); #marked unsafe in BUG#47995 680INSERT INTO t1 VALUES (RAND()); #marked unsafe in BUG#49222 681DELETE FROM t1; 682 683# Since we replicate the TIMESTAMP variable, functions affected by the 684# TIMESTAMP variable are safe to replicate. So we check that the 685# following following functions that depend on the TIMESTAMP variable 686# are not unsafe and don't generate a warning. 687 688SET TIME_ZONE= '+03:00'; 689SET TIMESTAMP=1000000; 690INSERT INTO t1 VALUES 691 (CURDATE()), 692 (CURRENT_DATE()), 693 (CURRENT_TIME()), 694 (CURRENT_TIMESTAMP()), 695 (CURTIME()), 696 (LOCALTIME()), 697 (LOCALTIMESTAMP()), 698 (NOW()), 699 (UNIX_TIMESTAMP()), 700 (UTC_DATE()), 701 (UTC_TIME()), 702 (UTC_TIMESTAMP()); 703SELECT * FROM t1; 704 705DROP TABLE t1; 706# 707#BUG#11758262-50439: MARK INSERT...SEL...ON DUP KEY UPD,REPLACE.. 708#The following statement may be unsafe when logged in statement format. 709#INSERT IGNORE...SELECT 710#INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 711#REPLACE ... SELECT 712#UPDATE IGNORE 713#CREATE TABLE... IGNORE SELECT 714#CREATE TABLE... REPLACE SELECT 715# 716###BUG 11765650 - 58637: MARK UPDATES THAT DEPEND ON ORDER OF TWO KEYS UNSAFE 717#INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY 718 719#setup tables 720CREATE TABLE filler_table (a INT, b INT); 721INSERT INTO filler_table values (1,1),(1,2); 722CREATE TABLE insert_table (a INT, b INT, PRIMARY KEY(a)); 723CREATE TABLE replace_table (a INT, b INT, PRIMARY KEY(a)); 724INSERT INTO replace_table values (1,1),(2,2); 725CREATE TABLE update_table (a INT, b INT, PRIMARY KEY(a)); 726INSERT INTO update_table values (1,1),(2,2); 727CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY); 728INSERT INTO insert_2_keys values (1, 1); 729 730#INSERT IGNORE... SELECT 731INSERT IGNORE INTO insert_table SELECT * FROM filler_table; 732TRUNCATE TABLE insert_table; 733#INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 734INSERT INTO insert_table SELECT * FROM filler_table ON DUPLICATE KEY UPDATE a = 1; 735TRUNCATE TABLE insert_table; 736#REPLACE...SELECT 737REPLACE INTO replace_table SELECT * FROM filler_table; 738#UPDATE IGNORE 739UPDATE IGNORE update_table SET a=2; 740#CREATE TABLE [IGNORE/REPLACE] SELECT 741CREATE TABLE create_ignore_test (a INT, b INT, PRIMARY KEY(b)) IGNORE SELECT * FROM filler_table; 742CREATE TABLE create_replace_test (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table; 743#temporary tables should not throw the warning. 744CREATE TEMPORARY TABLE temp1 (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table; 745 746#INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY 747INSERT INTO insert_2_keys VALUES (1, 2) 748 ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10; 749 750###clean up 751DROP TABLE filler_table; 752DROP TABLE insert_table; 753DROP TABLE update_table; 754DROP TABLE replace_table; 755DROP TABLE create_ignore_test; 756DROP TABLE create_replace_test; 757DROP TABLE insert_2_keys; 758 759--echo "End of tests" 760