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