Debian Codegraph @

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

raw
1 ######################################################################
2 # Test restoring backups into partitioned tables
3 ######################################################################
4
5 -- source include/have_ndb.inc
6 -- source suite/ndb/include/backup_restore_setup.inc
7
8 # Turn on extended partitioning support
9 set new=on;
10
11 CREATE TABLE `t1_c` (
12 `capgoaledatta` smallint(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'),(4,'4','q3plus.qt'),(1,'3','q3.net'),(3,'4','q3.net'),(3,'20','threetrees.qt');
18
19 CREATE TABLE `t2_c` (
20 `capgotod` smallint(5) unsigned NOT NULL auto_increment,
21 `gotod` smallint(5) unsigned NOT NULL default '0',
22 `goaledatta` char(2) default NULL,
23 `maturegarbagefa` varchar(32) default NULL,
24 `descrpooppo` varchar(64) default NULL,
25 `svcutonsa` varchar(64) NOT NULL default '',
26 PRIMARY KEY (`capgotod`),
27 KEY `i_quadaddsvr` (`gotod`)
28 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
29 INSERT INTO `t2_c` VALUES (5,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');
30
31 CREATE TABLE `t3_c` (
32 `CapGoaledatta` smallint(5) unsigned NOT NULL default '0',
33 `capgotod` smallint(5) unsigned NOT NULL default '0',
34 PRIMARY KEY (`capgotod`,`CapGoaledatta`)
35 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
36 INSERT INTO `t3_c` VALUES (5,3),(2,4),(5,4),(1,3);
37
38 CREATE TABLE `t4_c` (
39 `capfa` bigint(20) unsigned NOT NULL auto_increment,
40 `realm` varchar(32) NOT NULL default '',
41 `authpwchap` varchar(32) default NULL,
42 `fa` varchar(32) NOT NULL default '',
43 `payyingatta` tinyint(4) NOT NULL default '0',
44 `status` char(1) default NULL,
45 PRIMARY KEY (`fa`,`realm`),
46 KEY `capfa` (`capfa`),
47 KEY `i_quadentity` (`fa`,`realm`)
48 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
49 INSERT INTO `t4_c` VALUES (18,'john.smith','q3.net','dessjohn.smith',0,NULL),(21,'quad_katt_with_brandtad','q3.net','acne',0,NULL),(22,'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),(32,'463641969502','sqasdt.q3.net','N/A',1,'6'),(20,'joe','q3.net','joedesswd',0,NULL),(29,'436642900034','sqasdt.q3.net','N/A',0,'6'),(25,'contind','armerde.qt','acne',1,NULL);
50
51 CREATE TABLE `t5_c` (
52 `capfa` bigint(20) unsigned NOT NULL default '0',
53 `gotod` smallint(5) unsigned NOT NULL default '0',
54 `orderutonsa` varchar(64) NOT NULL default '',
55 PRIMARY KEY (`capfa`,`gotod`,`orderutonsa`)
56 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
57 INSERT INTO `t5_c` VALUES (21,2,''),(21,1,''),(22,4,'');
58
59 CREATE TABLE `t6_c` (
60 `capfa_parent` bigint(20) unsigned NOT NULL default '0',
61 `capfa_child` bigint(20) unsigned NOT NULL default '0',
62 `relatta` smallint(5) unsigned NOT NULL default '0',
63 PRIMARY KEY (`capfa_child`,`capfa_parent`,`relatta`)
64 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
65 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);
66
67 CREATE TABLE `t7_c` (
68 `dardpo` char(15) NOT NULL default '',
69 `dardtestard` tinyint(3) unsigned NOT NULL default '0',
70 `FastFA` char(5) NOT NULL default '',
71 `FastCode` char(6) NOT NULL default '',
72 `Fastca` char(1) NOT NULL default '',
73 `Fastmag` char(1) NOT NULL default '',
74 `Beareratta` char(2) NOT NULL default '',
75 PRIMARY KEY (`dardpo`,`dardtestard`)
76 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
77 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');
78
79 CREATE TABLE `t8_c` (
80 `kattjame` varchar(32) NOT NULL default '',
81 `realm` varchar(32) NOT NULL default '',
82 `realm_entered` varchar(32) NOT NULL default '',
83 `maturegarbagefa` varchar(32) NOT NULL default '',
84 `hunderaaarbagefa_parent` varchar(32) NOT NULL default '',
85 `kattjame_entered` varchar(32) NOT NULL default '',
86 `hunderaaarbagefa` varchar(32) NOT NULL default '',
87 `gest` varchar(16) default NULL,
88 `hassetino` varchar(16) NOT NULL default '',
89 `aaaproxysessfa` varchar(255) default NULL,
90 `autologonallowed` char(1) default NULL,
91 `squardporoot` varchar(15) NOT NULL default '',
92 `naspo` varchar(15) default NULL,
93 `beareratta` char(2) default NULL,
94 `fastCode` varchar(6) default NULL,
95 `fastFA` varchar(5) default NULL,
96 `fastca` char(1) default NULL,
97 `fastmag` char(1) default NULL,
98 `lastupdate` datetime default NULL,
99 `hassetistart` datetime NOT NULL,
100 `accthassetitime` int(10) unsigned default NULL,
101 `acctoutputoctets` bigint(20) unsigned default NULL,
102 `acctinputoctets` bigint(20) unsigned default NULL,
103 PRIMARY KEY (`kattjame`,`hunderaaarbagefa`,`hassetistart`,`hassetino`),
104 KEY `squardporoot` (`squardporoot`)
105 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
106 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);
107
108 CREATE TABLE `t9_c` (
109 `kattjame` varchar(32) NOT NULL default '',
110 `kattjame_entered` varchar(32) NOT NULL default '',
111 `realm` varchar(32) NOT NULL default '',
112 `realm_entered` varchar(32) NOT NULL default '',
113 `maturegarbagefa` varchar(32) NOT NULL default '',
114 `hunderaaarbagefa` varchar(32) NOT NULL default '',
115 `hunderaaarbagefa_parent` varchar(32) NOT NULL default '',
116 `gest` varchar(16) default NULL,
117 `hassetino` varchar(16) NOT NULL default '',
118 `squardporoot` varchar(15) NOT NULL default '',
119 `naspo` varchar(15) default NULL,
120 `beareratta` char(2) default NULL,
121 `fastCode` varchar(6) default NULL,
122 `fastFA` varchar(5) default NULL,
123 `fastca` char(1) default NULL,
124 `fastmag` char(1) default NULL,
125 `lastupdate` datetime default NULL,
126 `hassetistart` datetime NOT NULL,
127 `accthassetitime` int(10) unsigned default NULL,
128 `actcoutpuocttets` bigint(20) unsigned default NULL,
129 `actinputocctets` bigint(20) unsigned default NULL,
130 `terminateraste` tinyint(3) unsigned default NULL,
131 PRIMARY KEY (`kattjame`,`hunderaaarbagefa`,`hassetistart`,`hassetino`)
132 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
133 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);
134
135 create table t1 engine=myisam as select * from t1_c;
136 create table t2 engine=myisam as select * from t2_c;
137 create table t3 engine=myisam as select * from t3_c;
138 create table t4 engine=myisam as select * from t4_c;
139 create table t5 engine=myisam as select * from t5_c;
140 create table t6 engine=myisam as select * from t6_c;
141 create table t7 engine=myisam as select * from t7_c;
142 create table t8 engine=myisam as select * from t8_c;
143 create table t9 engine=myisam as select * from t9_c;
144
145
146 --source include/ndb_backup.inc
147 drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c;
148 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
149 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
150
151 select count(*) from t1;
152 select count(*) from t1_c;
153 select count(*)
154 from (select * from t1 union
155 select * from t1_c) a;
156
157 select count(*) from t2;
158 select count(*) from t2_c;
159 select count(*)
160 from (select * from t2 union
161 select * from t2_c) a;
162
163 select count(*) from t3;
164 select count(*) from t3_c;
165 select count(*)
166 from (select * from t3 union
167 select * from t3_c) a;
168
169 select count(*) from t4;
170 select count(*) from t4_c;
171 select count(*)
172 from (select * from t4 union
173 select * from t4_c) a;
174
175 select count(*) from t5;
176 select count(*) from t5_c;
177 select count(*)
178 from (select * from t5 union
179 select * from t5_c) a;
180
181 select count(*) from t6;
182 select count(*) from t6_c;
183 select count(*)
184 from (select * from t6 union
185 select * from t6_c) a;
186
187 select count(*) from t7;
188 select count(*) from t7_c;
189 select count(*)
190 from (select * from t7 union
191 select * from t7_c) a;
192
193 select count(*) from t8;
194 select count(*) from t8_c;
195 select count(*)
196 from (select * from t8 union
197 select * from t8_c) a;
198
199 select count(*) from t9;
200 select count(*) from t9_c;
201 select count(*)
202 from (select * from t9 union
203 select * from t9_c) a;
204
205 #
206 # Try Partitioned tables as well
207 #
208 ALTER TABLE t1_c
209 PARTITION BY RANGE (`capgoaledatta`)
210 (PARTITION p0 VALUES LESS THAN MAXVALUE);
211
212 ALTER TABLE t2_c
213 PARTITION BY LIST(`capgotod`)
214 (PARTITION p0 VALUES IN (0,1,2,3,4,5,6));
215
216 ALTER TABLE t3_c
217 PARTITION BY HASH (`CapGoaledatta`);
218
219 ALTER TABLE t5_c
220 PARTITION BY HASH (`capfa`)
221 PARTITIONS 4;
222
223 ALTER TABLE t6_c
224 PARTITION BY LINEAR HASH (`relatta`)
225 PARTITIONS 4;
226
227 ALTER TABLE t7_c
228 PARTITION BY LINEAR KEY (`dardtestard`);
229
230 --source include/ndb_backup.inc
231 drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c;
232 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
233 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
234
235 select count(*) from t1;
236 select count(*) from t1_c;
237 select count(*)
238 from (select * from t1 union
239 select * from t1_c) a;
240
241 select count(*) from t2;
242 select count(*) from t2_c;
243 select count(*)
244 from (select * from t2 union
245 select * from t2_c) a;
246
247 select count(*) from t3;
248 select count(*) from t3_c;
249 select count(*)
250 from (select * from t3 union
251 select * from t3_c) a;
252
253 select count(*) from t4;
254 select count(*) from t4_c;
255 select count(*)
256 from (select * from t4 union
257 select * from t4_c) a;
258
259 select count(*) from t5;
260 select count(*) from t5_c;
261 select count(*)
262 from (select * from t5 union
263 select * from t5_c) a;
264
265 select count(*) from t6;
266 select count(*) from t6_c;
267 select count(*)
268 from (select * from t6 union
269 select * from t6_c) a;
270
271 select count(*) from t7;
272 select count(*) from t7_c;
273 select count(*)
274 from (select * from t7 union
275 select * from t7_c) a;
276
277 select count(*) from t8;
278 select count(*) from t8_c;
279 select count(*)
280 from (select * from t8 union
281 select * from t8_c) a;
282
283 select count(*) from t9;
284 select count(*) from t9_c;
285 select count(*)
286 from (select * from t9 union
287 select * from t9_c) a;
288
289 drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c;
290 --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
291 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
292
293 select count(*) from t1;
294 select count(*) from t1_c;
295 select count(*)
296 from (select * from t1 union
297 select * from t1_c) a;
298
299 select count(*) from t2;
300 select count(*) from t2_c;
301 select count(*)
302 from (select * from t2 union
303 select * from t2_c) a;
304
305 select count(*) from t3;
306 select count(*) from t3_c;
307 select count(*)
308 from (select * from t3 union
309 select * from t3_c) a;
310
311 select count(*) from t4;
312 select count(*) from t4_c;
313 select count(*)
314 from (select * from t4 union
315 select * from t4_c) a;
316
317 select count(*) from t5;
318 select count(*) from t5_c;
319 select count(*)
320 from (select * from t5 union
321 select * from t5_c) a;
322
323 select count(*) from t6;
324 select count(*) from t6_c;
325 select count(*)
326 from (select * from t6 union
327 select * from t6_c) a;
328
329 select count(*) from t7;
330 select count(*) from t7_c;
331 select count(*)
332 from (select * from t7 union
333 select * from t7_c) a;
334
335 select count(*) from t8;
336 select count(*) from t8_c;
337 select count(*)
338 from (select * from t8 union
339 select * from t8_c) a;
340
341 select count(*) from t9;
342 select count(*) from t9_c;
343 select count(*)
344 from (select * from t9 union
345 select * from t9_c) a;
346
347 #
348 # Drop all tables except t2_c, then check that restore fails
349 # - drop all except one to make sure that error returned is for t2_c,
350 # order of tables in backup is not deterministic
351 #
352 drop table t1_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c;
353 --source include/ndb_backup.inc
354
355 # Masking timestamp from the restore output with 'X' to avoid tests
356 # running on different dates give 'Result content mismatch'.
357 # Sprinkle with log level, not relevant for this tests but shows that
358 # --show-log-level works.
359 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/XXXX-XX-XX XX:XX:XX/
360 --error 1
361 --exec $NDB_RESTORE --show-log-level -b $the_backup_id -n 1 -m -r --ndb-nodegroup_map "(0,1)" $NDB_BACKUPS-$the_backup_id 2>&1 >> $NDB_TOOLS_OUTPUT
362
363 drop table t2_c;
364 drop table t1,t2,t3,t4,t5,t6,t7,t8,t9;
365
366 #
367 # Test BUG#10287
368 #
369
370 source include/ndb_backup_id.inc;
371 echo 520093696,<the_backup_id>;
372
373 #
374 # Cleanup
375 #
376 --source suite/ndb/include/backup_restore_cleanup.inc
377 --remove_file $NDB_TOOLS_OUTPUT