Debian Codegraph @

mysql-test/suite/ndb_rpl/r/ndb_rpl_conflict_basic_insert.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 insert into mysql.ndb_replication values ("test", "t1_max_ins", 0, 7, "NDB$MAX_INS(X)");
7 insert into mysql.ndb_replication values ("test", "t1_max_del_win_ins", 0, 7, "NDB$MAX_DEL_WIN_INS(X)");
8 create table `t1_max_ins$EX`
9 (NDB$server_id int unsigned,
10 NDB$master_server_id int unsigned,
11 NDB$master_epoch bigint unsigned,
12 NDB$count int unsigned,
13 NDB$OP_TYPE ENUM( 'WRITE_ROW','UPDATE_ROW', 'DELETE_ROW',
14 'REFRESH_ROW','READ_ROW') NOT NULL,
15 NDB$CFT_CAUSE ENUM( 'ROW_DOES_NOT_EXIST',
16 'ROW_ALREADY_EXISTS',
17 'DATA_IN_CONFLICT',
18 'TRANS_IN_CONFLICT') NOT NULL,
19 a int not null,
20 primary key(NDB$server_id, NDB$master_server_id, NDB$master_epoch, NDB$count)) engine ndb;
21 create table `t1_max_del_win_ins$EX`
22 (NDB$server_id int unsigned,
23 NDB$master_server_id int unsigned,
24 NDB$master_epoch bigint unsigned,
25 NDB$count int unsigned,
26 NDB$OP_TYPE ENUM( 'WRITE_ROW','UPDATE_ROW', 'DELETE_ROW',
27 'REFRESH_ROW','READ_ROW') NOT NULL,
28 NDB$CFT_CAUSE ENUM( 'ROW_DOES_NOT_EXIST',
29 'ROW_ALREADY_EXISTS',
30 'DATA_IN_CONFLICT',
31 'TRANS_IN_CONFLICT') NOT NULL,
32 a int not null,
33 primary key(NDB$server_id, NDB$master_server_id, NDB$master_epoch, NDB$count)) engine ndb;
34 create table t1_max_ins (a int primary key, b varchar(32), X int unsigned) engine = ndb;
35 create table t1_max_del_win_ins (a int primary key, b varchar(32), X int unsigned) engine = ndb;
36 "Test 0 : No conflicts, row applied"
37 Insert a row in both tables to check replication without conflicts
38 insert into t1_max_ins values (1, 'Initial X=1', 1);
39 insert into t1_max_del_win_ins values (1, 'Initial X=1', 1);
40 "Expect t1_max_ins* to contain initial master row"
41 select * from t1_max_ins order by a;
42 a b X
43 1 Initial X=1 1
44 select * from t1_max_del_win_ins order by a;
45 a b X
46 1 Initial X=1 1
47 "Test 1 : No conflict detected, Row applied"
48 Insert some rows in slave to cause conflicts
49 insert into t1_max_ins values (2, 'Slave X=2', 2);
50 insert into t1_max_del_win_ins values (2, 'Slave X=2', 2);
51 Insert a row in both tables with a greater timestamp
52 Detect a conflict and accept replication
53 insert into t1_max_ins values (2, 'Master X=20', 20);
54 insert into t1_max_del_win_ins values (2, 'Master X=20', 20);
55 "Expect t1_max_ins and t1_max_del_win_ins to contains 2 rows."
56 "1. A row replicated without any conflicts(a=1)."
57 "2. A row replicated successfully because of higher value in the timestamp column(a=2)."
58 select * from t1_max_ins order by a;
59 a b X
60 1 Initial X=1 1
61 2 Master X=20 20
62 select * from t1_max_del_win_ins order by a;
63 a b X
64 1 Initial X=1 1
65 2 Master X=20 20
66 "Expect t1_max_ins* to have no entries"
67 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
68 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
69 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
70 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
71 "Test 2 : Conflict detected, Row rejected"
72 insert into t1_max_ins values (3, 'Slave X=30', 30);
73 insert into t1_max_del_win_ins values (3, 'Slave X=30', 30);
74 Insert a row in both tables with a smaller or equal timestamp
75 Detect a conflict and reject replication
76 insert into t1_max_ins values (3, 'Master X=3', 3);
77 insert into t1_max_del_win_ins values (3, 'Master X=3', 3);
78 "Expect t1_max_ins and t1_max_del_win_ins to contains 3 rows."
79 "1. A row replicated without any conflicts(a=1)."
80 "2. A row replicated successfully because of higher value in the timestamp column(a=2)."
81 "3. A row detected conflict and didn't replicate on the slave(a=3)."
82 select * from t1_max_ins order by a;
83 a b X
84 1 Initial X=1 1
85 2 Master X=20 20
86 3 Slave X=30 30
87 select * from t1_max_del_win_ins order by a;
88 a b X
89 1 Initial X=1 1
90 2 Master X=20 20
91 3 Slave X=30 30
92 "Expect t1_max_ins* to have 1 row."
93 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
94 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
95 2 1 1 WRITE_ROW DATA_IN_CONFLICT 3
96 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
97 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
98 2 1 1 WRITE_ROW DATA_IN_CONFLICT 3
99 drop table t1_max_ins, t1_max_del_win_ins;
100 delete from t1_max_ins$EX;
101 delete from t1_max_del_win_ins$EX;
102 delete from t1_max_ins$EX;
103 delete from t1_max_del_win_ins$EX;
104 create table t1_max_ins (a int primary key, b longtext, X int unsigned) engine = ndb;
105 create table t1_max_del_win_ins (a int primary key, b longtext, X int unsigned) engine = ndb;
106 "Testing with blobs"
107 "Test 3 : Conflict detection and resolution for INSERTs"
108 insert into t1_max_ins values (1, repeat('Initial X=1 ',1000), 1);
109 insert into t1_max_del_win_ins values (1, repeat('Initial X=1 ',1000), 1);
110 insert into t1_max_ins values (2, repeat('Slave X=2 ',2000), 2);
111 insert into t1_max_del_win_ins values (2, repeat('Slave X=2 ',2000), 2);
112 insert into t1_max_ins values (3, repeat('Slave X=30 ',3000), 30);
113 insert into t1_max_del_win_ins values (3, repeat('Slave X=30', 3000), 30);
114 Insert a row in both tables with a greater timestamp
115 insert into t1_max_ins values (2, repeat('Master X=20 ',2000), 20);
116 insert into t1_max_del_win_ins values (2, repeat('Master X=20 ',2000), 20);
117 Insert a row in both tables with a smaller timestamp
118 insert into t1_max_ins values (3, repeat('Master X=3 ',3000), 3);
119 insert into t1_max_del_win_ins values (3, repeat('Master X=3 ',3000), 3);
120 "Expect t1_max_ins* to contain 3 rows"
121 select a, left(b, 25), length(b), X from t1_max_ins order by a;
122 a left(b, 25) length(b) X
123 1 Initial X=1 Initial X=1 I 12000 1
124 2 Master X=20 Master X=20 M 24000 20
125 3 Slave X=30 Slave X=30 Sla 33000 30
126 select a, left(b, 25), length(b), X from t1_max_del_win_ins order by a;
127 a left(b, 25) length(b) X
128 1 Initial X=1 Initial X=1 I 12000 1
129 2 Master X=20 Master X=20 M 24000 20
130 3 Slave X=30Slave X=30Slave 30000 30
131 Expect t1_max_ins* to have 1 entry
132 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
133 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
134 2 1 1 WRITE_ROW DATA_IN_CONFLICT 3
135 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
136 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
137 2 1 1 WRITE_ROW DATA_IN_CONFLICT 3
138 drop table t1_max_ins, t1_max_del_win_ins;
139 delete from t1_max_ins$EX;
140 delete from t1_max_del_win_ins$EX;
141 delete from t1_max_ins$EX;
142 delete from t1_max_del_win_ins$EX;
143 create table t1_max_ins (a int primary key, b longtext, X int unsigned) engine = ndb;
144 create table t1_max_del_win_ins (a int primary key, b longtext, X int unsigned) engine = ndb;
145 "Test 4 : Conflict detection and resolution for UPDATEs"
146 insert into t1_max_ins values (1, repeat('Initial X=1',1000), 1);
147 insert into t1_max_del_win_ins values (1, repeat('Initial X=1',1000), 1);
148 update t1_max_ins set X = 20, b=repeat('Slave X=20',2000);
149 update t1_max_del_win_ins set X = 20, b=repeat('Slave X=20',2000);
150 update t1_max_ins set X = 2, b=repeat('Master X=2',2000);
151 update t1_max_del_win_ins set X = 2, b=repeat('Master X=2',2000);
152 "Expect t1_max* to contain a single row with X=20"
153 select a, left(b, 20), length(b), X from t1_max_ins order by a;
154 a left(b, 20) length(b) X
155 1 Slave X=20Slave X=20 20000 20
156 select a, left(b, 20), length(b), X from t1_max_del_win_ins order by a;
157 a left(b, 20) length(b) X
158 1 Slave X=20Slave X=20 20000 20
159 Expect t1_max* to have 1 entry for row with X=2 since this update is rejected at slave because of a smaller timestamp
160 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
161 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
162 2 1 1 UPDATE_ROW DATA_IN_CONFLICT 1
163 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
164 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
165 2 1 1 UPDATE_ROW DATA_IN_CONFLICT 1
166 update t1_max_ins set X = 30, b=repeat('Master X=30',3000);
167 update t1_max_del_win_ins set X = 30, b=repeat('Master X=30',3000);
168 "Expect t1_max* to contain a single row with X=30 since there is no conflict(higher timestamp)"
169 select a, left(b, 20), length(b), X from t1_max_ins order by a;
170 a left(b, 20) length(b) X
171 1 Master X=30Master X= 33000 30
172 select a, left(b, 20), length(b), X from t1_max_del_win_ins order by a;
173 a left(b, 20) length(b) X
174 1 Master X=30Master X= 33000 30
175 Expect t1_max* to have 1 old entry for row with X=2
176 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
177 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
178 2 1 1 UPDATE_ROW DATA_IN_CONFLICT 1
179 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
180 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
181 2 1 1 UPDATE_ROW DATA_IN_CONFLICT 1
182 delete from t1_max_ins$EX;
183 delete from t1_max_del_win_ins$EX;
184 "Test 5 : Conflict detection and resolution for DELETEs"
185 update t1_max_ins set X = 4, b=repeat('Slave X=4',2000);
186 update t1_max_del_win_ins set X = 4, b=repeat('Slave X=4',2000);
187 delete from t1_max_ins;
188 delete from t1_max_del_win_ins;
189 "Expect t1_max_ins to contain slave row since before images don't match according to MAX_INS algorithm"
190 "Expect t1_max_del_win_ins to be empty since delete will be applied anyways with MAX_DEL_WIN_INS algorithm"
191 select a, left(b, 20), length(b), X from t1_max_ins order by a;
192 a left(b, 20) length(b) X
193 1 Slave X=4Slave X=4Sl 18000 4
194 select a, left(b, 20), length(b), X from t1_max_del_win_ins order by a;
195 a left(b, 20) length(b) X
196 Expect t1_max_ins to contain 1 entry, and t1_max_del_win_ins to be empty
197 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
198 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
199 2 1 2 DELETE_ROW DATA_IN_CONFLICT 1
200 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
201 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
202 delete from t1_max_ins$EX;
203 delete from t1_max_del_win_ins$EX;
204 delete from t1_max_ins;
205 delete from t1_max_del_win_ins;
206 delete from t1_max_ins;
207 delete from t1_max_del_win_ins;
208 "Test 6 : Test that operations affecting Blobs are rejected correctly on the slave"
209 drop table t1_max_ins;
210 create table t1_max_ins (a int primary key, b int, c longtext, d longtext, X int unsigned) engine = ndb;
211 insert into t1_max_ins values (1, 1, repeat("B", 10000), repeat("E", 10001), 1);
212 insert into t1_max_ins values (2, 2, repeat("A", 10002), repeat("T", 10003), 1);
213 update t1_max_ins set X=20;
214 Initial values on Slave
215 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max_ins order by a;
216 a b SHA1(c) length(c) SHA1(d) length(d) X
217 1 1 4a222e18b539cdefbf0960eaa7f4362a4976e1e0 10000 9641d473ab1bd921263190eee074397084933e2d 10001 20
218 2 2 f833241322c062495632923d74314a6a5c23034d 10002 2dad269dfa115f6c7e53e91a73251e597aab8fe9 10003 20
219 Originate update which will be rejected due to NDB_MAX_INS algorithm.
220 update t1_max_ins set c=repeat("Z", 10006), d=repeat("I", 10005), X=2 where a=1;
221 Check slave has rejected due to smaller timestamp.
222 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max_ins order by a;
223 a b SHA1(c) length(c) SHA1(d) length(d) X
224 1 1 4a222e18b539cdefbf0960eaa7f4362a4976e1e0 10000 9641d473ab1bd921263190eee074397084933e2d 10001 20
225 2 2 f833241322c062495632923d74314a6a5c23034d 10002 2dad269dfa115f6c7e53e91a73251e597aab8fe9 10003 20
226 Originate delete which will be rejected due to NDB_OLD algorithm.
227 delete from t1_max_ins where a=1;
228 Check slave has rejected due to before image mismatch.
229 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max_ins order by a;
230 a b SHA1(c) length(c) SHA1(d) length(d) X
231 1 1 4a222e18b539cdefbf0960eaa7f4362a4976e1e0 10000 9641d473ab1bd921263190eee074397084933e2d 10001 20
232 2 2 f833241322c062495632923d74314a6a5c23034d 10002 2dad269dfa115f6c7e53e91a73251e597aab8fe9 10003 20
233 Originate insert which will be rejected due to NDB_MAX_INS algorithm.
234 insert into t1_max_ins values (1, 1, repeat("R", 10004), repeat("A", 10007), 1);
235 Check slave has rejected due to smaller timestamp.
236 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max_ins order by a;
237 a b SHA1(c) length(c) SHA1(d) length(d) X
238 1 1 4a222e18b539cdefbf0960eaa7f4362a4976e1e0 10000 9641d473ab1bd921263190eee074397084933e2d 10001 20
239 2 2 f833241322c062495632923d74314a6a5c23034d 10002 2dad269dfa115f6c7e53e91a73251e597aab8fe9 10003 20
240 Expect t1_max_ins to have 3 entries
241 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
242 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
243 2 1 1 UPDATE_ROW DATA_IN_CONFLICT 1
244 2 1 2 DELETE_ROW DATA_IN_CONFLICT 1
245 2 1 3 WRITE_ROW DATA_IN_CONFLICT 1
246 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_del_win_ins$EX order by NDB$count;
247 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
248 delete from t1_max_ins$EX;
249 delete from t1_max_del_win_ins$EX;
250 Test 7 : Check that non-Blob related operations in a batch with a Blob
251 operation are still subject to conflict detection.
252
253 insert into mysql.ndb_replication values ("test", "t2_max", 0, 7, "NDB$MAX(X)");
254 create table `t2_max$EX`
255 (server_id int unsigned,
256 master_server_id int unsigned,
257 master_epoch bigint unsigned,
258 count int unsigned,
259 a int not null,
260 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
261 create table t2_max (a int primary key, b int, X bigint unsigned) engine=ndb;
262 insert into t2_max values (1,1,10), (2,2,10), (3,3,10), (4,4,10), (5,5,10);
263 insert into t1_max_ins values (3, 3, repeat("R", 3000), repeat("A", 3000), 3);
264 Now issue a transaction with a successful Blob op, and unsuccessful
265 non-Blob op. Check that the Blob op succeeds, and the unsuccessful
266 non-Blob op is handled as expected.
267 begin;
268 update t2_max set b=b+1, X=1 where a=3;
269 insert into t1_max_ins values (3, 3, repeat("MAX_INS", 3000), repeat("Success", 3000), 30);
270 commit;
271 Contents on Slave
272 Expect Blob data applied to t1_max_ins, no update applied to t2_max
273 select a,b,left(c,7), length(c), left(d,7), length(d), X from t1_max_ins where a=3;
274 a b left(c,7) length(c) left(d,7) length(d) X
275 3 3 MAX_INS 21000 Success 21000 30
276 select * from t2_max order by a;
277 a b X
278 1 1 10
279 2 2 10
280 3 3 10
281 4 4 10
282 5 5 10
283 Expect 1 conflict detected, but not applied in t2_max
284 select NDB$server_id, NDB$master_server_id, NDB$count, NDB$OP_TYPE, NDB$CFT_CAUSE, a from t1_max_ins$EX order by NDB$count;
285 NDB$server_id NDB$master_server_id NDB$count NDB$OP_TYPE NDB$CFT_CAUSE a
286 select server_id, master_server_id, count, a from t2_max$EX order by count;
287 server_id master_server_id count a
288 2 1 1 3
289 drop table t2_max, t2_max$EX;
290 drop table t1_max_ins, `t1_max_ins$EX`, t1_max_del_win_ins, `t1_max_del_win_ins$EX`;
291 drop table mysql.ndb_replication;
292 include/rpl_end.inc