Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2 -- source include/have_ndb_debug.inc
3 -- source suite/ndb/include/backup_restore_setup.inc
4
5 use test;
6
7 create table test.t1 (a varchar(20) not null,
8 b varchar(20) not null,
9 c varchar(20) not null,
10 d varchar(20),
11 primary key(a,b)) engine=ndb;
12
13 insert into test.t1 values
14 ("1a","1b","1c","1d"),
15 ("2a","2b","2c","2d"),
16 ("3a","3b","3c","3d"),
17 ("4a","4b","4c","4d"),
18 ("5a","5b","5c","5d"),
19 ("6a","6b","6c","6d");
20
21 --echo Stall backup completion
22 --exec $NDB_MGM -e "all error 10039" >> $NDB_TOOLS_OUTPUT 2>&1
23 --echo Run backup
24 --source suite/ndb/t/ndb_backup_nowait_start.inc
25
26 --echo Make some changes which will be logged...
27 insert into test.t1 values
28 ("7a","7b","7c","7d"),
29 ("8a","8b","8c","8d"),
30 ("9a","9b","9c","9d");
31
32 update test.t1 set d=concat(d, "-modified");
33
34 delete from test.t1 where a in ("5a","8a");
35
36 --echo Allow backup to complete...
37 --exec $NDB_MGM -e "all error 0" >> $NDB_TOOLS_OUTPUT 2>&1
38
39 --source suite/ndb/t/ndb_backup_nowait_wait.inc
40
41 drop table test.t1;
42
43 --echo ********************************************
44 --echo * Tests with column change from pk <-> !pk *
45 --echo ********************************************
46
47 --echo Extend pk (a,b) -> (a,b,c)
48 --echo **************************
49 create table test.t1 (a varchar(20) not null,
50 b varchar(20) not null,
51 c varchar(20) not null,
52 d varchar(20),
53 primary key(a,b,c)) engine=ndb;
54
55 --echo Show no PK mapping index
56 select count(1) from ndbinfo.dict_obj_info where fq_name like '%RESTORE_PK_MAPPING%';
57
58 --echo Run restore
59 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
60 select count(1) from ndbinfo.dict_obj_info where fq_name like '%RESTORE_PK_MAPPING%';
61
62 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
63 select count(1) from ndbinfo.dict_obj_info where fq_name like '%RESTORE_PK_MAPPING%';
64
65 select * from test.t1 order by b;
66
67 drop table test.t1;
68
69 --echo Reduce pk (a,b) -> (a)
70 --echo **********************
71
72 create table test.t1 (a varchar(20) not null,
73 b varchar(20) not null,
74 c varchar(20) not null,
75 d varchar(20),
76 primary key(a)) engine=ndb;
77
78 --echo Run restore
79 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
80 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
81
82 select * from test.t1 order by b;
83
84 drop table test.t1;
85
86
87 --echo Reduced and Extended pk (a,b) -> (b,c)
88 --echo **************************************
89
90 create table test.t1 (a varchar(20) not null,
91 b varchar(20) not null,
92 c varchar(20) not null,
93 d varchar(20),
94 primary key(b,c)) engine=ndb;
95
96 --echo Run restore
97 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
98 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
99
100 select * from test.t1 order by b;
101
102 drop table test.t1;
103
104 --echo Reduced and Extended, promoted and demoted pk (a,b) -> (big B, small C)
105 --echo ***********************************************************************
106
107 create table test.t1 (a varchar(20) not null,
108 b varchar(40) not null,
109 c varchar(10) not null,
110 d varchar(20),
111 primary key(b,c)) engine=ndb;
112
113 --echo Run restore
114 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
115 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
116
117 select * from test.t1 order by b;
118
119 drop table test.t1;
120
121 --echo **************************
122 --echo * Tests with column drop *
123 --echo **************************
124
125 --echo Column which was part of pk is gone (a,b) -> (a)
126 --echo ************************************************
127
128 create table test.t1 (a varchar(20) not null,
129 c varchar(20) not null,
130 d varchar(20),
131 primary key(a)) engine=ndb;
132
133 --echo Run restore
134 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
135 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
136
137 select * from test.t1 order by a;
138
139 drop table test.t1;
140
141 --echo Column which was part of pk is gone, another is brought in (a,b) -> (a,c)
142 --echo **************************************************************************
143
144 create table test.t1 (a varchar(20) not null,
145 c varchar(20) not null,
146 d varchar(20),
147 primary key(a,c)) engine=ndb;
148
149 --echo Run restore
150 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
151 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
152
153 select * from test.t1 order by a;
154
155 drop table test.t1;
156
157 --echo Column which was part of pk is gone, another is brought in plus promote/demote (a,b) -> (a,c)
158 --echo **********************************************************************************************
159
160 create table test.t1 (a varchar(40) not null,
161 c varchar(10) not null,
162 d varchar(20),
163 primary key(a,c)) engine=ndb;
164
165 --echo Run restore
166 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --promote-attributes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
167 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --promote-attributes --lossy-conversions --exclude-missing-columns $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
168
169 select * from test.t1 order by a;
170
171 drop table test.t1;
172
173 --echo Column order change
174 --echo *******************
175
176 create table test.t1 (d varchar(20),
177 c varchar(20) not null,
178 b varchar(20) not null,
179 a varchar(20) not null,
180 primary key (b,a)) engine=ndb;
181
182 --echo Run restore
183 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
184 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
185
186 select * from test.t1 order by a;
187
188 drop table test.t1;
189
190
191 --echo Check index reuse between instances with --disable-indexes
192 --echo **********************************************************
193
194 --echo Extend pk (a,b) -> (a,b,c)
195 --echo **************************
196 create table test.t1 (a varchar(20) not null,
197 b varchar(20) not null,
198 c varchar(20) not null,
199 d varchar(20),
200 primary key(a,b,c)) engine=ndb;
201
202 --echo Run restore
203 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
204 select count(1) from ndbinfo.dict_obj_info where fq_name like '%RESTORE_PK_MAPPING%';
205
206 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
207 select count(1) from ndbinfo.dict_obj_info where fq_name like '%RESTORE_PK_MAPPING%';
208
209 --exec $NDB_RESTORE -b $the_backup_id -n 1 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
210 --echo Check that we dropped the pk mapping index
211 select count(1) from ndbinfo.dict_obj_info where fq_name like '%RESTORE_PK_MAPPING%';
212
213 select * from test.t1 order by b;
214
215 drop table test.t1;
216
217
218 # Slightly mixed order
219 create table test.t1 (d varchar(20),
220 c varchar(20) not null,
221 b varchar(20) not null,
222 a varchar(20) not null,
223 primary key(a,b,c)) engine=ndb;
224
225 insert into test.t1 (a,b,c,d) values
226 ("1a","1b","1c","1d"),
227 ("2a","2b","2c","2d"),
228 ("3a","3b","3c","3d"),
229 ("4a","4b","4c","4d"),
230 ("5a","5b","5c","5d"),
231 ("6a","6b","6c","6d");
232
233 --echo Stall backup completion
234 --exec $NDB_MGM -e "all error 10039" >> $NDB_TOOLS_OUTPUT 2>&1
235 --echo Run backup
236 --source suite/ndb/t/ndb_backup_nowait_start.inc
237
238 --echo Make some changes which will be logged...
239 insert into test.t1 (a,b,c,d) values
240 ("7a","7b","7c","7d"),
241 ("8a","8b","8c","8d"),
242 ("9a","9b","9c","9d");
243
244 update test.t1 set d=concat(d, "-modified");
245
246 delete from test.t1 where a in ("5a","8a");
247
248 --echo Allow backup to complete...
249 --exec $NDB_MGM -e "all error 0" >> $NDB_TOOLS_OUTPUT 2>&1
250
251 --source suite/ndb/t/ndb_backup_nowait_wait.inc
252
253 drop table test.t1;
254
255 --echo Column order mixing
256 --echo **************************
257 create table test.t1 (a varchar(20) not null,
258 b varchar(20) not null,
259 c varchar(20) not null,
260 d varchar(20),
261 primary key(a,b,c)) engine=ndb;
262
263 --echo Run restore
264 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
265 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
266
267 select * from test.t1 order by b;
268
269 drop table test.t1;
270
271 create table test.t1 (a varchar(20) not null,
272 b varchar(20) not null,
273 c varchar(20) not null,
274 d varchar(20),
275 primary key(b)) engine=ndb;
276
277 --echo Run restore
278 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
279 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
280
281 select * from test.t1 order by b;
282
283 drop table test.t1;
284
285
286 --echo ************************************************************************
287 --echo Tests for backup log containing update to column(s) becoming primary key
288 --echo ************************************************************************
289
290 create table test.t1 (a varchar(20) not null,
291 b varchar(20) not null,
292 c varchar(20) not null,
293 d varchar(20) not null,
294 e varchar(20),
295 primary key(a,b)) engine=ndb;
296
297 insert into test.t1 values
298 ("1a","1b","1c","1d","1e"),
299 ("2a","2b","2c","2d","2e"),
300 ("3a","3b","3c","3d","3e"),
301 ("4a","4b","4c","4d","4e"),
302 ("5a","5b","5c","5d","5e"),
303 ("6a","6b","6c","6d","6e");
304
305 --echo Stall backup completion
306 --exec $NDB_MGM -e "all error 10039" >> $NDB_TOOLS_OUTPUT 2>&1
307 --echo Run backup
308 --source suite/ndb/t/ndb_backup_nowait_start.inc
309
310 --echo Make some changes which will be logged...
311 insert into test.t1 values
312 ("7a","7b","7c","7d","7e"),
313 ("8a","8b","8c","8d","8e"),
314 ("9a","9b","9c","9d","9e");
315
316 # update column e alone
317 update test.t1 set e=concat(e, "-modified");
318
319 # update all 3 data columns together, with
320 # no real change to c and d
321 # Giving
322 # - multiple updates to the same rows
323 # - updates with multiple columns affected
324 #
325 update test.t1 set c=c, d=d, e=concat(d, "-e");
326
327 # Some deletes
328 delete from test.t1 where a in ("5a","8a");
329
330 --echo Allow backup to complete...
331 --exec $NDB_MGM -e "all error 0" >> $NDB_TOOLS_OUTPUT 2>&1
332
333 --source suite/ndb/t/ndb_backup_nowait_wait.inc
334
335 select * from test.t1 order by a;
336
337 drop table test.t1;
338
339 --echo Show that UPDATE on a single column becoming pk succeeds
340 --echo Extend PK (a,b) -> (a,b,c)
341
342 create table test.t1 (a varchar(20) not null,
343 b varchar(20) not null,
344 c varchar(20) not null,
345 d varchar(20) not null,
346 e varchar(20),
347 primary key(a,b,c)) engine=ndb;
348
349 --echo Run restore
350 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --ignore-extended-pk-updates $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
351 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --ignore-extended-pk-updates $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
352
353 select * from test.t1 order by a;
354
355 drop table test.t1;
356
357 --echo Show that UPDATE on a column becoming pk while another is dropped succeeds
358 --echo Extend + Contract PK (a,b) -> (b,c)
359
360 create table test.t1 (a varchar(20) not null,
361 b varchar(20) not null,
362 c varchar(20) not null,
363 d varchar(20) not null,
364 e varchar(20),
365 primary key(b,c)) engine=ndb;
366
367 --echo Run restore
368 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
369 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
370
371 select * from test.t1 order by a;
372
373 drop table test.t1;
374
375 --echo Show that UPDATE on a column becoming pk while another is dropped succeeds
376 --echo Extend + Contract PK (a,b) -> (b,c)
377 --echo Also show type promotion
378
379 create table test.t1 (a varchar(200) not null,
380 b varchar(200) not null,
381 c varchar(200) not null,
382 d varchar(200) not null,
383 e varchar(200),
384 primary key(b,c)) engine=ndb;
385
386 --echo Run restore
387 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --ignore-extended-pk-updates --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
388 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --ignore-extended-pk-updates --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
389
390 select * from test.t1 order by a;
391
392 drop table test.t1;
393
394 --echo Show that UPDATE on a column becoming pk while another is dropped succeeds
395 --echo Extend + Contract PK (a,b) -> (b,c)
396 --echo Also show type demotion
397
398 create table test.t1 (a varchar(10) not null,
399 b varchar(10) not null,
400 c varchar(10) not null,
401 d varchar(15) not null,
402 e varchar(10),
403 primary key(b,c)) engine=ndb;
404
405 --echo Run restore
406 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
407 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
408
409 select * from test.t1 order by a;
410
411 drop table test.t1;
412
413
414 --echo Show that UPDATE on multiple columns becoming pk while another is dropped succeeds
415 --echo Extend + Contract PK (a,b) -> (b,c,d)
416
417 create table test.t1 (a varchar(20) not null,
418 b varchar(20) not null,
419 c varchar(20) not null,
420 d varchar(20) not null,
421 e varchar(20),
422 primary key(b,c,d)) engine=ndb;
423
424 --echo Run restore
425 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
426 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
427
428 select * from test.t1 order by a;
429
430 drop table test.t1;
431
432 --echo Swap pk entirely
433 --echo PK (a,b) -> (c,d)
434
435 create table test.t1 (a varchar(20) not null,
436 b varchar(20) not null,
437 c varchar(20) not null,
438 d varchar(20) not null,
439 e varchar(20),
440 primary key(c,d)) engine=ndb;
441
442 --echo Run restore
443 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
444 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
445
446 select * from test.t1 order by a;
447
448 drop table test.t1;
449
450 --echo Swap pk entirely + db rewrite
451
452 create database minitel;
453 create table minitel.t1 (a varchar(20) not null,
454 b varchar(20) not null,
455 c varchar(20) not null,
456 d varchar(20) not null,
457 e varchar(20),
458 primary key(c,d)) engine=ndb;
459
460 --echo Run restore
461 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions --rewrite-database=test,minitel $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
462 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --allow-pk-changes --ignore-extended-pk-updates --lossy-conversions --rewrite-database=test,minitel $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
463
464 select * from minitel.t1 order by a;
465
466 drop table minitel.t1;
467 drop database minitel;
468
469 --source suite/ndb/include/backup_restore_cleanup.inc
470 --remove_file $NDB_TOOLS_OUTPUT