Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2 -- source suite/ndb/include/backup_restore_setup.inc
3
4 --echo Show preservation of autoincrement values via ndb_restore
5 --echo Cover some variants including :
6 --echo - Basic restore
7 --echo - Keyless table use case
8 --echo - Restore via a staging table
9 --echo - Restore with no data
10 --echo ----------------------------------------
11 --echo 1. Create tables and add some data
12 --echo ----------------------------------------
13 use test;
14
15 --disable_query_log
16 set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz;
17 set ndb_autoincrement_prefetch_sz = 1;
18 --enable_query_log
19
20 create table boring (
21 a tinyint auto_increment primary key,
22 b int
23 ) engine=ndb;
24
25 --echo Expect next == 7
26 insert into boring (b) values (1), (2), (3), (4), (5), (6);
27
28 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="boring";
29
30 create table boring_keyless (
31 a int
32 ) engine=ndb;
33
34 --echo Expect next == 5
35 insert into boring_keyless values (1), (2), (3), (4);
36
37
38 create table empty_default (
39 a mediumint primary key auto_increment,
40 b int
41 ) engine=ndb;
42
43 --echo Expect next == 1
44 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_default";
45
46 create table empty_specific (
47 a mediumint primary key auto_increment,
48 b int
49 ) engine=ndb auto_increment=73;
50
51 --echo Expect next == 73
52 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_specific";
53
54 create table big_varchars (
55 a bigint auto_increment primary key,
56 b varchar(2000)
57 ) engine=ndb;
58
59 insert into big_varchars (b) values (repeat('ILBJC', 400));
60 insert into big_varchars (b) values (repeat('ILBJC', 400));
61 insert into big_varchars (b) values (repeat('ILBJC', 400));
62 insert into big_varchars (b) values (repeat('ILBJC', 400));
63 insert into big_varchars (b) values (repeat('ILBJC', 400));
64
65 --echo Expect next == 6
66 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="big_varchars";
67
68 create table tinysignedauto (
69 a tinyint auto_increment primary key,
70 b varchar(2000)
71 ) engine=ndb;
72
73 create table smallsignedauto (
74 a smallint auto_increment primary key,
75 b varchar(2000)
76 ) engine=ndb;
77
78 create table medsignedauto (
79 a mediumint auto_increment primary key,
80 b varchar(2000)
81 ) engine=ndb;
82
83 create table intsignedauto (
84 a int auto_increment primary key,
85 b varchar(2000)
86 ) engine=ndb;
87
88 create table bigsignedauto (
89 a bigint auto_increment primary key,
90 b varchar(2000)
91 ) engine=ndb;
92
93 insert into tinysignedauto(b) values (repeat('ILBJC', 40));
94 insert into tinysignedauto(b) values (repeat('ILBJC', 40));
95 insert into tinysignedauto(b) values (repeat('ILBJC', 40));
96
97 insert into smallsignedauto(b) values (repeat('ILBJC', 40));
98 insert into smallsignedauto(b) values (repeat('ILBJC', 40));
99 insert into smallsignedauto(b) values (repeat('ILBJC', 40));
100
101 insert into medsignedauto(b) values (repeat('ILBJC', 40));
102 insert into medsignedauto(b) values (repeat('ILBJC', 40));
103 insert into medsignedauto(b) values (repeat('ILBJC', 40));
104
105 insert into intsignedauto(b) values (repeat('ILBJC', 40));
106 insert into intsignedauto(b) values (repeat('ILBJC', 40));
107 insert into intsignedauto(b) values (repeat('ILBJC', 40));
108
109 insert into bigsignedauto(b) values (repeat('ILBJC', 40));
110 insert into bigsignedauto(b) values (repeat('ILBJC', 40));
111 insert into bigsignedauto(b) values (repeat('ILBJC', 40));
112
113 --echo Expect next == 4
114 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name like "%signedauto" order by table_name;
115
116 insert into tinysignedauto values (-100, repeat('IWBJC', 40));
117 insert into smallsignedauto values (-100, repeat('IWBJC', 40));
118 insert into medsignedauto values (-100, repeat('IWBJC', 40));
119 insert into intsignedauto values (-100, repeat('IWBJC', 40));
120 insert into bigsignedauto values (-100, repeat('IWBJC', 40));
121
122
123 --echo Expect next == 4
124 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name like "%signedauto" order by table_name;
125
126
127 # TODO
128 # Show widening / narrowing of auto-increment column
129 # Auto-inc as part of secondary key
130
131
132 --echo ----------------------------------------
133 --echo 2. Take a backup
134 --echo ----------------------------------------
135
136 --source include/ndb_backup.inc
137
138 drop table bigsignedauto;
139 drop table intsignedauto;
140 drop table medsignedauto;
141 drop table smallsignedauto;
142 drop table tinysignedauto;
143 drop table big_varchars;
144 drop table empty_specific;
145 drop table empty_default;
146 drop table boring_keyless;
147 drop table boring;
148
149 --echo ----------------------------------------
150 --echo 3. Manually re-create schema via MySQLD with some changes
151 --echo ----------------------------------------
152
153 --echo Avoid any accidental tableid line-up
154 create table filler1(a int primary key) engine=ndb;
155 create table filler2(a int primary key) engine=ndb;
156 create table filler3(a int primary key) engine=ndb;
157 create table filler4(a int primary key) engine=ndb;
158 create table filler5(a int primary key) engine=ndb;
159 create table filler6(a int primary key) engine=ndb;
160 create table filler7(a int primary key) engine=ndb;
161 create table filler8(a int primary key) engine=ndb;
162
163 --echo big_varchars table now has a text column
164 --echo testing staging area transform
165 create table big_varchars (
166 a bigint auto_increment primary key,
167 b text
168 ) engine=ndb;
169
170 create table boring (
171 a tinyint auto_increment primary key,
172 b int
173 ) engine=ndb;
174
175 create table boring_keyless (
176 a int
177 ) engine=ndb;
178
179 create table empty_default (
180 a mediumint primary key auto_increment,
181 b int
182 ) engine=ndb;
183
184 --echo Expect next == 1
185 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_default";
186
187 create table empty_specific (
188 a mediumint primary key auto_increment,
189 b int
190 ) engine=ndb auto_increment=73;
191
192 --echo Expect next == 73
193 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_specific";
194
195 --echo Following unchanged
196 create table tinysignedauto (
197 a tinyint auto_increment primary key,
198 b varchar(2000)
199 ) engine=ndb;
200
201 create table smallsignedauto (
202 a smallint auto_increment primary key,
203 b varchar(2000)
204 ) engine=ndb;
205
206 create table medsignedauto (
207 a mediumint auto_increment primary key,
208 b varchar(2000)
209 ) engine=ndb;
210
211 create table intsignedauto (
212 a int auto_increment primary key,
213 b varchar(2000)
214 ) engine=ndb;
215
216 create table bigsignedauto (
217 a bigint auto_increment primary key,
218 b varchar(2000)
219 ) engine=ndb;
220
221 --echo ----------------------------------------
222 --echo 4. Restore data
223 --echo ----------------------------------------
224
225 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
226 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
227
228 --echo ----------------------------------------
229 --echo 5. Check autoincrement info
230 --echo ----------------------------------------
231
232 --echo Expect 7
233 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="boring";
234
235 --echo Expect 1
236 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_default";
237
238 --echo Expect 73
239 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="empty_specific";
240
241 --echo Expect 6
242 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name="big_varchars";
243
244 --echo Expect next == 4
245 select table_schema, table_name, auto_increment from information_schema.tables where table_schema="test" and table_name like "%signedauto" order by table_name;
246
247
248 --echo ----------------------------------------
249 --echo 6. Test via inserts
250 --echo ----------------------------------------
251
252 insert into boring (b) values (99);
253 --echo Expect 7
254 select max(a) from boring;
255
256 insert into boring_keyless values (99);
257 select * from boring_keyless order by a;
258
259 insert into empty_default (b) values (99);
260 --echo Expect 1,99
261 select * from empty_default;
262
263 insert into empty_specific (b) values (99);
264 --echo Expect 73,99
265 select * from empty_specific;
266
267 insert into big_varchars(b) values (repeat('IMBJC', 400));
268 --echo Expect 6
269 select max(a) from big_varchars;
270
271 insert into tinysignedauto (b) values (repeat('IMBJC', 40));
272 insert into smallsignedauto (b) values (repeat('IMBJC', 40));
273 insert into medsignedauto (b) values (repeat('IMBJC', 40));
274 insert into intsignedauto (b) values (repeat('IMBJC', 40));
275 insert into bigsignedauto (b) values (repeat('IMBJC', 40));
276
277 --echo Expect 4
278 select max(a) from tinysignedauto;
279 select max(a) from smallsignedauto;
280 select max(a) from medsignedauto;
281 select max(a) from intsignedauto;
282 select max(a) from bigsignedauto;
283
284
285 --echo ----------------------------------------
286 --echo 7. Cleanup
287 --echo ----------------------------------------
288
289 drop table boring;
290 drop table boring_keyless;
291 drop table empty_default;
292 drop table empty_specific;
293 drop table big_varchars;
294 drop table bigsignedauto;
295 drop table intsignedauto;
296 drop table medsignedauto;
297 drop table smallsignedauto;
298 drop table tinysignedauto;
299 drop table filler1, filler2, filler3, filler4;
300 drop table filler5, filler6, filler7, filler8;
301
302 --echo ----------------------------------------
303 --echo 8. Use ndb_restore metadata restore
304 --echo ----------------------------------------
305
306
307 --exec $NDB_RESTORE -b $the_backup_id -n 2 -m --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
308
309 --source suite/ndb/include/ndb_sync_metadata.inc
310
311
312 --echo ----------------------------------------
313 --echo 9. Check autoincrement info
314 --echo ----------------------------------------
315
316 --echo With just NdbApi metadata restored, auto_increment values are set to default values (1)
317
318 # We use show create table rather than INFORMATION_SCHEMA here to workaround
319 # the INFORMATION_SCHEMA returning cached state from the MySQLD data dictionary
320
321 # Expect 1 (so nothing shown by SHOW CREATE TABLE)
322 show create table boring;
323 show create table empty_default;
324 show create table empty_specific;
325 show create table big_varchars;
326 show create table bigsignedauto;
327 show create table intsignedauto;
328 show create table medsignedauto;
329 show create table smallsignedauto;
330 show create table tinysignedauto;
331
332 --echo ----------------------------------------
333 --echo 10. Restore data
334 --echo ----------------------------------------
335
336 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
337 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
338
339 #--source suite/ndb/include/ndb_sync_metadata.inc
340
341
342 --echo ----------------------------------------
343 --echo 11. Check autoincrement info
344 --echo ----------------------------------------
345
346 --echo Expect 7
347 show create table boring;
348
349 --echo Expect 1 (So nothing shown)
350 show create table empty_default;
351
352 --echo Expect 73
353 show create table empty_specific;
354
355 --echo Expect 6
356 show create table big_varchars;
357
358 --echo Expect 4
359 show create table bigsignedauto;
360 show create table intsignedauto;
361 show create table medsignedauto;
362 show create table smallsignedauto;
363 show create table tinysignedauto;
364
365 --echo ----------------------------------------
366 --echo 12. Test via inserts
367 --echo ----------------------------------------
368
369 insert into boring (b) values (99);
370 --echo Expect 7
371 select max(a) from boring;
372
373 insert into boring_keyless values (99);
374 select * from boring_keyless order by a;
375
376 insert into empty_default (b) values (99);
377 --echo Expect 1,99
378 select * from empty_default;
379
380 insert into empty_specific (b) values (99);
381 --echo Expect 73,99
382 select * from empty_specific;
383
384 insert into big_varchars(b) values (repeat('IMBJC', 400));
385 --echo Expect 6
386 select max(a) from big_varchars;
387
388 insert into tinysignedauto (b) values (repeat('IMBJC', 40));
389 insert into smallsignedauto (b) values (repeat('IMBJC', 40));
390 insert into medsignedauto (b) values (repeat('IMBJC', 40));
391 insert into intsignedauto (b) values (repeat('IMBJC', 40));
392 insert into bigsignedauto (b) values (repeat('IMBJC', 40));
393
394 --echo Expect 4
395 select max(a) from tinysignedauto;
396 select max(a) from smallsignedauto;
397 select max(a) from medsignedauto;
398 select max(a) from intsignedauto;
399 select max(a) from bigsignedauto;
400
401 --echo ----------------------------------------
402 --echo 13. Cleanup
403 --echo ----------------------------------------
404
405 --disable_query_log
406 set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz;
407 --enable_query_log
408
409 drop table boring;
410 drop table boring_keyless;
411 drop table empty_default;
412 drop table empty_specific;
413 drop table big_varchars;
414 drop table bigsignedauto;
415 drop table intsignedauto;
416 drop table medsignedauto;
417 drop table smallsignedauto;
418 drop table tinysignedauto;
419
420 --source suite/ndb/include/backup_restore_cleanup.inc
421 --remove_file $NDB_TOOLS_OUTPUT