Debian Codegraph @

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

raw
1 ######################################################################
2 # Test various issues ndb_restore had at some time
3 ######################################################################
4
5 -- source include/have_multi_ndb.inc
6 -- source suite/ndb/include/backup_restore_setup.inc
7
8 #
9 # Bug #27775 - mediumint auto inc not restored correctly
10 # - check mediumint
11 CREATE TABLE `t1_c` (
12 `capgoaledatta` mediumint(5) unsigned NOT NULL auto_increment,
13 `goaledatta` char(2) NOT NULL default '',
14 `maturegarbagefa` varchar(32) NOT NULL default '',
15 PRIMARY KEY (`capgoaledatta`,`goaledatta`,`maturegarbagefa`)
16 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
17 INSERT INTO `t1_c` VALUES (2,'3','q3plus.qt'),(400,'4','q3plus.qt'),(1,'3','q3.net'),(3,'4','q3.net'),(3000,'20','threetrees.qt');
18 #
19 # Bug #27758 Restoring NDB backups makes table usable in SQL nodes
20 # - space in key made table unusable after restore
21 #
22 # Bug #27775 - mediumint auto inc not restored correctly
23 # - check smallint
24 CREATE TABLE `t2_c` (
25 `capgotod` smallint(5) unsigned NOT NULL auto_increment,
26 `gotod` smallint(5) unsigned NOT NULL default '0',
27 `goaledatta` char(2) default NULL,
28 `maturegarbagefa` varchar(32) default NULL,
29 `descrpooppo` varchar(64) default NULL,
30 `svcutonsa` varchar(64) NOT NULL default '',
31 PRIMARY KEY (`capgotod`),
32 KEY `i quadaddsvr` (`gotod`)
33 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
34 INSERT INTO `t2_c` VALUES (500,4,'','q3.net','addavp:MK_CASELECTOR=1','postorod rattoaa'),(2,1,'4','','addavp:MK_BRANDTAD=345','REDS Brandtad'),(3,2,'4','q3.net','execorder','fixedRatediPO REDS'),(1,1,'3','','addavp:MK_BRANDTAD=123','TEST Brandtad'),(6,5,'','told.q3.net','addavp:MK_BRANDTAD=123','Brandtad Toldzone'),(4,3,'3','q3.net','addavp:MK_POOLHINT=2','ratedi PO TEST'),(5,0,'',NULL,NULL,'');
35
36 # Added ROW_FORMAT=FIXED to use below to see that setting is preserved
37 # by restore
38 CREATE TABLE `t3_c` (
39 `CapGoaledatta` smallint(5) unsigned NOT NULL default '0',
40 `capgotod` smallint(5) unsigned NOT NULL default '0',
41 PRIMARY KEY (`capgotod`,`CapGoaledatta`)
42 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;
43 INSERT INTO `t3_c` VALUES (5,3),(2,4),(5,4),(1,3);
44
45 # Bug #27775 - mediumint auto inc not restored correctly
46 # - check bigint
47 CREATE TABLE `t4_c` (
48 `capfa` bigint(20) unsigned NOT NULL auto_increment,
49 `realm` varchar(32) NOT NULL default '',
50 `authpwchap` varchar(32) default NULL,
51 `fa` varchar(32) NOT NULL default '',
52 `payyingatta` tinyint(4) NOT NULL default '0',
53 `status` char(1) default NULL,
54 PRIMARY KEY (`fa`,`realm`),
55 KEY `capfa` (`capfa`),
56 KEY `i_quadentity` (`fa`,`realm`)
57 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
58 INSERT INTO `t4_c` VALUES (18,'john.smith','q3.net','dessjohn.smith',0,NULL),(21,'quad_katt_with_brandtad','q3.net','acne',0,NULL),(2200,'quad_katt_carattoaa','q3.net','acne',0,NULL),(26,'436462612809','sqasdt.q3.net','N/A',0,'6'),(19,'john','smith.qt','dessjohn',0,NULL),(33,'436643196120','sqasdt.q3.net','N/A',1,'6'),(28,'436642900019','sqasdt.q3.net','N/A',0,'6'),(30,'436462900209','sqasdt.q3.net','N/A',0,'6'),(16,'436640006666','sqasdt.q3.net','',0,NULL),(19,'dette','el-redun.com','dessdette',0,NULL),(12,'quad_kattPP','q3.net','acne',2,NULL),(14,'436640008888','sqasdt.q3.net','',0,NULL),(29,'463624900028','sqasdt.q3.net','N/A',0,'6'),(15,'436640099099','sqasdt.q3.net','',0,NULL),(13,'pap','q3plus.qt','acne',1,NULL),(19,'436642612091','sqasdt.q3.net','N/A',0,'6'),(12,'quad_katt','q3.net','acne',0,NULL),(11,'quad_kattVK','q3.net','acne',1,NULL),(32000,'463641969502','sqasdt.q3.net','N/A',1,'6'),(20,'joe','q3.net','joedesswd',0,NULL),(290000000,'436642900034','sqasdt.q3.net','N/A',0,'6'),(25,'contind','armerde.qt','acne',1,NULL);
59
60 CREATE TABLE `t5_c` (
61 `capfa` bigint(20) unsigned NOT NULL default '0',
62 `gotod` smallint(5) unsigned NOT NULL default '0',
63 `orderutonsa` varchar(64) NOT NULL default '',
64 PRIMARY KEY (`capfa`,`gotod`,`orderutonsa`)
65 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
66 INSERT INTO `t5_c` VALUES (21,2,''),(21,1,''),(22,4,'');
67
68 CREATE TABLE `t6_c` (
69 `capfa_parent` bigint(20) unsigned NOT NULL default '0',
70 `capfa_child` bigint(20) unsigned NOT NULL default '0',
71 `relatta` smallint(5) unsigned NOT NULL default '0',
72 PRIMARY KEY (`capfa_child`,`capfa_parent`,`relatta`)
73 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
74 INSERT INTO `t6_c` VALUES (15,16,0),(19,20,0),(18326932092909551615,30,0),(26,29,0),(18326932092909551615,29,0),(19,18,0),(26,28,0),(12,14,0);
75
76 # Bug #27775 - mediumint auto inc not restored correctly
77 # - check tinyint
78 CREATE TABLE `t7_c` (
79 `dardpo` char(15) NOT NULL default '',
80 `dardtestard` tinyint(3) unsigned NOT NULL auto_increment,
81 `FastFA` char(5) NOT NULL default '',
82 `FastCode` char(6) NOT NULL default '',
83 `Fastca` char(1) NOT NULL default '',
84 `Fastmag` char(1) NOT NULL default '',
85 `Beareratta` char(2) NOT NULL default '',
86 PRIMARY KEY (`dardpo`,`dardtestard`)
87 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
88 INSERT INTO `t7_c` VALUES ('2.6.2.4',24,'CECHP','54545','0','0','5'),('2.2.5.4',26,'CANFA','33223','1','1','4'),('4.3.2.4',28,'ITALD','54222','1','0','5'),('129..0.0.eins',28,'G','99999','1','1','5'),('1.1.1.1',24,'AUTPT','32323','0','1','3');
89
90 CREATE TABLE `t8_c` (
91 `kattjame` varchar(32) NOT NULL default '',
92 `realm` varchar(32) NOT NULL default '',
93 `realm_entered` varchar(32) NOT NULL default '',
94 `maturegarbagefa` varchar(32) NOT NULL default '',
95 `hunderaaarbagefa_parent` varchar(32) NOT NULL default '',
96 `kattjame_entered` varchar(32) NOT NULL default '',
97 `hunderaaarbagefa` varchar(32) NOT NULL default '',
98 `gest` varchar(16) default NULL,
99 `hassetino` varchar(16) NOT NULL default '',
100 `aaaproxysessfa` varchar(255) default NULL,
101 `autologonallowed` char(1) default NULL,
102 `squardporoot` varchar(15) NOT NULL default '',
103 `naspo` varchar(15) default NULL,
104 `beareratta` char(2) default NULL,
105 `fastCode` varchar(6) default NULL,
106 `fastFA` varchar(5) default NULL,
107 `fastca` char(1) default NULL,
108 `fastmag` char(1) default NULL,
109 `lastupdate` datetime default NULL,
110 `hassetistart` datetime NOT NULL,
111 `accthassetitime` int(10) unsigned default NULL,
112 `acctoutputoctets` bigint(20) unsigned default NULL,
113 `acctinputoctets` bigint(20) unsigned default NULL,
114 PRIMARY KEY (`kattjame`,`hunderaaarbagefa`,`hassetistart`,`hassetino`),
115 KEY `squardporoot` (`squardporoot`)
116 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
117 INSERT INTO `t8_c` VALUES ('4tt45345235','pap','q3plus.qt','q3plus.qt','q3.net','436643196120','436643196929','8956234534568968','5524595699','uxasmt21.net.acne.qt/481889229462692422','','1.1.1.1','2.2.4.6','4','86989','34','x','x','2012-03-12 12:55:34','2012-12-05 11:20:04',3223433,3369,9565),('4545435545','john','q3.net','q3.net','acne.li','436643196120','436643196929','45345234568968','995696699','uxasmt21.net.acne.qt/481889229462692423','','1.1.1.1','2.2.9.8','2','86989','34','x','x','2012-03-12 11:35:03','2012-12-05 08:50:04',8821923,169,3565),('versteckter_q3net_katt','joe','q3.net','elredun.com','q3.net','436643196120','436643196939','91341234568968','695595699','uxasmt21.net.acne.qt/481889229462692421','','1.1.1.1','2.5.2.5','3','86989','34','x','x','2012-03-12 18:35:04','2012-12-05 12:35:04',1923123,9569,6565);
118
119 CREATE TABLE `t9_c` (
120 `kattjame` varchar(32) NOT NULL default '',
121 `kattjame_entered` varchar(32) NOT NULL default '',
122 `realm` varchar(32) NOT NULL default '',
123 `realm_entered` varchar(32) NOT NULL default '',
124 `maturegarbagefa` varchar(32) NOT NULL default '',
125 `hunderaaarbagefa` varchar(32) NOT NULL default '',
126 `hunderaaarbagefa_parent` varchar(32) NOT NULL default '',
127 `gest` varchar(16) default NULL,
128 `hassetino` varchar(16) NOT NULL default '',
129 `squardporoot` varchar(15) NOT NULL default '',
130 `naspo` varchar(15) default NULL,
131 `beareratta` char(2) default NULL,
132 `fastCode` varchar(6) default NULL,
133 `fastFA` varchar(5) default NULL,
134 `fastca` char(1) default NULL,
135 `fastmag` char(1) default NULL,
136 `lastupdate` datetime default NULL,
137 `hassetistart` datetime NOT NULL,
138 `accthassetitime` int(10) unsigned default NULL,
139 `actcoutpuocttets` bigint(20) unsigned default NULL,
140 `actinputocctets` bigint(20) unsigned default NULL,
141 `terminateraste` tinyint(3) unsigned default NULL,
142 PRIMARY KEY (`kattjame`,`hunderaaarbagefa`,`hassetistart`,`hassetino`)
143 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
144 INSERT INTO `t9_c` VALUES ('3g4jh8gar2t','joe','q3.net','elredun.com','q3.net','436643316120','436643316939','91341234568968','695595699','1.1.1.1','2.2.6.2','3','86989','34','x','x','2012-03-12 18:35:04','2012-12-05 12:35:04',3123123,9569,6565,1),('4tt45345235','pap','q3plus.qt','q3plus.qt','q3.net','436643316120','436643316939','8956234534568968','5254595969','1.1.1.1','8.6.2.2','4','86989','34','x','x','2012-03-12 12:55:34','2012-12-05 11:20:04',3223433,3369,9565,2),('4545435545','john','q3.net','q3.net','acne.li','436643316120','436643316939','45345234568968','995696699','1.1.1.1','2.9.9.2','2','86998','34','x','x','2012-03-12 11:35:03','2012-12-05 08:50:04',8823123,169,3565,3);
145
146 # Bug #20820
147 # auto inc table not handled correctly when restored from cluster backup
148 # - before fix ndb_restore would not set auto inc value correct,
149 # seen by select below
150 CREATE TABLE t10_c (a INT AUTO_INCREMENT KEY) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
151 INSERT INTO t10_c VALUES (1),(2),(3);
152 # Bug #27775 - mediumint auto inc not restored correctly
153 # - check int
154 insert into t10_c values (10000),(2000),(3000);
155
156 CREATE TABLE t11_c (a int, b00 int null, b01 int null,b02 int null,b03 int null,b04 int null,b05 int null,b06 int null,b07 int null,b08 int null,b90 int null,b10 int null,b11 int null,b12 int null,b13 int null,b14 int null,b15 int null,b16 int null,b17 int null,b18 int null,b19 int null,b20 int null,b21 int null,b22 int null,b23 int null,b24 int null,b25 int null,b26 int null,b27 int null,b28 int null,b29 int null,b30 int null,b31 int null,b32 int null) engine = ndb;
157 insert into t11_c values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
158
159 create table t1 engine=myisam as select * from t1_c;
160 create table t2 engine=myisam as select * from t2_c;
161 create table t3 engine=myisam as select * from t3_c;
162 create table t4 engine=myisam as select * from t4_c;
163 create table t5 engine=myisam as select * from t5_c;
164 create table t6 engine=myisam as select * from t6_c;
165 create table t7 engine=myisam as select * from t7_c;
166 create table t8 engine=myisam as select * from t8_c;
167 create table t9 engine=myisam as select * from t9_c;
168 create table t10 engine=myisam as select * from t10_c;
169 create table t11 engine=myisam as select * from t11_c;
170
171 # check that force varpart is preserved by ndb_restore
172 # t3_c has ROW_FORMAT=FIXED i.e. ForceVarPart=0
173 let $show_varpart_table=t3_c;
174 source show_varpart.inc;
175 let $show_varpart_table=t2_c;
176 source show_varpart.inc;
177
178 # Turn off metadata check so it doesn't intefere with table discovery
179 --connection server2
180 SET @old_ndb_metadata_check_server2 = @@global.ndb_metadata_check;
181 SET GLOBAL ndb_metadata_check = false;
182 --connection server1
183 SET @old_ndb_metadata_check_server1 = @@global.ndb_metadata_check;
184 SET GLOBAL ndb_metadata_check = false;
185
186 --source include/ndb_backup.inc
187 drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c,t10_c,t11_c;
188 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
189 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
190
191 # check that force varpart is preserved by ndb_restore
192 # t3_c has ROW_FORMAT=FIXED i.e. ForceVarPart=0
193 let $show_varpart_table=t3_c;
194 source show_varpart.inc;
195 let $show_varpart_table=t2_c;
196 source show_varpart.inc;
197
198 select count(*) from t1;
199 select count(*) from t1_c;
200 select count(*)
201 from (select * from t1 union
202 select * from t1_c) a;
203
204 select count(*) from t2;
205 select count(*) from t2_c;
206 select count(*)
207 from (select * from t2 union
208 select * from t2_c) a;
209
210 select count(*) from t3;
211 select count(*) from t3_c;
212 select count(*)
213 from (select * from t3 union
214 select * from t3_c) a;
215
216 select count(*) from t4;
217 select count(*) from t4_c;
218 select count(*)
219 from (select * from t4 union
220 select * from t4_c) a;
221
222 select count(*) from t5;
223 select count(*) from t5_c;
224 select count(*)
225 from (select * from t5 union
226 select * from t5_c) a;
227
228 select count(*) from t6;
229 select count(*) from t6_c;
230 select count(*)
231 from (select * from t6 union
232 select * from t6_c) a;
233
234 select count(*) from t7;
235 select count(*) from t7_c;
236 select count(*)
237 from (select * from t7 union
238 select * from t7_c) a;
239
240 select count(*) from t8;
241 select count(*) from t8_c;
242 select count(*)
243 from (select * from t8 union
244 select * from t8_c) a;
245
246 select count(*) from t9;
247 select count(*) from t9_c;
248 select count(*)
249 from (select * from t9 union
250 select * from t9_c) a;
251
252 select count(*) from t10;
253 select count(*) from t10_c;
254 select count(*)
255 from (select * from t10 union
256 select * from t10_c) a;
257
258 select count(*) from t11;
259 select count(*) from t11_c;
260 select count(*)
261 from (select * from t11 union
262 select * from t11_c) a;
263
264 # Bug #20820 cont'd
265 select * from t10_c order by a;
266 # Bug #27775 cont'd
267 # - auto inc info should be correct
268 select max(capgoaledatta) from t1_c;
269 select auto_increment from information_schema.tables
270 where table_name = 't1_c';
271 select max(capgotod) from t2_c;
272 select auto_increment from information_schema.tables
273 where table_name = 't2_c';
274 select max(capfa) from t4_c;
275 select auto_increment from information_schema.tables
276 where table_name = 't4_c';
277 select max(dardtestard) from t7_c;
278 select auto_increment from information_schema.tables
279 where table_name = 't7_c';
280 select max(a) from t10_c;
281 select auto_increment from information_schema.tables
282 where table_name = 't10_c';
283
284 #
285 # Try Partitioned tables as well
286 #
287 ALTER TABLE t7_c
288 PARTITION BY LINEAR KEY (`dardtestard`);
289
290 --source include/ndb_backup.inc
291 drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c,t10_c,t11_c;
292 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
293 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
294
295 select count(*) from t1;
296 select count(*) from t1_c;
297 select count(*)
298 from (select * from t1 union
299 select * from t1_c) a;
300
301 select count(*) from t2;
302 select count(*) from t2_c;
303 select count(*)
304 from (select * from t2 union
305 select * from t2_c) a;
306
307 select count(*) from t3;
308 select count(*) from t3_c;
309 select count(*)
310 from (select * from t3 union
311 select * from t3_c) a;
312
313 select count(*) from t4;
314 select count(*) from t4_c;
315 select count(*)
316 from (select * from t4 union
317 select * from t4_c) a;
318
319 select count(*) from t5;
320 select count(*) from t5_c;
321 select count(*)
322 from (select * from t5 union
323 select * from t5_c) a;
324
325 select count(*) from t6;
326 select count(*) from t6_c;
327 select count(*)
328 from (select * from t6 union
329 select * from t6_c) a;
330
331 select count(*) from t7;
332 select count(*) from t7_c;
333 select count(*)
334 from (select * from t7 union
335 select * from t7_c) a;
336
337 select count(*) from t8;
338 select count(*) from t8_c;
339 select count(*)
340 from (select * from t8 union
341 select * from t8_c) a;
342
343 select count(*) from t9;
344 select count(*) from t9_c;
345 select count(*)
346 from (select * from t9 union
347 select * from t9_c) a;
348
349 select count(*) from t10;
350 select count(*) from t10_c;
351 select count(*)
352 from (select * from t10 union
353 select * from t10_c) a;
354
355 select count(*) from t11;
356 select count(*) from t11_c;
357 select count(*)
358 from (select * from t11 union
359 select * from t11_c) a;
360
361 drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c,t10_c,t11_c;
362 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --ndb-nodegroup_map "(0,0)" --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
363 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
364
365 select count(*) from t1;
366 select count(*) from t1_c;
367 select count(*)
368 from (select * from t1 union
369 select * from t1_c) a;
370
371 select count(*) from t2;
372 select count(*) from t2_c;
373 select count(*)
374 from (select * from t2 union
375 select * from t2_c) a;
376
377 select count(*) from t3;
378 select count(*) from t3_c;
379 select count(*)
380 from (select * from t3 union
381 select * from t3_c) a;
382
383 select count(*) from t4;
384 select count(*) from t4_c;
385 select count(*)
386 from (select * from t4 union
387 select * from t4_c) a;
388
389 select count(*) from t5;
390 select count(*) from t5_c;
391 select count(*)
392 from (select * from t5 union
393 select * from t5_c) a;
394
395 select count(*) from t6;
396 select count(*) from t6_c;
397 select count(*)
398 from (select * from t6 union
399 select * from t6_c) a;
400
401 select count(*) from t7;
402 select count(*) from t7_c;
403 select count(*)
404 from (select * from t7 union
405 select * from t7_c) a;
406
407 select count(*) from t8;
408 select count(*) from t8_c;
409 select count(*)
410 from (select * from t8 union
411 select * from t8_c) a;
412
413 select count(*) from t9;
414 select count(*) from t9_c;
415 select count(*)
416 from (select * from t9 union
417 select * from t9_c) a;
418
419 select count(*) from t10;
420 select count(*) from t10_c;
421 select count(*)
422 from (select * from t10 union
423 select * from t10_c) a;
424
425 select count(*) from t11;
426 select count(*) from t11_c;
427 select count(*)
428 from (select * from t11 union
429 select * from t11_c) a;
430 #
431 # Drop all table except t2_c
432 # This to make sure that error returned from ndb_restore below is
433 # guaranteed to be from t2_c, this since order of tables in backup
434 # is none deterministic
435 #
436 drop table t1_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c,t10_c,t11_c;
437 --source include/ndb_backup.inc
438
439 # ndb_restore should now fail, table t2_c already exists
440 --disable_query_log
441 CREATE TEMPORARY TABLE ndb_restore_output (output_line VARCHAR(512)) ENGINE = HEAP;
442 let $dump_file = $MYSQLTEST_VARDIR/tmp/ndb_restore_misc_output.txt;
443 --error 1
444 --exec $NDB_RESTORE -b $the_backup_id --timestamp_printouts=false -n 1 -m -r --ndb-nodegroup_map "(0,1)" --verbose=0 $NDB_BACKUPS-$the_backup_id > $dump_file 2>&1
445 eval LOAD DATA INFILE '$dump_file' INTO TABLE ndb_restore_output;
446 UPDATE ndb_restore_output SET output_line = REPLACE(output_line, '\r', '');
447 --remove_file $dump_file
448 --enable_query_log
449 select output_line from ndb_restore_output where output_line like '%t2_c%' order by output_line;
450 --disable_query_log
451 DROP TABLE ndb_restore_output;
452 --enable_query_log
453
454 CREATE TABLE t11_c (
455 c1 int primary key, c2 char(10), c3 varchar(10)
456 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
457
458 CREATE TABLE t12_c (
459 c1 int primary key, c2 char(10), c3 varchar(10)
460 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
461
462 INSERT INTO t11_c VALUES(1, "aaaaa", "bbbbb"), (2, "ccccc", "ddddd"), (3, "eeeee","fffff");
463 INSERT INTO t12_c VALUES(4, "ggggg", "hhhhh"), (5, "iiiii", "jjjjj"), (6, "kkkkk","lllll");
464 --source include/ndb_backup.inc
465 drop table t2_c,t11_c,t12_c;
466 # Only part of tables is restored, it should work
467 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --print --print_meta $NDB_BACKUPS-$the_backup_id test t11_c >> $NDB_TOOLS_OUTPUT
468 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id test t11_c >> $NDB_TOOLS_OUTPUT
469
470 #Should only t11_c is restored
471 SELECT * FROM t11_c ORDER BY c1;
472
473 #
474 # Cleanup
475 #
476
477 drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t11_c;
478
479 #
480 # Test BUG#10287
481 #
482
483 source include/ndb_backup_id.inc;
484
485 # End of 5.0 tests (4.1 test intermixed to save test time)
486
487 #
488 # Test restore of epoch number bug#
489 #
490
491 # ensure correct restore of epoch numbers in old versions
492 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -e -b 1 -n 1 $NDB_SAVED_BACKUPS/50 >> $NDB_TOOLS_OUTPUT
493 select epoch from mysql.ndb_apply_status where server_id=0;
494 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -e -b 1 -n 1 $NDB_SAVED_BACKUPS/51 >> $NDB_TOOLS_OUTPUT
495 select epoch from mysql.ndb_apply_status where server_id=0;
496 # ensure correct restore of epoch numbers in current version
497 # number hould be "big"
498 --exec $NDB_RESTORE -e -b $the_backup_id -n 1 $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
499 # should evaluate to true == 1
500 select epoch > (1 << 32) from mysql.ndb_apply_status where server_id=0;
501
502 #
503 # Bug#40428 core dumped when restore backup log file(redo log)
504 #
505 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR --print --print_meta -b 1 -n 1 $NDB_SAVED_BACKUPS/50 >> $NDB_TOOLS_OUTPUT
506
507 #
508 # Bug #33040 ndb_restore crashes with --print_log
509 #
510 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR --print_log -b 1 -n 1 $NDB_SAVED_BACKUPS/50 >> $NDB_TOOLS_OUTPUT
511
512 #
513 # Bug#48005 ndb backup / restore does not restore the auto_increment
514 #
515 create table t1 (a int not null primary key auto_increment, b int) auto_increment=200
516 engine=ndb;
517 --disable_warnings
518 show create table t1;
519 --enable_warnings
520 --source include/ndb_backup.inc
521 drop table t1;
522 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
523 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
524
525 show create table t1;
526 drop table t1;
527
528 #
529 # mtoib
530 #
531 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --print_meta --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
532 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
533 --exec $NDB_RESTORE -b $the_backup_id -n 1 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
534
535 drop table t1;
536
537 #
538 # Bug#51374
539 # Restore using --disable-indexes --rebuild-indexes, then DROP the table
540 # The bug caused the indexes to remain after the table was dropped
541 #
542 create table t1 (a int, b int, c int not null,
543 primary key (a) using hash, unique(c) using hash)
544 engine = ndb;
545 # Save table id of t1
546 let $t1_id =
547 `SELECT id FROM ndbinfo.dict_obj_info WHERE fq_name = 'test/def/t1'`;
548 insert into t1 values (1,1,1), (2,2,2), (3,3,3);
549 --source include/ndb_backup.inc
550 --exec $NDB_RESTORE -b $the_backup_id -n 1 --disable-indexes --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
551 select * from t1 order by 1;
552 drop table t1;
553 # Check that all objects related to t1 was dropped
554 if (`SELECT COUNT(*) FROM ndbinfo.dict_obj_info WHERE parent_obj_id = $t1_id`)
555 {
556 echo Detected failure to drop all objects related to t1;
557 echo t1_id: $t1_id;
558 eval SELECT * FROM ndbinfo.dict_obj_info WHERE parent_obj_id = $t1_id;
559 die Failed to drop all objects for t1;
560 }
561
562 #
563 # Bug#51432
564 #
565 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -m $NDB_SAVED_BACKUPS/hashmap >> $NDB_TOOLS_OUTPUT
566
567 #
568 # Bug#56285
569 #
570 create table t1 (a int primary key) engine = ndb;
571 --exec $NDB_MGM -e "abort backup 33" >> $NDB_TOOLS_OUTPUT
572 --exec $NDB_MGM -e "abort backup 33" >> $NDB_TOOLS_OUTPUT
573 drop table t1;
574
575 #
576 # test backup of table with many attributes
577 #
578 let $i=499;
579 let $separator=;
580 let $sql=create table t1 (;
581 while ($i)
582 {
583 let $sql=$sql$separator c$i int;
584 let $separator=,;
585 dec $i;
586 }
587 let $sql=$sql, c500 varchar(11000) CHARACTER SET latin1;
588 let $sql=$sql, primary key using hash(c1)) engine=ndb;
589 eval $sql; # eval the sql and create the table
590
591 set @v10 = '0123456789';
592 set @v100 = concat(@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10,@v10);
593 set @v1000 =concat(@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100,@v100);
594 set @v10000 = concat(@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000,@v1000);
595 set @v11000 = concat(@v10000, @v1000);
596
597 insert into t1 (c1,c500) values (1,@v11000), (2,@v11000), (3,@v11000);
598 --source include/ndb_backup.inc
599 drop table t1;
600
601 #
602 # Bug#16593604
603 #
604
605 # Build the standard command used for running "ndb_mgm"
606 let $mgm_cmd = $NDB_MGM;
607
608 --echo # Trying to start another backup as wait started with same id should return error
609 --error 255
610 --exec $mgm_cmd -e "start backup $the_backup_id wait started"
611
612 --echo # Trying to start another backup as wait completed with same id should return error
613 --error 255
614 --exec $mgm_cmd -e "start backup $the_backup_id wait completed"
615
616 --echo # Trying to start another backup as nowait with same id should not return error
617 --exec $mgm_cmd -e "start backup $the_backup_id nowait"
618
619 #
620 # Bug#11764704 Exclude missing tables when restoring a backup
621 #
622 create database db1;
623 use db1;
624 CREATE TABLE t1 (a INT) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
625 CREATE TABLE t2 (b INT) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
626 CREATE TABLE t3 (c INT) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
627 INSERT INTO t1 VALUES (1),(2),(3);
628 INSERT INTO t2 VALUES (4),(5),(6);
629 INSERT INTO t3 VALUES (7),(8),(9);
630 --source include/ndb_backup.inc
631 truncate t1;
632 truncate t2;
633 drop table t3;
634 --echo #without 'exclude-missing-tables' should fail
635 --error 1
636 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
637 --error 1
638 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
639 --echo #calling 'exclude-missing-tables' with 'restore-meta' should fail
640 --error 1
641 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r -m --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
642 --echo #with 'exclude-missing-tables'
643 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --print_meta --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
644 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
645 select count(*) from t1;
646 select count(*) from t2;
647 --echo #with exclusive include, exclude tables arguments
648 --echo #exclude non missing table
649 truncate t1;
650 truncate t2;
651 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --print_meta --exclude-tables=db1.t1 --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
652 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta --exclude-tables=db1.t1 --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
653 select count(*) from t1;
654 select count(*) from t2;
655 --echo #exclude missing table
656 truncate t1;
657 truncate t2;
658 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --print_meta --exclude-tables=db1.t3 --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
659 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta --exclude-tables=db1.t3 --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
660 select count(*) from t1;
661 select count(*) from t2;
662 --echo #include non missing table
663 truncate t1;
664 truncate t2;
665 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --print_meta --include-tables=db1.t1 --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
666 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta --include-tables=db1.t1 --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
667 select count(*) from t1;
668 select count(*) from t2;
669 --echo #include missing table
670 truncate t1;
671 truncate t2;
672 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --print_meta --include-tables=db1.t1,db1.t3 --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
673 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta --include-tables=db1.t1,db1.t3 --exclude-missing-tables $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
674 select count(*) from t1;
675 select count(*) from t2;
676 --echo #clearup
677 drop database db1;
678 --echo # ndb_restore with unique index, check behaviour of --restore-epoch
679 use test;
680 create table t1(id int primary key, val int unique) engine=ndb;
681 --source include/ndb_backup.inc
682 drop table t1;
683 delete from mysql.ndb_apply_status;
684 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
685 --exec $NDB_RESTORE -b $the_backup_id -n 2 --restore-epoch $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
686
687 select count(1) from mysql.ndb_apply_status where server_id=0;
688 drop table t1;
689
690 --disable_query_log
691 CREATE TEMPORARY TABLE ndb_restore_output (output_line VARCHAR(512)) ENGINE = HEAP;
692 let $dump_file = $MYSQLTEST_VARDIR/tmp/ndb_restore_misc_output.txt;
693 --error 2
694 --exec $NDB_RESTORE -b $the_backup_id --ndb-nodeid=200 -r $NDB_BACKUPS-$the_backup_id > $dump_file 2>&1
695 eval LOAD DATA INFILE '$dump_file' INTO TABLE ndb_restore_output;
696 UPDATE ndb_restore_output SET output_line = REPLACE(output_line, '\r', '');
697 --remove_file $dump_file
698 --enable_query_log
699 select output_line from ndb_restore_output where output_line like '%not specified%' order by output_line;
700 --disable_query_log
701
702 --error 2
703 --exec $NDB_RESTORE --nodeid=2 -r $NDB_BACKUPS-$the_backup_id > $dump_file 2>&1
704 eval LOAD DATA INFILE '$dump_file' INTO TABLE ndb_restore_output;
705 UPDATE ndb_restore_output SET output_line = REPLACE(output_line, '\r', '');
706 --remove_file $dump_file
707 --enable_query_log
708 select output_line from ndb_restore_output where output_line like '%not specified%' order by output_line;
709 --disable_query_log
710
711 DROP TABLE ndb_restore_output;
712 --enable_query_log
713
714 # Reset metadata check value
715 --connection server2
716 SET GLOBAL ndb_metadata_check = @old_ndb_metadata_check_server2;
717 --connection server1
718 SET GLOBAL ndb_metadata_check = @old_ndb_metadata_check_server1;
719
720 --echo Coverage of increased --parallelism setting
721 create table t1 (a int primary key, b int) engine=ndb;
722
723 --echo Insert enough rows to get some volume of data
724 --echo per backup PART
725 --disable_query_log
726 --disable_result_log
727 --let $rows=40000
728 while ($rows)
729 {
730 --eval insert into t1 values ($rows, $rows);
731 --dec $rows
732 }
733 --enable_query_log
734 --enable_result_log
735
736 select count(1), sum(a) from t1;
737
738 --source include/ndb_backup.inc
739
740 truncate t1;
741
742 --exec $NDB_RESTORE -b $the_backup_id -n 1 --restore-data --parallelism=1000000 $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
743 --exec $NDB_RESTORE -b $the_backup_id -n 2 --restore-data --parallelism=1000000 $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
744
745 select count(1), sum(a) from t1;
746
747 --echo Cleanup
748 drop table t1;
749
750 --echo Coverage of ndb_restore read_size setting
751 --echo Create table with one partition to increase individual
752 --echo backup filesize
753 --echo Avoid redo log overload on restore by using nologging
754 CREATE TABLE t1 (a int primary key, b longblob) engine=ndb
755 COMMENT="NDB_TABLE=NOLOGGING=1"
756 partition by key(a) partitions 1;
757
758 --echo Insert lots of rows
759 # With Bug#38608189 ndbxfrm loop guard limits file read size
760 # there was a problem with the larger read_size as ndbxfrm
761 # had a low bound on how many (max 32kB) file reads it would
762 # allow to be done to satisfy a single user read request.
763 # In cases where a large read buffer was supplied, too many
764 # (max 32kB) reads were required.
765 #
766 # With compressed backups, a (max 32kB) file read may generate
767 # quite a lot of uncompressed data so that a large buffer can
768 # be filled with a few reads and the bug is not hit.
769 #
770 # To get coverage of the bug fix even with compressed backups,
771 # here we backup data that is hard to compress, so that it
772 # is more likely that the bug fix is validated.
773 #
774 # This is done by generating unique content per row, embedding
775 # the row number, and then encrypting it.
776 #
777 --disable_query_log
778 --disable_result_log
779 set @saved_block_encryption_mode = @@block_encryption_mode;
780 set block_encryption_mode='aes-256-cbc';
781 --let $rows=2560
782 while ($rows)
783 {
784 --eval insert into t1 values ($rows, AES_ENCRYPT(repeat('$rows-$rows', 1024), 'secret', '0123456789abcdef'));
785 --dec $rows
786 }
787 --enable_result_log
788 --enable_query_log
789 set block_encryption_mode=@saved_block_encryption_mode;
790
791 select count(1) from t1;
792
793 --echo Backup
794 --source include/ndb_backup.inc
795
796 --echo Restore with large backup read filesize
797 truncate t1;
798 --exec $NDB_RESTORE -b $the_backup_id -n 1 --restore-data --read-size=32M --show-part-id $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
799 --exec $NDB_RESTORE -b $the_backup_id -n 2 --restore-data --read-size=4M --show-part-id $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
800
801 --echo Check row count
802 select count(1) from t1;
803
804 drop table t1;
805
806 --echo Test backupid ranges
807 --echo Test generation backup with max explicit id (2^32 - 2)
808 --exec $NDB_MGM -e "start backup 4294967294" >> $NDB_TOOLS_OUTPUT
809
810 --echo Check ndb_restore can open max id backup
811 --exec $NDB_RESTORE -b4294967294 -n 1 -r --print_meta $NDB_BACKUPS-4294967294 >> $NDB_TOOLS_OUTPUT
812 --exec $NDB_RESTORE -b4294967294 -n 1 -r --print_data $NDB_BACKUPS-4294967294 >> $NDB_TOOLS_OUTPUT
813 --exec $NDB_RESTORE -b4294967294 -n 2 -r --print_data $NDB_BACKUPS-4294967294 >> $NDB_TOOLS_OUTPUT
814
815 --echo Test behaviour of generating auto-numbered
816 --echo next backup of 2^32-1
817 --exec $NDB_MGM -e "start backup" >> $NDB_TOOLS_OUTPUT
818
819 --echo Check ndb_restore can open max id backup
820 --exec $NDB_RESTORE -b4294967295 -n 1 -r --print_meta $NDB_BACKUPS-4294967295 >> $NDB_TOOLS_OUTPUT
821 --exec $NDB_RESTORE -b4294967295 -n 1 -r --print_data $NDB_BACKUPS-4294967295 >> $NDB_TOOLS_OUTPUT
822 --exec $NDB_RESTORE -b4294967295 -n 2 -r --print_data $NDB_BACKUPS-4294967295 >> $NDB_TOOLS_OUTPUT
823
824 --echo Test behaviour of generating auto-numbered
825 --echo next backup of 0
826 --exec $NDB_MGM -e "start backup" >> $NDB_TOOLS_OUTPUT
827
828 --echo Check ndb_restore can open zero id backup
829 --exec $NDB_RESTORE -b0 -n 1 -r --print_meta $NDB_BACKUPS-0 >> $NDB_TOOLS_OUTPUT
830 --exec $NDB_RESTORE -b0 -n 1 -r --print_data $NDB_BACKUPS-0 >> $NDB_TOOLS_OUTPUT
831 --exec $NDB_RESTORE -b0 -n 2 -r --print_data $NDB_BACKUPS-0 >> $NDB_TOOLS_OUTPUT
832
833
834 --echo Test behaviour with start backup + out-of-range backup ids
835 --echo MGM client allows 1..4294967294 (not 0 or 4294967295)
836 --error 255
837 --exec $NDB_MGM -e "start backup 0"
838 --error 255
839 --exec $NDB_MGM -e "start backup 4294967295"
840 --error 255
841 --exec $NDB_MGM -e "start backup 4294967296"
842 --error 255
843 --exec $NDB_MGM -e "start backup 999999999999"
844
845 --echo Test behaviour of ndb_restore with various backup ids
846 --echo Too large are rejected
847 --error 2
848 --exec $NDB_RESTORE -b4294967296 -n 1 -r --print_meta $NDB_BACKUPS-0
849 --error 2
850 --exec $NDB_RESTORE -b999999999999 -n 1 -r --print_meta $NDB_BACKUPS-0
851 --echo Negative are rejected
852 --error 2
853 --exec $NDB_RESTORE -b-1 -n 1 -r --print_meta $NDB_BACKUPS-0
854 --error 2
855 --exec $NDB_RESTORE -b-2 -n 1 -r --print_meta $NDB_BACKUPS-0
856 --echo No id is rejected
857 --error 2
858 --exec $NDB_RESTORE -n 1 -r --print_meta $NDB_BACKUPS-0
859
860 --source suite/ndb/include/backup_restore_cleanup.inc
861 --remove_file $NDB_TOOLS_OUTPUT