1
########################################
4
# Purpose: Test CDD backup and restore
5
########################################
7
-- source include/have_ndb.inc
8
# range, list and hash partitioning in ndb requires new_mode
14
DROP TABLE IF EXISTS test.t1;
15
DROP TABLE IF EXISTS test.t2;
16
DROP TABLE IF EXISTS test.t3;
17
DROP TABLE IF EXISTS test.t4;
18
DROP TABLE IF EXISTS test.t5;
19
DROP TABLE IF EXISTS test.t6;
22
############ Test 1 Simple DD backup and restore #############
23
-- echo **** Test 1 Simple DD backup and restore ****
25
CREATE LOGFILE GROUP log_group1
26
ADD UNDOFILE './log_group1/undofile.dat'
31
CREATE TABLESPACE table_space1
32
ADD DATAFILE './table_space1/datafile.dat'
33
USE LOGFILE GROUP log_group1
39
(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;
45
eval INSERT INTO test.t1 VALUES (NULL, "Sweden", $j, b'1');
49
SELECT COUNT(*) FROM test.t1;
50
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
52
-- source include/ndb_backup.inc
56
ALTER TABLESPACE table_space1
57
DROP DATAFILE './table_space1/datafile.dat'
60
DROP TABLESPACE table_space1
63
DROP LOGFILE GROUP log_group1
66
-- source include/ndb_restore_master.inc
68
SELECT COUNT(*) FROM test.t1;
70
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
72
################# Mixed Cluster Test ############################
73
-- echo **** Test 2 Mixed Cluster Test backup and restore ****
76
(pk1 MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(200) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL)ENGINE=NDB;
82
eval INSERT INTO test.t2 VALUES (NULL, "Sweden, Texas", $j, b'0');
87
CREATE TABLE test.t3 (c1 int not null auto_increment, data LONGBLOB, PRIMARY KEY(c1))TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;
89
CREATE TABLE test.t4 (c1 int not null auto_increment, data LONGBLOB, PRIMARY KEY(c1))ENGINE=NDB;
95
INSERT INTO test.t3 VALUES (NULL, repeat('a',1*1024));
96
INSERT INTO test.t3 VALUES (NULL, repeat('b',16*1024));
97
INSERT INTO test.t4 VALUES (NULL, repeat('a',1*1024));
98
INSERT INTO test.t4 VALUES (NULL, repeat('b',16*1024));
103
SELECT COUNT(*) FROM test.t1;
105
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
107
SELECT COUNT(*) FROM test.t2;
109
SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY pk1 LIMIT 5;
111
SELECT COUNT(*) FROM test.t3;
113
SELECT LENGTH(data) FROM test.t3 WHERE c1 = 1;
115
SELECT LENGTH(data) FROM test.t3 WHERE c1 = 2;
117
SELECT COUNT(*) FROM test.t4;
119
SELECT LENGTH(data) FROM test.t4 WHERE c1 = 1;
121
SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2;
123
-- source include/ndb_backup.inc
130
ALTER TABLESPACE table_space1
131
DROP DATAFILE './table_space1/datafile.dat'
134
DROP TABLESPACE table_space1
137
DROP LOGFILE GROUP log_group1
140
-- source include/ndb_restore_master.inc
142
SELECT COUNT(*) FROM test.t1;
144
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
146
SELECT COUNT(*) FROM test.t2;
148
SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY pk1 LIMIT 5;
150
SELECT COUNT(*) FROM test.t3;
152
SELECT LENGTH(data) FROM test.t3 WHERE c1 = 1;
154
SELECT LENGTH(data) FROM test.t3 WHERE c1 = 2;
156
SELECT COUNT(*) FROM test.t4;
158
SELECT LENGTH(data) FROM test.t4 WHERE c1 = 1;
160
SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2;
166
###################### Adding partition #################################
167
-- echo **** Test 3 Adding partition Test backup and restore ****
169
CREATE TABLESPACE table_space2
170
ADD DATAFILE './table_space2/datafile.dat'
171
USE LOGFILE GROUP log_group1
175
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;
177
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;
179
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);
181
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);
183
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));
185
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));
187
SHOW CREATE TABLE test.t1;
189
SHOW CREATE TABLE test.t2;
191
SHOW CREATE TABLE test.t3;
193
SHOW CREATE TABLE test.t4;
195
SHOW CREATE TABLE test.t5;
197
SHOW CREATE TABLE test.t6;
199
SELECT * FROM information_schema.partitions WHERE table_name= 't1';
201
SELECT * FROM information_schema.partitions WHERE table_name= 't2';
203
SELECT * FROM information_schema.partitions WHERE table_name= 't3';
205
SELECT * FROM information_schema.partitions WHERE table_name= 't4';
207
SELECT * FROM information_schema.partitions WHERE table_name= 't5';
209
SELECT * FROM information_schema.partitions WHERE table_name= 't6';
216
eval INSERT INTO test.t1 VALUES (NULL, "Sweden, Texas", $j, b'0');
217
eval INSERT INTO test.t4 VALUES (NULL, "Sweden, Texas", $j, b'0');
219
eval INSERT INTO test.t2 VALUES (NULL, "Sweden, Texas, ITALY, Kyle, JO, JBM,TU", $j, b'1');
220
eval INSERT INTO test.t5 VALUES (NULL, "Sweden, Texas, ITALY, Kyle, JO, JBM,TU", $j, b'1');
222
eval INSERT INTO test.t3 VALUES (NULL, "TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU", $j, b'1');
223
eval INSERT INTO test.t6 VALUES (NULL, "TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU", $j, b'1'); } --enable_query_log
225
SELECT COUNT(*) FROM test.t1;
227
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY c3 LIMIT 5;
229
SELECT COUNT(*) FROM test.t2;
231
SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY c3 LIMIT 5;
233
SELECT COUNT(*) FROM test.t3;
235
SELECT pk1, c2, c3, hex(c4) FROM test.t3 ORDER BY c3 LIMIT 5;
237
SELECT COUNT(*) FROM test.t4;
239
SELECT pk1, c2, c3, hex(c4) FROM test.t4 ORDER BY c3 LIMIT 5;
241
SELECT COUNT(*) FROM test.t5;
243
SELECT pk1, c2, c3, hex(c4) FROM test.t5 ORDER BY c3 LIMIT 5;
245
SELECT COUNT(*) FROM test.t6;
247
SELECT pk1, c2, c3, hex(c4) FROM test.t6 ORDER BY c3 LIMIT 5;
249
-- source include/ndb_backup.inc
258
ALTER TABLESPACE table_space1
259
DROP DATAFILE './table_space1/datafile.dat'
262
ALTER TABLESPACE table_space2
263
DROP DATAFILE './table_space2/datafile.dat'
266
DROP TABLESPACE table_space1
269
DROP TABLESPACE table_space2
272
DROP LOGFILE GROUP log_group1
275
-- source include/ndb_restore_master.inc
278
SHOW CREATE TABLE test.t1;
280
SHOW CREATE TABLE test.t2;
282
SHOW CREATE TABLE test.t3;
284
SHOW CREATE TABLE test.t4;
286
SHOW CREATE TABLE test.t5;
288
SHOW CREATE TABLE test.t6;
290
SELECT * FROM information_schema.partitions WHERE table_name= 't1';
292
SELECT * FROM information_schema.partitions WHERE table_name= 't2';
294
SELECT * FROM information_schema.partitions WHERE table_name= 't3';
296
SELECT * FROM information_schema.partitions WHERE table_name= 't4';
298
SELECT * FROM information_schema.partitions WHERE table_name= 't5';
300
SELECT * FROM information_schema.partitions WHERE table_name= 't6';
302
SELECT COUNT(*) FROM test.t1;
304
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY c3 LIMIT 5;
306
SELECT COUNT(*) FROM test.t2;
308
SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY c3 LIMIT 5;
310
SELECT COUNT(*) FROM test.t3;
312
SELECT pk1, c2, c3, hex(c4) FROM test.t3 ORDER BY c3 LIMIT 5;
314
SELECT COUNT(*) FROM test.t4;
316
SELECT pk1, c2, c3, hex(c4) FROM test.t4 ORDER BY c3 LIMIT 5;
318
SELECT COUNT(*) FROM test.t5;
320
SELECT pk1, c2, c3, hex(c4) FROM test.t5 ORDER BY c3 LIMIT 5;
322
SELECT COUNT(*) FROM test.t6;
324
SELECT pk1, c2, c3, hex(c4) FROM test.t6 ORDER BY c3 LIMIT 5;
335
ALTER TABLESPACE table_space1 DROP DATAFILE './table_space1/datafile.dat' ENGINE=NDB;
337
ALTER TABLESPACE table_space2 DROP DATAFILE './table_space2/datafile.dat' ENGINE=NDB;
339
DROP TABLESPACE table_space1 ENGINE = NDB;
341
DROP TABLESPACE table_space2 ENGINE = NDB;
343
DROP LOGFILE GROUP log_group1 ENGINE = NDB;