Debian Codegraph @

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

raw
1 # === Purpose ===
2 #
3 # This test checks replication with differences in schema between source and replica in two scenarios:
4 # A) The source has a GIPK and the replica has no GIPK
5 # B) The replica has a GIPK and the source has no GIPK
6 # For each scenario there is one case where the other (non-GIPK) columns are the same on source and replica;
7 # one where replica has an extra column to the right, and one where the source has an extra column to the right.
8 #
9 # ==== Requirements ====
10 #
11 # R1. If the source contains a GIPK and the replica does not or vice-versa, replication
12 # should work seamlessly despite acceptable schema differences.
13 #
14 # === Implementation ====
15 #
16 # Phase 1: Tests with a GIPK only on the source
17 #
18 # 1. Enable the generation of GIPKs in the source
19 # With the binary log temporarily disabled, create tables t1, t2 and t3 with no primary keys
20 # 2. Create the same tables on the replica
21 # Table t1 has the same number of columns in the replica
22 # Table t2 has less columns in the replica
23 # Table t3 has more columns in the replica
24 # 3. Insert some values on the tables
25 # Check they are replicated correctly
26 # 4. Update and delete tables in all tables
27 # Check changes are replicated correctly
28 #
29 # Phase 2: Tests with a GIPK only on the replica
30 #
31 # 5. Enable the generation of GIPKs on replication
32 # 6. Create tables t4, t5 and t6 without primary keys on the source
33 # On the replica, drop a column on t5 and add an extra column on t6
34 # 7. Insert some values on the tables
35 # Check they are replicated correctly
36 # 8. Update and delete rows in all tables
37 # Check changes are replicated correctly
38 #
39 # 9. Cleanup
40 #
41 # === References ===
42 #
43 # Wl#14639: Row-based replication between tables that differ in the existence of a generated invisible primary key
44 # WL#15419: Make the replica_generate_invisible_primary_key option settable per channel
45 #
46
47 --source include/have_binlog_format_row.inc
48 # This test needs --binlog-row-image = full
49 --let $option_name = binlog_row_image
50 --let $option_value = 'full'
51 --source include/only_with_option.inc
52 --source include/master-slave.inc
53
54 --echo # Phase 1: Tests with a GIPK only on the source
55
56 --echo
57 --echo ##################################################
58 --echo # 1. Enable the generation of GIPKs in the source
59 --echo # With the binary log temporarily disabled, create tables t1, t2 and t3 with no primary keys
60
61 --source include/rpl_connection_master.inc
62
63 # Set at the session level, so no restore needed for MTR
64 SET SESSION sql_generate_invisible_primary_key = ON;
65
66 --source include/disable_binlog.inc
67 CREATE TABLE t1(f1 INT, f2 INT);
68 CREATE TABLE t2(f1 INT, f2 INT);
69 CREATE TABLE t3(f1 INT, f2 INT);
70 --source include/restore_binlog.inc
71
72 SET SESSION sql_generate_invisible_primary_key = OFF;
73
74 --echo
75 --echo ##################################################
76 --echo # 2. Create the same tables on the replica
77 --echo # Table t1 has the same number of columns in the replica
78 --echo # Table t2 has less columns in the replica
79 --echo # Table t3 has more columns in the replica
80
81 --source include/rpl_connection_slave.inc
82
83 CREATE TABLE t1(f1 INT, f2 INT);
84 CREATE TABLE t2(f1 INT);
85 CREATE TABLE t3(f1 INT, f2 INT, f3 INT);
86
87 --echo
88 --echo ##################################################
89 --echo # 3. Insert some values on the tables
90 --echo # Check they are replicated correctly
91
92 --source include/rpl_connection_master.inc
93
94 INSERT INTO t1 VALUES (11, 11);
95 INSERT INTO t1 VALUES (12, 12);
96
97 # t2 has an extra row so after the delete we can
98 # update a row that can affect either the 2 remaining rows in the
99 # the replica
100
101 INSERT INTO t2 VALUES (21, 21);
102 INSERT INTO t2 VALUES (21, 22);
103 INSERT INTO t2 VALUES (21, 23);
104
105 INSERT INTO t3 VALUES (31, 31);
106 INSERT INTO t3 VALUES (32, 32);
107
108 --source include/sync_slave_sql_with_master.inc
109
110 --source include/rpl_connection_slave.inc
111
112 --let $row_count= `SELECT COUNT(*) FROM t1;`
113 --let $assert_text= The table t1 contains the 2 inserted rows
114 --let $assert_cond= $row_count = 2
115 --source include/assert.inc
116
117 --let $row_count= `SELECT COUNT(*) FROM t2;`
118 --let $assert_text= The table t2 contains the 3 inserted rows
119 --let $assert_cond= $row_count = 3
120
121 --source include/assert.inc
122 --let $row_count= `SELECT COUNT(*) FROM t3;`
123 --let $assert_text= The table t3 contains the 2 inserted rows
124 --let $assert_cond= $row_count = 2
125 --source include/assert.inc
126
127 --echo
128 --echo ##################################################
129 --echo # 4. Update and delete rows in all tables
130 --echo # Check changes are replicated correctly
131
132 --source include/rpl_connection_master.inc
133
134 # t1
135
136 UPDATE t1 SET t1.f2 = 13 WHERE t1.f1=12;
137
138 DELETE FROM t1 WHERE t1.f1=11;
139
140 # t2
141
142 DELETE FROM t2 WHERE t2.f2=21;
143
144 UPDATE t2 SET t2.f2 = 24 WHERE t2.f2=23;
145
146 # t3
147
148 UPDATE t3 SET t3.f2 = 33 WHERE t3.f1=31;
149
150 DELETE FROM t3 WHERE t3.f1=32;
151
152 --source include/sync_slave_sql_with_master.inc
153
154 --source include/rpl_connection_slave.inc
155
156 # t1
157
158 --let $row_count= `SELECT COUNT(*) FROM t1;`
159 --let $assert_text= The table t1 contains 1 row
160 --let $assert_cond= $row_count = 1
161 --source include/assert.inc
162
163 --let $row_count= `SELECT COUNT(*) FROM t1 WHERE t1.f2 = 13 AND t1.f1 = 12;`
164 --let $assert_text= The table t1 was updated
165 --let $assert_cond= $row_count = 1
166 --source include/assert.inc
167
168 # t2
169
170 --let $row_count= `SELECT COUNT(*) FROM t2 WHERE t2.f1 = 21`
171 --let $assert_text= The table t2 contains 2 rows with value 21
172 --let $assert_cond= $row_count = 2
173 --source include/assert.inc
174
175 # t3
176
177 --let $row_count= `SELECT COUNT(*) FROM t3;`
178 --let $assert_text= The table t3 contains 1 row
179 --let $assert_cond= $row_count = 1
180 --source include/assert.inc
181
182 --let $row_count= `SELECT COUNT(*) FROM t3 WHERE t3.f2 = 33 AND t3.f1 = 31;`
183 --let $assert_text= The table t3 was updated
184 --let $assert_cond= $row_count = 1
185 --source include/assert.inc
186
187 --source include/rpl_connection_master.inc
188
189 # t2
190
191 # Update a f1 on the source, but since the replica does not contain f2
192 # this update can update either rows in the replica.
193 UPDATE t2 SET t2.f1 = 22 WHERE t2.f2=22;
194
195 --source include/sync_slave_sql_with_master.inc
196
197 --let $row_count= `SELECT COUNT(*) FROM t2 WHERE t2.f1 = 21`
198 --let $assert_text= The table t2 contains 1 rows with value 21
199 --let $assert_cond= $row_count = 1
200 --source include/assert.inc
201
202 --let $row_count= `SELECT COUNT(*) FROM t2 WHERE t2.f1 = 22`
203 --let $assert_text= The table t2 contains 1 rows with value 22
204 --let $assert_cond= $row_count = 1
205 --source include/assert.inc
206
207 --echo # Phase 2: Tests with a GIPK only on the replica
208
209 --echo
210 --echo ##################################################
211 --echo # 5. Enable the generation of GIPKs on replication
212
213 --source include/rpl_connection_slave.inc
214
215 --source include/stop_slave_sql.inc
216 # Store the default value
217 --let $_pk_key_check_value = `SELECT require_table_primary_key_check FROM performance_schema.replication_applier_configuration WHERE channel_name=""`
218 CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE;
219 --source include/start_slave_sql.inc
220
221 --echo
222 --echo ##################################################
223 --echo # 6. Create some tables without primary keys on the source
224 --echo # On the replica, drop a column on t5 and add an extra column on t6
225
226 --source include/rpl_connection_master.inc
227
228 CREATE TABLE t4(f1 INT, f2 INT);
229
230 CREATE TABLE t5(f1 INT, f2 INT);
231
232 CREATE TABLE t6(f1 INT, f2 INT);
233
234 --source include/sync_slave_sql_with_master.inc
235
236 ALTER TABLE t5 DROP COLUMN f2;
237
238 ALTER TABLE t6 ADD COLUMN f3 INT AFTER f2;
239
240 --echo
241 --echo ##################################################
242 --echo # 7. Insert some values on the tables
243 --echo # Check they are replicated correctly
244
245 --source include/rpl_connection_master.inc
246
247 INSERT INTO t4 VALUES (41, 41);
248 INSERT INTO t4 VALUES (42, 42);
249
250 # t5 has an extra row so after the delete we can
251 # update a row that can affect either the 2 remaining rows in the
252 # the replica
253
254 INSERT INTO t5 VALUES (51, 51);
255 INSERT INTO t5 VALUES (51, 52);
256 INSERT INTO t5 VALUES (51, 53);
257
258 INSERT INTO t6 VALUES (61, 61);
259 INSERT INTO t6 VALUES (62, 62);
260
261 --source include/sync_slave_sql_with_master.inc
262
263 --source include/rpl_connection_slave.inc
264
265 --let $row_count= `SELECT COUNT(*) FROM t4;`
266 --let $assert_text= The table t4 contains the 2 inserted rows
267 --let $assert_cond= $row_count = 2
268 --source include/assert.inc
269
270 --let $row_count= `SELECT COUNT(*) FROM t5;`
271 --let $assert_text= The table t5 contains the 3 inserted rows
272 --let $assert_cond= $row_count = 3
273
274 --source include/assert.inc
275 --let $row_count= `SELECT COUNT(*) FROM t6;`
276 --let $assert_text= The table t6 contains the 2 inserted rows
277 --let $assert_cond= $row_count = 2
278 --source include/assert.inc
279
280 --echo
281 --echo ##################################################
282 --echo # 8. Update and delete rows in all tables
283 --echo # Check changes are replicated correctly
284
285 --source include/rpl_connection_master.inc
286
287 # t4
288
289 UPDATE t4 SET t4.f2 = 43 WHERE t4.f1=42;
290
291 DELETE FROM t4 WHERE t4.f1=41;
292
293 # t5
294
295 DELETE FROM t5 WHERE t5.f2=51;
296
297 UPDATE t5 SET t5.f2 = 54 WHERE t5.f2=53;
298
299 # t6
300
301 UPDATE t6 SET t6.f2 = 63 WHERE t6.f1=61;
302
303 DELETE FROM t6 WHERE t6.f1=62;
304
305 --source include/sync_slave_sql_with_master.inc
306
307 --source include/rpl_connection_slave.inc
308
309 # t4
310
311 --let $row_count= `SELECT COUNT(*) FROM t4;`
312 --let $assert_text= The table t4 contains 1 row
313 --let $assert_cond= $row_count = 1
314 --source include/assert.inc
315
316 --let $row_count= `SELECT COUNT(*) FROM t4 WHERE t4.f2 = 43 AND t4.f1 = 42;`
317 --let $assert_text= The table t4 was updated
318 --let $assert_cond= $row_count = 1
319 --source include/assert.inc
320
321 # t5
322
323 --let $row_count= `SELECT COUNT(*) FROM t5 WHERE t5.f1 = 51`
324 --let $assert_text= The table t5 contains 2 rows with value 51
325 --let $assert_cond= $row_count = 2
326 --source include/assert.inc
327
328 # t6
329
330 --let $row_count= `SELECT COUNT(*) FROM t6;`
331 --let $assert_text= The table t6 contains 1 row
332 --let $assert_cond= $row_count = 1
333 --source include/assert.inc
334
335 --let $row_count= `SELECT COUNT(*) FROM t6 WHERE t6.f2 = 63 AND t6.f1 = 61;`
336 --let $assert_text= The table t6 was updated
337 --let $assert_cond= $row_count = 1
338 --source include/assert.inc
339
340 --source include/rpl_connection_master.inc
341
342 # t5
343
344 # Update a f1 on the source, but since the replica does not contain f2
345 # this update can update either rows in the replica.
346 UPDATE t5 SET t5.f1 = 52 WHERE t5.f2=52;
347
348 --source include/sync_slave_sql_with_master.inc
349
350 --let $row_count= `SELECT COUNT(*) FROM t5 WHERE t5.f1 = 51`
351 --let $assert_text= The table t5 contains 1 rows with value 51
352 --let $assert_cond= $row_count = 1
353 --source include/assert.inc
354
355 --let $row_count= `SELECT COUNT(*) FROM t5 WHERE t5.f1 = 52`
356 --let $assert_text= The table t5 contains 1 rows with value 52
357 --let $assert_cond= $row_count = 1
358 --source include/assert.inc
359
360 --echo
361 --echo ##################################################
362 --echo # 9. Cleanup
363
364 --source include/stop_slave_sql.inc
365 --replace_result $_pk_key_check_value PRIMARY_KEY_CHECK_VALUE
366 --eval CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = $_pk_key_check_value
367 --source include/start_slave_sql.inc
368
369 --source include/rpl_connection_master.inc
370
371 DROP TABLE t1;
372 DROP TABLE t2;
373 DROP TABLE t3;
374 DROP TABLE t4;
375 DROP TABLE t5;
376 DROP TABLE t6;
377
378 --source include/rpl_end.inc