1
by brian
clean slate |
1 |
# Testing various forms of idempotency for replication that should
|
2 |
# work the same way under statement based as under row based.
|
|
3 |
||
4 |
source include/master-slave.inc; |
|
5 |
connection master; |
|
6 |
source include/have_innodb.inc; |
|
7 |
connection slave; |
|
8 |
source include/have_innodb.inc; |
|
9 |
||
10 |
connection master; |
|
11 |
CREATE TABLE t1 (a INT PRIMARY KEY); |
|
12 |
CREATE TABLE t2 (a INT); |
|
13 |
INSERT INTO t1 VALUES (-1),(-2),(-3); |
|
14 |
INSERT INTO t2 VALUES (-1),(-2),(-3); |
|
15 |
sync_slave_with_master; |
|
16 |
||
17 |
# A delete for a row that does not exist, the statement is
|
|
18 |
# deliberately written to be idempotent for statement-based
|
|
19 |
# replication as well. We test this towards both a table with a
|
|
20 |
# primary key and without a primary key.
|
|
21 |
||
22 |
connection slave; |
|
23 |
DELETE FROM t1 WHERE a = -2; |
|
24 |
DELETE FROM t2 WHERE a = -2; |
|
25 |
connection master; |
|
26 |
DELETE FROM t1 WHERE a = -2; |
|
27 |
DELETE FROM t2 WHERE a = -2; |
|
28 |
SELECT * FROM t1 ORDER BY a; |
|
29 |
SELECT * FROM t2 ORDER BY a; |
|
30 |
sync_slave_with_master; |
|
31 |
SELECT * FROM t1 ORDER BY a; |
|
32 |
SELECT * FROM t2 ORDER BY a; |
|
33 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
34 |
disable_query_log; |
|
35 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
36 |
enable_query_log; |
|
37 |
||
38 |
# An insert of a row that already exists. Since we are replacing the
|
|
39 |
# row if it already exists, the most apropriate representation is
|
|
40 |
# INSERT IGNORE. We only test this towards a table with a primary key,
|
|
41 |
# since the other case does not make sense.
|
|
42 |
||
43 |
INSERT IGNORE INTO t1 VALUES (-2); |
|
44 |
connection master; |
|
45 |
INSERT IGNORE INTO t1 VALUES (-2); |
|
46 |
SELECT * FROM t1 ORDER BY a; |
|
47 |
sync_slave_with_master; |
|
48 |
SELECT * FROM t1 ORDER BY a; |
|
49 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
50 |
disable_query_log; |
|
51 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
52 |
enable_query_log; |
|
53 |
||
54 |
# BUG#19958: RBR idempotency issue for UPDATE and DELETE
|
|
55 |
||
56 |
# Statement-based and row-based replication have different behaviour
|
|
57 |
# when updating a row with an explicit WHERE-clause that matches
|
|
58 |
# exactly one row (or no row at all). For statement-based replication,
|
|
59 |
# the statement is idempotent since the first time it is executed, it
|
|
60 |
# will update exactly one row, and the second time it will not update
|
|
61 |
# any row at all. This was not the case for row-based replication, so
|
|
62 |
# we test under both row-based and statement-based replication both
|
|
63 |
# for tables with and without primary keys.
|
|
64 |
||
65 |
connection slave; |
|
66 |
UPDATE t1 SET a = 1 WHERE a = -1; |
|
67 |
UPDATE t2 SET a = 1 WHERE a = -1; |
|
68 |
connection master; |
|
69 |
UPDATE t1 SET a = 1 WHERE a = -1; |
|
70 |
UPDATE t2 SET a = 1 WHERE a = -1; |
|
71 |
SELECT * FROM t1 ORDER BY a; |
|
72 |
SELECT * FROM t2 ORDER BY a; |
|
73 |
sync_slave_with_master; |
|
74 |
SELECT * FROM t1 ORDER BY a; |
|
75 |
SELECT * FROM t2 ORDER BY a; |
|
76 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
77 |
disable_query_log; |
|
78 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
79 |
enable_query_log; |
|
80 |
||
81 |
connection master; |
|
82 |
DROP TABLE t1, t2; |
|
83 |
sync_slave_with_master; |
|
84 |
||
85 |
# bug#31609 Not all RBR slave errors reported as errors
|
|
86 |
# bug#31552 Replication breaks when deleting rows from out-of-sync table
|
|
87 |
# without PK
|
|
88 |
||
89 |
#
|
|
90 |
# Idempotent applying is not default any longer.
|
|
91 |
# The default for slave-exec-mode option and server
|
|
92 |
# variable slave_exec_mode is 'STRICT'.
|
|
93 |
# When 'STRICT' mode is set, the slave SQL thread will stop whenever
|
|
94 |
# the row to change is not found. In 'IDEMPOTENT' mode, the SQL thread
|
|
95 |
# will continue running and apply the row - replace if it's Write_rows event -
|
|
96 |
# or skip to the next event.
|
|
97 |
||
98 |
# the previous part of the tests was with IDEMPOTENT slave's mode.
|
|
99 |
||
100 |
||
101 |
#
|
|
102 |
# Other than above idempotent errors dealing with foreign keys constraint
|
|
103 |
#
|
|
104 |
||
105 |
select @@global.slave_exec_mode /* must be IDEMPOTENT */; |
|
106 |
||
107 |
connection master; |
|
108 |
||
109 |
create table ti1 (b int primary key) engine = innodb; |
|
110 |
create table ti2 (a int primary key, b int, foreign key (b) references ti1(b)) |
|
111 |
engine = innodb; |
|
112 |
set foreign_key_checks=1 /* ensure the check */; |
|
113 |
||
114 |
insert into ti1 values (1),(2),(3); |
|
115 |
insert into ti2 set a=2, b=2; |
|
116 |
||
117 |
sync_slave_with_master; |
|
118 |
||
119 |
#connection slave;
|
|
120 |
select * from ti1 order by b /* must be (1),(2),(3) */; |
|
121 |
insert into ti2 set a=1, b=1; |
|
122 |
select * from ti2 order by b /* must be (1,1) (2,2) */; |
|
123 |
||
124 |
connection master; |
|
125 |
||
126 |
# from now on checking rbr specific idempotent errors
|
|
127 |
set @save_binlog_format= @@session.binlog_format; |
|
128 |
set @@session.binlog_format= row; |
|
129 |
delete from ti1 where b=1; |
|
130 |
||
131 |
select * from ti1 order by b /* must be (2),(3) */; |
|
132 |
||
133 |
# slave must catch up (expect some warnings in error.log)
|
|
134 |
sync_slave_with_master; |
|
135 |
||
136 |
#connection slave;
|
|
137 |
select * from ti1 order by b /* must stays as were on master (1),(2),(3) */; |
|
138 |
||
139 |
delete from ti1 where b=3; |
|
140 |
||
141 |
connection master; |
|
142 |
insert into ti2 set a=3, b=3; |
|
143 |
||
144 |
# slave must catch up (expect some warnings in error.log)
|
|
145 |
sync_slave_with_master; |
|
146 |
||
147 |
#connection slave;
|
|
148 |
select * from ti2 order by b /* must be (1,1),(2,2) - not inserted */; |
|
149 |
||
150 |
||
151 |
#
|
|
152 |
# Checking the new global sys variable
|
|
153 |
#
|
|
154 |
||
155 |
connection slave; |
|
156 |
||
157 |
set global slave_exec_mode='IDEMPOTENT'; |
|
158 |
set global slave_exec_mode='STRICT'; |
|
159 |
||
160 |
# checking mutual exclusion for the options
|
|
161 |
--error ER_SLAVE_AMBIGOUS_EXEC_MODE |
|
162 |
set global slave_exec_mode='IDEMPOTENT,STRICT'; |
|
163 |
||
164 |
select @@global.slave_exec_mode /* must be STRICT */; |
|
165 |
||
166 |
#
|
|
167 |
# Checking stops.
|
|
168 |
# In the following sections strict slave sql thread is going to
|
|
169 |
# stop when faces an idempotent error. In order to proceed
|
|
170 |
# the mode is temporarily switched to indempotent.
|
|
171 |
#
|
|
172 |
||
173 |
#
|
|
174 |
--echo *** foreign keys errors as above now forces to stop |
|
175 |
#
|
|
176 |
||
177 |
connection master; |
|
178 |
||
179 |
set foreign_key_checks=0; |
|
180 |
drop table ti2, ti1; |
|
181 |
||
182 |
create table ti1 (b int primary key) engine = innodb; |
|
183 |
create table ti2 (a int primary key, b int, foreign key (b) references ti1(b)) |
|
184 |
engine = innodb; |
|
185 |
set foreign_key_checks=1 /* ensure the check */; |
|
186 |
||
187 |
insert into ti1 values (1),(2),(3); |
|
188 |
insert into ti2 set a=2, b=2; |
|
189 |
||
190 |
sync_slave_with_master; |
|
191 |
||
192 |
#connection slave;
|
|
193 |
select * from ti1 order by b /* must be (1),(2),(3) */; |
|
194 |
--echo *** conspire future problem |
|
195 |
insert into ti2 set a=1, b=1; |
|
196 |
select * from ti2 order by b /* must be (1,1) (2,2) */; |
|
197 |
||
198 |
connection master; |
|
199 |
||
200 |
delete from ti1 where b=1 /* offending delete event */; |
|
201 |
select * from ti1 order by b /* must be (2),(3) */; |
|
202 |
||
203 |
# foreign key: row is referenced
|
|
204 |
||
205 |
--echo *** slave must stop |
|
206 |
source include/wait_for_slave_sql_to_stop.inc; |
|
207 |
||
208 |
connection slave; |
|
209 |
||
210 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
211 |
disable_query_log; |
|
212 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
213 |
enable_query_log; |
|
214 |
||
215 |
select * from ti1 order by b /* must be (1),(2),(3) - not deleted */; |
|
216 |
set foreign_key_checks= 0; |
|
217 |
delete from ti2 where b=1; |
|
218 |
set foreign_key_checks= 1; |
|
219 |
set global slave_exec_mode='IDEMPOTENT'; |
|
220 |
start slave sql_thread; |
|
221 |
connection master; |
|
222 |
sync_slave_with_master; |
|
223 |
#connection slave;
|
|
224 |
set global slave_exec_mode='STRICT'; |
|
225 |
||
226 |
connection master; |
|
227 |
||
228 |
sync_slave_with_master; |
|
229 |
||
230 |
#connection slave;
|
|
231 |
--echo *** conspire the following insert failure |
|
232 |
# foreign key: no referenced row
|
|
233 |
||
234 |
--echo *** conspire future problem |
|
235 |
delete from ti1 where b=3; |
|
236 |
||
237 |
connection master; |
|
238 |
insert into ti2 set a=3, b=3 /* offending write event */; |
|
239 |
--echo *** slave must stop |
|
240 |
||
241 |
source include/wait_for_slave_sql_to_stop.inc; |
|
242 |
||
243 |
connection slave; |
|
244 |
||
245 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
246 |
disable_query_log; |
|
247 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
248 |
enable_query_log; |
|
249 |
||
250 |
select * from ti2 order by b /* must be (2,2) */; |
|
251 |
set foreign_key_checks= 0; |
|
252 |
insert into ti1 set b=3; |
|
253 |
set foreign_key_checks= 1; |
|
254 |
set global slave_exec_mode='IDEMPOTENT'; |
|
255 |
start slave sql_thread; |
|
256 |
connection master; |
|
257 |
sync_slave_with_master; |
|
258 |
#connection slave;
|
|
259 |
set global slave_exec_mode='STRICT'; |
|
260 |
||
261 |
connection master; |
|
262 |
||
263 |
sync_slave_with_master; |
|
264 |
||
265 |
select * from ti2 order by b /* must be (2,2),(3,3) */; |
|
266 |
||
267 |
#
|
|
268 |
--echo *** other errors |
|
269 |
#
|
|
270 |
||
271 |
# dup key insert
|
|
272 |
||
273 |
#connection slave;
|
|
274 |
--echo *** conspiring query |
|
275 |
insert into ti1 set b=1; |
|
276 |
||
277 |
connection master; |
|
278 |
insert into ti1 set b=1 /* offending write event */; |
|
279 |
||
280 |
--echo *** slave must stop |
|
281 |
source include/wait_for_slave_sql_to_stop.inc; |
|
282 |
||
283 |
connection slave; |
|
284 |
||
285 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
286 |
disable_query_log; |
|
287 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
288 |
enable_query_log; |
|
289 |
||
290 |
set foreign_key_checks= 0; |
|
291 |
delete from ti1 where b=1; |
|
292 |
set foreign_key_checks= 1; |
|
293 |
set global slave_exec_mode='IDEMPOTENT'; |
|
294 |
start slave sql_thread; |
|
295 |
connection master; |
|
296 |
sync_slave_with_master; |
|
297 |
#connection slave;
|
|
298 |
set global slave_exec_mode='STRICT'; |
|
299 |
||
300 |
# key not found
|
|
301 |
||
302 |
connection master; |
|
303 |
||
304 |
CREATE TABLE t1 (a INT PRIMARY KEY); |
|
305 |
CREATE TABLE t2 (a INT); |
|
306 |
INSERT INTO t1 VALUES (-1),(-2),(-3); |
|
307 |
INSERT INTO t2 VALUES (-1),(-2),(-3); |
|
308 |
sync_slave_with_master; |
|
309 |
||
310 |
#connection slave;
|
|
311 |
DELETE FROM t1 WHERE a = -2; |
|
312 |
DELETE FROM t2 WHERE a = -2; |
|
313 |
connection master; |
|
314 |
DELETE FROM t1 WHERE a = -2; |
|
315 |
||
316 |
--echo *** slave must stop |
|
317 |
source include/wait_for_slave_sql_to_stop.inc; |
|
318 |
||
319 |
connection slave; |
|
320 |
||
321 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
322 |
disable_query_log; |
|
323 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
324 |
enable_query_log; |
|
325 |
||
326 |
set global slave_exec_mode='IDEMPOTENT'; |
|
327 |
start slave sql_thread; |
|
328 |
connection master; |
|
329 |
sync_slave_with_master; |
|
330 |
#connection slave;
|
|
331 |
set global slave_exec_mode='STRICT'; |
|
332 |
||
333 |
connection master; |
|
334 |
DELETE FROM t2 WHERE a = -2; |
|
335 |
--echo *** slave must stop |
|
336 |
source include/wait_for_slave_sql_to_stop.inc; |
|
337 |
||
338 |
connection slave; |
|
339 |
||
340 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
341 |
disable_query_log; |
|
342 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
343 |
enable_query_log; |
|
344 |
||
345 |
set global slave_exec_mode='IDEMPOTENT'; |
|
346 |
start slave sql_thread; |
|
347 |
connection master; |
|
348 |
sync_slave_with_master; |
|
349 |
#connection slave;
|
|
350 |
set global slave_exec_mode='STRICT'; |
|
351 |
||
352 |
UPDATE t1 SET a = 1 WHERE a = -1; |
|
353 |
UPDATE t2 SET a = 1 WHERE a = -1; |
|
354 |
||
355 |
connection master; |
|
356 |
UPDATE t1 SET a = 1 WHERE a = -1; |
|
357 |
||
358 |
--echo *** slave must stop |
|
359 |
source include/wait_for_slave_sql_to_stop.inc; |
|
360 |
||
361 |
connection slave; |
|
362 |
||
363 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
364 |
disable_query_log; |
|
365 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
366 |
enable_query_log; |
|
367 |
||
368 |
set global slave_exec_mode='IDEMPOTENT'; |
|
369 |
start slave sql_thread; |
|
370 |
connection master; |
|
371 |
sync_slave_with_master; |
|
372 |
#connection slave;
|
|
373 |
set global slave_exec_mode='STRICT'; |
|
374 |
||
375 |
||
376 |
connection master; |
|
377 |
UPDATE t2 SET a = 1 WHERE a = -1; |
|
378 |
||
379 |
--echo *** slave must stop |
|
380 |
source include/wait_for_slave_sql_to_stop.inc; |
|
381 |
||
382 |
connection slave; |
|
383 |
||
384 |
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1); |
|
385 |
disable_query_log; |
|
386 |
eval SELECT "$last_error" AS Last_SQL_Error; |
|
387 |
enable_query_log; |
|
388 |
||
389 |
set global slave_exec_mode='IDEMPOTENT'; |
|
390 |
start slave sql_thread; |
|
391 |
connection master; |
|
392 |
sync_slave_with_master; |
|
393 |
#connection slave;
|
|
394 |
set global slave_exec_mode='STRICT'; |
|
395 |
||
396 |
||
397 |
# cleanup for bug#31609 tests
|
|
398 |
||
399 |
connection master; |
|
400 |
set @@session.binlog_format= @save_binlog_format; |
|
401 |
drop table t1,t2,ti2,ti1; |
|
402 |
||
403 |
sync_slave_with_master; |
|
404 |
||
405 |
||
406 |
--echo *** end of tests |
|
407 |
||
408 |
||
409 |
||
410 |
||
411 |
||
412 |
||
413 |
||
414 |
||
415 |