Debian Codegraph @

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

raw
1 source include/have_debug.inc;
2 source include/have_innodb.inc;
3 -- source include/have_binlog_format_statement.inc
4 source include/master-slave.inc;
5 # MDEV-17614 INSERT on dup key update is replication unsafe
6 #
7 # The following cases are tested below:
8 # 1. 2 unique key, replication is UNSAFE
9 # 2. 2 unique key, with one auto increment key and implicit value to it.
10 # It is UNSAFE because autoinc column values of being inserted records
11 # are revealed dynamically, so unknown at the binlog-format decision time
12 # and hence this pessimistic expectation
13 # 3. 2 unique keys
14 # A. insert is only in 1 unique key, still all colums are specified => UNSAFE
15 # B. both unique keys are specified => UNSAFE
16 # C. only one unique key is specified => SAFE (motivated by MDEV-28310)
17 # 4. 2 unique key, with one auto increment key(but user gives auto inc value) =>
18 # UNSAFE to replicate
19
20 --echo # Case 1: UNSAFE
21 call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
22 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT,
23 UNIQUE(b), c int) engine=innodb;
24 sync_slave_with_master;
25 connection master;
26 INSERT INTO t1 VALUES (1, 1, 1);
27 BEGIN;
28 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
29 --connection master1
30 INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
31 --connection master
32 COMMIT;
33 SELECT * FROM t1;
34 --connection slave
35 # show the error message
36 --let $slave_sql_errno= 1062
37 --let $show_slave_sql_error= 1
38 --source include/wait_for_slave_sql_error.inc
39 --echo #Different value from server
40 SELECT * FROM t1;
41
42 # restart replication for the next testcase
43 --source include/stop_slave_io.inc
44 --source include/reset_slave.inc
45 connection master;
46 reset master;
47 drop table t1;
48 connection slave;
49 start slave;
50 --source include/wait_for_slave_to_start.inc
51
52 --echo # Case 2: UNSAFE
53 --connection master
54 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT,
55 UNIQUE(b), c int) engine=innodb;
56 sync_slave_with_master;
57 connection master;
58 INSERT INTO t1 VALUES (default, 1, 1);
59 BEGIN;
60 INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
61 --connection master1
62 INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
63 --connection master
64 COMMIT;
65 SELECT * FROM t1;
66 --sync_slave_with_master
67 --echo #same data as master
68 SELECT * FROM t1;
69
70 connection master;
71 drop table t1;
72 --sync_slave_with_master
73
74 --echo # Case 3A: UNSAFE
75 --connection master
76 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT,
77 UNIQUE(b), c int, d int ) engine=innodb;
78 sync_slave_with_master;
79 connection master;
80 INSERT INTO t1 VALUES (1, 1, 1, 1);
81 BEGIN;
82 INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
83 --connection master1
84 INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
85 --connection master
86 COMMIT;
87 SELECT * FROM t1;
88 --sync_slave_with_master
89 --echo #same data as master
90 SELECT * FROM t1;
91 connection master;
92 drop table t1;
93 --sync_slave_with_master
94
95 --echo # Case 3B: UNSAFE - all column specified.
96 --connection master
97 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT,
98 UNIQUE(b), c int, d int ) engine=innodb;
99 sync_slave_with_master;
100 connection master;
101 INSERT INTO t1 VALUES (1, 1, 1, 1);
102 BEGIN;
103 INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
104 --connection master1
105 INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
106 --connection master
107 COMMIT;
108 SELECT * FROM t1;
109 --sync_slave_with_master
110 --echo #same data as master
111 SELECT * FROM t1;
112 connection master;
113 drop table t1;
114 --sync_slave_with_master
115
116
117 --echo # Case 3C: SAFE - only one unique key (PK) specified.
118 --connection master
119 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT,
120 UNIQUE(b), c int, d int ) engine=innodb;
121 sync_slave_with_master;
122 connection master;
123 INSERT INTO t1 VALUES (1, 1, 1, 1);
124 BEGIN;
125 INSERT INTO t1 (`a`, `c`, `d`) VALUES (2, 2, 2) ON DUPLICATE KEY UPDATE c=99;
126 --connection master1
127 INSERT INTO t1 (`a`, `c`, `d`) VALUES(3, 2, 3) ON DUPLICATE KEY UPDATE c=100;
128 --connection master
129 COMMIT;
130 SELECT * FROM t1;
131 --sync_slave_with_master
132 --echo #same data as master
133 SELECT * FROM t1;
134 connection master;
135 drop table t1;
136 --sync_slave_with_master
137
138 --echo # Case 4: UNSAFE
139 --connection master
140 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT,
141 UNIQUE(b), c int) engine=innodb;
142 sync_slave_with_master;
143 connection master;
144 INSERT INTO t1 VALUES (1, 1, 1);
145 BEGIN;
146 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
147 --connection master1
148 INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
149 --connection master
150 COMMIT;
151 SELECT * FROM t1;
152 --connection slave
153 # show the error message
154 --let $slave_sql_errno= 1062
155 --let $show_slave_sql_error= 1
156 --source include/wait_for_slave_sql_error.inc
157 --echo #Different value from server
158 SELECT * FROM t1;
159
160 # restart replication for the next testcase
161 --source include/stop_slave_io.inc
162 --source include/reset_slave.inc
163 connection master;
164 reset master;
165 drop table t1;
166 connection slave;
167 start slave;
168 --source include/wait_for_slave_to_start.inc
169
170 --source include/rpl_end.inc