Debian Codegraph @

mysql-test/suite/rpl/t/rpl_slave_skip.test

raw
1 # Every statement in this test is either executing under ROW or
2 # STATEMENT format, which requires the slave thread to be able to apply
3 # both statement and row events. Hence, we only need to execute this
4 # test for MIXED mode.
5 source include/have_binlog_format_mixed.inc;
6
7 source include/have_innodb.inc;
8 source include/master-slave.inc;
9
10 connection slave;
11 source include/have_innodb.inc;
12 STOP SLAVE;
13 --source include/wait_for_slave_to_stop.inc
14
15 connection master;
16 SET SESSION BINLOG_FORMAT=ROW;
17
18 CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
19 CREATE TABLE t2 (c INT, d INT) ENGINE=MyISAM;
20 INSERT INTO t1 VALUES (1,1),(2,4),(3,9);
21 INSERT INTO t2 VALUES (1,1),(2,8),(3,27);
22 let $master_log_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
23 UPDATE t1,t2 SET b = d, d = b * 2 WHERE a = c;
24 source include/show_binlog_events.inc;
25
26 # These tables should be changed
27 SELECT * FROM t1;
28 SELECT * FROM t2;
29 save_master_pos;
30 connection slave;
31
32 # Stop when reaching the the first table map event.
33 --replace_result $master_log_pos MASTER_LOG_POS
34 eval START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=$master_log_pos;
35 source include/wait_for_slave_sql_to_stop.inc;
36 let $slave_param= Exec_Master_Log_Pos;
37 let $slave_param_value= $master_log_pos;
38 source include/check_slave_param.inc;
39 source include/check_slave_no_error.inc;
40
41 # Now we skip *one* table map event. If the execution starts right
42 # after that table map event, *one* of the involved tables will be
43 # changed.
44 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
45 START SLAVE;
46 --source include/wait_for_slave_to_start.inc
47 sync_with_master;
48
49 # These values should be what was inserted, not what was
50 # updated. Since we are skipping the first table map of the group
51 # representing the UPDATE statement above, we should skip the entire
52 # group and not start executing at the first table map.
53 SELECT * FROM t1;
54 SELECT * FROM t2;
55
56 STOP SLAVE;
57 --source include/wait_for_slave_to_stop.inc
58 --source include/reset_slave.inc
59 connection master;
60 RESET MASTER;
61
62 SET SESSION BINLOG_FORMAT=STATEMENT;
63 SET @foo = 12;
64 let $master_log_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
65 INSERT INTO t1 VALUES(@foo, 2*@foo);
66 save_master_pos;
67 source include/show_binlog_events.inc;
68
69 connection slave;
70 --replace_result $master_log_pos MASTER_LOG_POS
71 eval START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=$master_log_pos;
72 source include/wait_for_slave_sql_to_stop.inc;
73 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
74 START SLAVE;
75 --source include/wait_for_slave_to_start.inc
76 sync_with_master;
77
78 connection master;
79 DROP TABLE t1, t2;
80 sync_slave_with_master;
81
82 #
83 # More tests for BUG#28618
84 #
85 # Case 1.
86 # ROW binlog format and non-transactional tables.
87 # Create the group of events via triggers and try to skip
88 # some items of that group.
89 #
90
91 connection master;
92 SET SESSION BINLOG_FORMAT=ROW;
93 SET AUTOCOMMIT=0;
94
95 CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam;
96 CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam;
97 CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam;
98
99 INSERT INTO t1 VALUES (1,'master/slave');
100 INSERT INTO t2 VALUES (1,'master/slave');
101 INSERT INTO t3 VALUES (1,'master/slave');
102
103 DELIMITER |;
104
105 CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW
106 BEGIN
107 INSERT INTO t2 VALUES (NEW.a,NEW.b);
108 DELETE FROM t2 WHERE a < NEW.a;
109 END|
110
111 CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW
112 BEGIN
113 UPDATE t3 SET a =2, b = 'master only';
114 END|
115
116 DELIMITER ;|
117
118 sync_slave_with_master;
119 source include/stop_slave.inc;
120
121 connection master;
122 UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1;
123 DROP TRIGGER tr1;
124 DROP TRIGGER tr2;
125 INSERT INTO t1 VALUES (3,'master/slave');
126 INSERT INTO t2 VALUES (3,'master/slave');
127 INSERT INTO t3 VALUES (3,'master/slave');
128 COMMIT;
129
130 SELECT * FROM t1 ORDER BY a;
131 SELECT * FROM t2 ORDER BY a;
132 SELECT * FROM t3 ORDER BY a;
133
134 save_master_pos;
135
136 connection slave;
137 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
138 source include/start_slave.inc;
139 sync_with_master;
140
141 SELECT * FROM t1 ORDER BY a;
142 SELECT * FROM t2 ORDER BY a;
143 SELECT * FROM t3 ORDER BY a;
144
145 connection master;
146 DROP TABLE t1, t2, t3;
147 sync_slave_with_master;
148
149 --echo **** Case 2: Row binlog format and transactional tables ****
150
151 # Create the transaction and try to skip some
152 # queries from one.
153
154 connection master;
155 CREATE TABLE t4 (a INT, b VARCHAR(20)) ENGINE=innodb;
156 CREATE TABLE t5 (a INT, b VARCHAR(20)) ENGINE=innodb;
157 CREATE TABLE t6 (a INT, b VARCHAR(20)) ENGINE=innodb;
158
159 sync_slave_with_master;
160 source include/stop_slave.inc;
161
162 connection master;
163 BEGIN;
164 INSERT INTO t4 VALUES (2, 'master only');
165 INSERT INTO t5 VALUES (2, 'master only');
166 INSERT INTO t6 VALUES (2, 'master only');
167 COMMIT;
168
169 BEGIN;
170 INSERT INTO t4 VALUES (3, 'master/slave');
171 INSERT INTO t5 VALUES (3, 'master/slave');
172 INSERT INTO t6 VALUES (3, 'master/slave');
173 COMMIT;
174
175 SELECT * FROM t4 ORDER BY a;
176 SELECT * FROM t5 ORDER BY a;
177 SELECT * FROM t6 ORDER BY a;
178
179 save_master_pos;
180
181 connection slave;
182 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
183 source include/start_slave.inc;
184 sync_with_master;
185
186 SELECT * FROM t4 ORDER BY a;
187 SELECT * FROM t5 ORDER BY a;
188 SELECT * FROM t6 ORDER BY a;
189
190 # Test skipping two groups
191
192 connection slave;
193 source include/stop_slave.inc;
194
195 connection master;
196 BEGIN;
197 INSERT INTO t4 VALUES (6, 'master only');
198 INSERT INTO t5 VALUES (6, 'master only');
199 INSERT INTO t6 VALUES (6, 'master only');
200 COMMIT;
201
202 BEGIN;
203 INSERT INTO t4 VALUES (7, 'master only');
204 INSERT INTO t5 VALUES (7, 'master only');
205 INSERT INTO t6 VALUES (7, 'master only');
206 COMMIT;
207
208 SELECT * FROM t4 ORDER BY a;
209 SELECT * FROM t5 ORDER BY a;
210 SELECT * FROM t6 ORDER BY a;
211
212 save_master_pos;
213
214 connection slave;
215 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=12;
216 source include/start_slave.inc;
217 sync_with_master;
218
219 SELECT * FROM t4 ORDER BY a;
220 SELECT * FROM t5 ORDER BY a;
221 SELECT * FROM t6 ORDER BY a;
222
223 #
224 # And the same, but with autocommit = 0
225 #
226 connection slave;
227 source include/stop_slave.inc;
228
229 connection master;
230 SET AUTOCOMMIT=0;
231
232 INSERT INTO t4 VALUES (4, 'master only');
233 INSERT INTO t5 VALUES (4, 'master only');
234 INSERT INTO t6 VALUES (4, 'master only');
235 COMMIT;
236
237 INSERT INTO t4 VALUES (5, 'master/slave');
238 INSERT INTO t5 VALUES (5, 'master/slave');
239 INSERT INTO t6 VALUES (5, 'master/slave');
240 COMMIT;
241
242 SELECT * FROM t4 ORDER BY a;
243 SELECT * FROM t5 ORDER BY a;
244 SELECT * FROM t6 ORDER BY a;
245
246 save_master_pos;
247
248 connection slave;
249 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
250 source include/start_slave.inc;
251 sync_with_master;
252
253 SELECT * FROM t4 ORDER BY a;
254 SELECT * FROM t5 ORDER BY a;
255 SELECT * FROM t6 ORDER BY a;
256
257 connection master;
258 DROP TABLE t4, t5, t6;
259 sync_slave_with_master;
260
261 --echo **** Case 3: Statement logging format and LOAD DATA with non-transactional table ****
262
263 # LOAD DATA creates two events in binary log for statement binlog format.
264 # Try to skip the first.
265
266 connection master;
267 CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam;
268
269 sync_slave_with_master;
270 source include/stop_slave.inc;
271
272 connection master;
273 SET SESSION BINLOG_FORMAT=STATEMENT;
274
275 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
276 eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/std_data/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|';
277
278 SELECT * FROM t10 ORDER BY a;
279
280 save_master_pos;
281
282 connection slave;
283 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
284 source include/start_slave.inc;
285 sync_with_master;
286
287 SELECT * FROM t10 ORDER BY a;
288
289 connection master;
290 DROP TABLE t10;
291 sync_slave_with_master;
292
293 --source include/rpl_end.inc