Debian Codegraph @

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

raw
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
6 # Add suppression for expected warning(s) in slaves error log
7 call mtr.add_suppression("Slave SQL.*Can.t find record in .t[12].* error.* 1032");
8 call mtr.add_suppression("Slave SQL.*Cannot delete or update a parent row: a foreign key constraint fails .* error.* 1451");
9 call mtr.add_suppression("Slave SQL.*Cannot add or update a child row: a foreign key constraint fails .* error.* 1452");
10 call mtr.add_suppression("Slave SQL.*Could not execute Write_rows event on table test.* Duplicate entry .1. for key .PRIMARY.* error.* 1062");
11 call mtr.add_suppression("Can't find record in 't1'");
12 call mtr.add_suppression("Can't find record in 't2'");
13
14 connection master;
15 CREATE TABLE t1 (a INT PRIMARY KEY);
16 CREATE TABLE t2 (a INT);
17 INSERT INTO t1 VALUES (-1),(-2),(-3);
18 INSERT INTO t2 VALUES (-1),(-2),(-3);
19 sync_slave_with_master;
20
21 SET @old_slave_exec_mode= @@global.slave_exec_mode;
22 SET @@global.slave_exec_mode= IDEMPOTENT;
23
24 # A delete for a row that does not exist, the statement is
25 # deliberately written to be idempotent for statement-based
26 # replication as well. We test this towards both a table with a
27 # primary key and without a primary key.
28
29 connection slave;
30 DELETE FROM t1 WHERE a = -2;
31 DELETE FROM t2 WHERE a = -2;
32 connection master;
33 DELETE FROM t1 WHERE a = -2;
34 DELETE FROM t2 WHERE a = -2;
35 SELECT * FROM t1 ORDER BY a;
36 SELECT * FROM t2 ORDER BY a;
37 sync_slave_with_master;
38 SELECT * FROM t1 ORDER BY a;
39 SELECT * FROM t2 ORDER BY a;
40 --source include/check_slave_no_error.inc
41
42 # An insert of a row that already exists. Since we are replacing the
43 # row if it already exists, the most apropriate representation is
44 # INSERT IGNORE. We only test this towards a table with a primary key,
45 # since the other case does not make sense.
46
47 INSERT IGNORE INTO t1 VALUES (-2);
48 connection master;
49 INSERT IGNORE INTO t1 VALUES (-2);
50 SELECT * FROM t1 ORDER BY a;
51 sync_slave_with_master;
52 SELECT * FROM t1 ORDER BY a;
53 --source include/check_slave_no_error.inc
54
55
56 # BUG#19958: RBR idempotency issue for UPDATE and DELETE
57
58 # Statement-based and row-based replication have different behaviour
59 # when updating a row with an explicit WHERE-clause that matches
60 # exactly one row (or no row at all). For statement-based replication,
61 # the statement is idempotent since the first time it is executed, it
62 # will update exactly one row, and the second time it will not update
63 # any row at all. This was not the case for row-based replication, so
64 # we test under both row-based and statement-based replication both
65 # for tables with and without primary keys.
66
67 connection slave;
68 UPDATE t1 SET a = 1 WHERE a = -1;
69 UPDATE t2 SET a = 1 WHERE a = -1;
70 connection master;
71 UPDATE t1 SET a = 1 WHERE a = -1;
72 UPDATE t2 SET a = 1 WHERE a = -1;
73 SELECT * FROM t1 ORDER BY a;
74 SELECT * FROM t2 ORDER BY a;
75 sync_slave_with_master;
76 SELECT * FROM t1 ORDER BY a;
77 SELECT * FROM t2 ORDER BY a;
78 --source include/check_slave_no_error.inc
79
80 connection slave;
81 drop table t1, t2;
82
83 connection master;
84 DROP TABLE t1, t2;
85 sync_slave_with_master;
86 --source include/check_slave_no_error.inc
87 create database d;
88 create database e;
89
90 connection master;
91 create database d;
92 create database if not exists e;
93
94 sync_slave_with_master;
95 --source include/check_slave_no_error.inc
96 drop database d;
97 drop database e;
98
99 connection master;
100 drop database d;
101 drop database if exists e;
102 sync_slave_with_master;
103 --source include/check_slave_no_error.inc
104
105 SET @@global.slave_exec_mode= @old_slave_exec_mode;
106
107 --source include/rpl_end.inc