Debian Codegraph @

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

raw
1 # Testing various forms of idempotency for replication. This file is
2 # for tests that should only be executed in row mode.
3
4 source include/have_binlog_format_row.inc;
5 source include/master-slave.inc;
6 connection master;
7 source include/have_innodb.inc;
8 connection slave;
9 source include/have_innodb.inc;
10
11 # Add suppression for expected warning(s) in slaves error log
12 call mtr.add_suppression("Can.t find record in .t[12].* error.* 1032");
13 call mtr.add_suppression("Cannot delete or update a parent row: a foreign key constraint fails .* error.* 1451");
14 call mtr.add_suppression("Cannot add or update a child row: a foreign key constraint fails .* error.* 1452");
15 call mtr.add_suppression("Duplicate entry .1. for key .PRIMARY.* error.* 1062");
16 call mtr.add_suppression("Can't find record in 't1'");
17 call mtr.add_suppression("Can't find record in 't2'");
18
19
20 # bug#31609 Not all RBR slave errors reported as errors
21 # bug#31552 Replication breaks when deleting rows from out-of-sync table
22 # without PK
23
24 # The default for slave-exec-mode option and server
25 # variable slave_exec_mode is 'STRICT'.
26 # When 'STRICT' mode is set, the slave SQL thread will stop whenever
27 # the row to change is not found. In 'IDEMPOTENT' mode, the SQL thread
28 # will continue running and apply the row - replace if it's Write_rows event -
29 # or skip to the next event.
30
31 # the previous part of the tests was with IDEMPOTENT slave's mode.
32
33
34 #
35 # Other than above idempotent errors dealing with foreign keys constraint
36 #
37 connection slave;
38
39 set @old_slave_exec_mode= @@global.slave_exec_mode;
40 set @@global.slave_exec_mode= IDEMPOTENT;
41
42 connection master;
43
44 create table ti1 (b int primary key) engine = innodb;
45 create table ti2 (a int primary key, b int, foreign key (b) references ti1(b))
46 engine = innodb;
47 set foreign_key_checks=1 /* ensure the check */;
48
49 insert into ti1 values (1),(2),(3);
50 insert into ti2 set a=2, b=2;
51
52 sync_slave_with_master;
53
54 #connection slave;
55 select * from ti1 order by b /* must be (1),(2),(3) */;
56 insert into ti2 set a=1, b=1;
57 select * from ti2 order by b /* must be (1,1) (2,2) */;
58
59 connection master;
60
61 # from now on checking rbr specific idempotent errors
62 set @save_binlog_format= @@session.binlog_format;
63 set @@session.binlog_format= row;
64 delete from ti1 where b=1;
65
66 select * from ti1 order by b /* must be (2),(3) */;
67
68 # slave must catch up (expect some warnings in error.log)
69 sync_slave_with_master;
70
71 #connection slave;
72 select * from ti1 order by b /* must stays as were on master (1),(2),(3) */;
73
74 delete from ti1 where b=3;
75
76 connection master;
77 insert into ti2 set a=3, b=3;
78
79 # slave must catch up (expect some warnings in error.log)
80 sync_slave_with_master;
81
82 #connection slave;
83 select * from ti2 order by b /* must be (1,1),(2,2) - not inserted */;
84
85
86 #
87 # Checking the new global sys variable
88 #
89
90 connection slave;
91
92 set global slave_exec_mode='IDEMPOTENT';
93 set global slave_exec_mode='STRICT';
94
95 # checking mutual exclusion for the options
96 --error ER_WRONG_VALUE_FOR_VAR
97 set global slave_exec_mode='IDEMPOTENT,STRICT';
98
99 select @@global.slave_exec_mode /* must be STRICT */;
100
101 #
102 # Checking stops.
103 # In the following sections strict slave sql thread is going to
104 # stop when faces an idempotent error. In order to proceed
105 # the mode is temporarily switched to indempotent.
106 #
107
108 #
109 --echo *** foreign keys errors as above now forces to stop
110 #
111
112 connection master;
113
114 set foreign_key_checks=0;
115 drop table ti2, ti1;
116
117 create table ti1 (b int primary key) engine = innodb;
118 create table ti2 (a int primary key, b int, foreign key (b) references ti1(b))
119 engine = innodb;
120 set foreign_key_checks=1 /* ensure the check */;
121
122 insert into ti1 values (1),(2),(3);
123 insert into ti2 set a=2, b=2;
124
125 sync_slave_with_master;
126
127 #connection slave;
128 select * from ti1 order by b /* must be (1),(2),(3) */;
129 --echo *** conspire future problem
130 insert into ti2 set a=1, b=1;
131 select * from ti2 order by b /* must be (1,1) (2,2) */;
132
133 connection master;
134
135 delete from ti1 where b=1 /* offending delete event */;
136 select * from ti1 order by b /* must be (2),(3) */;
137
138 # foreign key: row is referenced
139
140 --echo *** slave must stop (Trying to delete a referenced foreing key)
141 connection slave;
142 # ER_ROW_IS_REFERENCED_2 # Cannot add or update a child row: a foreign key constraint fails
143 --let slave_sql_errno= 1451
144 source include/wait_for_slave_sql_error.inc;
145
146 let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
147 disable_query_log;
148 eval SELECT "$last_error" AS Last_SQL_Error;
149 enable_query_log;
150
151 select * from ti1 order by b /* must be (1),(2),(3) - not deleted */;
152 set foreign_key_checks= 0;
153 delete from ti2 where b=1;
154 set foreign_key_checks= 1;
155 set global slave_exec_mode='IDEMPOTENT';
156 start slave sql_thread;
157 connection master;
158 sync_slave_with_master;
159 #connection slave;
160 set global slave_exec_mode='STRICT';
161
162 connection master;
163
164 sync_slave_with_master;
165
166 #connection slave;
167 --echo *** conspire the following insert failure
168 # foreign key: no referenced row
169
170 --echo *** conspire future problem
171 delete from ti1 where b=3;
172
173 connection master;
174 insert into ti2 set a=3, b=3 /* offending write event */;
175
176 --echo *** slave must stop (Trying to insert an invalid foreign key)
177 connection slave;
178 # ER_NO_REFERENCED_ROW_2 # Cannot add or update a parent row: a foreign key constraint fails
179 --let slave_sql_errno= 1452
180 source include/wait_for_slave_sql_error.inc;
181
182 let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
183 disable_query_log;
184 eval SELECT "$last_error" AS Last_SQL_Error;
185 enable_query_log;
186
187 select * from ti2 order by b /* must be (2,2) */;
188 set foreign_key_checks= 0;
189 insert into ti1 set b=3;
190 set foreign_key_checks= 1;
191 set global slave_exec_mode='IDEMPOTENT';
192 start slave sql_thread;
193 connection master;
194 sync_slave_with_master;
195 #connection slave;
196 set global slave_exec_mode='STRICT';
197
198 connection master;
199
200 sync_slave_with_master;
201
202 select * from ti2 order by b /* must be (2,2),(3,3) */;
203
204 #
205 --echo *** other errors
206 #
207
208 # dup key insert
209
210 #connection slave;
211 --echo *** conspiring query
212 insert into ti1 set b=1;
213
214 connection master;
215 insert into ti1 set b=1 /* offending write event */;
216
217 --echo *** slave must stop (Trying to insert a duplicate key)
218 connection slave;
219 # ER_DUP_ENTRY # Duplicate entry for key
220 --let slave_sql_errno= 1062
221 source include/wait_for_slave_sql_error.inc;
222
223 let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
224 disable_query_log;
225 eval SELECT "$last_error" AS Last_SQL_Error;
226 enable_query_log;
227
228 set foreign_key_checks= 0;
229 delete from ti1 where b=1;
230 set foreign_key_checks= 1;
231 set global slave_exec_mode='IDEMPOTENT';
232 start slave sql_thread;
233 connection master;
234 sync_slave_with_master;
235 #connection slave;
236 set global slave_exec_mode='STRICT';
237
238 # key not found
239
240 connection master;
241
242 CREATE TABLE t1 (a INT PRIMARY KEY);
243 CREATE TABLE t2 (a INT);
244 INSERT INTO t1 VALUES (-1),(-2),(-3);
245 INSERT INTO t2 VALUES (-1),(-2),(-3);
246 sync_slave_with_master;
247
248 #connection slave;
249 DELETE FROM t1 WHERE a = -2;
250 DELETE FROM t2 WHERE a = -2;
251 connection master;
252 DELETE FROM t1 WHERE a = -2;
253
254 --echo *** slave must stop (Key was not found)
255 connection slave;
256 # ER_KEY_NOT_FOUND # Can't find record
257 --let slave_sql_errno= 1032
258 source include/wait_for_slave_sql_error.inc;
259
260
261 let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
262 disable_query_log;
263 eval SELECT "$last_error" AS Last_SQL_Error;
264 enable_query_log;
265
266 set global slave_exec_mode='IDEMPOTENT';
267 start slave sql_thread;
268 connection master;
269 sync_slave_with_master;
270 #connection slave;
271 set global slave_exec_mode='STRICT';
272
273 connection master;
274 DELETE FROM t2 WHERE a = -2;
275 --echo *** slave must stop (Key was not found)
276 connection slave;
277 # ER_KEY_NOT_FOUND # Can't find record
278 --let slave_sql_errno= 1032
279 source include/wait_for_slave_sql_error.inc;
280
281 let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
282 disable_query_log;
283 eval SELECT "$last_error" AS Last_SQL_Error;
284 enable_query_log;
285
286 set global slave_exec_mode='IDEMPOTENT';
287 start slave sql_thread;
288 connection master;
289 sync_slave_with_master;
290 #connection slave;
291 set global slave_exec_mode='STRICT';
292
293 UPDATE t1 SET a = 1 WHERE a = -1;
294 UPDATE t2 SET a = 1 WHERE a = -1;
295
296 connection master;
297 UPDATE t1 SET a = 1 WHERE a = -1;
298
299 --echo *** slave must stop (Key was not found)
300 connection slave;
301 # ER_KEY_NOT_FOUND # Can't find record
302 --let slave_sql_errno= 1032
303 source include/wait_for_slave_sql_error.inc;
304
305 let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
306 disable_query_log;
307 eval SELECT "$last_error" AS Last_SQL_Error;
308 enable_query_log;
309
310 set global slave_exec_mode='IDEMPOTENT';
311 start slave sql_thread;
312 connection master;
313 sync_slave_with_master;
314 #connection slave;
315 set global slave_exec_mode='STRICT';
316
317
318 connection master;
319 UPDATE t2 SET a = 1 WHERE a = -1;
320
321 --echo *** slave must stop (Key was not found)
322 connection slave;
323 # ER_KEY_NOT_FOUND # Can't find record
324 --let slave_sql_errno= 1032
325 source include/wait_for_slave_sql_error.inc;
326
327 let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
328 disable_query_log;
329 eval SELECT "$last_error" AS Last_SQL_Error;
330 enable_query_log;
331
332 set global slave_exec_mode='IDEMPOTENT';
333 start slave sql_thread;
334 connection master;
335 sync_slave_with_master;
336 #connection slave;
337 SET @@global.slave_exec_mode= @old_slave_exec_mode;
338
339 # cleanup for bug#31609 tests
340
341 connection master;
342
343 drop table t1,t2,ti2,ti1;
344 sync_slave_with_master;
345 set @@global.slave_exec_mode= @old_slave_exec_mode;
346
347 --echo *** end of tests
348 --source include/rpl_end.inc