summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/rpl/t/rpl_slave_skip.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/rpl/t/rpl_slave_skip.test
parentInitial commit. (diff)
downloadmariadb-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/rpl/t/rpl_slave_skip.test')
-rw-r--r--mysql-test/suite/rpl/t/rpl_slave_skip.test293
1 files changed, 293 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/t/rpl_slave_skip.test b/mysql-test/suite/rpl/t/rpl_slave_skip.test
new file mode 100644
index 00000000..933e7d14
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_slave_skip.test
@@ -0,0 +1,293 @@
+# Every statement in this test is either executing under ROW or
+# STATEMENT format, which requires the slave thread to be able to apply
+# both statement and row events. Hence, we only need to execute this
+# test for MIXED mode.
+source include/have_binlog_format_mixed.inc;
+
+source include/have_innodb.inc;
+source include/master-slave.inc;
+
+connection slave;
+source include/have_innodb.inc;
+STOP SLAVE;
+--source include/wait_for_slave_to_stop.inc
+
+connection master;
+SET SESSION BINLOG_FORMAT=ROW;
+
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+CREATE TABLE t2 (c INT, d INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1),(2,4),(3,9);
+INSERT INTO t2 VALUES (1,1),(2,8),(3,27);
+let $master_log_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
+UPDATE t1,t2 SET b = d, d = b * 2 WHERE a = c;
+source include/show_binlog_events.inc;
+
+# These tables should be changed
+SELECT * FROM t1;
+SELECT * FROM t2;
+save_master_pos;
+connection slave;
+
+# Stop when reaching the the first table map event.
+--replace_result $master_log_pos MASTER_LOG_POS
+eval START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=$master_log_pos;
+source include/wait_for_slave_sql_to_stop.inc;
+let $slave_param= Exec_Master_Log_Pos;
+let $slave_param_value= $master_log_pos;
+source include/check_slave_param.inc;
+source include/check_slave_no_error.inc;
+
+# Now we skip *one* table map event. If the execution starts right
+# after that table map event, *one* of the involved tables will be
+# changed.
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
+START SLAVE;
+--source include/wait_for_slave_to_start.inc
+sync_with_master;
+
+# These values should be what was inserted, not what was
+# updated. Since we are skipping the first table map of the group
+# representing the UPDATE statement above, we should skip the entire
+# group and not start executing at the first table map.
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+STOP SLAVE;
+--source include/wait_for_slave_to_stop.inc
+--source include/reset_slave.inc
+connection master;
+RESET MASTER;
+
+SET SESSION BINLOG_FORMAT=STATEMENT;
+SET @foo = 12;
+let $master_log_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
+INSERT INTO t1 VALUES(@foo, 2*@foo);
+save_master_pos;
+source include/show_binlog_events.inc;
+
+connection slave;
+--replace_result $master_log_pos MASTER_LOG_POS
+eval START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=$master_log_pos;
+source include/wait_for_slave_sql_to_stop.inc;
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
+START SLAVE;
+--source include/wait_for_slave_to_start.inc
+sync_with_master;
+
+connection master;
+DROP TABLE t1, t2;
+sync_slave_with_master;
+
+#
+# More tests for BUG#28618
+#
+# Case 1.
+# ROW binlog format and non-transactional tables.
+# Create the group of events via triggers and try to skip
+# some items of that group.
+#
+
+connection master;
+SET SESSION BINLOG_FORMAT=ROW;
+SET AUTOCOMMIT=0;
+
+CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam;
+CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam;
+CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam;
+
+INSERT INTO t1 VALUES (1,'master/slave');
+INSERT INTO t2 VALUES (1,'master/slave');
+INSERT INTO t3 VALUES (1,'master/slave');
+
+DELIMITER |;
+
+CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW
+BEGIN
+ INSERT INTO t2 VALUES (NEW.a,NEW.b);
+ DELETE FROM t2 WHERE a < NEW.a;
+END|
+
+CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW
+BEGIN
+ UPDATE t3 SET a =2, b = 'master only';
+END|
+
+DELIMITER ;|
+
+sync_slave_with_master;
+source include/stop_slave.inc;
+
+connection master;
+UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1;
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+INSERT INTO t1 VALUES (3,'master/slave');
+INSERT INTO t2 VALUES (3,'master/slave');
+INSERT INTO t3 VALUES (3,'master/slave');
+COMMIT;
+
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+SELECT * FROM t3 ORDER BY a;
+
+save_master_pos;
+
+connection slave;
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
+source include/start_slave.inc;
+sync_with_master;
+
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+SELECT * FROM t3 ORDER BY a;
+
+connection master;
+DROP TABLE t1, t2, t3;
+sync_slave_with_master;
+
+--echo **** Case 2: Row binlog format and transactional tables ****
+
+# Create the transaction and try to skip some
+# queries from one.
+
+connection master;
+CREATE TABLE t4 (a INT, b VARCHAR(20)) ENGINE=innodb;
+CREATE TABLE t5 (a INT, b VARCHAR(20)) ENGINE=innodb;
+CREATE TABLE t6 (a INT, b VARCHAR(20)) ENGINE=innodb;
+
+sync_slave_with_master;
+source include/stop_slave.inc;
+
+connection master;
+BEGIN;
+INSERT INTO t4 VALUES (2, 'master only');
+INSERT INTO t5 VALUES (2, 'master only');
+INSERT INTO t6 VALUES (2, 'master only');
+COMMIT;
+
+BEGIN;
+INSERT INTO t4 VALUES (3, 'master/slave');
+INSERT INTO t5 VALUES (3, 'master/slave');
+INSERT INTO t6 VALUES (3, 'master/slave');
+COMMIT;
+
+SELECT * FROM t4 ORDER BY a;
+SELECT * FROM t5 ORDER BY a;
+SELECT * FROM t6 ORDER BY a;
+
+save_master_pos;
+
+connection slave;
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
+source include/start_slave.inc;
+sync_with_master;
+
+SELECT * FROM t4 ORDER BY a;
+SELECT * FROM t5 ORDER BY a;
+SELECT * FROM t6 ORDER BY a;
+
+# Test skipping two groups
+
+connection slave;
+source include/stop_slave.inc;
+
+connection master;
+BEGIN;
+INSERT INTO t4 VALUES (6, 'master only');
+INSERT INTO t5 VALUES (6, 'master only');
+INSERT INTO t6 VALUES (6, 'master only');
+COMMIT;
+
+BEGIN;
+INSERT INTO t4 VALUES (7, 'master only');
+INSERT INTO t5 VALUES (7, 'master only');
+INSERT INTO t6 VALUES (7, 'master only');
+COMMIT;
+
+SELECT * FROM t4 ORDER BY a;
+SELECT * FROM t5 ORDER BY a;
+SELECT * FROM t6 ORDER BY a;
+
+save_master_pos;
+
+connection slave;
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=12;
+source include/start_slave.inc;
+sync_with_master;
+
+SELECT * FROM t4 ORDER BY a;
+SELECT * FROM t5 ORDER BY a;
+SELECT * FROM t6 ORDER BY a;
+
+#
+# And the same, but with autocommit = 0
+#
+connection slave;
+source include/stop_slave.inc;
+
+connection master;
+SET AUTOCOMMIT=0;
+
+INSERT INTO t4 VALUES (4, 'master only');
+INSERT INTO t5 VALUES (4, 'master only');
+INSERT INTO t6 VALUES (4, 'master only');
+COMMIT;
+
+INSERT INTO t4 VALUES (5, 'master/slave');
+INSERT INTO t5 VALUES (5, 'master/slave');
+INSERT INTO t6 VALUES (5, 'master/slave');
+COMMIT;
+
+SELECT * FROM t4 ORDER BY a;
+SELECT * FROM t5 ORDER BY a;
+SELECT * FROM t6 ORDER BY a;
+
+save_master_pos;
+
+connection slave;
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
+source include/start_slave.inc;
+sync_with_master;
+
+SELECT * FROM t4 ORDER BY a;
+SELECT * FROM t5 ORDER BY a;
+SELECT * FROM t6 ORDER BY a;
+
+connection master;
+DROP TABLE t4, t5, t6;
+sync_slave_with_master;
+
+--echo **** Case 3: Statement logging format and LOAD DATA with non-transactional table ****
+
+# LOAD DATA creates two events in binary log for statement binlog format.
+# Try to skip the first.
+
+connection master;
+CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam;
+
+sync_slave_with_master;
+source include/stop_slave.inc;
+
+connection master;
+SET SESSION BINLOG_FORMAT=STATEMENT;
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/std_data/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|';
+
+SELECT * FROM t10 ORDER BY a;
+
+save_master_pos;
+
+connection slave;
+SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
+source include/start_slave.inc;
+sync_with_master;
+
+SELECT * FROM t10 ORDER BY a;
+
+connection master;
+DROP TABLE t10;
+sync_slave_with_master;
+
+--source include/rpl_end.inc