1CALL mtr.add_suppression("Statement may not be safe to log in statement format."); 2call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 3drop table if exists t1, t2; 4create table t1 (a int) engine=innodb; 5create table t2 (a int) engine=myisam; 6reset master; 7begin; 8insert into t1 values(1); 9insert into t2 select * from t1; 10Warnings: 11Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them. 12commit; 13include/show_binlog_events.inc 14Log_name Pos Event_type Server_id End_log_pos Info 15master-bin.000001 # Query # # BEGIN 16master-bin.000001 # Query # # use `test`; insert into t1 values(1) 17master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 18master-bin.000001 # Xid # # COMMIT /* XID */ 19delete from t1; 20delete from t2; 21reset master; 22begin; 23insert into t1 values(2); 24insert into t2 select * from t1; 25Warnings: 26Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them. 27rollback; 28Warnings: 29Warning 1196 Some non-transactional changed tables couldn't be rolled back 30include/show_binlog_events.inc 31Log_name Pos Event_type Server_id End_log_pos Info 32master-bin.000001 # Query # # BEGIN 33master-bin.000001 # Query # # use `test`; insert into t1 values(2) 34master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 35master-bin.000001 # Query # # ROLLBACK 36delete from t1; 37delete from t2; 38reset master; 39begin; 40insert into t1 values(3); 41savepoint my_savepoint; 42insert into t1 values(4); 43insert into t2 select * from t1; 44Warnings: 45Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them. 46rollback to savepoint my_savepoint; 47Warnings: 48Warning 1196 Some non-transactional changed tables couldn't be rolled back 49commit; 50include/show_binlog_events.inc 51Log_name Pos Event_type Server_id End_log_pos Info 52master-bin.000001 # Query # # BEGIN 53master-bin.000001 # Query # # use `test`; insert into t1 values(3) 54master-bin.000001 # Query # # SAVEPOINT `my_savepoint` 55master-bin.000001 # Query # # use `test`; insert into t1 values(4) 56master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 57master-bin.000001 # Query # # ROLLBACK TO `my_savepoint` 58master-bin.000001 # Xid # # COMMIT /* XID */ 59delete from t1; 60delete from t2; 61reset master; 62begin; 63insert into t1 values(5); 64savepoint my_savepoint; 65insert into t1 values(6); 66insert into t2 select * from t1; 67Warnings: 68Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them. 69rollback to savepoint my_savepoint; 70Warnings: 71Warning 1196 Some non-transactional changed tables couldn't be rolled back 72insert into t1 values(7); 73commit; 74select a from t1 order by a; 75a 765 777 78include/show_binlog_events.inc 79Log_name Pos Event_type Server_id End_log_pos Info 80master-bin.000001 # Query # # BEGIN 81master-bin.000001 # Query # # use `test`; insert into t1 values(5) 82master-bin.000001 # Query # # SAVEPOINT `my_savepoint` 83master-bin.000001 # Query # # use `test`; insert into t1 values(6) 84master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 85master-bin.000001 # Query # # ROLLBACK TO `my_savepoint` 86master-bin.000001 # Query # # use `test`; insert into t1 values(7) 87master-bin.000001 # Xid # # COMMIT /* XID */ 88delete from t1; 89delete from t2; 90reset master; 91select get_lock("a",10); 92get_lock("a",10) 931 94begin; 95insert into t1 values(8); 96insert into t2 select * from t1; 97Warnings: 98Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them. 99select get_lock("a",10); 100get_lock("a",10) 1011 102include/show_binlog_events.inc 103Log_name Pos Event_type Server_id End_log_pos Info 104master-bin.000001 # Query # # BEGIN 105master-bin.000001 # Query # # use `test`; insert into t1 values(8) 106master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 107master-bin.000001 # Query # # ROLLBACK 108delete from t1; 109delete from t2; 110reset master; 111insert into t1 values(9); 112insert into t2 select * from t1; 113include/show_binlog_events.inc 114Log_name Pos Event_type Server_id End_log_pos Info 115master-bin.000001 # Query # # BEGIN 116master-bin.000001 # Query # # use `test`; insert into t1 values(9) 117master-bin.000001 # Xid # # COMMIT /* XID */ 118master-bin.000001 # Query # # BEGIN 119master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 120master-bin.000001 # Query # # COMMIT 121delete from t1; 122delete from t2; 123reset master; 124insert into t1 values(10); 125begin; 126insert into t2 select * from t1; 127include/show_binlog_events.inc 128Log_name Pos Event_type Server_id End_log_pos Info 129master-bin.000001 # Query # # BEGIN 130master-bin.000001 # Query # # use `test`; insert into t1 values(10) 131master-bin.000001 # Xid # # COMMIT /* XID */ 132master-bin.000001 # Query # # BEGIN 133master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 134master-bin.000001 # Query # # COMMIT 135insert into t1 values(11); 136commit; 137include/show_binlog_events.inc 138Log_name Pos Event_type Server_id End_log_pos Info 139master-bin.000001 # Query # # BEGIN 140master-bin.000001 # Query # # use `test`; insert into t1 values(10) 141master-bin.000001 # Xid # # COMMIT /* XID */ 142master-bin.000001 # Query # # BEGIN 143master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 144master-bin.000001 # Query # # COMMIT 145master-bin.000001 # Query # # BEGIN 146master-bin.000001 # Query # # use `test`; insert into t1 values(11) 147master-bin.000001 # Xid # # COMMIT /* XID */ 148alter table t2 engine=INNODB; 149delete from t1; 150delete from t2; 151reset master; 152begin; 153insert into t1 values(12); 154insert into t2 select * from t1; 155commit; 156include/show_binlog_events.inc 157Log_name Pos Event_type Server_id End_log_pos Info 158master-bin.000001 # Query # # BEGIN 159master-bin.000001 # Query # # use `test`; insert into t1 values(12) 160master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 161master-bin.000001 # Xid # # COMMIT /* XID */ 162delete from t1; 163delete from t2; 164reset master; 165begin; 166insert into t1 values(13); 167insert into t2 select * from t1; 168rollback; 169include/show_binlog_events.inc 170delete from t1; 171delete from t2; 172reset master; 173begin; 174insert into t1 values(14); 175savepoint my_savepoint; 176insert into t1 values(15); 177insert into t2 select * from t1; 178rollback to savepoint my_savepoint; 179commit; 180include/show_binlog_events.inc 181Log_name Pos Event_type Server_id End_log_pos Info 182master-bin.000001 # Query # # BEGIN 183master-bin.000001 # Query # # use `test`; insert into t1 values(14) 184master-bin.000001 # Query # # SAVEPOINT `my_savepoint` 185master-bin.000001 # Xid # # COMMIT /* XID */ 186delete from t1; 187delete from t2; 188reset master; 189begin; 190insert into t1 values(16); 191savepoint my_savepoint; 192insert into t1 values(17); 193insert into t2 select * from t1; 194rollback to savepoint my_savepoint; 195insert into t1 values(18); 196commit; 197select a from t1 order by a; 198a 19916 20018 201include/show_binlog_events.inc 202Log_name Pos Event_type Server_id End_log_pos Info 203master-bin.000001 # Query # # BEGIN 204master-bin.000001 # Query # # use `test`; insert into t1 values(16) 205master-bin.000001 # Query # # SAVEPOINT `my_savepoint` 206master-bin.000001 # Query # # use `test`; insert into t1 values(18) 207master-bin.000001 # Xid # # COMMIT /* XID */ 208delete from t1; 209delete from t2; 210alter table t2 engine=MyISAM; 211insert into t1 values (1); 212begin; 213select * from t1 for update; 214a 2151 216select (@before:=unix_timestamp())*0; 217(@before:=unix_timestamp())*0 2180 219begin; 220select * from t1 for update; 221insert into t2 values (20); 222ERROR HY000: Lock wait timeout exceeded; try restarting transaction 223select (@after:=unix_timestamp())*0; 224(@after:=unix_timestamp())*0 2250 226select (@after-@before) >= 2; 227(@after-@before) >= 2 2281 229commit; 230drop table t1,t2; 231commit; 232begin; 233create temporary table ti (a int) engine=innodb; 234rollback; 235Warnings: 236Warning # The creation of some temporary tables could not be rolled back. 237insert into ti values(1); 238set autocommit=0; 239create temporary table t1 (a int) engine=myisam; 240commit; 241insert t1 values (1); 242rollback; 243Warnings: 244Warning 1196 Some non-transactional changed tables couldn't be rolled back 245create table t0 (n int); 246insert t0 select * from t1; 247set autocommit=1; 248insert into t0 select GET_LOCK("lock1",null); 249Warnings: 250Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. 251set autocommit=0; 252create table t2 (n int) engine=innodb; 253insert into t2 values (3); 254select get_lock("lock1",60); 255get_lock("lock1",60) 2561 257include/show_binlog_events.inc 258Log_name Pos Event_type Server_id End_log_pos Info 259master-bin.000001 # Query # # BEGIN 260master-bin.000001 # Query # # use `test`; insert into t1 values(16) 261master-bin.000001 # Query # # SAVEPOINT `my_savepoint` 262master-bin.000001 # Query # # use `test`; insert into t1 values(18) 263master-bin.000001 # Xid # # COMMIT /* XID */ 264master-bin.000001 # Query # # BEGIN 265master-bin.000001 # Query # # use `test`; delete from t1 266master-bin.000001 # Xid # # COMMIT /* XID */ 267master-bin.000001 # Query # # BEGIN 268master-bin.000001 # Query # # use `test`; delete from t2 269master-bin.000001 # Xid # # COMMIT /* XID */ 270master-bin.000001 # Query # # use `test`; alter table t2 engine=MyISAM 271master-bin.000001 # Query # # BEGIN 272master-bin.000001 # Query # # use `test`; insert into t1 values (1) 273master-bin.000001 # Xid # # COMMIT /* XID */ 274master-bin.000001 # Query # # BEGIN 275master-bin.000001 # Query # # use `test`; insert into t2 values (20) 276master-bin.000001 # Query # # COMMIT 277master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */ 278master-bin.000001 # Query # # BEGIN 279master-bin.000001 # Query # # use `test`; create temporary table ti (a int) engine=innodb 280master-bin.000001 # Query # # ROLLBACK 281master-bin.000001 # Query # # BEGIN 282master-bin.000001 # Query # # use `test`; insert into ti values(1) 283master-bin.000001 # Query # # COMMIT 284master-bin.000001 # Query # # BEGIN 285master-bin.000001 # Query # # use `test`; create temporary table t1 (a int) engine=myisam 286master-bin.000001 # Query # # COMMIT 287master-bin.000001 # Query # # BEGIN 288master-bin.000001 # Query # # use `test`; insert t1 values (1) 289master-bin.000001 # Query # # COMMIT 290master-bin.000001 # Query # # use `test`; create table t0 (n int) 291master-bin.000001 # Query # # BEGIN 292master-bin.000001 # Query # # use `test`; insert t0 select * from t1 293master-bin.000001 # Query # # COMMIT 294master-bin.000001 # Query # # BEGIN 295master-bin.000001 # Query # # use `test`; insert into t0 select GET_LOCK("lock1",null) 296master-bin.000001 # Query # # COMMIT 297master-bin.000001 # Query # # use `test`; create table t2 (n int) engine=innodb 298master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `ti` 299master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t1` 300do release_lock("lock1"); 301drop table t0,t2; 302set autocommit=0; 303CREATE TABLE t1 (a int, b int) engine=myisam; 304reset master; 305INSERT INTO t1 values (1,1),(1,2); 306CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1; 307ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 308DROP TABLE if exists t2; 309Warnings: 310Note 1051 Unknown table 'test.t2' 311INSERT INTO t1 values (3,3); 312CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1; 313ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 314ROLLBACK; 315Warnings: 316Warning 1196 Some non-transactional changed tables couldn't be rolled back 317DROP TABLE IF EXISTS t2; 318Warnings: 319Note 1051 Unknown table 'test.t2' 320CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb; 321INSERT INTO t1 VALUES (4,4); 322CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; 323Warnings: 324Note 1050 Table 't2' already exists 325SELECT * from t2; 326a b 327TRUNCATE table t2; 328INSERT INTO t1 VALUES (5,5); 329INSERT INTO t2 select * from t1; 330ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 331SELECT * FROM t2; 332a b 333DROP TABLE t2; 334INSERT INTO t1 values (6,6); 335CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ; 336INSERT INTO t1 values (7,7); 337Warnings: 338Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction. 339ROLLBACK; 340Warnings: 341Warning # Some non-transactional changed tables couldn't be rolled back 342Warning # The creation of some temporary tables could not be rolled back. 343INSERT INTO t1 values (8,8); 344CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; 345Warnings: 346Note 1050 Table 't2' already exists 347COMMIT; 348INSERT INTO t1 values (9,9); 349CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; 350Warnings: 351Note 1050 Table 't2' already exists 352ROLLBACK; 353Warnings: 354Warning 1196 Some non-transactional changed tables couldn't be rolled back 355SELECT * from t2; 356a b 357TRUNCATE table t2; 358INSERT INTO t1 values (10,10); 359INSERT INTO t2 select * from t1; 360ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 361SELECT * from t1; 362a b 3631 1 3641 2 3653 3 3664 4 3675 5 3686 6 3697 7 3708 8 3719 9 37210 10 373INSERT INTO t2 values (100,100); 374CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; 375Warnings: 376Note 1050 Table 't2' already exists 377COMMIT; 378INSERT INTO t2 values (101,101); 379CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; 380Warnings: 381Note 1050 Table 't2' already exists 382ROLLBACK; 383SELECT * from t2; 384a b 385100 100 386DROP TABLE t1,t2; 387include/show_binlog_events.inc 388Log_name Pos Event_type Server_id End_log_pos Info 389master-bin.000001 # Query # # BEGIN 390master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (1,1),(1,2) 391master-bin.000001 # Query # # COMMIT 392master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */ 393master-bin.000001 # Query # # BEGIN 394master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (3,3) 395master-bin.000001 # Query # # COMMIT 396master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */ 397master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb 398master-bin.000001 # Query # # BEGIN 399master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (4,4) 400master-bin.000001 # Query # # COMMIT 401master-bin.000001 # Query # # use `test`; TRUNCATE table t2 402master-bin.000001 # Query # # BEGIN 403master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (5,5) 404master-bin.000001 # Query # # COMMIT 405master-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */ 406master-bin.000001 # Query # # BEGIN 407master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (6,6) 408master-bin.000001 # Query # # COMMIT 409master-bin.000001 # Query # # BEGIN 410master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb 411master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (7,7) 412master-bin.000001 # Query # # ROLLBACK 413master-bin.000001 # Query # # BEGIN 414master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (8,8) 415master-bin.000001 # Query # # COMMIT 416master-bin.000001 # Query # # BEGIN 417master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (9,9) 418master-bin.000001 # Query # # COMMIT 419master-bin.000001 # Query # # use `test`; TRUNCATE table t2 420master-bin.000001 # Query # # BEGIN 421master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (10,10) 422master-bin.000001 # Query # # COMMIT 423master-bin.000001 # Query # # BEGIN 424master-bin.000001 # Query # # use `test`; INSERT INTO t2 values (100,100) 425master-bin.000001 # Query # # COMMIT 426master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `t2` /* generated by server */ 427master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ 428reset master; 429create table t1 (a int) engine=innodb; 430create table t2 (a int) engine=myisam; 431select get_lock("a",10); 432get_lock("a",10) 4331 434begin; 435insert into t1 values(8); 436insert into t2 select * from t1; 437Warnings: 438Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them. 439select get_lock("a",10); 440get_lock("a",10) 4411 442flush logs; 443select 444(@a:=load_file("MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output")) 445is not null AS Loaded; 446Loaded 4471 448select 449@a like "%#%error_code=0%ROLLBACK\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR 450@a like "%#%error_code=0%ROLLBACK\r\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%", 451@a not like "%#%error_code=%error_code=%"; 452@a like "%#%error_code=0%ROLLBACK\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR 453@a like "%#%error_code=0%ROLLBACK\r\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" @a not like "%#%error_code=%error_code=%" 4541 1 455drop table t1, t2; 456create temporary table tt (a int unique); 457create table ti (a int) engine=innodb; 458reset master; 459begin; 460insert into ti values (1); 461insert into ti values (2) ; 462insert into tt select * from ti; 463rollback; 464Warnings: 465Warning 1196 Some non-transactional changed tables couldn't be rolled back 466select count(*) from tt /* 2 */; 467count(*) 4682 469include/show_binlog_events.inc 470Log_name Pos Event_type Server_id End_log_pos Info 471master-bin.000001 # Query # # BEGIN 472master-bin.000001 # Query # # use `test`; insert into ti values (1) 473master-bin.000001 # Query # # use `test`; insert into ti values (2) 474master-bin.000001 # Query # # use `test`; insert into tt select * from ti 475master-bin.000001 # Query # # ROLLBACK 476select count(*) from ti /* zero */; 477count(*) 4780 479insert into ti select * from tt; 480select * from ti /* that is what slave would miss - a bug */; 481a 4821 4832 484delete from ti; 485delete from tt where a=1; 486reset master; 487begin; 488insert into ti values (1); 489insert into ti values (2) /* to make the dup error in the following */; 490insert into tt select * from ti /* one affected and error */; 491ERROR 23000: Duplicate entry '2' for key 'a' 492rollback; 493Warnings: 494Warning 1196 Some non-transactional changed tables couldn't be rolled back 495include/show_binlog_events.inc 496Log_name Pos Event_type Server_id End_log_pos Info 497master-bin.000001 # Query # # BEGIN 498master-bin.000001 # Query # # use `test`; insert into ti values (1) 499master-bin.000001 # Query # # use `test`; insert into ti values (2) /* to make the dup error in the following */ 500master-bin.000001 # Query # # use `test`; insert into tt select * from ti /* one affected and error */ 501master-bin.000001 # Query # # ROLLBACK 502select count(*) from ti /* zero */; 503count(*) 5040 505insert into ti select * from tt; 506select * from tt /* that is what otherwise slave missed - the bug */; 507a 5081 5092 510drop table ti, tt; 511drop function if exists bug27417; 512drop table if exists t1,t2; 513CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; 514CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); 515create function bug27417(n int) 516RETURNS int(11) 517begin 518insert into t1 values (null); 519return n; 520end| 521reset master; 522insert into t2 values (bug27417(1)); 523Warnings: 524Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. 525insert into t2 select bug27417(2); 526Warnings: 527Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. 528reset master; 529insert into t2 values (bug27417(2)); 530ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 531include/show_binlog_events.inc 532Log_name Pos Event_type Server_id End_log_pos Info 533master-bin.000001 # Query # # BEGIN 534master-bin.000001 # Intvar # # INSERT_ID=3 535master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(2)) 536master-bin.000001 # Query # # COMMIT 537/* only (!) with fixes for #23333 will show there is the query */; 538select count(*) from t1 /* must be 3 */; 539count(*) 5403 541reset master; 542select count(*) from t2; 543count(*) 5442 545delete from t2 where a=bug27417(3); 546Warnings: 547Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. 548select count(*) from t2 /* nothing got deleted */; 549count(*) 5502 551include/show_binlog_events.inc 552Log_name Pos Event_type Server_id End_log_pos Info 553master-bin.000001 # Query # # BEGIN 554master-bin.000001 # Intvar # # INSERT_ID=4 555master-bin.000001 # Query # # use `test`; delete from t2 where a=bug27417(3) 556master-bin.000001 # Query # # COMMIT 557/* the query must be in regardless of #23333 */; 558select count(*) from t1 /* must be 5 */; 559count(*) 5605 561delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */; 562affected rows: 0 563Warnings: 564Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. 565select count(*) from t1 /* must be 7 */; 566count(*) 5677 568drop table t1,t2; 569CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; 570CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; 571CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; 572CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; 573CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; 574insert into t2 values (1); 575reset master; 576insert into t2 values (bug27417(1)); 577ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 578include/show_binlog_events.inc 579Log_name Pos Event_type Server_id End_log_pos Info 580master-bin.000001 # Query # # BEGIN 581master-bin.000001 # Intvar # # INSERT_ID=1 582master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(1)) 583master-bin.000001 # Query # # ROLLBACK 584/* the output must denote there is the query */; 585select count(*) from t1 /* must be 1 */; 586count(*) 5871 588delete from t1; 589delete from t2; 590insert into t2 values (2); 591reset master; 592insert into t2 select bug27417(1) union select bug27417(2); 593ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 594include/show_binlog_events.inc 595Log_name Pos Event_type Server_id End_log_pos Info 596master-bin.000001 # Query # # BEGIN 597master-bin.000001 # Intvar # # INSERT_ID=2 598master-bin.000001 # Query # # use `test`; insert into t2 select bug27417(1) union select bug27417(2) 599master-bin.000001 # Query # # ROLLBACK 600/* the output must denote there is the query */; 601select count(*) from t1 /* must be 2 */; 602count(*) 6032 604delete from t1; 605insert into t3 values (1,1),(2,3),(3,4); 606reset master; 607update t3 set b=b+bug27417(1); 608ERROR 23000: Duplicate entry '4' for key 'b' 609include/show_binlog_events.inc 610Log_name Pos Event_type Server_id End_log_pos Info 611master-bin.000001 # Query # # BEGIN 612master-bin.000001 # Intvar # # INSERT_ID=4 613master-bin.000001 # Query # # use `test`; update t3 set b=b+bug27417(1) 614master-bin.000001 # Query # # COMMIT 615/* the output must denote there is the query */; 616select count(*) from t1 /* must be 2 */; 617count(*) 6182 619delete from t3; 620delete from t4; 621insert into t3 values (1,1); 622insert into t4 values (1,1),(2,2); 623reset master; 624UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */; 625ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 626include/show_binlog_events.inc 627Log_name Pos Event_type Server_id End_log_pos Info 628master-bin.000001 # Query # # BEGIN 629master-bin.000001 # Intvar # # INSERT_ID=6 630master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */ 631master-bin.000001 # Query # # ROLLBACK 632/* the output must denote there is the query */; 633select count(*) from t1 /* must be 4 */; 634count(*) 6354 636delete from t1; 637delete from t3; 638delete from t4; 639insert into t3 values (1,1),(2,2); 640insert into t4 values (1,1),(2,2); 641reset master; 642UPDATE t3,t4 SET t3.a=t4.a + bug27417(1); 643ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 644select count(*) from t1 /* must be 1 */; 645count(*) 6461 647drop table t4; 648delete from t1; 649delete from t2; 650delete from t3; 651insert into t2 values (1); 652insert into t3 values (1,1); 653create trigger trg_del before delete on t2 for each row 654insert into t3 values (bug27417(1), 2); 655reset master; 656delete from t2; 657ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 658include/show_binlog_events.inc 659Log_name Pos Event_type Server_id End_log_pos Info 660master-bin.000001 # Query # # BEGIN 661master-bin.000001 # Intvar # # INSERT_ID=9 662master-bin.000001 # Query # # use `test`; delete from t2 663master-bin.000001 # Query # # ROLLBACK 664/* the output must denote there is the query */; 665select count(*) from t1 /* must be 1 */; 666count(*) 6671 668drop trigger trg_del; 669delete from t1; 670delete from t2; 671delete from t5; 672create trigger trg_del_t2 after delete on t2 for each row 673insert into t1 values (1); 674insert into t2 values (2),(3); 675insert into t5 values (1),(2); 676reset master; 677delete t2.* from t2,t5 where t2.a=t5.a + 1; 678ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 679include/show_binlog_events.inc 680Log_name Pos Event_type Server_id End_log_pos Info 681master-bin.000001 # Query # # BEGIN 682master-bin.000001 # Query # # use `test`; delete t2.* from t2,t5 where t2.a=t5.a + 1 683master-bin.000001 # Query # # ROLLBACK 684/* the output must denote there is the query */; 685select count(*) from t1 /* must be 1 */; 686count(*) 6871 688delete from t1; 689create table t4 (a int default 0, b int primary key) engine=innodb; 690insert into t4 values (0, 17); 691reset master; 692load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2); 693ERROR 23000: Duplicate entry '17' for key 'PRIMARY' 694select * from t4; 695a b 6960 17 697select count(*) from t1 /* must be 2 */; 698count(*) 6992 700include/show_binlog_events.inc 701Log_name Pos Event_type Server_id End_log_pos Info 702master-bin.000001 # Query # # BEGIN 703master-bin.000001 # Intvar # # INSERT_ID=10 704master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# 705master-bin.000001 # Intvar # # INSERT_ID=10 706master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @`b`) SET `b`= @b + bug27417(2) ;file_id=# 707master-bin.000001 # Query # # ROLLBACK 708/* the output must denote there is the query */; 709drop trigger trg_del_t2; 710drop table t1,t2,t3,t4,t5; 711drop function bug27417; 712end of tests 713set @@session.binlog_format=statement; 714create temporary table tt (a int unique); 715create table ti (a int) engine=innodb; 716reset master; 717begin; 718insert into ti values (1); 719insert into ti values (2) ; 720insert into tt select * from ti; 721rollback; 722Warnings: 723Warning 1196 Some non-transactional changed tables couldn't be rolled back 724select count(*) from tt /* 2 */; 725count(*) 7262 727include/show_binlog_events.inc 728Log_name Pos Event_type Server_id End_log_pos Info 729master-bin.000001 # Query # # BEGIN 730master-bin.000001 # Query # # use `test`; insert into ti values (1) 731master-bin.000001 # Query # # use `test`; insert into ti values (2) 732master-bin.000001 # Query # # use `test`; insert into tt select * from ti 733master-bin.000001 # Query # # ROLLBACK 734select count(*) from ti /* zero */; 735count(*) 7360 737insert into ti select * from tt; 738select * from ti /* that is what slave would miss - bug#28960 */; 739a 7401 7412 742delete from ti; 743delete from tt where a=1; 744reset master; 745begin; 746insert into ti values (1); 747insert into ti values (2) /* to make the dup error in the following */; 748insert into tt select * from ti /* one affected and error */; 749ERROR 23000: Duplicate entry '2' for key 'a' 750rollback; 751Warnings: 752Warning 1196 Some non-transactional changed tables couldn't be rolled back 753include/show_binlog_events.inc 754Log_name Pos Event_type Server_id End_log_pos Info 755master-bin.000001 # Query # # BEGIN 756master-bin.000001 # Query # # use `test`; insert into ti values (1) 757master-bin.000001 # Query # # use `test`; insert into ti values (2) /* to make the dup error in the following */ 758master-bin.000001 # Query # # use `test`; insert into tt select * from ti /* one affected and error */ 759master-bin.000001 # Query # # ROLLBACK 760select count(*) from ti /* zero */; 761count(*) 7620 763insert into ti select * from tt; 764select * from tt /* that is what otherwise slave missed - the bug */; 765a 7661 7672 768drop table ti; 769drop function if exists bug27417; 770drop table if exists t1,t2; 771CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; 772CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); 773create function bug27417(n int) 774RETURNS int(11) 775begin 776insert into t1 values (null); 777return n; 778end| 779reset master; 780insert into t2 values (bug27417(1)); 781Warnings: 782Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. 783insert into t2 select bug27417(2); 784Warnings: 785Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. 786reset master; 787insert into t2 values (bug27417(2)); 788ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 789include/show_binlog_events.inc 790Log_name Pos Event_type Server_id End_log_pos Info 791master-bin.000001 # Query # # BEGIN 792master-bin.000001 # Intvar # # INSERT_ID=3 793master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(2)) 794master-bin.000001 # Query # # COMMIT 795select count(*) from t1 /* must be 3 */; 796count(*) 7973 798reset master; 799select count(*) from t2; 800count(*) 8012 802delete from t2 where a=bug27417(3); 803Warnings: 804Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. 805select count(*) from t2 /* nothing got deleted */; 806count(*) 8072 808include/show_binlog_events.inc 809Log_name Pos Event_type Server_id End_log_pos Info 810master-bin.000001 # Query # # BEGIN 811master-bin.000001 # Intvar # # INSERT_ID=4 812master-bin.000001 # Query # # use `test`; delete from t2 where a=bug27417(3) 813master-bin.000001 # Query # # COMMIT 814select count(*) from t1 /* must be 5 */; 815count(*) 8165 817delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */; 818affected rows: 0 819Warnings: 820Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. 821select count(*) from t1 /* must be 7 */; 822count(*) 8237 824drop table t1,t2; 825CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; 826CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; 827CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; 828CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; 829CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; 830insert into t2 values (1); 831reset master; 832insert into t2 values (bug27417(1)); 833ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 834include/show_binlog_events.inc 835Log_name Pos Event_type Server_id End_log_pos Info 836master-bin.000001 # Query # # BEGIN 837master-bin.000001 # Intvar # # INSERT_ID=1 838master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(1)) 839master-bin.000001 # Query # # ROLLBACK 840select count(*) from t1 /* must be 1 */; 841count(*) 8421 843delete from t1; 844delete from t2; 845insert into t2 values (2); 846reset master; 847insert into t2 select bug27417(1) union select bug27417(2); 848ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 849include/show_binlog_events.inc 850Log_name Pos Event_type Server_id End_log_pos Info 851master-bin.000001 # Query # # BEGIN 852master-bin.000001 # Intvar # # INSERT_ID=2 853master-bin.000001 # Query # # use `test`; insert into t2 select bug27417(1) union select bug27417(2) 854master-bin.000001 # Query # # ROLLBACK 855select count(*) from t1 /* must be 2 */; 856count(*) 8572 858delete from t1; 859insert into t3 values (1,1),(2,3),(3,4); 860reset master; 861update t3 set b=b+bug27417(1); 862ERROR 23000: Duplicate entry '4' for key 'b' 863include/show_binlog_events.inc 864Log_name Pos Event_type Server_id End_log_pos Info 865master-bin.000001 # Query # # BEGIN 866master-bin.000001 # Intvar # # INSERT_ID=4 867master-bin.000001 # Query # # use `test`; update t3 set b=b+bug27417(1) 868master-bin.000001 # Query # # COMMIT 869select count(*) from t1 /* must be 2 */; 870count(*) 8712 872delete from t3; 873delete from t4; 874insert into t3 values (1,1); 875insert into t4 values (1,1),(2,2); 876reset master; 877UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */; 878ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 879include/show_binlog_events.inc 880Log_name Pos Event_type Server_id End_log_pos Info 881master-bin.000001 # Query # # BEGIN 882master-bin.000001 # Intvar # # INSERT_ID=6 883master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */ 884master-bin.000001 # Query # # ROLLBACK 885select count(*) from t1 /* must be 4 */; 886count(*) 8874 888delete from t1; 889delete from t3; 890delete from t4; 891insert into t3 values (1,1),(2,2); 892insert into t4 values (1,1),(2,2); 893reset master; 894UPDATE t3,t4 SET t3.a = t4.a + bug27417(1) where t3.a = 1; 895ERROR 23000: Duplicate entry '2' for key 'PRIMARY' 896select count(*) from t1 /* must be 1 */; 897count(*) 8981 899drop table t4; 900delete from t1; 901delete from t2; 902delete from t3; 903insert into t2 values (1); 904insert into t3 values (1,1); 905create trigger trg_del before delete on t2 for each row 906insert into t3 values (bug27417(1), 2); 907reset master; 908delete from t2; 909ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 910include/show_binlog_events.inc 911Log_name Pos Event_type Server_id End_log_pos Info 912master-bin.000001 # Query # # BEGIN 913master-bin.000001 # Intvar # # INSERT_ID=9 914master-bin.000001 # Query # # use `test`; delete from t2 915master-bin.000001 # Query # # ROLLBACK 916select count(*) from t1 /* must be 1 */; 917count(*) 9181 919drop trigger trg_del; 920delete from t1; 921delete from t2; 922delete from t5; 923create trigger trg_del_t2 after delete on t2 for each row 924insert into t1 values (1); 925insert into t2 values (2),(3); 926insert into t5 values (1),(2); 927reset master; 928delete t2.* from t2,t5 where t2.a=t5.a + 1; 929ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 930include/show_binlog_events.inc 931Log_name Pos Event_type Server_id End_log_pos Info 932master-bin.000001 # Query # # BEGIN 933master-bin.000001 # Query # # use `test`; delete t2.* from t2,t5 where t2.a=t5.a + 1 934master-bin.000001 # Query # # ROLLBACK 935select count(*) from t1 /* must be 1 */; 936count(*) 9371 938delete from t1; 939create table t4 (a int default 0, b int primary key) engine=innodb; 940insert into t4 values (0, 17); 941reset master; 942load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2); 943ERROR 23000: Duplicate entry '17' for key 'PRIMARY' 944select * from t4; 945a b 9460 17 947select count(*) from t1 /* must be 2 */; 948count(*) 9492 950include/show_binlog_events.inc 951Log_name Pos Event_type Server_id End_log_pos Info 952master-bin.000001 # Query # # BEGIN 953master-bin.000001 # Intvar # # INSERT_ID=10 954master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci 955master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# 956master-bin.000001 # Intvar # # INSERT_ID=10 957master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci 958master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @`b`) SET `b`= @b + bug27417(2) ;file_id=# 959master-bin.000001 # Query # # ROLLBACK 960drop trigger trg_del_t2; 961drop table t1,t2,t3,t4,t5; 962drop function bug27417; 963set @@session.binlog_format=@@global.binlog_format; 964end of tests 965