Debian Codegraph @

mysql-test/suite/ndb/t/ndb_restore_conv_remap.test

raw
1 -- source include/have_ndb.inc
2 -- source suite/ndb/include/backup_restore_setup.inc
3
4 --disable_query_log
5 set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz;
6 set ndb_autoincrement_prefetch_sz = 1;
7 --enable_query_log
8
9 use test;
10
11 --echo More complex example
12 create table idx_tbl (entry_key bigint unsigned not null auto_increment,
13 k1 varchar(100),
14 k2 varchar(100),
15 data int,
16 primary key (k1, k2),
17 unique(entry_key)) engine=ndb;
18
19 create table type1_1 (entry_key bigint unsigned not null primary key,
20 data int) engine=ndb;
21
22 create table type1_2 (entry_key bigint unsigned not null primary key,
23 data int) engine=ndb;
24
25 create table type2_1 (entry_key bigint unsigned not null,
26 mv_idx bigint unsigned not null auto_increment,
27 data int,
28 primary key (entry_key, mv_idx)) engine=ndb;
29
30 create table type2_2 (entry_key bigint unsigned not null,
31 mv_idx bigint unsigned not null auto_increment,
32 data text,
33 primary key (entry_key, mv_idx)) engine=ndb;
34
35
36 --echo Populate a number of entities, each with
37 --echo a couple of type_1 rows and 2 sets of type_2 rows
38
39 insert into idx_tbl (k1,k2,data) values ('Edinburgh', 'UK', 1);
40 select last_insert_id() into @ek;
41 insert into type1_1 values (@ek, 11);
42 insert into type1_2 values (@ek, 12);
43 insert into type2_1 (entry_key, data) values (@ek, 110);
44 insert into type2_1 (entry_key, data) values (@ek, 111);
45 insert into type2_1 (entry_key, data) values (@ek, 112);
46 insert into type2_1 (entry_key, data) values (@ek, 113);
47 insert into type2_2 (entry_key, data) values (@ek, repeat("0121", 10000));
48 insert into type2_2 (entry_key, data) values (@ek, repeat("0122", 10000));
49
50 insert into idx_tbl (k1,k2,data) values ('Bristol', 'UK', 2);
51 select last_insert_id() into @ek;
52 insert into type1_1 values (@ek, 21);
53 insert into type1_2 values (@ek, 22);
54 insert into type2_1 (entry_key, data) values (@ek, 210);
55 insert into type2_1 (entry_key, data) values (@ek, 211);
56 insert into type2_1 (entry_key, data) values (@ek, 212);
57 insert into type2_1 (entry_key, data) values (@ek, 213);
58 insert into type2_2 (entry_key, data) values (@ek, repeat("0221", 10000));
59 insert into type2_2 (entry_key, data) values (@ek, repeat("0222", 10000));
60
61 insert into idx_tbl (k1,k2,data) values ('Glasgow', 'UK', 3);
62 select last_insert_id() into @ek;
63 insert into type1_1 values (@ek, 31);
64 insert into type1_2 values (@ek, 32);
65 insert into type2_1 (entry_key, data) values (@ek, 310);
66 insert into type2_1 (entry_key, data) values (@ek, 311);
67 insert into type2_1 (entry_key, data) values (@ek, 312);
68 insert into type2_1 (entry_key, data) values (@ek, 313);
69 insert into type2_2 (entry_key, data) values (@ek, repeat("0321", 10000));
70 insert into type2_2 (entry_key, data) values (@ek, repeat("0322", 10000));
71
72 insert into idx_tbl (k1,k2,data) values ('London', 'UK', 4);
73 select last_insert_id() into @ek;
74 insert into type1_1 values (@ek, 41);
75 insert into type1_2 values (@ek, 42);
76 insert into type2_1 (entry_key, data) values (@ek, 410);
77 insert into type2_1 (entry_key, data) values (@ek, 411);
78 insert into type2_1 (entry_key, data) values (@ek, 412);
79 insert into type2_1 (entry_key, data) values (@ek, 413);
80 insert into type2_2 (entry_key, data) values (@ek, repeat("0421", 10000));
81 insert into type2_2 (entry_key, data) values (@ek, repeat("0422", 10000));
82
83 insert into idx_tbl (k1,k2,data) values ('Paris', 'France', 5);
84 select last_insert_id() into @ek;
85 insert into type1_1 values (@ek, 51);
86 insert into type1_2 values (@ek, 52);
87 insert into type2_1 (entry_key, data) values (@ek, 510);
88 insert into type2_1 (entry_key, data) values (@ek, 511);
89 insert into type2_1 (entry_key, data) values (@ek, 512);
90 insert into type2_1 (entry_key, data) values (@ek, 513);
91 insert into type2_2 (entry_key, data) values (@ek, repeat("0521", 10000));
92 insert into type2_2 (entry_key, data) values (@ek, repeat("0522", 10000));
93
94 insert into idx_tbl (k1,k2,data) values ('Heidelberg', 'Germany', 6);
95 select last_insert_id() into @ek;
96 insert into type1_1 values (@ek, 61);
97 insert into type1_2 values (@ek, 62);
98 insert into type2_1 (entry_key, data) values (@ek, 610);
99 insert into type2_1 (entry_key, data) values (@ek, 611);
100 insert into type2_1 (entry_key, data) values (@ek, 612);
101 insert into type2_1 (entry_key, data) values (@ek, 613);
102 insert into type2_2 (entry_key, data) values (@ek, repeat("0621", 10000));
103 insert into type2_2 (entry_key, data) values (@ek, repeat("0622", 10000));
104
105 insert into idx_tbl (k1,k2,data) values ('Riga', 'Latvia', 7);
106 select last_insert_id() into @ek;
107 insert into type1_1 values (@ek, 71);
108 insert into type1_2 values (@ek, 72);
109 insert into type2_1 (entry_key, data) values (@ek, 710);
110 insert into type2_1 (entry_key, data) values (@ek, 711);
111 insert into type2_1 (entry_key, data) values (@ek, 712);
112 insert into type2_1 (entry_key, data) values (@ek, 713);
113 insert into type2_2 (entry_key, data) values (@ek, repeat("0721", 10000));
114 insert into type2_2 (entry_key, data) values (@ek, repeat("0722", 10000));
115
116 insert into idx_tbl (k1,k2,data) values ('Orlando', 'USA', 8);
117 select last_insert_id() into @ek;
118 insert into type1_1 values (@ek, 81);
119 insert into type1_2 values (@ek, 82);
120 insert into type2_1 (entry_key, data) values (@ek, 810);
121 insert into type2_1 (entry_key, data) values (@ek, 811);
122 insert into type2_1 (entry_key, data) values (@ek, 812);
123 insert into type2_1 (entry_key, data) values (@ek, 813);
124 insert into type2_2 (entry_key, data) values (@ek, repeat("0821", 10000));
125 insert into type2_2 (entry_key, data) values (@ek, repeat("0822", 10000));
126
127 select * from idx_tbl order by entry_key;
128 select * from type1_1 order by entry_key;
129 select * from type1_2 order by entry_key;
130 select * from type2_1 order by entry_key, mv_idx;
131 select entry_key, mv_idx, right(data, 4) from type2_2 order by entry_key, mv_idx;
132
133 --echo First set of content
134 --echo --------------------
135 select idx_tbl.entry_key,
136 idx_tbl.k1,
137 idx_tbl.k2,
138 idx_tbl.data,
139 type1_1.data,
140 type1_2.data,
141 type2_1.mv_idx,
142 type2_1.data,
143 type2_2.mv_idx,
144 right(type2_2.data, 4),
145 length(type2_2.data)
146 from
147 idx_tbl join type1_1 join type1_2 join type2_1 join type2_2
148 on
149 idx_tbl.entry_key = type1_1.entry_key
150 and
151 idx_tbl.entry_key = type1_2.entry_key
152 and
153 idx_tbl.entry_key = type2_1.entry_key
154 and
155 idx_tbl.entry_key = type2_2.entry_key
156 order by idx_tbl.entry_key, type2_1.data, right(type2_2.data, 4);
157
158 --echo Show auto-inc values
159 select count(1), max(entry_key) from test.idx_tbl;
160 show create table idx_tbl;
161 select count(1), max(mv_idx) from test.type2_1;
162 show create table type2_1;
163 select count(1), max(mv_idx) from test.type2_2;
164 show create table type2_2;
165
166 --echo No mv_idx duplicates in type_2 tables
167 select mv_idx, count(1) as ct
168 from type2_1 group by mv_idx
169 having ct > 1;
170 select mv_idx, count(1) as ct
171 from type2_2 group by mv_idx
172 having ct > 1;
173
174
175 --echo Run backup
176 --source include/ndb_backup.inc
177
178 --let first_backup_id=$the_backup_id
179
180 --echo Create a different set of entities, with the same auto-inc
181 --echo keys for entry_key and mv_idx
182 update idx_tbl set k1="Stockholm", k2="Sweden" where entry_key = 1;
183 update idx_tbl set k1="Trondheim", k2="Norway" where entry_key = 2;
184 update idx_tbl set k1="Portland", k2="USA" where entry_key=3;
185 update idx_tbl set k1="Bangalore", k2="India" where entry_key=4;
186 update idx_tbl set k1="Helsinki", k2="Finland" where entry_key=5;
187 update idx_tbl set k1="Lisbon", k2="Portugal" where entry_key=6;
188 update idx_tbl set k1="Norrwaste", k2="Sweden" where entry_key=7;
189 update idx_tbl set k1="ROW", k2="Earth" where entry_key=8;
190
191 --echo Modify data to be distinct
192 update idx_tbl set data = data+10;
193 update type1_1 set data = data+100;
194 update type1_2 set data = data+100;
195 update type2_1 set data = data+1000;
196 update type2_2 set data = repeat(right(data, 4)+1000, 10000);
197
198 --echo Second set of content
199 --echo ---------------------
200
201 select idx_tbl.entry_key,
202 idx_tbl.k1,
203 idx_tbl.k2,
204 idx_tbl.data,
205 type1_1.data,
206 type1_2.data,
207 type2_1.mv_idx,
208 type2_1.data,
209 type2_2.mv_idx,
210 right(type2_2.data,4),
211 length(type2_2.data)
212 from
213 idx_tbl join type1_1 join type1_2 join type2_1 join type2_2
214 on
215 idx_tbl.entry_key = type1_1.entry_key
216 and
217 idx_tbl.entry_key = type1_2.entry_key
218 and
219 idx_tbl.entry_key = type2_1.entry_key
220 and
221 idx_tbl.entry_key = type2_2.entry_key
222 order by idx_tbl.entry_key, type2_1.data, right(type2_2.data,4);
223
224 --echo Show auto-inc values
225 select count(1), max(entry_key) from test.idx_tbl;
226 show create table idx_tbl;
227 select count(1), max(mv_idx) from test.type2_1;
228 show create table type2_1;
229 select count(1), max(mv_idx) from test.type2_2;
230 show create table type2_2;
231
232 --echo No mv_idx duplicates in type_2 tables
233 select mv_idx, count(1) as ct
234 from type2_1 group by mv_idx
235 having ct > 1;
236 select mv_idx, count(1) as ct
237 from type2_2 group by mv_idx
238 having ct > 1;
239
240 --echo Run backup
241 --source include/ndb_backup.inc
242
243 --let second_backup_id=$the_backup_id
244
245 drop table idx_tbl, type1_1, type1_2, type2_1, type2_2;
246
247 --echo Run normal restore for first set of content
248 --exec $NDB_RESTORE -b $first_backup_id -n 1 -m $NDB_BACKUPS-$first_backup_id >> $NDB_TOOLS_OUTPUT
249 --exec $NDB_RESTORE -b $first_backup_id -n 1 -r $NDB_BACKUPS-$first_backup_id >> $NDB_TOOLS_OUTPUT
250 --exec $NDB_RESTORE -b $first_backup_id -n 2 -r $NDB_BACKUPS-$first_backup_id >> $NDB_TOOLS_OUTPUT
251
252 --source suite/ndb/include/ndb_sync_metadata.inc
253
254 --let next_entry_key=query_get_value(select auto_increment from information_schema.tables where table_schema='test' and table_name='idx_tbl', AUTO_INCREMENT, 1)
255 --let next_type2_1=query_get_value(select auto_increment from information_schema.tables where table_schema='test' and table_name='type2_1', AUTO_INCREMENT, 1)
256 --let next_type2_2=query_get_value(select auto_increment from information_schema.tables where table_schema='test' and table_name='type2_2', AUTO_INCREMENT, 1)
257
258 --let remap_args = -x test.idx_tbl.entry_key:offset:$next_entry_key
259 --let remap_args = $remap_args -x test.type1_1.entry_key:offset:$next_entry_key
260 --let remap_args = $remap_args -x test.type1_2.entry_key:offset:$next_entry_key
261 --let remap_args = $remap_args -x test.type2_1.entry_key:offset:$next_entry_key
262 --let remap_args = $remap_args -x test.type2_1.mv_idx:offset:$next_type2_1
263 --let remap_args = $remap_args -x test.type2_2.entry_key:offset:$next_entry_key
264 --let remap_args = $remap_args -x test.type2_2.mv_idx:offset:$next_type2_2
265
266 --echo Remap args : $remap_args
267
268 --echo Run offset restore for second set of content
269 --exec $NDB_RESTORE -b $second_backup_id -n 1 -r $remap_args $NDB_BACKUPS-$second_backup_id >> $NDB_TOOLS_OUTPUT
270 --exec $NDB_RESTORE -b $second_backup_id -n 2 -r $remap_args $NDB_BACKUPS-$second_backup_id >> $NDB_TOOLS_OUTPUT
271
272 --echo Combined dataset
273 --echo -----------------
274 select idx_tbl.entry_key,
275 idx_tbl.k1,
276 idx_tbl.k2,
277 idx_tbl.data,
278 type1_1.data,
279 type1_2.data,
280 type2_1.mv_idx,
281 type2_1.data,
282 type2_2.mv_idx,
283 right(type2_2.data,4),
284 length(type2_2.data)
285 from
286 idx_tbl join type1_1 join type1_2 join type2_1 join type2_2
287 on
288 idx_tbl.entry_key = type1_1.entry_key
289 and
290 idx_tbl.entry_key = type1_2.entry_key
291 and
292 idx_tbl.entry_key = type2_1.entry_key
293 and
294 idx_tbl.entry_key = type2_2.entry_key
295 order by idx_tbl.entry_key, type2_1.data, right(type2_2.data,4);
296
297 --echo Show auto-inc values
298 select count(1), max(entry_key) from test.idx_tbl;
299 show create table idx_tbl;
300 select count(1), max(mv_idx) from test.type2_1;
301 show create table type2_1;
302 select count(1), max(mv_idx) from test.type2_2;
303 show create table type2_2;
304
305 --echo No mv_idx duplicates in type_2 tables
306 select mv_idx, count(1) as ct
307 from type2_1 group by mv_idx
308 having ct > 1;
309 select mv_idx, count(1) as ct
310 from type2_2 group by mv_idx
311 having ct > 1;
312
313 --echo Verify the insert of further entity succeeds
314 insert into idx_tbl (k1,k2,data) values ('Sancerre', 'France', 9);
315 select last_insert_id() into @ek;
316 insert into type1_1 values (@ek, 91);
317 insert into type1_2 values (@ek, 92);
318 insert into type2_1 (entry_key, data) values (@ek, 910);
319 insert into type2_1 (entry_key, data) values (@ek, 911);
320 insert into type2_1 (entry_key, data) values (@ek, 912);
321 insert into type2_1 (entry_key, data) values (@ek, 913);
322 insert into type2_2 (entry_key, data) values (@ek, repeat("0921", 10000));
323 insert into type2_2 (entry_key, data) values (@ek, repeat("0922", 10000));
324
325 --echo Modified content
326 --echo ----------------
327 select idx_tbl.entry_key,
328 idx_tbl.k1,
329 idx_tbl.k2,
330 idx_tbl.data,
331 type1_1.data,
332 type1_2.data,
333 type2_1.mv_idx,
334 type2_1.data,
335 type2_2.mv_idx,
336 right(type2_2.data,4),
337 length(type2_2.data)
338 from
339 idx_tbl join type1_1 join type1_2 join type2_1 join type2_2
340 on
341 idx_tbl.entry_key = type1_1.entry_key
342 and
343 idx_tbl.entry_key = type1_2.entry_key
344 and
345 idx_tbl.entry_key = type2_1.entry_key
346 and
347 idx_tbl.entry_key = type2_2.entry_key
348 order by idx_tbl.entry_key, type2_1.data, right(type2_2.data,4);
349
350 --echo Show auto-inc values
351 select count(1), max(entry_key) from test.idx_tbl;
352 show create table idx_tbl;
353 select count(1), max(mv_idx) from test.type2_1;
354 show create table type2_1;
355 select count(1), max(mv_idx) from test.type2_2;
356 show create table type2_2;
357
358 --echo No mv_idx duplicates in type_2 tables
359 select mv_idx, count(1) as ct
360 from type2_1 group by mv_idx
361 having ct > 1;
362 select mv_idx, count(1) as ct
363 from type2_2 group by mv_idx
364 having ct > 1;
365
366 drop table idx_tbl, type1_1, type1_2, type2_1, type2_2;
367
368 --disable_query_log
369 set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz;
370 --enable_query_log
371
372 --source suite/ndb/include/backup_restore_cleanup.inc
373 --remove_file $NDB_TOOLS_OUTPUT