Debian Codegraph @

mysql-test/suite/ndb/r/ndb_restore_conv_remap.result

raw
1 use test;
2 More complex example
3 create table idx_tbl (entry_key bigint unsigned not null auto_increment,
4 k1 varchar(100),
5 k2 varchar(100),
6 data int,
7 primary key (k1, k2),
8 unique(entry_key)) engine=ndb;
9 create table type1_1 (entry_key bigint unsigned not null primary key,
10 data int) engine=ndb;
11 create table type1_2 (entry_key bigint unsigned not null primary key,
12 data int) engine=ndb;
13 create table type2_1 (entry_key bigint unsigned not null,
14 mv_idx bigint unsigned not null auto_increment,
15 data int,
16 primary key (entry_key, mv_idx)) engine=ndb;
17 create table type2_2 (entry_key bigint unsigned not null,
18 mv_idx bigint unsigned not null auto_increment,
19 data text,
20 primary key (entry_key, mv_idx)) engine=ndb;
21 Populate a number of entities, each with
22 a couple of type_1 rows and 2 sets of type_2 rows
23 insert into idx_tbl (k1,k2,data) values ('Edinburgh', 'UK', 1);
24 select last_insert_id() into @ek;
25 insert into type1_1 values (@ek, 11);
26 insert into type1_2 values (@ek, 12);
27 insert into type2_1 (entry_key, data) values (@ek, 110);
28 insert into type2_1 (entry_key, data) values (@ek, 111);
29 insert into type2_1 (entry_key, data) values (@ek, 112);
30 insert into type2_1 (entry_key, data) values (@ek, 113);
31 insert into type2_2 (entry_key, data) values (@ek, repeat("0121", 10000));
32 insert into type2_2 (entry_key, data) values (@ek, repeat("0122", 10000));
33 insert into idx_tbl (k1,k2,data) values ('Bristol', 'UK', 2);
34 select last_insert_id() into @ek;
35 insert into type1_1 values (@ek, 21);
36 insert into type1_2 values (@ek, 22);
37 insert into type2_1 (entry_key, data) values (@ek, 210);
38 insert into type2_1 (entry_key, data) values (@ek, 211);
39 insert into type2_1 (entry_key, data) values (@ek, 212);
40 insert into type2_1 (entry_key, data) values (@ek, 213);
41 insert into type2_2 (entry_key, data) values (@ek, repeat("0221", 10000));
42 insert into type2_2 (entry_key, data) values (@ek, repeat("0222", 10000));
43 insert into idx_tbl (k1,k2,data) values ('Glasgow', 'UK', 3);
44 select last_insert_id() into @ek;
45 insert into type1_1 values (@ek, 31);
46 insert into type1_2 values (@ek, 32);
47 insert into type2_1 (entry_key, data) values (@ek, 310);
48 insert into type2_1 (entry_key, data) values (@ek, 311);
49 insert into type2_1 (entry_key, data) values (@ek, 312);
50 insert into type2_1 (entry_key, data) values (@ek, 313);
51 insert into type2_2 (entry_key, data) values (@ek, repeat("0321", 10000));
52 insert into type2_2 (entry_key, data) values (@ek, repeat("0322", 10000));
53 insert into idx_tbl (k1,k2,data) values ('London', 'UK', 4);
54 select last_insert_id() into @ek;
55 insert into type1_1 values (@ek, 41);
56 insert into type1_2 values (@ek, 42);
57 insert into type2_1 (entry_key, data) values (@ek, 410);
58 insert into type2_1 (entry_key, data) values (@ek, 411);
59 insert into type2_1 (entry_key, data) values (@ek, 412);
60 insert into type2_1 (entry_key, data) values (@ek, 413);
61 insert into type2_2 (entry_key, data) values (@ek, repeat("0421", 10000));
62 insert into type2_2 (entry_key, data) values (@ek, repeat("0422", 10000));
63 insert into idx_tbl (k1,k2,data) values ('Paris', 'France', 5);
64 select last_insert_id() into @ek;
65 insert into type1_1 values (@ek, 51);
66 insert into type1_2 values (@ek, 52);
67 insert into type2_1 (entry_key, data) values (@ek, 510);
68 insert into type2_1 (entry_key, data) values (@ek, 511);
69 insert into type2_1 (entry_key, data) values (@ek, 512);
70 insert into type2_1 (entry_key, data) values (@ek, 513);
71 insert into type2_2 (entry_key, data) values (@ek, repeat("0521", 10000));
72 insert into type2_2 (entry_key, data) values (@ek, repeat("0522", 10000));
73 insert into idx_tbl (k1,k2,data) values ('Heidelberg', 'Germany', 6);
74 select last_insert_id() into @ek;
75 insert into type1_1 values (@ek, 61);
76 insert into type1_2 values (@ek, 62);
77 insert into type2_1 (entry_key, data) values (@ek, 610);
78 insert into type2_1 (entry_key, data) values (@ek, 611);
79 insert into type2_1 (entry_key, data) values (@ek, 612);
80 insert into type2_1 (entry_key, data) values (@ek, 613);
81 insert into type2_2 (entry_key, data) values (@ek, repeat("0621", 10000));
82 insert into type2_2 (entry_key, data) values (@ek, repeat("0622", 10000));
83 insert into idx_tbl (k1,k2,data) values ('Riga', 'Latvia', 7);
84 select last_insert_id() into @ek;
85 insert into type1_1 values (@ek, 71);
86 insert into type1_2 values (@ek, 72);
87 insert into type2_1 (entry_key, data) values (@ek, 710);
88 insert into type2_1 (entry_key, data) values (@ek, 711);
89 insert into type2_1 (entry_key, data) values (@ek, 712);
90 insert into type2_1 (entry_key, data) values (@ek, 713);
91 insert into type2_2 (entry_key, data) values (@ek, repeat("0721", 10000));
92 insert into type2_2 (entry_key, data) values (@ek, repeat("0722", 10000));
93 insert into idx_tbl (k1,k2,data) values ('Orlando', 'USA', 8);
94 select last_insert_id() into @ek;
95 insert into type1_1 values (@ek, 81);
96 insert into type1_2 values (@ek, 82);
97 insert into type2_1 (entry_key, data) values (@ek, 810);
98 insert into type2_1 (entry_key, data) values (@ek, 811);
99 insert into type2_1 (entry_key, data) values (@ek, 812);
100 insert into type2_1 (entry_key, data) values (@ek, 813);
101 insert into type2_2 (entry_key, data) values (@ek, repeat("0821", 10000));
102 insert into type2_2 (entry_key, data) values (@ek, repeat("0822", 10000));
103 select * from idx_tbl order by entry_key;
104 entry_key k1 k2 data
105 1 Edinburgh UK 1
106 2 Bristol UK 2
107 3 Glasgow UK 3
108 4 London UK 4
109 5 Paris France 5
110 6 Heidelberg Germany 6
111 7 Riga Latvia 7
112 8 Orlando USA 8
113 select * from type1_1 order by entry_key;
114 entry_key data
115 1 11
116 2 21
117 3 31
118 4 41
119 5 51
120 6 61
121 7 71
122 8 81
123 select * from type1_2 order by entry_key;
124 entry_key data
125 1 12
126 2 22
127 3 32
128 4 42
129 5 52
130 6 62
131 7 72
132 8 82
133 select * from type2_1 order by entry_key, mv_idx;
134 entry_key mv_idx data
135 1 1 110
136 1 2 111
137 1 3 112
138 1 4 113
139 2 5 210
140 2 6 211
141 2 7 212
142 2 8 213
143 3 9 310
144 3 10 311
145 3 11 312
146 3 12 313
147 4 13 410
148 4 14 411
149 4 15 412
150 4 16 413
151 5 17 510
152 5 18 511
153 5 19 512
154 5 20 513
155 6 21 610
156 6 22 611
157 6 23 612
158 6 24 613
159 7 25 710
160 7 26 711
161 7 27 712
162 7 28 713
163 8 29 810
164 8 30 811
165 8 31 812
166 8 32 813
167 select entry_key, mv_idx, right(data, 4) from type2_2 order by entry_key, mv_idx;
168 entry_key mv_idx right(data, 4)
169 1 1 0121
170 1 2 0122
171 2 3 0221
172 2 4 0222
173 3 5 0321
174 3 6 0322
175 4 7 0421
176 4 8 0422
177 5 9 0521
178 5 10 0522
179 6 11 0621
180 6 12 0622
181 7 13 0721
182 7 14 0722
183 8 15 0821
184 8 16 0822
185 First set of content
186 --------------------
187 select idx_tbl.entry_key,
188 idx_tbl.k1,
189 idx_tbl.k2,
190 idx_tbl.data,
191 type1_1.data,
192 type1_2.data,
193 type2_1.mv_idx,
194 type2_1.data,
195 type2_2.mv_idx,
196 right(type2_2.data, 4),
197 length(type2_2.data)
198 from
199 idx_tbl join type1_1 join type1_2 join type2_1 join type2_2
200 on
201 idx_tbl.entry_key = type1_1.entry_key
202 and
203 idx_tbl.entry_key = type1_2.entry_key
204 and
205 idx_tbl.entry_key = type2_1.entry_key
206 and
207 idx_tbl.entry_key = type2_2.entry_key
208 order by idx_tbl.entry_key, type2_1.data, right(type2_2.data, 4);
209 entry_key k1 k2 data data data mv_idx data mv_idx right(type2_2.data, 4) length(type2_2.data)
210 1 Edinburgh UK 1 11 12 1 110 1 0121 40000
211 1 Edinburgh UK 1 11 12 1 110 2 0122 40000
212 1 Edinburgh UK 1 11 12 2 111 1 0121 40000
213 1 Edinburgh UK 1 11 12 2 111 2 0122 40000
214 1 Edinburgh UK 1 11 12 3 112 1 0121 40000
215 1 Edinburgh UK 1 11 12 3 112 2 0122 40000
216 1 Edinburgh UK 1 11 12 4 113 1 0121 40000
217 1 Edinburgh UK 1 11 12 4 113 2 0122 40000
218 2 Bristol UK 2 21 22 5 210 3 0221 40000
219 2 Bristol UK 2 21 22 5 210 4 0222 40000
220 2 Bristol UK 2 21 22 6 211 3 0221 40000
221 2 Bristol UK 2 21 22 6 211 4 0222 40000
222 2 Bristol UK 2 21 22 7 212 3 0221 40000
223 2 Bristol UK 2 21 22 7 212 4 0222 40000
224 2 Bristol UK 2 21 22 8 213 3 0221 40000
225 2 Bristol UK 2 21 22 8 213 4 0222 40000
226 3 Glasgow UK 3 31 32 9 310 5 0321 40000
227 3 Glasgow UK 3 31 32 9 310 6 0322 40000
228 3 Glasgow UK 3 31 32 10 311 5 0321 40000
229 3 Glasgow UK 3 31 32 10 311 6 0322 40000
230 3 Glasgow UK 3 31 32 11 312 5 0321 40000
231 3 Glasgow UK 3 31 32 11 312 6 0322 40000
232 3 Glasgow UK 3 31 32 12 313 5 0321 40000
233 3 Glasgow UK 3 31 32 12 313 6 0322 40000
234 4 London UK 4 41 42 13 410 7 0421 40000
235 4 London UK 4 41 42 13 410 8 0422 40000
236 4 London UK 4 41 42 14 411 7 0421 40000
237 4 London UK 4 41 42 14 411 8 0422 40000
238 4 London UK 4 41 42 15 412 7 0421 40000
239 4 London UK 4 41 42 15 412 8 0422 40000
240 4 London UK 4 41 42 16 413 7 0421 40000
241 4 London UK 4 41 42 16 413 8 0422 40000
242 5 Paris France 5 51 52 17 510 9 0521 40000
243 5 Paris France 5 51 52 17 510 10 0522 40000
244 5 Paris France 5 51 52 18 511 9 0521 40000
245 5 Paris France 5 51 52 18 511 10 0522 40000
246 5 Paris France 5 51 52 19 512 9 0521 40000
247 5 Paris France 5 51 52 19 512 10 0522 40000
248 5 Paris France 5 51 52 20 513 9 0521 40000
249 5 Paris France 5 51 52 20 513 10 0522 40000
250 6 Heidelberg Germany 6 61 62 21 610 11 0621 40000
251 6 Heidelberg Germany 6 61 62 21 610 12 0622 40000
252 6 Heidelberg Germany 6 61 62 22 611 11 0621 40000
253 6 Heidelberg Germany 6 61 62 22 611 12 0622 40000
254 6 Heidelberg Germany 6 61 62 23 612 11 0621 40000
255 6 Heidelberg Germany 6 61 62 23 612 12 0622 40000
256 6 Heidelberg Germany 6 61 62 24 613 11 0621 40000
257 6 Heidelberg Germany 6 61 62 24 613 12 0622 40000
258 7 Riga Latvia 7 71 72 25 710 13 0721 40000
259 7 Riga Latvia 7 71 72 25 710 14 0722 40000
260 7 Riga Latvia 7 71 72 26 711 13 0721 40000
261 7 Riga Latvia 7 71 72 26 711 14 0722 40000
262 7 Riga Latvia 7 71 72 27 712 13 0721 40000
263 7 Riga Latvia 7 71 72 27 712 14 0722 40000
264 7 Riga Latvia 7 71 72 28 713 13 0721 40000
265 7 Riga Latvia 7 71 72 28 713 14 0722 40000
266 8 Orlando USA 8 81 82 29 810 15 0821 40000
267 8 Orlando USA 8 81 82 29 810 16 0822 40000
268 8 Orlando USA 8 81 82 30 811 15 0821 40000
269 8 Orlando USA 8 81 82 30 811 16 0822 40000
270 8 Orlando USA 8 81 82 31 812 15 0821 40000
271 8 Orlando USA 8 81 82 31 812 16 0822 40000
272 8 Orlando USA 8 81 82 32 813 15 0821 40000
273 8 Orlando USA 8 81 82 32 813 16 0822 40000
274 Show auto-inc values
275 select count(1), max(entry_key) from test.idx_tbl;
276 count(1) max(entry_key)
277 8 8
278 show create table idx_tbl;
279 Table Create Table
280 idx_tbl CREATE TABLE `idx_tbl` (
281 `entry_key` bigint unsigned NOT NULL AUTO_INCREMENT,
282 `k1` varchar(100) NOT NULL,
283 `k2` varchar(100) NOT NULL,
284 `data` int DEFAULT NULL,
285 PRIMARY KEY (`k1`,`k2`),
286 UNIQUE KEY `entry_key` (`entry_key`)
287 ) ENGINE=ndbcluster AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
288 select count(1), max(mv_idx) from test.type2_1;
289 count(1) max(mv_idx)
290 32 32
291 show create table type2_1;
292 Table Create Table
293 type2_1 CREATE TABLE `type2_1` (
294 `entry_key` bigint unsigned NOT NULL,
295 `mv_idx` bigint unsigned NOT NULL AUTO_INCREMENT,
296 `data` int DEFAULT NULL,
297 PRIMARY KEY (`entry_key`,`mv_idx`)
298 ) ENGINE=ndbcluster AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
299 select count(1), max(mv_idx) from test.type2_2;
300 count(1) max(mv_idx)
301 16 16
302 show create table type2_2;
303 Table Create Table
304 type2_2 CREATE TABLE `type2_2` (
305 `entry_key` bigint unsigned NOT NULL,
306 `mv_idx` bigint unsigned NOT NULL AUTO_INCREMENT,
307 `data` text,
308 PRIMARY KEY (`entry_key`,`mv_idx`)
309 ) ENGINE=ndbcluster AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
310 No mv_idx duplicates in type_2 tables
311 select mv_idx, count(1) as ct
312 from type2_1 group by mv_idx
313 having ct > 1;
314 mv_idx ct
315 select mv_idx, count(1) as ct
316 from type2_2 group by mv_idx
317 having ct > 1;
318 mv_idx ct
319 Run backup
320 Create a different set of entities, with the same auto-inc
321 keys for entry_key and mv_idx
322 update idx_tbl set k1="Stockholm", k2="Sweden" where entry_key = 1;
323 update idx_tbl set k1="Trondheim", k2="Norway" where entry_key = 2;
324 update idx_tbl set k1="Portland", k2="USA" where entry_key=3;
325 update idx_tbl set k1="Bangalore", k2="India" where entry_key=4;
326 update idx_tbl set k1="Helsinki", k2="Finland" where entry_key=5;
327 update idx_tbl set k1="Lisbon", k2="Portugal" where entry_key=6;
328 update idx_tbl set k1="Norrwaste", k2="Sweden" where entry_key=7;
329 update idx_tbl set k1="ROW", k2="Earth" where entry_key=8;
330 Modify data to be distinct
331 update idx_tbl set data = data+10;
332 update type1_1 set data = data+100;
333 update type1_2 set data = data+100;
334 update type2_1 set data = data+1000;
335 update type2_2 set data = repeat(right(data, 4)+1000, 10000);
336 Second set of content
337 ---------------------
338 select idx_tbl.entry_key,
339 idx_tbl.k1,
340 idx_tbl.k2,
341 idx_tbl.data,
342 type1_1.data,
343 type1_2.data,
344 type2_1.mv_idx,
345 type2_1.data,
346 type2_2.mv_idx,
347 right(type2_2.data,4),
348 length(type2_2.data)
349 from
350 idx_tbl join type1_1 join type1_2 join type2_1 join type2_2
351 on
352 idx_tbl.entry_key = type1_1.entry_key
353 and
354 idx_tbl.entry_key = type1_2.entry_key
355 and
356 idx_tbl.entry_key = type2_1.entry_key
357 and
358 idx_tbl.entry_key = type2_2.entry_key
359 order by idx_tbl.entry_key, type2_1.data, right(type2_2.data,4);
360 entry_key k1 k2 data data data mv_idx data mv_idx right(type2_2.data,4) length(type2_2.data)
361 1 Stockholm Sweden 11 111 112 1 1110 1 1121 40000
362 1 Stockholm Sweden 11 111 112 1 1110 2 1122 40000
363 1 Stockholm Sweden 11 111 112 2 1111 1 1121 40000
364 1 Stockholm Sweden 11 111 112 2 1111 2 1122 40000
365 1 Stockholm Sweden 11 111 112 3 1112 1 1121 40000
366 1 Stockholm Sweden 11 111 112 3 1112 2 1122 40000
367 1 Stockholm Sweden 11 111 112 4 1113 1 1121 40000
368 1 Stockholm Sweden 11 111 112 4 1113 2 1122 40000
369 2 Trondheim Norway 12 121 122 5 1210 3 1221 40000
370 2 Trondheim Norway 12 121 122 5 1210 4 1222 40000
371 2 Trondheim Norway 12 121 122 6 1211 3 1221 40000
372 2 Trondheim Norway 12 121 122 6 1211 4 1222 40000
373 2 Trondheim Norway 12 121 122 7 1212 3 1221 40000
374 2 Trondheim Norway 12 121 122 7 1212 4 1222 40000
375 2 Trondheim Norway 12 121 122 8 1213 3 1221 40000
376 2 Trondheim Norway 12 121 122 8 1213 4 1222 40000
377 3 Portland USA 13 131 132 9 1310 5 1321 40000
378 3 Portland USA 13 131 132 9 1310 6 1322 40000
379 3 Portland USA 13 131 132 10 1311 5 1321 40000
380 3 Portland USA 13 131 132 10 1311 6 1322 40000
381 3 Portland USA 13 131 132 11 1312 5 1321 40000
382 3 Portland USA 13 131 132 11 1312 6 1322 40000
383 3 Portland USA 13 131 132 12 1313 5 1321 40000
384 3 Portland USA 13 131 132 12 1313 6 1322 40000
385 4 Bangalore India 14 141 142 13 1410 7 1421 40000
386 4 Bangalore India 14 141 142 13 1410 8 1422 40000
387 4 Bangalore India 14 141 142 14 1411 7 1421 40000
388 4 Bangalore India 14 141 142 14 1411 8 1422 40000
389 4 Bangalore India 14 141 142 15 1412 7 1421 40000
390 4 Bangalore India 14 141 142 15 1412 8 1422 40000
391 4 Bangalore India 14 141 142 16 1413 7 1421 40000
392 4 Bangalore India 14 141 142 16 1413 8 1422 40000
393 5 Helsinki Finland 15 151 152 17 1510 9 1521 40000
394 5 Helsinki Finland 15 151 152 17 1510 10 1522 40000
395 5 Helsinki Finland 15 151 152 18 1511 9 1521 40000
396 5 Helsinki Finland 15 151 152 18 1511 10 1522 40000
397 5 Helsinki Finland 15 151 152 19 1512 9 1521 40000
398 5 Helsinki Finland 15 151 152 19 1512 10 1522 40000
399 5 Helsinki Finland 15 151 152 20 1513 9 1521 40000
400 5 Helsinki Finland 15 151 152 20 1513 10 1522 40000
401 6 Lisbon Portugal 16 161 162 21 1610 11 1621 40000
402 6 Lisbon Portugal 16 161 162 21 1610 12 1622 40000
403 6 Lisbon Portugal 16 161 162 22 1611 11 1621 40000
404 6 Lisbon Portugal 16 161 162 22 1611 12 1622 40000
405 6 Lisbon Portugal 16 161 162 23 1612 11 1621 40000
406 6 Lisbon Portugal 16 161 162 23 1612 12 1622 40000
407 6 Lisbon Portugal 16 161 162 24 1613 11 1621 40000
408 6 Lisbon Portugal 16 161 162 24 1613 12 1622 40000
409 7 Norrwaste Sweden 17 171 172 25 1710 13 1721 40000
410 7 Norrwaste Sweden 17 171 172 25 1710 14 1722 40000
411 7 Norrwaste Sweden 17 171 172 26 1711 13 1721 40000
412 7 Norrwaste Sweden 17 171 172 26 1711 14 1722 40000
413 7 Norrwaste Sweden 17 171 172 27 1712 13 1721 40000
414 7 Norrwaste Sweden 17 171 172 27 1712 14 1722 40000
415 7 Norrwaste Sweden 17 171 172 28 1713 13 1721 40000
416 7 Norrwaste Sweden 17 171 172 28 1713 14 1722 40000
417 8 ROW Earth 18 181 182 29 1810 15 1821 40000
418 8 ROW Earth 18 181 182 29 1810 16 1822 40000
419 8 ROW Earth 18 181 182 30 1811 15 1821 40000
420 8 ROW Earth 18 181 182 30 1811 16 1822 40000
421 8 ROW Earth 18 181 182 31 1812 15 1821 40000
422 8 ROW Earth 18 181 182 31 1812 16 1822 40000
423 8 ROW Earth 18 181 182 32 1813 15 1821 40000
424 8 ROW Earth 18 181 182 32 1813 16 1822 40000
425 Show auto-inc values
426 select count(1), max(entry_key) from test.idx_tbl;
427 count(1) max(entry_key)
428 8 8
429 show create table idx_tbl;
430 Table Create Table
431 idx_tbl CREATE TABLE `idx_tbl` (
432 `entry_key` bigint unsigned NOT NULL AUTO_INCREMENT,
433 `k1` varchar(100) NOT NULL,
434 `k2` varchar(100) NOT NULL,
435 `data` int DEFAULT NULL,
436 PRIMARY KEY (`k1`,`k2`),
437 UNIQUE KEY `entry_key` (`entry_key`)
438 ) ENGINE=ndbcluster AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
439 select count(1), max(mv_idx) from test.type2_1;
440 count(1) max(mv_idx)
441 32 32
442 show create table type2_1;
443 Table Create Table
444 type2_1 CREATE TABLE `type2_1` (
445 `entry_key` bigint unsigned NOT NULL,
446 `mv_idx` bigint unsigned NOT NULL AUTO_INCREMENT,
447 `data` int DEFAULT NULL,
448 PRIMARY KEY (`entry_key`,`mv_idx`)
449 ) ENGINE=ndbcluster AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
450 select count(1), max(mv_idx) from test.type2_2;
451 count(1) max(mv_idx)
452 16 16
453 show create table type2_2;
454 Table Create Table
455 type2_2 CREATE TABLE `type2_2` (
456 `entry_key` bigint unsigned NOT NULL,
457 `mv_idx` bigint unsigned NOT NULL AUTO_INCREMENT,
458 `data` text,
459 PRIMARY KEY (`entry_key`,`mv_idx`)
460 ) ENGINE=ndbcluster AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
461 No mv_idx duplicates in type_2 tables
462 select mv_idx, count(1) as ct
463 from type2_1 group by mv_idx
464 having ct > 1;
465 mv_idx ct
466 select mv_idx, count(1) as ct
467 from type2_2 group by mv_idx
468 having ct > 1;
469 mv_idx ct
470 Run backup
471 drop table idx_tbl, type1_1, type1_2, type2_1, type2_2;
472 Run normal restore for first set of content
473 Waiting for metadata sync
474 Metadata synchronised
475 Remap args : -x test.idx_tbl.entry_key:offset:9 -x test.type1_1.entry_key:offset:9 -x test.type1_2.entry_key:offset:9 -x test.type2_1.entry_key:offset:9 -x test.type2_1.mv_idx:offset:33 -x test.type2_2.entry_key:offset:9 -x test.type2_2.mv_idx:offset:17
476 Run offset restore for second set of content
477 Combined dataset
478 -----------------
479 select idx_tbl.entry_key,
480 idx_tbl.k1,
481 idx_tbl.k2,
482 idx_tbl.data,
483 type1_1.data,
484 type1_2.data,
485 type2_1.mv_idx,
486 type2_1.data,
487 type2_2.mv_idx,
488 right(type2_2.data,4),
489 length(type2_2.data)
490 from
491 idx_tbl join type1_1 join type1_2 join type2_1 join type2_2
492 on
493 idx_tbl.entry_key = type1_1.entry_key
494 and
495 idx_tbl.entry_key = type1_2.entry_key
496 and
497 idx_tbl.entry_key = type2_1.entry_key
498 and
499 idx_tbl.entry_key = type2_2.entry_key
500 order by idx_tbl.entry_key, type2_1.data, right(type2_2.data,4);
501 entry_key k1 k2 data data data mv_idx data mv_idx right(type2_2.data,4) length(type2_2.data)
502 1 Edinburgh UK 1 11 12 1 110 1 0121 40000
503 1 Edinburgh UK 1 11 12 1 110 2 0122 40000
504 1 Edinburgh UK 1 11 12 2 111 1 0121 40000
505 1 Edinburgh UK 1 11 12 2 111 2 0122 40000
506 1 Edinburgh UK 1 11 12 3 112 1 0121 40000
507 1 Edinburgh UK 1 11 12 3 112 2 0122 40000
508 1 Edinburgh UK 1 11 12 4 113 1 0121 40000
509 1 Edinburgh UK 1 11 12 4 113 2 0122 40000
510 2 Bristol UK 2 21 22 5 210 3 0221 40000
511 2 Bristol UK 2 21 22 5 210 4 0222 40000
512 2 Bristol UK 2 21 22 6 211 3 0221 40000
513 2 Bristol UK 2 21 22 6 211 4 0222 40000
514 2 Bristol UK 2 21 22 7 212 3 0221 40000
515 2 Bristol UK 2 21 22 7 212 4 0222 40000
516 2 Bristol UK 2 21 22 8 213 3 0221 40000
517 2 Bristol UK 2 21 22 8 213 4 0222 40000
518 3 Glasgow UK 3 31 32 9 310 5 0321 40000
519 3 Glasgow UK 3 31 32 9 310 6 0322 40000
520 3 Glasgow UK 3 31 32 10 311 5 0321 40000
521 3 Glasgow UK 3 31 32 10 311 6 0322 40000
522 3 Glasgow UK 3 31 32 11 312 5 0321 40000
523 3 Glasgow UK 3 31 32 11 312 6 0322 40000
524 3 Glasgow UK 3 31 32 12 313 5 0321 40000
525 3 Glasgow UK 3 31 32 12 313 6 0322 40000
526 4 London UK 4 41 42 13 410 7 0421 40000
527 4 London UK 4 41 42 13 410 8 0422 40000
528 4 London UK 4 41 42 14 411 7 0421 40000
529 4 London UK 4 41 42 14 411 8 0422 40000
530 4 London UK 4 41 42 15 412 7 0421 40000
531 4 London UK 4 41 42 15 412 8 0422 40000
532 4 London UK 4 41 42 16 413 7 0421 40000
533 4 London UK 4 41 42 16 413 8 0422 40000
534 5 Paris France 5 51 52 17 510 9 0521 40000
535 5 Paris France 5 51 52 17 510 10 0522 40000
536 5 Paris France 5 51 52 18 511 9 0521 40000
537 5 Paris France 5 51 52 18 511 10 0522 40000
538 5 Paris France 5 51 52 19 512 9 0521 40000
539 5 Paris France 5 51 52 19 512 10 0522 40000
540 5 Paris France 5 51 52 20 513 9 0521 40000
541 5 Paris France 5 51 52 20 513 10 0522 40000
542 6 Heidelberg Germany 6 61 62 21 610 11 0621 40000
543 6 Heidelberg Germany 6 61 62 21 610 12 0622 40000
544 6 Heidelberg Germany 6 61 62 22 611 11 0621 40000
545 6 Heidelberg Germany 6 61 62 22 611 12 0622 40000
546 6 Heidelberg Germany 6 61 62 23 612 11 0621 40000
547 6 Heidelberg Germany 6 61 62 23 612 12 0622 40000
548 6 Heidelberg Germany 6 61 62 24 613 11 0621 40000
549 6 Heidelberg Germany 6 61 62 24 613 12 0622 40000
550 7 Riga Latvia 7 71 72 25 710 13 0721 40000
551 7 Riga Latvia 7 71 72 25 710 14 0722 40000
552 7 Riga Latvia 7 71 72 26 711 13 0721 40000
553 7 Riga Latvia 7 71 72 26 711 14 0722 40000
554 7 Riga Latvia 7 71 72 27 712 13 0721 40000
555 7 Riga Latvia 7 71 72 27 712 14 0722 40000
556 7 Riga Latvia 7 71 72 28 713 13 0721 40000
557 7 Riga Latvia 7 71 72 28 713 14 0722 40000
558 8 Orlando USA 8 81 82 29 810 15 0821 40000
559 8 Orlando USA 8 81 82 29 810 16 0822 40000
560 8 Orlando USA 8 81 82 30 811 15 0821 40000
561 8 Orlando USA 8 81 82 30 811 16 0822 40000
562 8 Orlando USA 8 81 82 31 812 15 0821 40000
563 8 Orlando USA 8 81 82 31 812 16 0822 40000
564 8 Orlando USA 8 81 82 32 813 15 0821 40000
565 8 Orlando USA 8 81 82 32 813 16 0822 40000
566 10 Stockholm Sweden 11 111 112 34 1110 18 1121 40000
567 10 Stockholm Sweden 11 111 112 34 1110 19 1122 40000
568 10 Stockholm Sweden 11 111 112 35 1111 18 1121 40000
569 10 Stockholm Sweden 11 111 112 35 1111 19 1122 40000
570 10 Stockholm Sweden 11 111 112 36 1112 18 1121 40000
571 10 Stockholm Sweden 11 111 112 36 1112 19 1122 40000
572 10 Stockholm Sweden 11 111 112 37 1113 18 1121 40000
573 10 Stockholm Sweden 11 111 112 37 1113 19 1122 40000
574 11 Trondheim Norway 12 121 122 38 1210 20 1221 40000
575 11 Trondheim Norway 12 121 122 38 1210 21 1222 40000
576 11 Trondheim Norway 12 121 122 39 1211 20 1221 40000
577 11 Trondheim Norway 12 121 122 39 1211 21 1222 40000
578 11 Trondheim Norway 12 121 122 40 1212 20 1221 40000
579 11 Trondheim Norway 12 121 122 40 1212 21 1222 40000
580 11 Trondheim Norway 12 121 122 41 1213 20 1221 40000
581 11 Trondheim Norway 12 121 122 41 1213 21 1222 40000
582 12 Portland USA 13 131 132 42 1310 22 1321 40000
583 12 Portland USA 13 131 132 42 1310 23 1322 40000
584 12 Portland USA 13 131 132 43 1311 22 1321 40000
585 12 Portland USA 13 131 132 43 1311 23 1322 40000
586 12 Portland USA 13 131 132 44 1312 22 1321 40000
587 12 Portland USA 13 131 132 44 1312 23 1322 40000
588 12 Portland USA 13 131 132 45 1313 22 1321 40000
589 12 Portland USA 13 131 132 45 1313 23 1322 40000
590 13 Bangalore India 14 141 142 46 1410 24 1421 40000
591 13 Bangalore India 14 141 142 46 1410 25 1422 40000
592 13 Bangalore India 14 141 142 47 1411 24 1421 40000
593 13 Bangalore India 14 141 142 47 1411 25 1422 40000
594 13 Bangalore India 14 141 142 48 1412 24 1421 40000
595 13 Bangalore India 14 141 142 48 1412 25 1422 40000
596 13 Bangalore India 14 141 142 49 1413 24 1421 40000
597 13 Bangalore India 14 141 142 49 1413 25 1422 40000
598 14 Helsinki Finland 15 151 152 50 1510 26 1521 40000
599 14 Helsinki Finland 15 151 152 50 1510 27 1522 40000
600 14 Helsinki Finland 15 151 152 51 1511 26 1521 40000
601 14 Helsinki Finland 15 151 152 51 1511 27 1522 40000
602 14 Helsinki Finland 15 151 152 52 1512 26 1521 40000
603 14 Helsinki Finland 15 151 152 52 1512 27 1522 40000
604 14 Helsinki Finland 15 151 152 53 1513 26 1521 40000
605 14 Helsinki Finland 15 151 152 53 1513 27 1522 40000
606 15 Lisbon Portugal 16 161 162 54 1610 28 1621 40000
607 15 Lisbon Portugal 16 161 162 54 1610 29 1622 40000
608 15 Lisbon Portugal 16 161 162 55 1611 28 1621 40000
609 15 Lisbon Portugal 16 161 162 55 1611 29 1622 40000
610 15 Lisbon Portugal 16 161 162 56 1612 28 1621 40000
611 15 Lisbon Portugal 16 161 162 56 1612 29 1622 40000
612 15 Lisbon Portugal 16 161 162 57 1613 28 1621 40000
613 15 Lisbon Portugal 16 161 162 57 1613 29 1622 40000
614 16 Norrwaste Sweden 17 171 172 58 1710 30 1721 40000
615 16 Norrwaste Sweden 17 171 172 58 1710 31 1722 40000
616 16 Norrwaste Sweden 17 171 172 59 1711 30 1721 40000
617 16 Norrwaste Sweden 17 171 172 59 1711 31 1722 40000
618 16 Norrwaste Sweden 17 171 172 60 1712 30 1721 40000
619 16 Norrwaste Sweden 17 171 172 60 1712 31 1722 40000
620 16 Norrwaste Sweden 17 171 172 61 1713 30 1721 40000
621 16 Norrwaste Sweden 17 171 172 61 1713 31 1722 40000
622 17 ROW Earth 18 181 182 62 1810 32 1821 40000
623 17 ROW Earth 18 181 182 62 1810 33 1822 40000
624 17 ROW Earth 18 181 182 63 1811 32 1821 40000
625 17 ROW Earth 18 181 182 63 1811 33 1822 40000
626 17 ROW Earth 18 181 182 64 1812 32 1821 40000
627 17 ROW Earth 18 181 182 64 1812 33 1822 40000
628 17 ROW Earth 18 181 182 65 1813 32 1821 40000
629 17 ROW Earth 18 181 182 65 1813 33 1822 40000
630 Show auto-inc values
631 select count(1), max(entry_key) from test.idx_tbl;
632 count(1) max(entry_key)
633 16 17
634 show create table idx_tbl;
635 Table Create Table
636 idx_tbl CREATE TABLE `idx_tbl` (
637 `entry_key` bigint unsigned NOT NULL AUTO_INCREMENT,
638 `k1` varchar(100) NOT NULL,
639 `k2` varchar(100) NOT NULL,
640 `data` int DEFAULT NULL,
641 PRIMARY KEY (`k1`,`k2`),
642 UNIQUE KEY `entry_key` (`entry_key`)
643 ) ENGINE=ndbcluster AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
644 select count(1), max(mv_idx) from test.type2_1;
645 count(1) max(mv_idx)
646 64 65
647 show create table type2_1;
648 Table Create Table
649 type2_1 CREATE TABLE `type2_1` (
650 `entry_key` bigint unsigned NOT NULL,
651 `mv_idx` bigint unsigned NOT NULL AUTO_INCREMENT,
652 `data` int DEFAULT NULL,
653 PRIMARY KEY (`entry_key`,`mv_idx`)
654 ) ENGINE=ndbcluster AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
655 select count(1), max(mv_idx) from test.type2_2;
656 count(1) max(mv_idx)
657 32 33
658 show create table type2_2;
659 Table Create Table
660 type2_2 CREATE TABLE `type2_2` (
661 `entry_key` bigint unsigned NOT NULL,
662 `mv_idx` bigint unsigned NOT NULL AUTO_INCREMENT,
663 `data` text,
664 PRIMARY KEY (`entry_key`,`mv_idx`)
665 ) ENGINE=ndbcluster AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
666 No mv_idx duplicates in type_2 tables
667 select mv_idx, count(1) as ct
668 from type2_1 group by mv_idx
669 having ct > 1;
670 mv_idx ct
671 select mv_idx, count(1) as ct
672 from type2_2 group by mv_idx
673 having ct > 1;
674 mv_idx ct
675 Verify the insert of further entity succeeds
676 insert into idx_tbl (k1,k2,data) values ('Sancerre', 'France', 9);
677 select last_insert_id() into @ek;
678 insert into type1_1 values (@ek, 91);
679 insert into type1_2 values (@ek, 92);
680 insert into type2_1 (entry_key, data) values (@ek, 910);
681 insert into type2_1 (entry_key, data) values (@ek, 911);
682 insert into type2_1 (entry_key, data) values (@ek, 912);
683 insert into type2_1 (entry_key, data) values (@ek, 913);
684 insert into type2_2 (entry_key, data) values (@ek, repeat("0921", 10000));
685 insert into type2_2 (entry_key, data) values (@ek, repeat("0922", 10000));
686 Modified content
687 ----------------
688 select idx_tbl.entry_key,
689 idx_tbl.k1,
690 idx_tbl.k2,
691 idx_tbl.data,
692 type1_1.data,
693 type1_2.data,
694 type2_1.mv_idx,
695 type2_1.data,
696 type2_2.mv_idx,
697 right(type2_2.data,4),
698 length(type2_2.data)
699 from
700 idx_tbl join type1_1 join type1_2 join type2_1 join type2_2
701 on
702 idx_tbl.entry_key = type1_1.entry_key
703 and
704 idx_tbl.entry_key = type1_2.entry_key
705 and
706 idx_tbl.entry_key = type2_1.entry_key
707 and
708 idx_tbl.entry_key = type2_2.entry_key
709 order by idx_tbl.entry_key, type2_1.data, right(type2_2.data,4);
710 entry_key k1 k2 data data data mv_idx data mv_idx right(type2_2.data,4) length(type2_2.data)
711 1 Edinburgh UK 1 11 12 1 110 1 0121 40000
712 1 Edinburgh UK 1 11 12 1 110 2 0122 40000
713 1 Edinburgh UK 1 11 12 2 111 1 0121 40000
714 1 Edinburgh UK 1 11 12 2 111 2 0122 40000
715 1 Edinburgh UK 1 11 12 3 112 1 0121 40000
716 1 Edinburgh UK 1 11 12 3 112 2 0122 40000
717 1 Edinburgh UK 1 11 12 4 113 1 0121 40000
718 1 Edinburgh UK 1 11 12 4 113 2 0122 40000
719 2 Bristol UK 2 21 22 5 210 3 0221 40000
720 2 Bristol UK 2 21 22 5 210 4 0222 40000
721 2 Bristol UK 2 21 22 6 211 3 0221 40000
722 2 Bristol UK 2 21 22 6 211 4 0222 40000
723 2 Bristol UK 2 21 22 7 212 3 0221 40000
724 2 Bristol UK 2 21 22 7 212 4 0222 40000
725 2 Bristol UK 2 21 22 8 213 3 0221 40000
726 2 Bristol UK 2 21 22 8 213 4 0222 40000
727 3 Glasgow UK 3 31 32 9 310 5 0321 40000
728 3 Glasgow UK 3 31 32 9 310 6 0322 40000
729 3 Glasgow UK 3 31 32 10 311 5 0321 40000
730 3 Glasgow UK 3 31 32 10 311 6 0322 40000
731 3 Glasgow UK 3 31 32 11 312 5 0321 40000
732 3 Glasgow UK 3 31 32 11 312 6 0322 40000
733 3 Glasgow UK 3 31 32 12 313 5 0321 40000
734 3 Glasgow UK 3 31 32 12 313 6 0322 40000
735 4 London UK 4 41 42 13 410 7 0421 40000
736 4 London UK 4 41 42 13 410 8 0422 40000
737 4 London UK 4 41 42 14 411 7 0421 40000
738 4 London UK 4 41 42 14 411 8 0422 40000
739 4 London UK 4 41 42 15 412 7 0421 40000
740 4 London UK 4 41 42 15 412 8 0422 40000
741 4 London UK 4 41 42 16 413 7 0421 40000
742 4 London UK 4 41 42 16 413 8 0422 40000
743 5 Paris France 5 51 52 17 510 9 0521 40000
744 5 Paris France 5 51 52 17 510 10 0522 40000
745 5 Paris France 5 51 52 18 511 9 0521 40000
746 5 Paris France 5 51 52 18 511 10 0522 40000
747 5 Paris France 5 51 52 19 512 9 0521 40000
748 5 Paris France 5 51 52 19 512 10 0522 40000
749 5 Paris France 5 51 52 20 513 9 0521 40000
750 5 Paris France 5 51 52 20 513 10 0522 40000
751 6 Heidelberg Germany 6 61 62 21 610 11 0621 40000
752 6 Heidelberg Germany 6 61 62 21 610 12 0622 40000
753 6 Heidelberg Germany 6 61 62 22 611 11 0621 40000
754 6 Heidelberg Germany 6 61 62 22 611 12 0622 40000
755 6 Heidelberg Germany 6 61 62 23 612 11 0621 40000
756 6 Heidelberg Germany 6 61 62 23 612 12 0622 40000
757 6 Heidelberg Germany 6 61 62 24 613 11 0621 40000
758 6 Heidelberg Germany 6 61 62 24 613 12 0622 40000
759 7 Riga Latvia 7 71 72 25 710 13 0721 40000
760 7 Riga Latvia 7 71 72 25 710 14 0722 40000
761 7 Riga Latvia 7 71 72 26 711 13 0721 40000
762 7 Riga Latvia 7 71 72 26 711 14 0722 40000
763 7 Riga Latvia 7 71 72 27 712 13 0721 40000
764 7 Riga Latvia 7 71 72 27 712 14 0722 40000
765 7 Riga Latvia 7 71 72 28 713 13 0721 40000
766 7 Riga Latvia 7 71 72 28 713 14 0722 40000
767 8 Orlando USA 8 81 82 29 810 15 0821 40000
768 8 Orlando USA 8 81 82 29 810 16 0822 40000
769 8 Orlando USA 8 81 82 30 811 15 0821 40000
770 8 Orlando USA 8 81 82 30 811 16 0822 40000
771 8 Orlando USA 8 81 82 31 812 15 0821 40000
772 8 Orlando USA 8 81 82 31 812 16 0822 40000
773 8 Orlando USA 8 81 82 32 813 15 0821 40000
774 8 Orlando USA 8 81 82 32 813 16 0822 40000
775 10 Stockholm Sweden 11 111 112 34 1110 18 1121 40000
776 10 Stockholm Sweden 11 111 112 34 1110 19 1122 40000
777 10 Stockholm Sweden 11 111 112 35 1111 18 1121 40000
778 10 Stockholm Sweden 11 111 112 35 1111 19 1122 40000
779 10 Stockholm Sweden 11 111 112 36 1112 18 1121 40000
780 10 Stockholm Sweden 11 111 112 36 1112 19 1122 40000
781 10 Stockholm Sweden 11 111 112 37 1113 18 1121 40000
782 10 Stockholm Sweden 11 111 112 37 1113 19 1122 40000
783 11 Trondheim Norway 12 121 122 38 1210 20 1221 40000
784 11 Trondheim Norway 12 121 122 38 1210 21 1222 40000
785 11 Trondheim Norway 12 121 122 39 1211 20 1221 40000
786 11 Trondheim Norway 12 121 122 39 1211 21 1222 40000
787 11 Trondheim Norway 12 121 122 40 1212 20 1221 40000
788 11 Trondheim Norway 12 121 122 40 1212 21 1222 40000
789 11 Trondheim Norway 12 121 122 41 1213 20 1221 40000
790 11 Trondheim Norway 12 121 122 41 1213 21 1222 40000
791 12 Portland USA 13 131 132 42 1310 22 1321 40000
792 12 Portland USA 13 131 132 42 1310 23 1322 40000
793 12 Portland USA 13 131 132 43 1311 22 1321 40000
794 12 Portland USA 13 131 132 43 1311 23 1322 40000
795 12 Portland USA 13 131 132 44 1312 22 1321 40000
796 12 Portland USA 13 131 132 44 1312 23 1322 40000
797 12 Portland USA 13 131 132 45 1313 22 1321 40000
798 12 Portland USA 13 131 132 45 1313 23 1322 40000
799 13 Bangalore India 14 141 142 46 1410 24 1421 40000
800 13 Bangalore India 14 141 142 46 1410 25 1422 40000
801 13 Bangalore India 14 141 142 47 1411 24 1421 40000
802 13 Bangalore India 14 141 142 47 1411 25 1422 40000
803 13 Bangalore India 14 141 142 48 1412 24 1421 40000
804 13 Bangalore India 14 141 142 48 1412 25 1422 40000
805 13 Bangalore India 14 141 142 49 1413 24 1421 40000
806 13 Bangalore India 14 141 142 49 1413 25 1422 40000
807 14 Helsinki Finland 15 151 152 50 1510 26 1521 40000
808 14 Helsinki Finland 15 151 152 50 1510 27 1522 40000
809 14 Helsinki Finland 15 151 152 51 1511 26 1521 40000
810 14 Helsinki Finland 15 151 152 51 1511 27 1522 40000
811 14 Helsinki Finland 15 151 152 52 1512 26 1521 40000
812 14 Helsinki Finland 15 151 152 52 1512 27 1522 40000
813 14 Helsinki Finland 15 151 152 53 1513 26 1521 40000
814 14 Helsinki Finland 15 151 152 53 1513 27 1522 40000
815 15 Lisbon Portugal 16 161 162 54 1610 28 1621 40000
816 15 Lisbon Portugal 16 161 162 54 1610 29 1622 40000
817 15 Lisbon Portugal 16 161 162 55 1611 28 1621 40000
818 15 Lisbon Portugal 16 161 162 55 1611 29 1622 40000
819 15 Lisbon Portugal 16 161 162 56 1612 28 1621 40000
820 15 Lisbon Portugal 16 161 162 56 1612 29 1622 40000
821 15 Lisbon Portugal 16 161 162 57 1613 28 1621 40000
822 15 Lisbon Portugal 16 161 162 57 1613 29 1622 40000
823 16 Norrwaste Sweden 17 171 172 58 1710 30 1721 40000
824 16 Norrwaste Sweden 17 171 172 58 1710 31 1722 40000
825 16 Norrwaste Sweden 17 171 172 59 1711 30 1721 40000
826 16 Norrwaste Sweden 17 171 172 59 1711 31 1722 40000
827 16 Norrwaste Sweden 17 171 172 60 1712 30 1721 40000
828 16 Norrwaste Sweden 17 171 172 60 1712 31 1722 40000
829 16 Norrwaste Sweden 17 171 172 61 1713 30 1721 40000
830 16 Norrwaste Sweden 17 171 172 61 1713 31 1722 40000
831 17 ROW Earth 18 181 182 62 1810 32 1821 40000
832 17 ROW Earth 18 181 182 62 1810 33 1822 40000
833 17 ROW Earth 18 181 182 63 1811 32 1821 40000
834 17 ROW Earth 18 181 182 63 1811 33 1822 40000
835 17 ROW Earth 18 181 182 64 1812 32 1821 40000
836 17 ROW Earth 18 181 182 64 1812 33 1822 40000
837 17 ROW Earth 18 181 182 65 1813 32 1821 40000
838 17 ROW Earth 18 181 182 65 1813 33 1822 40000
839 18 Sancerre France 9 91 92 66 910 34 0921 40000
840 18 Sancerre France 9 91 92 66 910 35 0922 40000
841 18 Sancerre France 9 91 92 67 911 34 0921 40000
842 18 Sancerre France 9 91 92 67 911 35 0922 40000
843 18 Sancerre France 9 91 92 68 912 34 0921 40000
844 18 Sancerre France 9 91 92 68 912 35 0922 40000
845 18 Sancerre France 9 91 92 69 913 34 0921 40000
846 18 Sancerre France 9 91 92 69 913 35 0922 40000
847 Show auto-inc values
848 select count(1), max(entry_key) from test.idx_tbl;
849 count(1) max(entry_key)
850 17 18
851 show create table idx_tbl;
852 Table Create Table
853 idx_tbl CREATE TABLE `idx_tbl` (
854 `entry_key` bigint unsigned NOT NULL AUTO_INCREMENT,
855 `k1` varchar(100) NOT NULL,
856 `k2` varchar(100) NOT NULL,
857 `data` int DEFAULT NULL,
858 PRIMARY KEY (`k1`,`k2`),
859 UNIQUE KEY `entry_key` (`entry_key`)
860 ) ENGINE=ndbcluster AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
861 select count(1), max(mv_idx) from test.type2_1;
862 count(1) max(mv_idx)
863 68 69
864 show create table type2_1;
865 Table Create Table
866 type2_1 CREATE TABLE `type2_1` (
867 `entry_key` bigint unsigned NOT NULL,
868 `mv_idx` bigint unsigned NOT NULL AUTO_INCREMENT,
869 `data` int DEFAULT NULL,
870 PRIMARY KEY (`entry_key`,`mv_idx`)
871 ) ENGINE=ndbcluster AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
872 select count(1), max(mv_idx) from test.type2_2;
873 count(1) max(mv_idx)
874 34 35
875 show create table type2_2;
876 Table Create Table
877 type2_2 CREATE TABLE `type2_2` (
878 `entry_key` bigint unsigned NOT NULL,
879 `mv_idx` bigint unsigned NOT NULL AUTO_INCREMENT,
880 `data` text,
881 PRIMARY KEY (`entry_key`,`mv_idx`)
882 ) ENGINE=ndbcluster AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
883 No mv_idx duplicates in type_2 tables
884 select mv_idx, count(1) as ct
885 from type2_1 group by mv_idx
886 having ct > 1;
887 mv_idx ct
888 select mv_idx, count(1) as ct
889 from type2_2 group by mv_idx
890 having ct > 1;
891 mv_idx ct
892 drop table idx_tbl, type1_1, type1_2, type2_1, type2_2;