1SET BINLOG_FORMAT=MIXED; 2RESET MASTER; 3CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB; 4INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); 5SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 6BEGIN; 7UPDATE t1 SET b = 2*a WHERE a > 1; 8COMMIT; 9SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 10BEGIN; 11UPDATE t1 SET b = a * a WHERE a > 3; 12COMMIT; 13SET BINLOG_FORMAT=STATEMENT; 14SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 15BEGIN; 16UPDATE t1 SET b = 1*a WHERE a > 1; 17ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. 18COMMIT; 19SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 20BEGIN; 21UPDATE t1 SET b = 2*a WHERE a > 2; 22ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. 23COMMIT; 24SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 25BEGIN; 26UPDATE t1 SET b = 3*a WHERE a > 3; 27COMMIT; 28SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 29BEGIN; 30UPDATE t1 SET b = 4*a WHERE a > 4; 31COMMIT; 32SET BINLOG_FORMAT=MIXED; 33SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 34BEGIN; 35UPDATE t1 SET b = 1*a WHERE a > 1; 36COMMIT; 37SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 38BEGIN; 39UPDATE t1 SET b = 2*a WHERE a > 2; 40COMMIT; 41SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 42BEGIN; 43UPDATE t1 SET b = 3*a WHERE a > 3; 44COMMIT; 45SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 46BEGIN; 47UPDATE t1 SET b = 4*a WHERE a > 4; 48COMMIT; 49SET BINLOG_FORMAT=ROW; 50SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 51BEGIN; 52UPDATE t1 SET b = 1*a WHERE a > 1; 53COMMIT; 54SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 55BEGIN; 56UPDATE t1 SET b = 2*a WHERE a > 2; 57COMMIT; 58SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 59BEGIN; 60UPDATE t1 SET b = 3*a WHERE a > 3; 61COMMIT; 62SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 63BEGIN; 64UPDATE t1 SET b = 4*a WHERE a > 4; 65COMMIT; 66include/show_binlog_events.inc 67Log_name Pos Event_type Server_id End_log_pos Info 68master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB 69master-bin.000001 # Query # # BEGIN 70master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6) 71master-bin.000001 # Xid # # COMMIT /* XID */ 72master-bin.000001 # Query # # BEGIN 73master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 2*a WHERE a > 1 74master-bin.000001 # Xid # # COMMIT /* XID */ 75master-bin.000001 # Query # # BEGIN 76master-bin.000001 # Table_map # # table_id: # (test.t1) 77master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F 78master-bin.000001 # Xid # # COMMIT /* XID */ 79master-bin.000001 # Query # # BEGIN 80master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3 81master-bin.000001 # Xid # # COMMIT /* XID */ 82master-bin.000001 # Query # # BEGIN 83master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4 84master-bin.000001 # Xid # # COMMIT /* XID */ 85master-bin.000001 # Query # # BEGIN 86master-bin.000001 # Table_map # # table_id: # (test.t1) 87master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F 88master-bin.000001 # Xid # # COMMIT /* XID */ 89master-bin.000001 # Query # # BEGIN 90master-bin.000001 # Table_map # # table_id: # (test.t1) 91master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F 92master-bin.000001 # Xid # # COMMIT /* XID */ 93master-bin.000001 # Query # # BEGIN 94master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3 95master-bin.000001 # Xid # # COMMIT /* XID */ 96master-bin.000001 # Query # # BEGIN 97master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4 98master-bin.000001 # Xid # # COMMIT /* XID */ 99master-bin.000001 # Query # # BEGIN 100master-bin.000001 # Table_map # # table_id: # (test.t1) 101master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F 102master-bin.000001 # Xid # # COMMIT /* XID */ 103master-bin.000001 # Query # # BEGIN 104master-bin.000001 # Table_map # # table_id: # (test.t1) 105master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F 106master-bin.000001 # Xid # # COMMIT /* XID */ 107master-bin.000001 # Query # # BEGIN 108master-bin.000001 # Table_map # # table_id: # (test.t1) 109master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F 110master-bin.000001 # Xid # # COMMIT /* XID */ 111master-bin.000001 # Query # # BEGIN 112master-bin.000001 # Table_map # # table_id: # (test.t1) 113master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F 114master-bin.000001 # Xid # # COMMIT /* XID */ 115DROP TABLE t1; 116flush status; 117show status like "binlog_cache_use"; 118Variable_name Value 119Binlog_cache_use 0 120show status like "binlog_cache_disk_use"; 121Variable_name Value 122Binlog_cache_disk_use 0 123create table t1 (a int) engine=innodb; 124show status like "binlog_cache_use"; 125Variable_name Value 126Binlog_cache_use 1 127show status like "binlog_cache_disk_use"; 128Variable_name Value 129Binlog_cache_disk_use 1 130begin; 131delete from t1; 132commit; 133show status like "binlog_cache_use"; 134Variable_name Value 135Binlog_cache_use 2 136show status like "binlog_cache_disk_use"; 137Variable_name Value 138Binlog_cache_disk_use 1 139drop table t1; 140CREATE TABLE `t1` ( 141`a` int(11) NOT NULL auto_increment, 142`b` int(11) default NULL, 143PRIMARY KEY (`a`) 144) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; 145CREATE TABLE `t2` ( 146`a` int(11) NOT NULL auto_increment, 147`b` int(11) default NULL, 148PRIMARY KEY (`a`) 149) ENGINE=INNODB DEFAULT CHARSET=latin1 ; 150insert into t1 values (1,1),(2,2); 151insert into t2 values (1,1),(4,4); 152reset master; 153UPDATE t2,t1 SET t2.a=t1.a+2; 154ERROR 23000: Duplicate entry '3' for key 'PRIMARY' 155select * from t2 /* must be (3,1), (4,4) */; 156a b 1571 1 1584 4 159# There must no UPDATE in binlog; 160include/show_binlog_events.inc 161Log_name Pos Event_type Server_id End_log_pos Info 162delete from t1; 163delete from t2; 164insert into t1 values (1,2),(3,4),(4,4); 165insert into t2 values (1,2),(3,4),(4,4); 166reset master; 167UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; 168ERROR 23000: Duplicate entry '4' for key 'PRIMARY' 169# There must be no UPDATE query event; 170include/show_binlog_events.inc 171Log_name Pos Event_type Server_id End_log_pos Info 172drop table t1, t2; 173End of tests 174