Debian Codegraph @

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

raw
1 ########################################
2 # Author: JBM
3 # Date: 2006-01-24
4 # Purpose: Test CDD backup and restore
5 ########################################
6
7 -- source include/have_multi_ndb.inc
8 -- source suite/ndb/include/backup_restore_setup.inc
9
10 --disable_query_log
11 set new=on;
12 --enable_query_log
13
14 ############ Test 1 Simple DD backup and restore #############
15 -- echo **** Test 1 Simple DD backup and restore ****
16
17 CREATE LOGFILE GROUP log_group1
18 ADD UNDOFILE './log_group1/undofile.dat'
19 INITIAL_SIZE 16M
20 UNDO_BUFFER_SIZE = 1M
21 ENGINE=NDB;
22
23 CREATE TABLESPACE table_space1
24 ADD DATAFILE './table_space1/datafile.dat'
25 USE LOGFILE GROUP log_group1
26 INITIAL_SIZE 24M
27 ENGINE NDB;
28
29
30 CREATE TABLE test.t1
31 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 CHAR(50) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL) TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;
32
33 let $j= 500;
34 --disable_query_log
35 while ($j)
36 {
37 eval INSERT INTO test.t1 VALUES (NULL, "Sweden", $j, b'1');
38 dec $j;
39 }
40 --enable_query_log
41 SELECT COUNT(*) FROM test.t1;
42 SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
43
44 -- source include/ndb_backup.inc
45
46 DROP TABLE test.t1;
47
48 ALTER TABLESPACE table_space1
49 DROP DATAFILE './table_space1/datafile.dat';
50
51 DROP TABLESPACE table_space1;
52
53 DROP LOGFILE GROUP log_group1
54 ENGINE NDB;
55
56 -- source include/ndb_restore_master.inc
57
58 # Wait until the following changes have been synced:
59 # Logfile group 'log_group1'
60 # Tablespace 'table_space1'
61 # Table 'test.t1'
62 --let $max_wait = 60
63 --source wait_immediate_metadata_sync.inc
64
65 --connection server2
66 # Sync on server 2 as well
67 --let $max_wait = 60
68 --source wait_immediate_metadata_sync.inc
69
70 --connection server1
71
72 SELECT COUNT(*) FROM test.t1;
73
74 SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
75
76 ################# Mixed Cluster Test ############################
77 -- echo **** Test 2 Mixed Cluster Test backup and restore ****
78
79 CREATE TABLE test.t2
80 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(200) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL)ENGINE=NDB;
81
82 let $j= 500;
83 --disable_query_log
84 while ($j)
85 {
86 eval INSERT INTO test.t2 VALUES (NULL, "Sweden, Texas", $j, b'0');
87 dec $j;
88 }
89 --enable_query_log
90
91 CREATE TABLE test.t3 (c1 int not null auto_increment, data LONGBLOB, PRIMARY KEY(c1))TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;
92
93 CREATE TABLE test.t4 (c1 int not null auto_increment, data LONGBLOB, PRIMARY KEY(c1))ENGINE=NDB;
94
95 let $j= 50;
96 --disable_query_log
97 while ($j)
98 {
99 INSERT INTO test.t3 VALUES (NULL, repeat('a',1*1024));
100 INSERT INTO test.t3 VALUES (NULL, repeat('b',16*1024));
101 INSERT INTO test.t4 VALUES (NULL, repeat('a',1*1024));
102 INSERT INTO test.t4 VALUES (NULL, repeat('b',16*1024));
103 dec $j;
104 }
105 --enable_query_log
106
107 SELECT COUNT(*) FROM test.t1;
108
109 SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
110
111 SELECT COUNT(*) FROM test.t2;
112
113 SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY pk1 LIMIT 5;
114
115 SELECT COUNT(*) FROM test.t3;
116
117 SELECT LENGTH(data) FROM test.t3 WHERE c1 = 1;
118
119 SELECT LENGTH(data) FROM test.t3 WHERE c1 = 2;
120
121 SELECT COUNT(*) FROM test.t4;
122
123 SELECT LENGTH(data) FROM test.t4 WHERE c1 = 1;
124
125 SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2;
126
127 -- source include/ndb_backup.inc
128
129 DROP TABLE test.t1;
130 DROP TABLE test.t2;
131 DROP TABLE test.t3;
132 DROP TABLE test.t4;
133
134 ALTER TABLESPACE table_space1
135 DROP DATAFILE './table_space1/datafile.dat';
136
137 DROP TABLESPACE table_space1;
138
139 DROP LOGFILE GROUP log_group1
140 ENGINE NDB;
141
142 -- source include/ndb_restore_master.inc
143
144 # Wait until the following 6 object changes are synced:
145 # Logfile group 'log_group1'
146 # Tablespace 'table_space1'
147 # Table 'test.t1'
148 # Table 'test.t2'
149 # Table 'test.t3'
150 # Table 'test.t4'
151 --let $max_wait = 60
152 --source wait_immediate_metadata_sync.inc
153
154 --connection server2
155 # Sync on server 2 as well
156 --let $max_wait = 60
157 --source wait_immediate_metadata_sync.inc
158
159 --connection server1
160
161 SELECT COUNT(*) FROM test.t1;
162
163 SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
164
165 SELECT COUNT(*) FROM test.t2;
166
167 SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY pk1 LIMIT 5;
168
169 SELECT COUNT(*) FROM test.t3;
170
171 SELECT LENGTH(data) FROM test.t3 WHERE c1 = 1;
172
173 SELECT LENGTH(data) FROM test.t3 WHERE c1 = 2;
174
175 SELECT COUNT(*) FROM test.t4;
176
177 SELECT LENGTH(data) FROM test.t4 WHERE c1 = 1;
178
179 SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2;
180
181 DROP TABLE test.t1;
182 DROP TABLE test.t2;
183 DROP TABLE test.t3;
184 DROP TABLE test.t4;
185 ###################### Adding partition #################################
186 -- echo **** Test 3 Adding partition Test backup and restore ****
187
188 CREATE TABLESPACE table_space2
189 ADD DATAFILE './table_space2/datafile.dat'
190 USE LOGFILE GROUP log_group1
191 INITIAL_SIZE 12M
192 ENGINE NDB;
193
194 CREATE TABLE test.t1 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 VARCHAR(150) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))TABLESPACE table_space1 STORAGE DISK ENGINE=NDB PARTITION BY HASH(c3) PARTITIONS 4;
195
196 CREATE TABLE test.t4 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 VARCHAR(180) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))ENGINE=NDB PARTITION BY HASH(c3) PARTITIONS 2;
197
198 CREATE TABLE test.t2 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 TEXT NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))TABLESPACE table_space2 STORAGE DISK ENGINE=NDB PARTITION BY KEY(c3) (PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
199
200 CREATE TABLE test.t5 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 TEXT NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))ENGINE=NDB PARTITION BY KEY(pk1) (PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
201
202 CREATE TABLE test.t3 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 VARCHAR(202) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))TABLESPACE table_space2 STORAGE DISK ENGINE=NDB PARTITION BY RANGE (c3) PARTITIONS 3 (PARTITION x1 VALUES LESS THAN (105), PARTITION x2 VALUES LESS THAN (333), PARTITION x3 VALUES LESS THAN (720));
203
204 CREATE TABLE test.t6 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 VARCHAR(220) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))ENGINE=NDB PARTITION BY RANGE (pk1) PARTITIONS 2 (PARTITION x1 VALUES LESS THAN (333), PARTITION x2 VALUES LESS THAN (720));
205
206 SHOW CREATE TABLE test.t1;
207
208 SHOW CREATE TABLE test.t2;
209
210 SHOW CREATE TABLE test.t3;
211
212 SHOW CREATE TABLE test.t4;
213
214 SHOW CREATE TABLE test.t5;
215
216 SHOW CREATE TABLE test.t6;
217
218 --sorted_result
219 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't1';
220
221 --sorted_result
222 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't2';
223
224 --sorted_result
225 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't3';
226
227 --sorted_result
228 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't4';
229
230 --sorted_result
231 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't5';
232
233 --sorted_result
234 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't6';
235
236
237 let $j= 500;
238 --disable_query_log
239 while ($j)
240 {
241 eval INSERT INTO test.t1 VALUES (NULL, "Sweden, Texas", $j, b'0');
242 eval INSERT INTO test.t4 VALUES (NULL, "Sweden, Texas", $j, b'0');
243 dec $j;
244 eval INSERT INTO test.t2 VALUES (NULL, "Sweden, Texas, ITALY, Kyle, JO, JBM,TU", $j, b'1');
245 eval INSERT INTO test.t5 VALUES (NULL, "Sweden, Texas, ITALY, Kyle, JO, JBM,TU", $j, b'1');
246 dec $j;
247 eval INSERT INTO test.t3 VALUES (NULL, "TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU", $j, b'1');
248 eval INSERT INTO test.t6 VALUES (NULL, "TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU", $j, b'1'); } --enable_query_log
249
250 SELECT COUNT(*) FROM test.t1;
251
252 SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY c3 LIMIT 5;
253
254 SELECT COUNT(*) FROM test.t2;
255
256 SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY c3 LIMIT 5;
257
258 SELECT COUNT(*) FROM test.t3;
259
260 SELECT pk1, c2, c3, hex(c4) FROM test.t3 ORDER BY c3 LIMIT 5;
261
262 SELECT COUNT(*) FROM test.t4;
263
264 SELECT pk1, c2, c3, hex(c4) FROM test.t4 ORDER BY c3 LIMIT 5;
265
266 SELECT COUNT(*) FROM test.t5;
267
268 SELECT pk1, c2, c3, hex(c4) FROM test.t5 ORDER BY c3 LIMIT 5;
269
270 SELECT COUNT(*) FROM test.t6;
271
272 SELECT pk1, c2, c3, hex(c4) FROM test.t6 ORDER BY c3 LIMIT 5;
273
274 -- source include/ndb_backup.inc
275
276 DROP TABLE test.t1;
277 DROP TABLE test.t2;
278 DROP TABLE test.t3;
279 DROP TABLE test.t4;
280 DROP TABLE test.t5;
281 DROP TABLE test.t6;
282
283 ALTER TABLESPACE table_space1
284 DROP DATAFILE './table_space1/datafile.dat';
285
286 ALTER TABLESPACE table_space2
287 DROP DATAFILE './table_space2/datafile.dat';
288
289 DROP TABLESPACE table_space1;
290
291 DROP TABLESPACE table_space2;
292
293 DROP LOGFILE GROUP log_group1
294 ENGINE NDB;
295
296 -- source include/ndb_restore_master.inc
297
298 # Wait until the following 9 objects are synced:
299 # Logfile group 'log_group1'
300 # Tablespace 'table_space1'
301 # Tablespace 'table_space2'
302 # Table 'test.t1'
303 # Table 'test.t2'
304 # Table 'test.t3'
305 # Table 'test.t4'
306 # Table 'test.t5'
307 # Table 'test.t6'
308 --let $max_wait = 90
309 --source wait_immediate_metadata_sync.inc
310
311 --connection server2
312 # Sync on server 2 as well
313 --let $max_wait = 90
314 --source wait_immediate_metadata_sync.inc
315
316 --connection server1
317
318 SHOW CREATE TABLE test.t1;
319
320 SHOW CREATE TABLE test.t2;
321
322 SHOW CREATE TABLE test.t3;
323
324 SHOW CREATE TABLE test.t4;
325
326 SHOW CREATE TABLE test.t5;
327
328 SHOW CREATE TABLE test.t6;
329
330 --sorted_result
331 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't1';
332
333 --sorted_result
334 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't2';
335
336 --sorted_result
337 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't3';
338
339 --sorted_result
340 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't4';
341
342 --sorted_result
343 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't5';
344
345 --sorted_result
346 SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't6';
347
348 SELECT COUNT(*) FROM test.t1;
349
350 SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY c3 LIMIT 5;
351
352 SELECT COUNT(*) FROM test.t2;
353
354 SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY c3 LIMIT 5;
355
356 SELECT COUNT(*) FROM test.t3;
357
358 SELECT pk1, c2, c3, hex(c4) FROM test.t3 ORDER BY c3 LIMIT 5;
359
360 SELECT COUNT(*) FROM test.t4;
361
362 SELECT pk1, c2, c3, hex(c4) FROM test.t4 ORDER BY c3 LIMIT 5;
363
364 SELECT COUNT(*) FROM test.t5;
365
366 SELECT pk1, c2, c3, hex(c4) FROM test.t5 ORDER BY c3 LIMIT 5;
367
368 SELECT COUNT(*) FROM test.t6;
369
370 SELECT pk1, c2, c3, hex(c4) FROM test.t6 ORDER BY c3 LIMIT 5;
371
372 # Cleanup
373
374 DROP TABLE test.t1;
375 DROP TABLE test.t2;
376 DROP TABLE test.t3;
377 DROP TABLE test.t4;
378 DROP TABLE test.t5;
379 DROP TABLE test.t6;
380
381 ALTER TABLESPACE table_space1 DROP DATAFILE './table_space1/datafile.dat';
382
383 ALTER TABLESPACE table_space2 DROP DATAFILE './table_space2/datafile.dat';
384
385 DROP TABLESPACE table_space1;
386
387 DROP TABLESPACE table_space2;
388
389 DROP LOGFILE GROUP log_group1 ENGINE = NDB;
390 --source suite/ndb/include/backup_restore_cleanup.inc
391
392 #End 5.1 test case
393
394
395 --remove_file $NDB_TOOLS_OUTPUT