Edit File: binlog_stm_mix_innodb_myisam.result
CALL mtr.add_suppression("Statement may not be safe to log in statement format."); call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); drop table if exists t1, t2; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; reset master; begin; insert into t1 values(1); insert into t2 select * from t1; Warnings: Note 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. commit; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(1) master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 master-bin.000001 # Xid # # COMMIT /* XID */ delete from t1; delete from t2; reset master; begin; insert into t1 values(2); insert into t2 select * from t1; Warnings: Note 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. rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(2) master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 master-bin.000001 # Query # # ROLLBACK delete from t1; delete from t2; reset master; begin; insert into t1 values(3); savepoint my_savepoint; insert into t1 values(4); insert into t2 select * from t1; Warnings: Note 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. rollback to savepoint my_savepoint; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back commit; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(3) master-bin.000001 # Query # # SAVEPOINT `my_savepoint` master-bin.000001 # Query # # use `test`; insert into t1 values(4) master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 master-bin.000001 # Query # # ROLLBACK TO `my_savepoint` master-bin.000001 # Xid # # COMMIT /* XID */ delete from t1; delete from t2; reset master; begin; insert into t1 values(5); savepoint my_savepoint; insert into t1 values(6); insert into t2 select * from t1; Warnings: Note 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. rollback to savepoint my_savepoint; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back insert into t1 values(7); commit; select a from t1 order by a; a 5 7 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(5) master-bin.000001 # Query # # SAVEPOINT `my_savepoint` master-bin.000001 # Query # # use `test`; insert into t1 values(6) master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 master-bin.000001 # Query # # ROLLBACK TO `my_savepoint` master-bin.000001 # Query # # use `test`; insert into t1 values(7) master-bin.000001 # Xid # # COMMIT /* XID */ delete from t1; delete from t2; reset master; select get_lock("a",10); get_lock("a",10) 1 begin; insert into t1 values(8); insert into t2 select * from t1; Warnings: Note 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. select get_lock("a",10); get_lock("a",10) 1 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(8) master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 master-bin.000001 # Query # # ROLLBACK delete from t1; delete from t2; reset master; insert into t1 values(9); insert into t2 select * from t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(9) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 master-bin.000001 # Query # # COMMIT delete from t1; delete from t2; reset master; insert into t1 values(10); begin; insert into t2 select * from t1; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(10) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 master-bin.000001 # Query # # COMMIT insert into t1 values(11); commit; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(10) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(11) master-bin.000001 # Xid # # COMMIT /* XID */ alter table t2 engine=INNODB; delete from t1; delete from t2; reset master; begin; insert into t1 values(12); insert into t2 select * from t1; commit; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(12) master-bin.000001 # Query # # use `test`; insert into t2 select * from t1 master-bin.000001 # Xid # # COMMIT /* XID */ delete from t1; delete from t2; reset master; begin; insert into t1 values(13); insert into t2 select * from t1; rollback; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info delete from t1; delete from t2; reset master; begin; insert into t1 values(14); savepoint my_savepoint; insert into t1 values(15); insert into t2 select * from t1; rollback to savepoint my_savepoint; commit; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(14) master-bin.000001 # Xid # # COMMIT /* XID */ delete from t1; delete from t2; reset master; begin; insert into t1 values(16); savepoint my_savepoint; insert into t1 values(17); insert into t2 select * from t1; rollback to savepoint my_savepoint; insert into t1 values(18); commit; select a from t1 order by a; a 16 18 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(16) master-bin.000001 # Query # # use `test`; insert into t1 values(18) master-bin.000001 # Xid # # COMMIT /* XID */ delete from t1; delete from t2; alter table t2 engine=MyISAM; insert into t1 values (1); begin; select * from t1 for update; a 1 select (@before:=unix_timestamp())*0; (@before:=unix_timestamp())*0 0 begin; select * from t1 for update; insert into t2 values (20); ERROR HY000: Lock wait timeout exceeded; try restarting transaction select (@after:=unix_timestamp())*0; (@after:=unix_timestamp())*0 0 select (@after-@before) >= 2; (@after-@before) >= 2 1 commit; drop table t1,t2; commit; begin; create temporary table ti (a int) engine=innodb; rollback; insert into ti values(1); set autocommit=0; create temporary table t1 (a int) engine=myisam; commit; insert t1 values (1); rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back create table t0 (n int); insert t0 select * from t1; set autocommit=1; insert into t0 select GET_LOCK("lock1",null); Warnings: Note 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. set autocommit=0; create table t2 (n int) engine=innodb; insert into t2 values (3); select get_lock("lock1",60); get_lock("lock1",60) 1 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values(16) master-bin.000001 # Query # # use `test`; insert into t1 values(18) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; delete from t1 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; delete from t2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # use `test`; alter table t2 engine=MyISAM master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values (1) master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t2 values (20) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t1`,`t2` /* generated by server */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; create temporary table ti (a int) engine=innodb master-bin.000001 # Query # # ROLLBACK master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into ti values(1) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; create temporary table t1 (a int) engine=myisam master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert t1 values (1) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; create table t0 (n int) master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert t0 select * from t1 master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t0 select GET_LOCK("lock1",null) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; create table t2 (n int) engine=innodb master-bin.000001 # Query # # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t1`,`ti` do release_lock("lock1"); drop table t0,t2; set autocommit=0; CREATE TABLE t1 (a int, b int) engine=myisam; reset master; INSERT INTO t1 values (1,1),(1,2); CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' DROP TABLE if exists t2; Warnings: Note 1051 Unknown table 't2' INSERT INTO t1 values (3,3); CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back DROP TABLE IF EXISTS t2; Warnings: Note 1051 Unknown table 't2' CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb; INSERT INTO t1 VALUES (4,4); CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; Warnings: Note 1050 Table 't2' already exists SELECT * from t2; a b TRUNCATE table t2; INSERT INTO t1 VALUES (5,5); INSERT INTO t2 select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' SELECT * FROM t2; a b DROP TABLE t2; INSERT INTO t1 values (6,6); CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ; INSERT INTO t1 values (7,7); Warnings: Note 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. ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back INSERT INTO t1 values (8,8); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; Warnings: Note 1050 Table 't2' already exists COMMIT; INSERT INTO t1 values (9,9); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; Warnings: Note 1050 Table 't2' already exists ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back SELECT * from t2; a b TRUNCATE table t2; INSERT INTO t1 values (10,10); INSERT INTO t2 select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' SELECT * from t1; a b 1 1 1 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10 INSERT INTO t2 values (100,100); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; Warnings: Note 1050 Table 't2' already exists COMMIT; INSERT INTO t2 values (101,101); CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; Warnings: Note 1050 Table 't2' already exists ROLLBACK; SELECT * from t2; a b 100 100 DROP TABLE t1,t2; show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (1,1),(1,2) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (3,3) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */ master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (4,4) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; TRUNCATE table t2 master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (5,5) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (6,6) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (7,7) master-bin.000001 # Query # # ROLLBACK master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (8,8) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (9,9) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; TRUNCATE table t2 master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (10,10) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t2 values (100,100) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TEMPORARY TABLE `t2` /* generated by server */ master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ reset master; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; select get_lock("a",10); get_lock("a",10) 1 begin; insert into t1 values(8); insert into t2 select * from t1; Warnings: Note 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. select get_lock("a",10); get_lock("a",10) 1 flush logs; select (@a:=load_file("MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output")) is not null; (@a:=load_file("MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output")) is not null 1 select @a like "%#%error_code=0%ROLLBACK\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR @a like "%#%error_code=0%ROLLBACK\r\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%", @a not like "%#%error_code=%error_code=%"; @a like "%#%error_code=0%ROLLBACK\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR @a like "%#%error_code=0%ROLLBACK\r\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" @a not like "%#%error_code=%error_code=%" 1 1 drop table t1, t2; create temporary table tt (a int unique); create table ti (a int) engine=innodb; reset master; begin; insert into ti values (1); insert into ti values (2) ; insert into tt select * from ti; rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back select count(*) from tt /* 2 */; count(*) 2 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into ti values (1) master-bin.000001 # Query # # use `test`; insert into ti values (2) master-bin.000001 # Query # # use `test`; insert into tt select * from ti master-bin.000001 # Query # # ROLLBACK select count(*) from ti /* zero */; count(*) 0 insert into ti select * from tt; select * from ti /* that is what slave would miss - a bug */; a 1 2 delete from ti; delete from tt where a=1; reset master; begin; insert into ti values (1); insert into ti values (2) /* to make the dup error in the following */; insert into tt select * from ti /* one affected and error */; ERROR 23000: Duplicate entry '2' for key 'a' rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into ti values (1) master-bin.000001 # Query # # use `test`; insert into ti values (2) /* to make the dup error in the following */ master-bin.000001 # Query # # use `test`; insert into tt select * from ti /* one affected and error */ master-bin.000001 # Query # # ROLLBACK select count(*) from ti /* zero */; count(*) 0 insert into ti select * from tt; select * from tt /* that is what otherwise slave missed - the bug */; a 1 2 drop table ti, tt; drop function if exists bug27417; drop table if exists t1,t2; CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); create function bug27417(n int) RETURNS int(11) begin insert into t1 values (null); return n; end| reset master; insert into t2 values (bug27417(1)); Warnings: Note 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. insert into t2 select bug27417(2); Warnings: Note 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. reset master; insert into t2 values (bug27417(2)); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(2)) master-bin.000001 # Query # # COMMIT /* only (!) with fixes for #23333 will show there is the query */; select count(*) from t1 /* must be 3 */; count(*) 3 reset master; select count(*) from t2; count(*) 2 delete from t2 where a=bug27417(3); Warnings: Note 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. select count(*) from t2 /* nothing got deleted */; count(*) 2 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=4 master-bin.000001 # Query # # use `test`; delete from t2 where a=bug27417(3) master-bin.000001 # Query # # COMMIT /* the query must be in regardless of #23333 */; select count(*) from t1 /* must be 5 */; count(*) 5 delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */; affected rows: 0 Warnings: Note 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. select count(*) from t1 /* must be 7 */; count(*) 7 drop table t1,t2; CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; insert into t2 values (1); reset master; insert into t2 values (bug27417(1)); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=1 master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(1)) master-bin.000001 # Query # # ROLLBACK /* the output must denote there is the query */; select count(*) from t1 /* must be 1 */; count(*) 1 delete from t1; delete from t2; insert into t2 values (2); reset master; insert into t2 select bug27417(1) union select bug27417(2); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=2 master-bin.000001 # Query # # use `test`; insert into t2 select bug27417(1) union select bug27417(2) master-bin.000001 # Query # # ROLLBACK /* the output must denote there is the query */; select count(*) from t1 /* must be 2 */; count(*) 2 delete from t1; insert into t3 values (1,1),(2,3),(3,4); reset master; update t3 set b=b+bug27417(1); ERROR 23000: Duplicate entry '4' for key 'b' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=4 master-bin.000001 # Query # # use `test`; update t3 set b=b+bug27417(1) master-bin.000001 # Query # # COMMIT /* the output must denote there is the query */; select count(*) from t1 /* must be 2 */; count(*) 2 delete from t3; delete from t4; insert into t3 values (1,1); insert into t4 values (1,1),(2,2); reset master; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */; ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=6 master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */ master-bin.000001 # Query # # ROLLBACK /* the output must denote there is the query */; select count(*) from t1 /* must be 4 */; count(*) 4 delete from t1; delete from t3; delete from t4; insert into t3 values (1,1),(2,2); insert into t4 values (1,1),(2,2); reset master; UPDATE t3,t4 SET t3.a=t4.a + bug27417(1); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' select count(*) from t1 /* must be 1 */; count(*) 1 drop table t4; delete from t1; delete from t2; delete from t3; insert into t2 values (1); insert into t3 values (1,1); create trigger trg_del before delete on t2 for each row insert into t3 values (bug27417(1), 2); reset master; delete from t2; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=9 master-bin.000001 # Query # # use `test`; delete from t2 master-bin.000001 # Query # # ROLLBACK /* the output must denote there is the query */; select count(*) from t1 /* must be 1 */; count(*) 1 drop trigger trg_del; delete from t1; delete from t2; delete from t5; create trigger trg_del_t2 after delete on t2 for each row insert into t1 values (1); insert into t2 values (2),(3); insert into t5 values (1),(2); reset master; delete t2.* from t2,t5 where t2.a=t5.a + 1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; delete t2.* from t2,t5 where t2.a=t5.a + 1 master-bin.000001 # Query # # ROLLBACK /* the output must denote there is the query */; select count(*) from t1 /* must be 1 */; count(*) 1 delete from t1; create table t4 (a int default 0, b int primary key) engine=innodb; insert into t4 values (0, 17); reset master; load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2); ERROR 23000: Duplicate entry '17' for key 'PRIMARY' select * from t4; a b 0 17 select count(*) from t1 /* must be 2 */; count(*) 2 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 master-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=# master-bin.000001 # Query # # ROLLBACK /* the output must denote there is the query */; drop trigger trg_del_t2; drop table t1,t2,t3,t4,t5; drop function bug27417; end of tests set @@session.binlog_format=statement; create temporary table tt (a int unique); create table ti (a int) engine=innodb; reset master; begin; insert into ti values (1); insert into ti values (2) ; insert into tt select * from ti; rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back select count(*) from tt /* 2 */; count(*) 2 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into ti values (1) master-bin.000001 # Query # # use `test`; insert into ti values (2) master-bin.000001 # Query # # use `test`; insert into tt select * from ti master-bin.000001 # Query # # ROLLBACK select count(*) from ti /* zero */; count(*) 0 insert into ti select * from tt; select * from ti /* that is what slave would miss - bug#28960 */; a 1 2 delete from ti; delete from tt where a=1; reset master; begin; insert into ti values (1); insert into ti values (2) /* to make the dup error in the following */; insert into tt select * from ti /* one affected and error */; ERROR 23000: Duplicate entry '2' for key 'a' rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into ti values (1) master-bin.000001 # Query # # use `test`; insert into ti values (2) /* to make the dup error in the following */ master-bin.000001 # Query # # use `test`; insert into tt select * from ti /* one affected and error */ master-bin.000001 # Query # # ROLLBACK select count(*) from ti /* zero */; count(*) 0 insert into ti select * from tt; select * from tt /* that is what otherwise slave missed - the bug */; a 1 2 drop table ti; drop function if exists bug27417; drop table if exists t1,t2; CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); create function bug27417(n int) RETURNS int(11) begin insert into t1 values (null); return n; end| reset master; insert into t2 values (bug27417(1)); Warnings: Note 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. insert into t2 select bug27417(2); Warnings: Note 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. reset master; insert into t2 values (bug27417(2)); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=3 master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(2)) master-bin.000001 # Query # # COMMIT select count(*) from t1 /* must be 3 */; count(*) 3 reset master; select count(*) from t2; count(*) 2 delete from t2 where a=bug27417(3); Warnings: Note 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. select count(*) from t2 /* nothing got deleted */; count(*) 2 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=4 master-bin.000001 # Query # # use `test`; delete from t2 where a=bug27417(3) master-bin.000001 # Query # # COMMIT select count(*) from t1 /* must be 5 */; count(*) 5 delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */; affected rows: 0 Warnings: Note 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. select count(*) from t1 /* must be 7 */; count(*) 7 drop table t1,t2; CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; insert into t2 values (1); reset master; insert into t2 values (bug27417(1)); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=1 master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(1)) master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 1 */; count(*) 1 delete from t1; delete from t2; insert into t2 values (2); reset master; insert into t2 select bug27417(1) union select bug27417(2); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=2 master-bin.000001 # Query # # use `test`; insert into t2 select bug27417(1) union select bug27417(2) master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 2 */; count(*) 2 delete from t1; insert into t3 values (1,1),(2,3),(3,4); reset master; update t3 set b=b+bug27417(1); ERROR 23000: Duplicate entry '4' for key 'b' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=4 master-bin.000001 # Query # # use `test`; update t3 set b=b+bug27417(1) master-bin.000001 # Query # # COMMIT select count(*) from t1 /* must be 2 */; count(*) 2 delete from t3; delete from t4; insert into t3 values (1,1); insert into t4 values (1,1),(2,2); reset master; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */; ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=6 master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */ master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 4 */; count(*) 4 delete from t1; delete from t3; delete from t4; insert into t3 values (1,1),(2,2); insert into t4 values (1,1),(2,2); reset master; UPDATE t3,t4 SET t3.a = t4.a + bug27417(1) where t3.a = 1; ERROR 23000: Duplicate entry '2' for key 'PRIMARY' select count(*) from t1 /* must be 1 */; count(*) 1 drop table t4; delete from t1; delete from t2; delete from t3; insert into t2 values (1); insert into t3 values (1,1); create trigger trg_del before delete on t2 for each row insert into t3 values (bug27417(1), 2); reset master; delete from t2; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=9 master-bin.000001 # Query # # use `test`; delete from t2 master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 1 */; count(*) 1 drop trigger trg_del; delete from t1; delete from t2; delete from t5; create trigger trg_del_t2 after delete on t2 for each row insert into t1 values (1); insert into t2 values (2),(3); insert into t5 values (1),(2); reset master; delete t2.* from t2,t5 where t2.a=t5.a + 1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; delete t2.* from t2,t5 where t2.a=t5.a + 1 master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 1 */; count(*) 1 delete from t1; create table t4 (a int default 0, b int primary key) engine=innodb; insert into t4 values (0, 17); reset master; load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2); ERROR 23000: Duplicate entry '17' for key 'PRIMARY' select * from t4; a b 0 17 select count(*) from t1 /* must be 2 */; count(*) 2 show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci master-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=# master-bin.000001 # Query # # ROLLBACK drop trigger trg_del_t2; drop table t1,t2,t3,t4,t5; drop function bug27417; set @@session.binlog_format=@@global.binlog_format; end of tests
Back to File Manager