1 source include/master-slave.inc;
2 source include/have_innodb.inc;
4 --echo **** On Slave ****
6 source include/have_innodb.inc;
9 --echo **** On Master ****
11 SET SESSION BINLOG_FORMAT=ROW;
13 CREATE TABLE t1 (a INT, b INT);
14 CREATE TABLE t2 (c INT, d INT);
15 INSERT INTO t1 VALUES (1,1),(2,4),(3,9);
16 INSERT INTO t2 VALUES (1,1),(2,8),(3,27);
17 let $master_log_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
18 UPDATE t1,t2 SET b = d, d = b * 2 WHERE a = c;
19 source include/show_binlog_events.inc;
21 # These tables should be changed
25 --echo **** On Slave ****
28 # Stop when reaching the the first table map event.
29 --replace_result $master_log_pos MASTER_LOG_POS
30 eval START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=$master_log_pos;
31 source include/wait_for_slave_sql_to_stop.inc;
32 let $slave_param= Exec_Master_Log_Pos;
33 let $slave_param_value= $master_log_pos;
34 source include/check_slave_param.inc;
35 source include/check_slave_no_error.inc;
37 # Now we skip *one* table map event. If the execution starts right
38 # after that table map event, *one* of the involved tables will be
40 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
44 # These values should be what was inserted, not what was
45 # updated. Since we are skipping the first table map of the group
46 # representing the UPDATE statement above, we should skip the entire
47 # group and not start executing at the first table map.
56 SET SESSION BINLOG_FORMAT=STATEMENT;
58 let $master_log_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
59 INSERT INTO t1 VALUES(@foo, 2*@foo);
61 source include/show_binlog_events.inc;
64 --replace_result $master_log_pos MASTER_LOG_POS
65 eval START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=$master_log_pos;
66 source include/wait_for_slave_sql_to_stop.inc;
67 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
71 --echo **** On Master ****
74 sync_slave_with_master;
77 # More tests for BUG#28618
80 # ROW binlog format and non-transactional tables.
81 # Create the group of events via triggers and try to skip
82 # some items of that group.
86 SET SESSION BINLOG_FORMAT=ROW;
89 CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam;
90 CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam;
91 CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam;
93 INSERT INTO t1 VALUES (1,'master/slave');
94 INSERT INTO t2 VALUES (1,'master/slave');
95 INSERT INTO t3 VALUES (1,'master/slave');
99 CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW
101 INSERT INTO t2 VALUES (NEW.a,NEW.b);
102 DELETE FROM t2 WHERE a < NEW.a;
105 CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW
107 UPDATE t3 SET a =2, b = 'master only';
112 --echo **** On Slave ****
113 sync_slave_with_master;
114 source include/stop_slave.inc;
116 --echo **** On Master ****
118 UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1;
121 INSERT INTO t1 VALUES (3,'master/slave');
122 INSERT INTO t2 VALUES (3,'master/slave');
123 INSERT INTO t3 VALUES (3,'master/slave');
126 SELECT * FROM t1 ORDER BY a;
127 SELECT * FROM t2 ORDER BY a;
128 SELECT * FROM t3 ORDER BY a;
132 --echo *** On Slave ***
134 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
135 source include/start_slave.inc;
138 SELECT * FROM t1 ORDER BY a;
139 SELECT * FROM t2 ORDER BY a;
140 SELECT * FROM t3 ORDER BY a;
143 DROP TABLE t1, t2, t3;
144 sync_slave_with_master;
146 --echo **** Case 2: Row binlog format and transactional tables ****
148 # Create the transaction and try to skip some
151 --echo *** On Master ***
153 CREATE TABLE t4 (a INT, b VARCHAR(20)) ENGINE=innodb;
154 CREATE TABLE t5 (a INT, b VARCHAR(20)) ENGINE=innodb;
155 CREATE TABLE t6 (a INT, b VARCHAR(20)) ENGINE=innodb;
157 --echo **** On Slave ****
158 sync_slave_with_master;
159 source include/stop_slave.inc;
161 --echo *** On Master ***
164 INSERT INTO t4 VALUES (2, 'master only');
165 INSERT INTO t5 VALUES (2, 'master only');
166 INSERT INTO t6 VALUES (2, 'master only');
170 INSERT INTO t4 VALUES (3, 'master/slave');
171 INSERT INTO t5 VALUES (3, 'master/slave');
172 INSERT INTO t6 VALUES (3, 'master/slave');
175 SELECT * FROM t4 ORDER BY a;
176 SELECT * FROM t5 ORDER BY a;
177 SELECT * FROM t6 ORDER BY a;
181 --echo *** On Slave ***
183 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
184 source include/start_slave.inc;
187 SELECT * FROM t4 ORDER BY a;
188 SELECT * FROM t5 ORDER BY a;
189 SELECT * FROM t6 ORDER BY a;
191 # Test skipping two groups
193 --echo **** On Slave ****
195 source include/stop_slave.inc;
197 --echo *** On Master ***
200 INSERT INTO t4 VALUES (6, 'master only');
201 INSERT INTO t5 VALUES (6, 'master only');
202 INSERT INTO t6 VALUES (6, 'master only');
206 INSERT INTO t4 VALUES (7, 'master only');
207 INSERT INTO t5 VALUES (7, 'master only');
208 INSERT INTO t6 VALUES (7, 'master only');
211 SELECT * FROM t4 ORDER BY a;
212 SELECT * FROM t5 ORDER BY a;
213 SELECT * FROM t6 ORDER BY a;
217 --echo *** On Slave ***
219 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=10;
220 source include/start_slave.inc;
223 SELECT * FROM t4 ORDER BY a;
224 SELECT * FROM t5 ORDER BY a;
225 SELECT * FROM t6 ORDER BY a;
228 # And the same, but with autocommit = 0
231 source include/stop_slave.inc;
236 INSERT INTO t4 VALUES (4, 'master only');
237 INSERT INTO t5 VALUES (4, 'master only');
238 INSERT INTO t6 VALUES (4, 'master only');
241 INSERT INTO t4 VALUES (5, 'master/slave');
242 INSERT INTO t5 VALUES (5, 'master/slave');
243 INSERT INTO t6 VALUES (5, 'master/slave');
246 SELECT * FROM t4 ORDER BY a;
247 SELECT * FROM t5 ORDER BY a;
248 SELECT * FROM t6 ORDER BY a;
252 --echo *** On Slave ***
254 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
255 source include/start_slave.inc;
258 SELECT * FROM t4 ORDER BY a;
259 SELECT * FROM t5 ORDER BY a;
260 SELECT * FROM t6 ORDER BY a;
263 DROP TABLE t4, t5, t6;
264 sync_slave_with_master;
266 --echo **** Case 3: Statement logging format and LOAD DATA with non-transactional table ****
268 # LOAD DATA creates two events in binary log for statement binlog format.
269 # Try to skip the first.
271 --echo *** On Master ***
273 CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam;
275 --echo *** On Slave ***
276 sync_slave_with_master;
277 source include/stop_slave.inc;
279 --echo *** On Master ***
281 SET SESSION BINLOG_FORMAT=STATEMENT;
283 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
284 eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/std_data/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|';
286 SELECT * FROM t10 ORDER BY a;
290 --echo *** On Slave ***
292 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
293 source include/start_slave.inc;
296 SELECT * FROM t10 ORDER BY a;
300 sync_slave_with_master;
302 --source include/rpl_end.inc