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 # Gtid # # GTID #-#-# 69master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB 70master-bin.000001 # Gtid # # BEGIN GTID #-#-# 71master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6) 72master-bin.000001 # Xid # # COMMIT /* XID */ 73master-bin.000001 # Gtid # # BEGIN GTID #-#-# 74master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 2*a WHERE a > 1 75master-bin.000001 # Xid # # COMMIT /* XID */ 76master-bin.000001 # Gtid # # BEGIN GTID #-#-# 77master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = a * a WHERE a > 3 78master-bin.000001 # Table_map # # table_id: # (test.t1) 79master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F 80master-bin.000001 # Xid # # COMMIT /* XID */ 81master-bin.000001 # Gtid # # BEGIN GTID #-#-# 82master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3 83master-bin.000001 # Xid # # COMMIT /* XID */ 84master-bin.000001 # Gtid # # BEGIN GTID #-#-# 85master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4 86master-bin.000001 # Xid # # COMMIT /* XID */ 87master-bin.000001 # Gtid # # BEGIN GTID #-#-# 88master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 1*a WHERE a > 1 89master-bin.000001 # Table_map # # table_id: # (test.t1) 90master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F 91master-bin.000001 # Xid # # COMMIT /* XID */ 92master-bin.000001 # Gtid # # BEGIN GTID #-#-# 93master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 2*a WHERE a > 2 94master-bin.000001 # Table_map # # table_id: # (test.t1) 95master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F 96master-bin.000001 # Xid # # COMMIT /* XID */ 97master-bin.000001 # Gtid # # BEGIN GTID #-#-# 98master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3 99master-bin.000001 # Xid # # COMMIT /* XID */ 100master-bin.000001 # Gtid # # BEGIN GTID #-#-# 101master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4 102master-bin.000001 # Xid # # COMMIT /* XID */ 103master-bin.000001 # Gtid # # BEGIN GTID #-#-# 104master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 1*a WHERE a > 1 105master-bin.000001 # Table_map # # table_id: # (test.t1) 106master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F 107master-bin.000001 # Xid # # COMMIT /* XID */ 108master-bin.000001 # Gtid # # BEGIN GTID #-#-# 109master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 2*a WHERE a > 2 110master-bin.000001 # Table_map # # table_id: # (test.t1) 111master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F 112master-bin.000001 # Xid # # COMMIT /* XID */ 113master-bin.000001 # Gtid # # BEGIN GTID #-#-# 114master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 3*a WHERE a > 3 115master-bin.000001 # Table_map # # table_id: # (test.t1) 116master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F 117master-bin.000001 # Xid # # COMMIT /* XID */ 118master-bin.000001 # Gtid # # BEGIN GTID #-#-# 119master-bin.000001 # Annotate_rows # # UPDATE t1 SET b = 4*a WHERE a > 4 120master-bin.000001 # Table_map # # table_id: # (test.t1) 121master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F 122master-bin.000001 # Xid # # COMMIT /* XID */ 123DROP TABLE t1; 124flush status; 125show status like "binlog_cache_use"; 126Variable_name Value 127Binlog_cache_use 0 128show status like "binlog_cache_disk_use"; 129Variable_name Value 130Binlog_cache_disk_use 0 131create table t1 (a int) engine=innodb; 132show status like "binlog_cache_use"; 133Variable_name Value 134Binlog_cache_use 1 135show status like "binlog_cache_disk_use"; 136Variable_name Value 137Binlog_cache_disk_use 1 138begin; 139delete from t1; 140commit; 141show status like "binlog_cache_use"; 142Variable_name Value 143Binlog_cache_use 2 144show status like "binlog_cache_disk_use"; 145Variable_name Value 146Binlog_cache_disk_use 1 147drop table t1; 148CREATE TABLE `t1` ( 149`a` int(11) NOT NULL auto_increment, 150`b` int(11) default NULL, 151PRIMARY KEY (`a`) 152) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; 153CREATE TABLE `t2` ( 154`a` int(11) NOT NULL auto_increment, 155`b` int(11) default NULL, 156PRIMARY KEY (`a`) 157) ENGINE=INNODB DEFAULT CHARSET=latin1 ; 158insert into t1 values (1,1),(2,2); 159insert into t2 values (1,1),(4,4); 160reset master; 161UPDATE t2,t1 SET t2.a=t1.a+2; 162ERROR 23000: Duplicate entry '3' for key 'PRIMARY' 163select * from t2 /* must be (3,1), (4,4) */; 164a b 1651 1 1664 4 167# There must no UPDATE in binlog; 168include/show_binlog_events.inc 169delete from t1; 170delete from t2; 171insert into t1 values (1,2),(3,4),(4,4); 172insert into t2 values (1,2),(3,4),(4,4); 173reset master; 174UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; 175ERROR 23000: Duplicate entry '4' for key 'PRIMARY' 176# There must be no UPDATE query event; 177include/show_binlog_events.inc 178drop table t1, t2; 179*** MDEV-11937: InnoDB flushes redo log too often *** 180CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 181SET @old_flush = @@GLOBAL.innodb_flush_log_at_trx_commit; 182SET GLOBAL innodb_flush_log_at_trx_commit=1; 183SELECT IF(@num_sync < 100*1.5, "OK", 184CONCAT("ERROR: More than 1 fsync per commit (saw ", @num_sync/100, ")")) AS status; 185status 186OK 187DROP TABLE t1; 188SET GLOBAL innodb_flush_log_at_trx_commit=@old_flush; 189End of tests 190