diff options
Diffstat (limited to 'mysql-test/suite/rpl/include/rpl_insert_delayed.test')
| -rw-r--r-- | mysql-test/suite/rpl/include/rpl_insert_delayed.test | 163 |
1 files changed, 163 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/include/rpl_insert_delayed.test b/mysql-test/suite/rpl/include/rpl_insert_delayed.test new file mode 100644 index 00000000..1a9c4a11 --- /dev/null +++ b/mysql-test/suite/rpl/include/rpl_insert_delayed.test @@ -0,0 +1,163 @@ +# The two bugs below (BUG#25507 and BUG#26116) existed only in +# statement-based binlogging; we test that now they are fixed; +# we also test that mixed and row-based binlogging work too, +# for completeness. + +connection master; +--disable_warnings +CREATE SCHEMA IF NOT EXISTS mysqlslap; +USE mysqlslap; +--enable_warnings + +# +# BUG#25507 "multi-row insert delayed + auto increment causes +# duplicate key entries on slave"; +# happened only in statement-based binlogging. +# + +CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)) ENGINE=MyISAM; +let $query = "INSERT DELAYED INTO t1 VALUES (null, 'Dr. No'), (null, 'From Russia With Love'), (null, 'Goldfinger'), (null, 'Thunderball'), (null, 'You Only Live Twice')"; +--exec $MYSQL_SLAP --silent --concurrency=5 --iterations=200 --query=$query --delimiter=";" + +FLUSH TABLE t1; # another way to be sure INSERT DELAYED has inserted +SELECT COUNT(*) FROM t1; +# when bug existed slave failed below ("duplicate key" error at random INSERT) +sync_slave_with_master; +use mysqlslap; +SELECT COUNT(*) FROM t1; + +# +# BUG#26116 "If multi-row INSERT DELAYED has errors, +# statement-based binlogging breaks"; +# happened only in statement-based binlogging. +# + +connection master; +truncate table t1; +# first scenario: duplicate on first row +insert delayed into t1 values(10, "my name"); +flush table t1; +if (`SELECT @@global.binlog_format = 'STATEMENT'`) +{ + # statement below will be converted to non-delayed INSERT and so + # will stop at first error, guaranteeing replication. + --error ER_DUP_ENTRY + insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); +} +if (`SELECT @@global.binlog_format != 'STATEMENT'`) +{ + insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); +} +flush table t1; +select * from t1; +sync_slave_with_master; +# when bug existed in statement-based binlogging, t1 on slave had +# different content from on master +select * from t1; + +# second scenario: duplicate on second row +connection master; +delete from t1 where id!=10; +if (`SELECT @@global.binlog_format = 'STATEMENT'`) +{ + # statement below will be converted to non-delayed INSERT and so + # will be binlogged with its ER_DUP_ENTRY error code, guaranteeing + # replication (slave will hit the same error code and so be fine). + --error ER_DUP_ENTRY + insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); +} +if (`SELECT @@global.binlog_format != 'STATEMENT'`) +{ + insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); +} +flush table t1; # to wait for INSERT DELAYED to be done +select * from t1; +sync_slave_with_master; +# when bug existed in statement-based binlogging, query was binlogged +# with error_code=0 so slave stopped +select * from t1; + +# clean up +connection master; +USE test; +DROP SCHEMA mysqlslap; +sync_slave_with_master; +use test; +connection master; + +# +# Bug #29571: INSERT DELAYED IGNORE written to binary log on the master but +# on the slave +# +if (`SELECT @@global.binlog_format = 'STATEMENT'`) +{ + #flush the logs before the test + connection slave; + FLUSH LOGS; + source include/wait_for_binlog_checkpoint.inc; + connection master; + FLUSH LOGS; + source include/wait_for_binlog_checkpoint.inc; +} + +CREATE TABLE t1(a int, UNIQUE(a)); +--let $_start= query_get_value(SHOW MASTER STATUS, Position, 1) + +INSERT DELAYED IGNORE INTO t1 VALUES(1); +--disable_warnings +INSERT DELAYED IGNORE INTO t1 VALUES(1); +--enable_warnings +flush table t1; # to wait for INSERT DELAYED to be done +if (`SELECT @@global.binlog_format = 'STATEMENT'`) +{ + #must show two INSERT DELAYED + --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) + + # The first INSERT DELAYED + --let $stmt= query_get_value(SHOW BINLOG EVENTS IN '$binlog_file' FROM $_start, Info, 2) + --echo $stmt + +# The second INSERT DELAYED statement is the 3 item if two INSERT DELAYED are +# handled together + --let $stmt= query_get_value(SHOW BINLOG EVENTS IN '$binlog_file' FROM $_start, Info, 3) + +# The second INSERT DELAYED statement is the 5 item if two INSERT DELAYED are +# handled separately + if ($stmt == COMMIT) + { + --let $stmt= query_get_value(SHOW BINLOG EVENTS IN '$binlog_file' FROM $_start, Info, 5) + } + --echo $stmt +} +select * from t1; + +sync_slave_with_master; +echo On slave; +if (`SELECT @@global.binlog_format = 'STATEMENT'`) +{ + #must show two INSERT DELAYED + --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) + # The show_binlog_events.inc already skips format_description, gtid list, and + # one binlog checkpoint events. Skip three more, second binlog checkpoint, + # Gtid, and CREATE TABLE, so we start at the first DML event + --let $binlog_limit= 3,6 + --source include/show_binlog_events.inc +} +select * from t1; + + +# clean up +connection master; +drop table t1; +sync_slave_with_master; +if (`SELECT @@global.binlog_format = 'STATEMENT'`) +{ + #flush the logs after the test + FLUSH LOGS; + connection master; + FLUSH LOGS; +} +connection master; + + +--echo End of 5.0 tests |