Debian Codegraph @

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

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