Debian Codegraph @

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

raw
1 Show preservation of autoincrement values via ndb_restore
2 Cover some variants including :
3 - Basic restore
4 - Keyless table use case
5 - Restore via a staging table
6 - Restore with no data
7 ----------------------------------------
8 1. Create tables and add some data
9 ----------------------------------------
10 use test;
11 create table boring (
12 a tinyint auto_increment primary key,
13 b int
14 ) engine=ndb;
15 Expect next == 7
16 insert into boring (b) values (1), (2), (3), (4), (5), (6);
17 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="boring";
18 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
19 test boring 7
20 create table boring_keyless (
21 a int
22 ) engine=ndb;
23 Expect next == 5
24 insert into boring_keyless values (1), (2), (3), (4);
25 create table empty_default (
26 a mediumint primary key auto_increment,
27 b int
28 ) engine=ndb;
29 Expect next == 1
30 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_default";
31 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
32 test empty_default 1
33 create table empty_specific (
34 a mediumint primary key auto_increment,
35 b int
36 ) engine=ndb auto_increment=73;
37 Expect next == 73
38 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_specific";
39 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
40 test empty_specific 73
41 create table big_varchars (
42 a bigint auto_increment primary key,
43 b varchar(2000)
44 ) engine=ndb;
45 insert into big_varchars (b) values (repeat('ILBJC', 400));
46 insert into big_varchars (b) values (repeat('ILBJC', 400));
47 insert into big_varchars (b) values (repeat('ILBJC', 400));
48 insert into big_varchars (b) values (repeat('ILBJC', 400));
49 insert into big_varchars (b) values (repeat('ILBJC', 400));
50 Expect next == 6
51 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="big_varchars";
52 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
53 test big_varchars 6
54 create table tinysignedauto (
55 a tinyint auto_increment primary key,
56 b varchar(2000)
57 ) engine=ndb;
58 create table smallsignedauto (
59 a smallint auto_increment primary key,
60 b varchar(2000)
61 ) engine=ndb;
62 create table medsignedauto (
63 a mediumint auto_increment primary key,
64 b varchar(2000)
65 ) engine=ndb;
66 create table intsignedauto (
67 a int auto_increment primary key,
68 b varchar(2000)
69 ) engine=ndb;
70 create table bigsignedauto (
71 a bigint auto_increment primary key,
72 b varchar(2000)
73 ) engine=ndb;
74 insert into tinysignedauto(b) values (repeat('ILBJC', 40));
75 insert into tinysignedauto(b) values (repeat('ILBJC', 40));
76 insert into tinysignedauto(b) values (repeat('ILBJC', 40));
77 insert into smallsignedauto(b) values (repeat('ILBJC', 40));
78 insert into smallsignedauto(b) values (repeat('ILBJC', 40));
79 insert into smallsignedauto(b) values (repeat('ILBJC', 40));
80 insert into medsignedauto(b) values (repeat('ILBJC', 40));
81 insert into medsignedauto(b) values (repeat('ILBJC', 40));
82 insert into medsignedauto(b) values (repeat('ILBJC', 40));
83 insert into intsignedauto(b) values (repeat('ILBJC', 40));
84 insert into intsignedauto(b) values (repeat('ILBJC', 40));
85 insert into intsignedauto(b) values (repeat('ILBJC', 40));
86 insert into bigsignedauto(b) values (repeat('ILBJC', 40));
87 insert into bigsignedauto(b) values (repeat('ILBJC', 40));
88 insert into bigsignedauto(b) values (repeat('ILBJC', 40));
89 Expect next == 4
90 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name like "%signedauto" order by table_name;
91 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
92 test bigsignedauto 4
93 test intsignedauto 4
94 test medsignedauto 4
95 test smallsignedauto 4
96 test tinysignedauto 4
97 insert into tinysignedauto values (-100, repeat('IWBJC', 40));
98 insert into smallsignedauto values (-100, repeat('IWBJC', 40));
99 insert into medsignedauto values (-100, repeat('IWBJC', 40));
100 insert into intsignedauto values (-100, repeat('IWBJC', 40));
101 insert into bigsignedauto values (-100, repeat('IWBJC', 40));
102 Expect next == 4
103 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name like "%signedauto" order by table_name;
104 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
105 test bigsignedauto 4
106 test intsignedauto 4
107 test medsignedauto 4
108 test smallsignedauto 4
109 test tinysignedauto 4
110 ----------------------------------------
111 2. Take a backup
112 ----------------------------------------
113 drop table bigsignedauto;
114 drop table intsignedauto;
115 drop table medsignedauto;
116 drop table smallsignedauto;
117 drop table tinysignedauto;
118 drop table big_varchars;
119 drop table empty_specific;
120 drop table empty_default;
121 drop table boring_keyless;
122 drop table boring;
123 ----------------------------------------
124 3. Manually re-create schema via MySQLD with some changes
125 ----------------------------------------
126 Avoid any accidental tableid line-up
127 create table filler1(a int primary key) engine=ndb;
128 create table filler2(a int primary key) engine=ndb;
129 create table filler3(a int primary key) engine=ndb;
130 create table filler4(a int primary key) engine=ndb;
131 create table filler5(a int primary key) engine=ndb;
132 create table filler6(a int primary key) engine=ndb;
133 create table filler7(a int primary key) engine=ndb;
134 create table filler8(a int primary key) engine=ndb;
135 big_varchars table now has a text column
136 testing staging area transform
137 create table big_varchars (
138 a bigint auto_increment primary key,
139 b text
140 ) engine=ndb;
141 create table boring (
142 a tinyint auto_increment primary key,
143 b int
144 ) engine=ndb;
145 create table boring_keyless (
146 a int
147 ) engine=ndb;
148 create table empty_default (
149 a mediumint primary key auto_increment,
150 b int
151 ) engine=ndb;
152 Expect next == 1
153 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_default";
154 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
155 test empty_default 1
156 create table empty_specific (
157 a mediumint primary key auto_increment,
158 b int
159 ) engine=ndb auto_increment=73;
160 Expect next == 73
161 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_specific";
162 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
163 test empty_specific 73
164 Following unchanged
165 create table tinysignedauto (
166 a tinyint auto_increment primary key,
167 b varchar(2000)
168 ) engine=ndb;
169 create table smallsignedauto (
170 a smallint auto_increment primary key,
171 b varchar(2000)
172 ) engine=ndb;
173 create table medsignedauto (
174 a mediumint auto_increment primary key,
175 b varchar(2000)
176 ) engine=ndb;
177 create table intsignedauto (
178 a int auto_increment primary key,
179 b varchar(2000)
180 ) engine=ndb;
181 create table bigsignedauto (
182 a bigint auto_increment primary key,
183 b varchar(2000)
184 ) engine=ndb;
185 ----------------------------------------
186 4. Restore data
187 ----------------------------------------
188 ----------------------------------------
189 5. Check autoincrement info
190 ----------------------------------------
191 Expect 7
192 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="boring";
193 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
194 test boring 7
195 Expect 1
196 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_default";
197 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
198 test empty_default 1
199 Expect 73
200 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_specific";
201 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
202 test empty_specific 73
203 Expect 6
204 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="big_varchars";
205 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
206 test big_varchars 6
207 Expect next == 4
208 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name like "%signedauto" order by table_name;
209 TABLE_SCHEMA TABLE_NAME AUTO_INCREMENT
210 test bigsignedauto 4
211 test intsignedauto 4
212 test medsignedauto 4
213 test smallsignedauto 4
214 test tinysignedauto 4
215 ----------------------------------------
216 6. Test via inserts
217 ----------------------------------------
218 insert into boring (b) values (99);
219 Expect 7
220 select max(a) from boring;
221 max(a)
222 7
223 insert into boring_keyless values (99);
224 select * from boring_keyless order by a;
225 a
226 1
227 2
228 3
229 4
230 99
231 insert into empty_default (b) values (99);
232 Expect 1,99
233 select * from empty_default;
234 a b
235 1 99
236 insert into empty_specific (b) values (99);
237 Expect 73,99
238 select * from empty_specific;
239 a b
240 73 99
241 insert into big_varchars(b) values (repeat('IMBJC', 400));
242 Expect 6
243 select max(a) from big_varchars;
244 max(a)
245 6
246 insert into tinysignedauto (b) values (repeat('IMBJC', 40));
247 insert into smallsignedauto (b) values (repeat('IMBJC', 40));
248 insert into medsignedauto (b) values (repeat('IMBJC', 40));
249 insert into intsignedauto (b) values (repeat('IMBJC', 40));
250 insert into bigsignedauto (b) values (repeat('IMBJC', 40));
251 Expect 4
252 select max(a) from tinysignedauto;
253 max(a)
254 4
255 select max(a) from smallsignedauto;
256 max(a)
257 4
258 select max(a) from medsignedauto;
259 max(a)
260 4
261 select max(a) from intsignedauto;
262 max(a)
263 4
264 select max(a) from bigsignedauto;
265 max(a)
266 4
267 ----------------------------------------
268 7. Cleanup
269 ----------------------------------------
270 drop table boring;
271 drop table boring_keyless;
272 drop table empty_default;
273 drop table empty_specific;
274 drop table big_varchars;
275 drop table bigsignedauto;
276 drop table intsignedauto;
277 drop table medsignedauto;
278 drop table smallsignedauto;
279 drop table tinysignedauto;
280 drop table filler1, filler2, filler3, filler4;
281 drop table filler5, filler6, filler7, filler8;
282 ----------------------------------------
283 8. Use ndb_restore metadata restore
284 ----------------------------------------
285 Waiting for metadata sync
286 Metadata synchronised
287 ----------------------------------------
288 9. Check autoincrement info
289 ----------------------------------------
290 With just NdbApi metadata restored, auto_increment values are set to default values (1)
291 show create table boring;
292 Table Create Table
293 boring CREATE TABLE `boring` (
294 `a` tinyint NOT NULL AUTO_INCREMENT,
295 `b` int DEFAULT NULL,
296 PRIMARY KEY (`a`)
297 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
298 show create table empty_default;
299 Table Create Table
300 empty_default CREATE TABLE `empty_default` (
301 `a` mediumint NOT NULL AUTO_INCREMENT,
302 `b` int DEFAULT NULL,
303 PRIMARY KEY (`a`)
304 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
305 show create table empty_specific;
306 Table Create Table
307 empty_specific CREATE TABLE `empty_specific` (
308 `a` mediumint NOT NULL AUTO_INCREMENT,
309 `b` int DEFAULT NULL,
310 PRIMARY KEY (`a`)
311 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
312 show create table big_varchars;
313 Table Create Table
314 big_varchars CREATE TABLE `big_varchars` (
315 `a` bigint NOT NULL AUTO_INCREMENT,
316 `b` varchar(2000) DEFAULT NULL,
317 PRIMARY KEY (`a`)
318 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
319 show create table bigsignedauto;
320 Table Create Table
321 bigsignedauto CREATE TABLE `bigsignedauto` (
322 `a` bigint NOT NULL AUTO_INCREMENT,
323 `b` varchar(2000) DEFAULT NULL,
324 PRIMARY KEY (`a`)
325 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
326 show create table intsignedauto;
327 Table Create Table
328 intsignedauto CREATE TABLE `intsignedauto` (
329 `a` int NOT NULL AUTO_INCREMENT,
330 `b` varchar(2000) DEFAULT NULL,
331 PRIMARY KEY (`a`)
332 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
333 show create table medsignedauto;
334 Table Create Table
335 medsignedauto CREATE TABLE `medsignedauto` (
336 `a` mediumint NOT NULL AUTO_INCREMENT,
337 `b` varchar(2000) DEFAULT NULL,
338 PRIMARY KEY (`a`)
339 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
340 show create table smallsignedauto;
341 Table Create Table
342 smallsignedauto CREATE TABLE `smallsignedauto` (
343 `a` smallint NOT NULL AUTO_INCREMENT,
344 `b` varchar(2000) DEFAULT NULL,
345 PRIMARY KEY (`a`)
346 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
347 show create table tinysignedauto;
348 Table Create Table
349 tinysignedauto CREATE TABLE `tinysignedauto` (
350 `a` tinyint NOT NULL AUTO_INCREMENT,
351 `b` varchar(2000) DEFAULT NULL,
352 PRIMARY KEY (`a`)
353 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
354 ----------------------------------------
355 10. Restore data
356 ----------------------------------------
357 ----------------------------------------
358 11. Check autoincrement info
359 ----------------------------------------
360 Expect 7
361 show create table boring;
362 Table Create Table
363 boring CREATE TABLE `boring` (
364 `a` tinyint NOT NULL AUTO_INCREMENT,
365 `b` int DEFAULT NULL,
366 PRIMARY KEY (`a`)
367 ) ENGINE=ndbcluster AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
368 Expect 1 (So nothing shown)
369 show create table empty_default;
370 Table Create Table
371 empty_default CREATE TABLE `empty_default` (
372 `a` mediumint NOT NULL AUTO_INCREMENT,
373 `b` int DEFAULT NULL,
374 PRIMARY KEY (`a`)
375 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
376 Expect 73
377 show create table empty_specific;
378 Table Create Table
379 empty_specific CREATE TABLE `empty_specific` (
380 `a` mediumint NOT NULL AUTO_INCREMENT,
381 `b` int DEFAULT NULL,
382 PRIMARY KEY (`a`)
383 ) ENGINE=ndbcluster AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
384 Expect 6
385 show create table big_varchars;
386 Table Create Table
387 big_varchars CREATE TABLE `big_varchars` (
388 `a` bigint NOT NULL AUTO_INCREMENT,
389 `b` varchar(2000) DEFAULT NULL,
390 PRIMARY KEY (`a`)
391 ) ENGINE=ndbcluster AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
392 Expect 4
393 show create table bigsignedauto;
394 Table Create Table
395 bigsignedauto CREATE TABLE `bigsignedauto` (
396 `a` bigint NOT NULL AUTO_INCREMENT,
397 `b` varchar(2000) DEFAULT NULL,
398 PRIMARY KEY (`a`)
399 ) ENGINE=ndbcluster AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
400 show create table intsignedauto;
401 Table Create Table
402 intsignedauto CREATE TABLE `intsignedauto` (
403 `a` int NOT NULL AUTO_INCREMENT,
404 `b` varchar(2000) DEFAULT NULL,
405 PRIMARY KEY (`a`)
406 ) ENGINE=ndbcluster AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
407 show create table medsignedauto;
408 Table Create Table
409 medsignedauto CREATE TABLE `medsignedauto` (
410 `a` mediumint NOT NULL AUTO_INCREMENT,
411 `b` varchar(2000) DEFAULT NULL,
412 PRIMARY KEY (`a`)
413 ) ENGINE=ndbcluster AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
414 show create table smallsignedauto;
415 Table Create Table
416 smallsignedauto CREATE TABLE `smallsignedauto` (
417 `a` smallint NOT NULL AUTO_INCREMENT,
418 `b` varchar(2000) DEFAULT NULL,
419 PRIMARY KEY (`a`)
420 ) ENGINE=ndbcluster AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
421 show create table tinysignedauto;
422 Table Create Table
423 tinysignedauto CREATE TABLE `tinysignedauto` (
424 `a` tinyint NOT NULL AUTO_INCREMENT,
425 `b` varchar(2000) DEFAULT NULL,
426 PRIMARY KEY (`a`)
427 ) ENGINE=ndbcluster AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
428 ----------------------------------------
429 12. Test via inserts
430 ----------------------------------------
431 insert into boring (b) values (99);
432 Expect 7
433 select max(a) from boring;
434 max(a)
435 7
436 insert into boring_keyless values (99);
437 select * from boring_keyless order by a;
438 a
439 1
440 2
441 3
442 4
443 99
444 insert into empty_default (b) values (99);
445 Expect 1,99
446 select * from empty_default;
447 a b
448 1 99
449 insert into empty_specific (b) values (99);
450 Expect 73,99
451 select * from empty_specific;
452 a b
453 73 99
454 insert into big_varchars(b) values (repeat('IMBJC', 400));
455 Expect 6
456 select max(a) from big_varchars;
457 max(a)
458 6
459 insert into tinysignedauto (b) values (repeat('IMBJC', 40));
460 insert into smallsignedauto (b) values (repeat('IMBJC', 40));
461 insert into medsignedauto (b) values (repeat('IMBJC', 40));
462 insert into intsignedauto (b) values (repeat('IMBJC', 40));
463 insert into bigsignedauto (b) values (repeat('IMBJC', 40));
464 Expect 4
465 select max(a) from tinysignedauto;
466 max(a)
467 4
468 select max(a) from smallsignedauto;
469 max(a)
470 4
471 select max(a) from medsignedauto;
472 max(a)
473 4
474 select max(a) from intsignedauto;
475 max(a)
476 4
477 select max(a) from bigsignedauto;
478 max(a)
479 4
480 ----------------------------------------
481 13. Cleanup
482 ----------------------------------------
483 drop table boring;
484 drop table boring_keyless;
485 drop table empty_default;
486 drop table empty_specific;
487 drop table big_varchars;
488 drop table bigsignedauto;
489 drop table intsignedauto;
490 drop table medsignedauto;
491 drop table smallsignedauto;
492 drop table tinysignedauto;