diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/binlog/t/binlog_innodb.test | |
parent | Initial commit. (diff) | |
download | mariadb-upstream.tar.xz mariadb-upstream.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/binlog/t/binlog_innodb.test')
-rw-r--r-- | mysql-test/suite/binlog/t/binlog_innodb.test | 204 |
1 files changed, 204 insertions, 0 deletions
diff --git a/mysql-test/suite/binlog/t/binlog_innodb.test b/mysql-test/suite/binlog/t/binlog_innodb.test new file mode 100644 index 00000000..5f372e6d --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_innodb.test @@ -0,0 +1,204 @@ +source include/have_innodb.inc; +source include/have_log_bin.inc; + +SET BINLOG_FORMAT=MIXED; + +RESET MASTER; + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); + +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +# Should be logged as statement +UPDATE t1 SET b = 2*a WHERE a > 1; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +# Should be logged as rows +UPDATE t1 SET b = a * a WHERE a > 3; +COMMIT; + +# Check that errors are generated when trying to use READ COMMITTED +# transaction isolation level in STATEMENT binlog mode. + +SET BINLOG_FORMAT=STATEMENT; + +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +BEGIN; +error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE; +UPDATE t1 SET b = 1*a WHERE a > 1; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE; +UPDATE t1 SET b = 2*a WHERE a > 2; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +UPDATE t1 SET b = 3*a WHERE a > 3; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; +BEGIN; +UPDATE t1 SET b = 4*a WHERE a > 4; +COMMIT; + +SET BINLOG_FORMAT=MIXED; + +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +BEGIN; +UPDATE t1 SET b = 1*a WHERE a > 1; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +UPDATE t1 SET b = 2*a WHERE a > 2; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +UPDATE t1 SET b = 3*a WHERE a > 3; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; +BEGIN; +UPDATE t1 SET b = 4*a WHERE a > 4; +COMMIT; + +SET BINLOG_FORMAT=ROW; + +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +BEGIN; +UPDATE t1 SET b = 1*a WHERE a > 1; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +UPDATE t1 SET b = 2*a WHERE a > 2; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +UPDATE t1 SET b = 3*a WHERE a > 3; +COMMIT; + +SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; +BEGIN; +UPDATE t1 SET b = 4*a WHERE a > 4; +COMMIT; + +source include/show_binlog_events.inc; + +DROP TABLE t1; + + +# +# Let us test binlog_cache_use and binlog_cache_disk_use status vars. +# Actually this test has nothing to do with innodb per se, it just requires +# transactional table. +# +flush status; +show status like "binlog_cache_use"; +show status like "binlog_cache_disk_use"; + +create table t1 (a int) engine=innodb; + +# Now we are going to create transaction which is long enough so its +# transaction binlog will be flushed to disk... +let $1=2000; +disable_query_log; +begin; +while ($1) +{ + eval insert into t1 values( $1 ); + dec $1; +} +commit; +enable_query_log; +show status like "binlog_cache_use"; +show status like "binlog_cache_disk_use"; + +# Transaction which should not be flushed to disk and so should not +# increase binlog_cache_disk_use. +begin; +delete from t1; +commit; +show status like "binlog_cache_use"; +show status like "binlog_cache_disk_use"; +drop table t1; + +# +# Bug#27716 multi-update did partially and has not binlogged +# + +CREATE TABLE `t1` ( + `a` int(11) NOT NULL auto_increment, + `b` int(11) default NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; + +CREATE TABLE `t2` ( + `a` int(11) NOT NULL auto_increment, + `b` int(11) default NULL, + PRIMARY KEY (`a`) +) ENGINE=INNODB DEFAULT CHARSET=latin1 ; + +# A. testing multi_update::send_eof() execution branch +insert into t1 values (1,1),(2,2); +insert into t2 values (1,1),(4,4); +reset master; +--error ER_DUP_ENTRY +UPDATE t2,t1 SET t2.a=t1.a+2; +# check +select * from t2 /* must be (3,1), (4,4) */; +--echo # There must no UPDATE in binlog; +source include/show_binlog_events.inc; + +# B. testing multi_update::send_error() execution branch +delete from t1; +delete from t2; +insert into t1 values (1,2),(3,4),(4,4); +insert into t2 values (1,2),(3,4),(4,4); +reset master; +--error ER_DUP_ENTRY +UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; +--echo # There must be no UPDATE query event; +source include/show_binlog_events.inc; + +# cleanup bug#27716 +drop table t1, t2; + +--echo *** MDEV-11937: InnoDB flushes redo log too often *** + +# Count number of log fsyncs reported by InnoDB per commit. +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; + +SET @old_flush = @@GLOBAL.innodb_flush_log_at_trx_commit; +SET GLOBAL innodb_flush_log_at_trx_commit=1; + +--let $syncs1 = query_get_value(SHOW STATUS LIKE 'Innodb_data_fsyncs', Value, 1) +--let $ROWS = 100 +--disable_query_log +let $count = $ROWS; +while ($count) { + eval INSERT INTO t1 VALUES ($count); + dec $count; +} +--let $syncs2 = query_get_value(SHOW STATUS LIKE 'Innodb_data_fsyncs', Value, 1) +eval SET @num_sync = $syncs2 - $syncs1; +--enable_query_log + +# Allow a bit of slack, in case some background process or something +# is introducing a few more syncs. +eval SELECT IF(@num_sync < $ROWS*1.5, "OK", + CONCAT("ERROR: More than 1 fsync per commit (saw ", @num_sync/$ROWS, ")")) AS status; + +DROP TABLE t1; +SET GLOBAL innodb_flush_log_at_trx_commit=@old_flush; + + +--echo End of tests |