Debian Codegraph @

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

raw
1 ************************************************************
2 * Creating multiple databases with identical tables
3 * (have blobs and indexes to cover the hidden tables)
4 ************************************************************
5 CREATE DATABASE db0;
6 CREATE DATABASE db0_rewrite;
7 CREATE DATABASE db1;
8 CREATE DATABASE db2;
9 USE db0;
10 CREATE TABLE t0 (
11 id INT PRIMARY KEY,
12 cint INT,
13 cvarchar VARCHAR(5),
14 cblob BLOB(1000004),
15 UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC),
16 UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC)
17 ) ENGINE=NDB;
18 USE db0_rewrite;
19 CREATE TABLE t0 (
20 id INT PRIMARY KEY,
21 cint INT,
22 cvarchar VARCHAR(5),
23 cblob BLOB(1000004),
24 UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC),
25 UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC)
26 ) ENGINE=NDB;
27 USE db1;
28 CREATE TABLE t0 (
29 id INT PRIMARY KEY,
30 cint INT,
31 cvarchar VARCHAR(5),
32 cblob BLOB(1000004),
33 UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC),
34 UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC)
35 ) ENGINE=NDB;
36 USE db2;
37 CREATE TABLE t0 (
38 id INT PRIMARY KEY,
39 cint INT,
40 cvarchar VARCHAR(5),
41 cblob BLOB(1000004),
42 UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC),
43 UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC)
44 ) ENGINE=NDB;
45 ************************************************************
46 * Inserting data
47 * (create disjunct sets of rows to merge without conflicts;
48 * at this time, ndb_restore does not offer any detection
49 * of data conflicts between databases in the backup or in
50 * memory; databases are restored in an unspecified order)
51 * (make blob data long enough to be held in extra table)
52 ************************************************************
53 USE db0;
54 INSERT INTO t0 VALUES (0, 0, '00000', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
55 INSERT INTO t0 VALUES (1, 1, '11111', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
56 INSERT INTO t0 VALUES (2, 2, '22222', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
57 USE db1;
58 INSERT INTO t0 VALUES (3, 3, '33333', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
59 INSERT INTO t0 VALUES (4, 4, '44444', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
60 INSERT INTO t0 VALUES (5, 5, '55555', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
61 USE db2;
62 INSERT INTO t0 VALUES (6, 6, '66666', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
63 INSERT INTO t0 VALUES (7, 7, '77777', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
64 INSERT INTO t0 VALUES (8, 8, '88888', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
65 ************************************************************
66 * Creating in-memory copies of the NDB tables
67 ************************************************************
68 CREATE TABLE db0.t0_data ENGINE=MYISAM AS SELECT * FROM db0.t0;
69 CREATE TABLE db1.t0_data ENGINE=MYISAM AS SELECT * FROM db1.t0;
70 CREATE TABLE db2.t0_data ENGINE=MYISAM AS SELECT * FROM db2.t0;
71 ************************************************************
72 * Backing up databases
73 ************************************************************
74 ************************************************************
75 * Restoring databases with no rewrite (sanity check)
76 ************************************************************
77 CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
78 CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
79 CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
80 DELETE FROM db0.t0;
81 DELETE FROM db1.t0;
82 DELETE FROM db2.t0;
83 SELECT COUNT(*) FROM db0.t0;
84 COUNT(*)
85 3
86 SELECT COUNT(*) FROM db1.t0;
87 COUNT(*)
88 3
89 SELECT COUNT(*) FROM db2.t0;
90 COUNT(*)
91 3
92 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
93 COUNT(*)
94 3
95 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
96 COUNT(*)
97 3
98 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
99 COUNT(*)
100 3
101 DROP TABLE db0.t0_temp;
102 DROP TABLE db1.t0_temp;
103 DROP TABLE db2.t0_temp;
104 ************************************************************
105 * Negative testing: check wrong usage of command-line option
106 * (expected exit code for usage errors: NDBT_WRONGARGS = 2)
107 ************************************************************
108 ************************************************************
109 * Restoring databases with rewritten source db excluded
110 ************************************************************
111 CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
112 CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
113 CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
114 DELETE FROM db0.t0;
115 DELETE FROM db0_rewrite.t0;
116 DELETE FROM db1.t0;
117 DELETE FROM db2.t0;
118 SELECT COUNT(*) FROM db0.t0;
119 COUNT(*)
120 0
121 SELECT COUNT(*) FROM db0_rewrite.t0;
122 COUNT(*)
123 0
124 SELECT COUNT(*) FROM db1.t0;
125 COUNT(*)
126 3
127 SELECT COUNT(*) FROM db2.t0;
128 COUNT(*)
129 3
130 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
131 COUNT(*)
132 0
133 SELECT COUNT(*) FROM db0_rewrite.t0 NATURAL JOIN db0.t0_temp;
134 COUNT(*)
135 0
136 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
137 COUNT(*)
138 3
139 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
140 COUNT(*)
141 3
142 DROP TABLE db0.t0_temp;
143 DROP TABLE db1.t0_temp;
144 DROP TABLE db2.t0_temp;
145 ************************************************************
146 * Restoring databases with rewritten source db included
147 ************************************************************
148 CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
149 CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
150 CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
151 DELETE FROM db0.t0;
152 DELETE FROM db0_rewrite.t0;
153 DELETE FROM db1.t0;
154 DELETE FROM db2.t0;
155 SELECT COUNT(*) FROM db0.t0;
156 COUNT(*)
157 0
158 SELECT COUNT(*) FROM db0_rewrite.t0;
159 COUNT(*)
160 3
161 SELECT COUNT(*) FROM db1.t0;
162 COUNT(*)
163 0
164 SELECT COUNT(*) FROM db2.t0;
165 COUNT(*)
166 0
167 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
168 COUNT(*)
169 0
170 SELECT COUNT(*) FROM db0_rewrite.t0 NATURAL JOIN db0.t0_temp;
171 COUNT(*)
172 3
173 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
174 COUNT(*)
175 0
176 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
177 COUNT(*)
178 0
179 DROP TABLE db0.t0_temp;
180 DROP TABLE db1.t0_temp;
181 DROP TABLE db2.t0_temp;
182 ************************************************************
183 * Restoring databases with rewritten destination db excluded
184 ************************************************************
185 CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
186 CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
187 CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
188 DELETE FROM db0.t0;
189 DELETE FROM db0_rewrite.t0;
190 DELETE FROM db1.t0;
191 DELETE FROM db2.t0;
192 SELECT COUNT(*) FROM db0.t0;
193 COUNT(*)
194 0
195 SELECT COUNT(*) FROM db0_rewrite.t0;
196 COUNT(*)
197 3
198 SELECT COUNT(*) FROM db1.t0;
199 COUNT(*)
200 3
201 SELECT COUNT(*) FROM db2.t0;
202 COUNT(*)
203 3
204 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
205 COUNT(*)
206 0
207 SELECT COUNT(*) FROM db0_rewrite.t0 NATURAL JOIN db0.t0_temp;
208 COUNT(*)
209 3
210 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
211 COUNT(*)
212 3
213 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
214 COUNT(*)
215 3
216 DROP TABLE db0.t0_temp;
217 DROP TABLE db1.t0_temp;
218 DROP TABLE db2.t0_temp;
219 ************************************************************
220 * Restoring databases with rewritten destination db included
221 ************************************************************
222 CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
223 CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
224 CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
225 DELETE FROM db0.t0;
226 DELETE FROM db0_rewrite.t0;
227 DELETE FROM db1.t0;
228 DELETE FROM db2.t0;
229 SELECT COUNT(*) FROM db0.t0;
230 COUNT(*)
231 0
232 SELECT COUNT(*) FROM db0_rewrite.t0;
233 COUNT(*)
234 0
235 SELECT COUNT(*) FROM db1.t0;
236 COUNT(*)
237 0
238 SELECT COUNT(*) FROM db2.t0;
239 COUNT(*)
240 0
241 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
242 COUNT(*)
243 0
244 SELECT COUNT(*) FROM db0_rewrite.t0 NATURAL JOIN db0.t0_temp;
245 COUNT(*)
246 0
247 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
248 COUNT(*)
249 0
250 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
251 COUNT(*)
252 0
253 DROP TABLE db0.t0_temp;
254 DROP TABLE db1.t0_temp;
255 DROP TABLE db2.t0_temp;
256 ************************************************************
257 * Restoring databases with redundant/self-rewrite options
258 ************************************************************
259 CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
260 CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
261 CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
262 DELETE FROM db0.t0;
263 DELETE FROM db1.t0;
264 DELETE FROM db2.t0;
265 SELECT COUNT(*) FROM db0.t0;
266 COUNT(*)
267 3
268 SELECT COUNT(*) FROM db1.t0;
269 COUNT(*)
270 3
271 SELECT COUNT(*) FROM db2.t0;
272 COUNT(*)
273 3
274 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
275 COUNT(*)
276 3
277 SELECT COUNT(*) FROM db0_rewrite.t0 NATURAL JOIN db0.t0_temp;
278 COUNT(*)
279 0
280 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
281 COUNT(*)
282 3
283 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
284 COUNT(*)
285 3
286 DROP TABLE db0.t0_temp;
287 DROP TABLE db1.t0_temp;
288 DROP TABLE db2.t0_temp;
289 ************************************************************
290 * Restoring databases with overriding rewrite options
291 ************************************************************
292 CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
293 CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
294 CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
295 DELETE FROM db0.t0;
296 DELETE FROM db1.t0;
297 DELETE FROM db2.t0;
298 SELECT COUNT(*) FROM db0.t0;
299 COUNT(*)
300 3
301 SELECT COUNT(*) FROM db1.t0;
302 COUNT(*)
303 3
304 SELECT COUNT(*) FROM db2.t0;
305 COUNT(*)
306 3
307 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
308 COUNT(*)
309 3
310 SELECT COUNT(*) FROM db0_rewrite.t0 NATURAL JOIN db0.t0_temp;
311 COUNT(*)
312 0
313 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
314 COUNT(*)
315 3
316 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
317 COUNT(*)
318 3
319 DROP TABLE db0.t0_temp;
320 DROP TABLE db1.t0_temp;
321 DROP TABLE db2.t0_temp;
322 ************************************************************
323 * Restoring databases with a single rewrite
324 ************************************************************
325 CREATE TABLE db0.t0_temp LIKE db0.t0_data;
326 CREATE TABLE db1.t0_temp LIKE db1.t0_data;
327 CREATE TABLE db2.t0_temp LIKE db2.t0_data;
328 DELETE FROM db0.t0;
329 DELETE FROM db1.t0;
330 DELETE FROM db2.t0;
331 SELECT COUNT(*) FROM db0.t0;
332 COUNT(*)
333 0
334 SELECT COUNT(*) FROM db1.t0;
335 COUNT(*)
336 6
337 SELECT COUNT(*) FROM db2.t0;
338 COUNT(*)
339 3
340 INSERT db1.t0_temp SELECT * FROM db0.t0_data;
341 INSERT db1.t0_temp SELECT * FROM db1.t0_data;
342 INSERT db2.t0_temp SELECT * FROM db2.t0_data;
343 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
344 COUNT(*)
345 0
346 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
347 COUNT(*)
348 6
349 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
350 COUNT(*)
351 3
352 DROP TABLE db0.t0_temp;
353 DROP TABLE db1.t0_temp;
354 DROP TABLE db2.t0_temp;
355 ************************************************************
356 * Restoring databases with multiple rewrites
357 ************************************************************
358 CREATE TABLE db0.t0_temp LIKE db0.t0_data;
359 CREATE TABLE db1.t0_temp LIKE db1.t0_data;
360 CREATE TABLE db2.t0_temp LIKE db2.t0_data;
361 DELETE FROM db0.t0;
362 DELETE FROM db1.t0;
363 DELETE FROM db2.t0;
364 SELECT COUNT(*) FROM db0.t0;
365 COUNT(*)
366 0
367 SELECT COUNT(*) FROM db1.t0;
368 COUNT(*)
369 3
370 SELECT COUNT(*) FROM db2.t0;
371 COUNT(*)
372 6
373 INSERT db1.t0_temp SELECT * FROM db0.t0_data;
374 INSERT db2.t0_temp SELECT * FROM db1.t0_data;
375 INSERT db2.t0_temp SELECT * FROM db2.t0_data;
376 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
377 COUNT(*)
378 0
379 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
380 COUNT(*)
381 3
382 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
383 COUNT(*)
384 6
385 DROP TABLE db0.t0_temp;
386 DROP TABLE db1.t0_temp;
387 DROP TABLE db2.t0_temp;
388 ************************************************************
389 * Restoring databases with multiple rewrites into same target
390 ************************************************************
391 CREATE TABLE db0.t0_temp LIKE db0.t0_data;
392 CREATE TABLE db1.t0_temp LIKE db1.t0_data;
393 CREATE TABLE db2.t0_temp LIKE db2.t0_data;
394 DELETE FROM db0.t0;
395 DELETE FROM db1.t0;
396 DELETE FROM db2.t0;
397 SELECT COUNT(*) FROM db0.t0;
398 COUNT(*)
399 0
400 SELECT COUNT(*) FROM db1.t0;
401 COUNT(*)
402 0
403 SELECT COUNT(*) FROM db2.t0;
404 COUNT(*)
405 9
406 INSERT db2.t0_temp SELECT * FROM db0.t0_data;
407 INSERT db2.t0_temp SELECT * FROM db1.t0_data;
408 INSERT db2.t0_temp SELECT * FROM db2.t0_data;
409 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
410 COUNT(*)
411 0
412 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
413 COUNT(*)
414 0
415 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
416 COUNT(*)
417 9
418 DROP TABLE db0.t0_temp;
419 DROP TABLE db1.t0_temp;
420 DROP TABLE db2.t0_temp;
421 ************************************************************
422 * Restoring databases with swapping rewrites
423 ************************************************************
424 CREATE TABLE db0.t0_temp LIKE db0.t0_data;
425 CREATE TABLE db1.t0_temp LIKE db1.t0_data;
426 CREATE TABLE db2.t0_temp LIKE db2.t0_data;
427 DELETE FROM db0.t0;
428 DELETE FROM db1.t0;
429 DELETE FROM db2.t0;
430 SELECT COUNT(*) FROM db0.t0;
431 COUNT(*)
432 3
433 SELECT COUNT(*) FROM db1.t0;
434 COUNT(*)
435 3
436 SELECT COUNT(*) FROM db2.t0;
437 COUNT(*)
438 3
439 INSERT db1.t0_temp SELECT * FROM db0.t0_data;
440 INSERT db0.t0_temp SELECT * FROM db1.t0_data;
441 INSERT db2.t0_temp SELECT * FROM db2.t0_data;
442 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
443 COUNT(*)
444 3
445 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
446 COUNT(*)
447 3
448 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
449 COUNT(*)
450 3
451 DROP TABLE db0.t0_temp;
452 DROP TABLE db1.t0_temp;
453 DROP TABLE db2.t0_temp;
454 ************************************************************
455 * Restoring databases with permutating rewrites
456 ************************************************************
457 CREATE TABLE db0.t0_temp LIKE db0.t0_data;
458 CREATE TABLE db1.t0_temp LIKE db1.t0_data;
459 CREATE TABLE db2.t0_temp LIKE db2.t0_data;
460 DELETE FROM db0.t0;
461 DELETE FROM db1.t0;
462 DELETE FROM db2.t0;
463 SELECT COUNT(*) FROM db0.t0;
464 COUNT(*)
465 3
466 SELECT COUNT(*) FROM db1.t0;
467 COUNT(*)
468 3
469 SELECT COUNT(*) FROM db2.t0;
470 COUNT(*)
471 3
472 INSERT db1.t0_temp SELECT * FROM db0.t0_data;
473 INSERT db2.t0_temp SELECT * FROM db1.t0_data;
474 INSERT db0.t0_temp SELECT * FROM db2.t0_data;
475 SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
476 COUNT(*)
477 3
478 SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
479 COUNT(*)
480 3
481 SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
482 COUNT(*)
483 3
484 DROP TABLE db0.t0_temp;
485 DROP TABLE db1.t0_temp;
486 DROP TABLE db2.t0_temp;
487 ************************************************************
488 * Deleting tables and databases
489 ************************************************************
490 DROP DATABASE db0;
491 DROP DATABASE db0_rewrite;
492 DROP DATABASE db1;
493 DROP DATABASE db2;