1source include/have_innodb.inc; 2source include/have_log_bin.inc; 3 4SET BINLOG_FORMAT=MIXED; 5 6RESET MASTER; 7 8CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB; 9INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); 10 11SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 12BEGIN; 13# Should be logged as statement 14UPDATE t1 SET b = 2*a WHERE a > 1; 15COMMIT; 16 17SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 18BEGIN; 19# Should be logged as rows 20UPDATE t1 SET b = a * a WHERE a > 3; 21COMMIT; 22 23# Check that errors are generated when trying to use READ COMMITTED 24# transaction isolation level in STATEMENT binlog mode. 25 26SET BINLOG_FORMAT=STATEMENT; 27 28SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 29BEGIN; 30error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE; 31UPDATE t1 SET b = 1*a WHERE a > 1; 32COMMIT; 33 34SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 35BEGIN; 36error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE; 37UPDATE t1 SET b = 2*a WHERE a > 2; 38COMMIT; 39 40SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 41BEGIN; 42UPDATE t1 SET b = 3*a WHERE a > 3; 43COMMIT; 44 45SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 46BEGIN; 47UPDATE t1 SET b = 4*a WHERE a > 4; 48COMMIT; 49 50SET BINLOG_FORMAT=MIXED; 51 52SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 53BEGIN; 54UPDATE t1 SET b = 1*a WHERE a > 1; 55COMMIT; 56 57SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 58BEGIN; 59UPDATE t1 SET b = 2*a WHERE a > 2; 60COMMIT; 61 62SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 63BEGIN; 64UPDATE t1 SET b = 3*a WHERE a > 3; 65COMMIT; 66 67SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 68BEGIN; 69UPDATE t1 SET b = 4*a WHERE a > 4; 70COMMIT; 71 72SET BINLOG_FORMAT=ROW; 73 74SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 75BEGIN; 76UPDATE t1 SET b = 1*a WHERE a > 1; 77COMMIT; 78 79SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 80BEGIN; 81UPDATE t1 SET b = 2*a WHERE a > 2; 82COMMIT; 83 84SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 85BEGIN; 86UPDATE t1 SET b = 3*a WHERE a > 3; 87COMMIT; 88 89SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 90BEGIN; 91UPDATE t1 SET b = 4*a WHERE a > 4; 92COMMIT; 93 94source include/show_binlog_events.inc; 95 96DROP TABLE t1; 97 98 99# 100# Let us test binlog_cache_use and binlog_cache_disk_use status vars. 101# Actually this test has nothing to do with innodb per se, it just requires 102# transactional table. 103# 104flush status; 105show status like "binlog_cache_use"; 106show status like "binlog_cache_disk_use"; 107 108create table t1 (a int) engine=innodb; 109 110# Now we are going to create transaction which is long enough so its 111# transaction binlog will be flushed to disk... 112let $1=2000; 113disable_query_log; 114begin; 115while ($1) 116{ 117 eval insert into t1 values( $1 ); 118 dec $1; 119} 120commit; 121enable_query_log; 122show status like "binlog_cache_use"; 123show status like "binlog_cache_disk_use"; 124 125# Transaction which should not be flushed to disk and so should not 126# increase binlog_cache_disk_use. 127begin; 128delete from t1; 129commit; 130show status like "binlog_cache_use"; 131show status like "binlog_cache_disk_use"; 132drop table t1; 133 134# 135# Bug#27716 multi-update did partially and has not binlogged 136# 137 138CREATE TABLE `t1` ( 139 `a` int(11) NOT NULL auto_increment, 140 `b` int(11) default NULL, 141 PRIMARY KEY (`a`) 142) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; 143 144CREATE TABLE `t2` ( 145 `a` int(11) NOT NULL auto_increment, 146 `b` int(11) default NULL, 147 PRIMARY KEY (`a`) 148) ENGINE=INNODB DEFAULT CHARSET=latin1 ; 149 150# A. testing multi_update::send_eof() execution branch 151insert into t1 values (1,1),(2,2); 152insert into t2 values (1,1),(4,4); 153reset master; 154--error ER_DUP_ENTRY 155UPDATE t2,t1 SET t2.a=t1.a+2; 156# check 157select * from t2 /* must be (3,1), (4,4) */; 158--echo # There must no UPDATE in binlog; 159source include/show_binlog_events.inc; 160 161# B. testing multi_update::send_error() execution branch 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; 167--error ER_DUP_ENTRY 168UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; 169--echo # There must be no UPDATE query event; 170source include/show_binlog_events.inc; 171 172# cleanup bug#27716 173drop table t1, t2; 174 175--echo *** MDEV-11937: InnoDB flushes redo log too often *** 176 177# Count number of log fsyncs reported by InnoDB per commit. 178CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 179 180SET @old_flush = @@GLOBAL.innodb_flush_log_at_trx_commit; 181SET GLOBAL innodb_flush_log_at_trx_commit=1; 182 183--let $syncs1 = query_get_value(SHOW STATUS LIKE 'Innodb_os_log_fsyncs', Value, 1) 184--let $ROWS = 100 185--disable_query_log 186let $count = $ROWS; 187while ($count) { 188 eval INSERT INTO t1 VALUES ($count); 189 dec $count; 190} 191--let $syncs2 = query_get_value(SHOW STATUS LIKE 'Innodb_os_log_fsyncs', Value, 1) 192eval SET @num_sync = $syncs2 - $syncs1; 193--enable_query_log 194 195# Allow a bit of slack, in case some background process or something 196# is introducing a few more syncs. 197eval SELECT IF(@num_sync < $ROWS*1.5, "OK", 198 CONCAT("ERROR: More than 1 fsync per commit (saw ", @num_sync/$ROWS, ")")) AS status; 199 200DROP TABLE t1; 201SET GLOBAL innodb_flush_log_at_trx_commit=@old_flush; 202 203 204--echo End of tests 205