1
# Testing various forms of idempotency for replication that should
2
# work the same way under statement based as under row based.
4
source include/master-slave.inc;
6
source include/have_innodb.inc;
8
source include/have_innodb.inc;
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;
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.
23
DELETE FROM t1 WHERE a = -2;
24
DELETE FROM t2 WHERE a = -2;
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);
35
eval SELECT "$last_error" AS Last_SQL_Error;
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.
43
INSERT IGNORE INTO t1 VALUES (-2);
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);
51
eval SELECT "$last_error" AS Last_SQL_Error;
54
# BUG#19958: RBR idempotency issue for UPDATE and DELETE
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.
66
UPDATE t1 SET a = 1 WHERE a = -1;
67
UPDATE t2 SET a = 1 WHERE a = -1;
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);
78
eval SELECT "$last_error" AS Last_SQL_Error;
83
sync_slave_with_master;
85
# bug#31609 Not all RBR slave errors reported as errors
86
# bug#31552 Replication breaks when deleting rows from out-of-sync table
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.
98
# the previous part of the tests was with IDEMPOTENT slave's mode.
102
# Other than above idempotent errors dealing with foreign keys constraint
105
select @@global.slave_exec_mode /* must be IDEMPOTENT */;
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))
112
set foreign_key_checks=1 /* ensure the check */;
114
insert into ti1 values (1),(2),(3);
115
insert into ti2 set a=2, b=2;
117
sync_slave_with_master;
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) */;
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;
131
select * from ti1 order by b /* must be (2),(3) */;
133
# slave must catch up (expect some warnings in error.log)
134
sync_slave_with_master;
137
select * from ti1 order by b /* must stays as were on master (1),(2),(3) */;
139
delete from ti1 where b=3;
142
insert into ti2 set a=3, b=3;
144
# slave must catch up (expect some warnings in error.log)
145
sync_slave_with_master;
148
select * from ti2 order by b /* must be (1,1),(2,2) - not inserted */;
152
# Checking the new global sys variable
157
set global slave_exec_mode='IDEMPOTENT';
158
set global slave_exec_mode='STRICT';
160
# checking mutual exclusion for the options
161
--error ER_SLAVE_AMBIGOUS_EXEC_MODE
162
set global slave_exec_mode='IDEMPOTENT,STRICT';
164
select @@global.slave_exec_mode /* must be STRICT */;
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.
174
--echo *** foreign keys errors as above now forces to stop
179
set foreign_key_checks=0;
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))
185
set foreign_key_checks=1 /* ensure the check */;
187
insert into ti1 values (1),(2),(3);
188
insert into ti2 set a=2, b=2;
190
sync_slave_with_master;
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) */;
200
delete from ti1 where b=1 /* offending delete event */;
201
select * from ti1 order by b /* must be (2),(3) */;
203
# foreign key: row is referenced
205
--echo *** slave must stop
206
source include/wait_for_slave_sql_to_stop.inc;
210
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
212
eval SELECT "$last_error" AS Last_SQL_Error;
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;
222
sync_slave_with_master;
224
set global slave_exec_mode='STRICT';
228
sync_slave_with_master;
231
--echo *** conspire the following insert failure
232
# foreign key: no referenced row
234
--echo *** conspire future problem
235
delete from ti1 where b=3;
238
insert into ti2 set a=3, b=3 /* offending write event */;
239
--echo *** slave must stop
241
source include/wait_for_slave_sql_to_stop.inc;
245
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
247
eval SELECT "$last_error" AS Last_SQL_Error;
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;
257
sync_slave_with_master;
259
set global slave_exec_mode='STRICT';
263
sync_slave_with_master;
265
select * from ti2 order by b /* must be (2,2),(3,3) */;
268
--echo *** other errors
274
--echo *** conspiring query
275
insert into ti1 set b=1;
278
insert into ti1 set b=1 /* offending write event */;
280
--echo *** slave must stop
281
source include/wait_for_slave_sql_to_stop.inc;
285
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
287
eval SELECT "$last_error" AS Last_SQL_Error;
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;
296
sync_slave_with_master;
298
set global slave_exec_mode='STRICT';
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;
311
DELETE FROM t1 WHERE a = -2;
312
DELETE FROM t2 WHERE a = -2;
314
DELETE FROM t1 WHERE a = -2;
316
--echo *** slave must stop
317
source include/wait_for_slave_sql_to_stop.inc;
321
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
323
eval SELECT "$last_error" AS Last_SQL_Error;
326
set global slave_exec_mode='IDEMPOTENT';
327
start slave sql_thread;
329
sync_slave_with_master;
331
set global slave_exec_mode='STRICT';
334
DELETE FROM t2 WHERE a = -2;
335
--echo *** slave must stop
336
source include/wait_for_slave_sql_to_stop.inc;
340
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
342
eval SELECT "$last_error" AS Last_SQL_Error;
345
set global slave_exec_mode='IDEMPOTENT';
346
start slave sql_thread;
348
sync_slave_with_master;
350
set global slave_exec_mode='STRICT';
352
UPDATE t1 SET a = 1 WHERE a = -1;
353
UPDATE t2 SET a = 1 WHERE a = -1;
356
UPDATE t1 SET a = 1 WHERE a = -1;
358
--echo *** slave must stop
359
source include/wait_for_slave_sql_to_stop.inc;
363
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
365
eval SELECT "$last_error" AS Last_SQL_Error;
368
set global slave_exec_mode='IDEMPOTENT';
369
start slave sql_thread;
371
sync_slave_with_master;
373
set global slave_exec_mode='STRICT';
377
UPDATE t2 SET a = 1 WHERE a = -1;
379
--echo *** slave must stop
380
source include/wait_for_slave_sql_to_stop.inc;
384
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Errno, 1);
386
eval SELECT "$last_error" AS Last_SQL_Error;
389
set global slave_exec_mode='IDEMPOTENT';
390
start slave sql_thread;
392
sync_slave_with_master;
394
set global slave_exec_mode='STRICT';
397
# cleanup for bug#31609 tests
400
set @@session.binlog_format= @save_binlog_format;
401
drop table t1,t2,ti2,ti1;
403
sync_slave_with_master;
406
--echo *** end of tests