501 lines
20 KiB
Plaintext
501 lines
20 KiB
Plaintext
**** Test 1 Simple DD backup and restore ****
|
|
CREATE LOGFILE GROUP log_group1
|
|
ADD UNDOFILE './log_group1/undofile.dat'
|
|
INITIAL_SIZE 16M
|
|
UNDO_BUFFER_SIZE = 1M
|
|
ENGINE=NDB;
|
|
CREATE TABLESPACE table_space1
|
|
ADD DATAFILE './table_space1/datafile.dat'
|
|
USE LOGFILE GROUP log_group1
|
|
INITIAL_SIZE 24M
|
|
ENGINE NDB;
|
|
CREATE TABLE test.t1
|
|
(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;
|
|
SELECT COUNT(*) FROM test.t1;
|
|
COUNT(*)
|
|
500
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
1 Sweden 500 1
|
|
2 Sweden 499 1
|
|
3 Sweden 498 1
|
|
4 Sweden 497 1
|
|
5 Sweden 496 1
|
|
DROP TABLE test.t1;
|
|
ALTER TABLESPACE table_space1
|
|
DROP DATAFILE './table_space1/datafile.dat';
|
|
DROP TABLESPACE table_space1;
|
|
DROP LOGFILE GROUP log_group1
|
|
ENGINE NDB;
|
|
SET @old_ndb_metadata_check_interval = @@global.ndb_metadata_check_interval;
|
|
SET GLOBAL ndb_metadata_check_interval = 0;
|
|
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;
|
|
SET @old_ndb_metadata_check_interval = @@global.ndb_metadata_check_interval;
|
|
SET GLOBAL ndb_metadata_check_interval = 0;
|
|
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;
|
|
SELECT COUNT(*) FROM test.t1;
|
|
COUNT(*)
|
|
500
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
1 Sweden 500 1
|
|
2 Sweden 499 1
|
|
3 Sweden 498 1
|
|
4 Sweden 497 1
|
|
5 Sweden 496 1
|
|
**** Test 2 Mixed Cluster Test backup and restore ****
|
|
CREATE TABLE test.t2
|
|
(pk1 MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(200) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL)ENGINE=NDB;
|
|
CREATE TABLE test.t3 (c1 int not null auto_increment, data LONGBLOB, PRIMARY KEY(c1))TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;
|
|
CREATE TABLE test.t4 (c1 int not null auto_increment, data LONGBLOB, PRIMARY KEY(c1))ENGINE=NDB;
|
|
SELECT COUNT(*) FROM test.t1;
|
|
COUNT(*)
|
|
500
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
1 Sweden 500 1
|
|
2 Sweden 499 1
|
|
3 Sweden 498 1
|
|
4 Sweden 497 1
|
|
5 Sweden 496 1
|
|
SELECT COUNT(*) FROM test.t2;
|
|
COUNT(*)
|
|
500
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY pk1 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
1 Sweden, Texas 500 0
|
|
2 Sweden, Texas 499 0
|
|
3 Sweden, Texas 498 0
|
|
4 Sweden, Texas 497 0
|
|
5 Sweden, Texas 496 0
|
|
SELECT COUNT(*) FROM test.t3;
|
|
COUNT(*)
|
|
100
|
|
SELECT LENGTH(data) FROM test.t3 WHERE c1 = 1;
|
|
LENGTH(data)
|
|
1024
|
|
SELECT LENGTH(data) FROM test.t3 WHERE c1 = 2;
|
|
LENGTH(data)
|
|
16384
|
|
SELECT COUNT(*) FROM test.t4;
|
|
COUNT(*)
|
|
100
|
|
SELECT LENGTH(data) FROM test.t4 WHERE c1 = 1;
|
|
LENGTH(data)
|
|
1024
|
|
SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2;
|
|
LENGTH(data)
|
|
16384
|
|
DROP TABLE test.t1;
|
|
DROP TABLE test.t2;
|
|
DROP TABLE test.t3;
|
|
DROP TABLE test.t4;
|
|
ALTER TABLESPACE table_space1
|
|
DROP DATAFILE './table_space1/datafile.dat';
|
|
DROP TABLESPACE table_space1;
|
|
DROP LOGFILE GROUP log_group1
|
|
ENGINE NDB;
|
|
SET GLOBAL ndb_metadata_check_interval = 0;
|
|
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;
|
|
SET GLOBAL ndb_metadata_check_interval = 0;
|
|
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;
|
|
SELECT COUNT(*) FROM test.t1;
|
|
COUNT(*)
|
|
500
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
1 Sweden 500 1
|
|
2 Sweden 499 1
|
|
3 Sweden 498 1
|
|
4 Sweden 497 1
|
|
5 Sweden 496 1
|
|
SELECT COUNT(*) FROM test.t2;
|
|
COUNT(*)
|
|
500
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY pk1 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
1 Sweden, Texas 500 0
|
|
2 Sweden, Texas 499 0
|
|
3 Sweden, Texas 498 0
|
|
4 Sweden, Texas 497 0
|
|
5 Sweden, Texas 496 0
|
|
SELECT COUNT(*) FROM test.t3;
|
|
COUNT(*)
|
|
100
|
|
SELECT LENGTH(data) FROM test.t3 WHERE c1 = 1;
|
|
LENGTH(data)
|
|
1024
|
|
SELECT LENGTH(data) FROM test.t3 WHERE c1 = 2;
|
|
LENGTH(data)
|
|
16384
|
|
SELECT COUNT(*) FROM test.t4;
|
|
COUNT(*)
|
|
100
|
|
SELECT LENGTH(data) FROM test.t4 WHERE c1 = 1;
|
|
LENGTH(data)
|
|
1024
|
|
SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2;
|
|
LENGTH(data)
|
|
16384
|
|
DROP TABLE test.t1;
|
|
DROP TABLE test.t2;
|
|
DROP TABLE test.t3;
|
|
DROP TABLE test.t4;
|
|
**** Test 3 Adding partition Test backup and restore ****
|
|
CREATE TABLESPACE table_space2
|
|
ADD DATAFILE './table_space2/datafile.dat'
|
|
USE LOGFILE GROUP log_group1
|
|
INITIAL_SIZE 12M
|
|
ENGINE NDB;
|
|
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;
|
|
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;
|
|
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);
|
|
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);
|
|
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));
|
|
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));
|
|
SHOW CREATE TABLE test.t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` varchar(150) NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) /*!50100 TABLESPACE `table_space1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`c3`)
|
|
PARTITIONS 4 */
|
|
SHOW CREATE TABLE test.t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` text NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) /*!50100 TABLESPACE `table_space2` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (c3)
|
|
(PARTITION p0 ENGINE = ndbcluster,
|
|
PARTITION p1 ENGINE = ndbcluster) */
|
|
SHOW CREATE TABLE test.t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` varchar(202) NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) /*!50100 TABLESPACE `table_space2` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`c3`)
|
|
(PARTITION x1 VALUES LESS THAN (105) ENGINE = ndbcluster,
|
|
PARTITION x2 VALUES LESS THAN (333) ENGINE = ndbcluster,
|
|
PARTITION x3 VALUES LESS THAN (720) ENGINE = ndbcluster) */
|
|
SHOW CREATE TABLE test.t4;
|
|
Table Create Table
|
|
t4 CREATE TABLE `t4` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` varchar(180) NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`c3`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE test.t5;
|
|
Table Create Table
|
|
t5 CREATE TABLE `t5` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` text NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (pk1)
|
|
(PARTITION p0 ENGINE = ndbcluster,
|
|
PARTITION p1 ENGINE = ndbcluster) */
|
|
SHOW CREATE TABLE test.t6;
|
|
Table Create Table
|
|
t6 CREATE TABLE `t6` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` varchar(220) NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`pk1`)
|
|
(PARTITION x1 VALUES LESS THAN (333) ENGINE = ndbcluster,
|
|
PARTITION x2 VALUES LESS THAN (720) ENGINE = ndbcluster) */
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't1';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION
|
|
t1 p0 1 HASH `c3`
|
|
t1 p1 2 HASH `c3`
|
|
t1 p2 3 HASH `c3`
|
|
t1 p3 4 HASH `c3`
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't2';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION
|
|
t2 p0 1 KEY `c3`
|
|
t2 p1 2 KEY `c3`
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't3';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION PARTITION_DESCRIPTION
|
|
t3 x1 1 RANGE `c3` 105
|
|
t3 x2 2 RANGE `c3` 333
|
|
t3 x3 3 RANGE `c3` 720
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't4';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION
|
|
t4 p0 1 HASH `c3`
|
|
t4 p1 2 HASH `c3`
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't5';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION
|
|
t5 p0 1 KEY `pk1`
|
|
t5 p1 2 KEY `pk1`
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't6';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION PARTITION_DESCRIPTION
|
|
t6 x1 1 RANGE `pk1` 333
|
|
t6 x2 2 RANGE `pk1` 720
|
|
SELECT COUNT(*) FROM test.t1;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 Sweden, Texas 2 0
|
|
249 Sweden, Texas 4 0
|
|
248 Sweden, Texas 6 0
|
|
247 Sweden, Texas 8 0
|
|
246 Sweden, Texas 10 0
|
|
SELECT COUNT(*) FROM test.t2;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 1 1
|
|
249 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 3 1
|
|
248 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 5 1
|
|
247 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 7 1
|
|
246 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 9 1
|
|
SELECT COUNT(*) FROM test.t3;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t3 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 0 1
|
|
249 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 2 1
|
|
248 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 4 1
|
|
247 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 6 1
|
|
246 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 8 1
|
|
SELECT COUNT(*) FROM test.t4;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t4 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 Sweden, Texas 2 0
|
|
249 Sweden, Texas 4 0
|
|
248 Sweden, Texas 6 0
|
|
247 Sweden, Texas 8 0
|
|
246 Sweden, Texas 10 0
|
|
SELECT COUNT(*) FROM test.t5;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t5 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 1 1
|
|
249 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 3 1
|
|
248 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 5 1
|
|
247 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 7 1
|
|
246 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 9 1
|
|
SELECT COUNT(*) FROM test.t6;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t6 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 0 1
|
|
249 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 2 1
|
|
248 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 4 1
|
|
247 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 6 1
|
|
246 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 8 1
|
|
DROP TABLE test.t1;
|
|
DROP TABLE test.t2;
|
|
DROP TABLE test.t3;
|
|
DROP TABLE test.t4;
|
|
DROP TABLE test.t5;
|
|
DROP TABLE test.t6;
|
|
ALTER TABLESPACE table_space1
|
|
DROP DATAFILE './table_space1/datafile.dat';
|
|
ALTER TABLESPACE table_space2
|
|
DROP DATAFILE './table_space2/datafile.dat';
|
|
DROP TABLESPACE table_space1;
|
|
DROP TABLESPACE table_space2;
|
|
DROP LOGFILE GROUP log_group1
|
|
ENGINE NDB;
|
|
SET GLOBAL ndb_metadata_check_interval = 0;
|
|
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;
|
|
SET GLOBAL ndb_metadata_check_interval = 0;
|
|
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;
|
|
SHOW CREATE TABLE test.t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` varchar(150) NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) /*!50100 TABLESPACE `table_space1` STORAGE DISK */ ENGINE=ndbcluster AUTO_INCREMENT=251 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`c3`)
|
|
PARTITIONS 4 */
|
|
SHOW CREATE TABLE test.t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` text NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) /*!50100 TABLESPACE `table_space2` STORAGE DISK */ ENGINE=ndbcluster AUTO_INCREMENT=251 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (c3)
|
|
(PARTITION p0 ENGINE = ndbcluster,
|
|
PARTITION p1 ENGINE = ndbcluster) */
|
|
SHOW CREATE TABLE test.t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` varchar(202) NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) /*!50100 TABLESPACE `table_space2` STORAGE DISK */ ENGINE=ndbcluster AUTO_INCREMENT=251 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`c3`)
|
|
(PARTITION x1 VALUES LESS THAN (105) ENGINE = ndbcluster,
|
|
PARTITION x2 VALUES LESS THAN (333) ENGINE = ndbcluster,
|
|
PARTITION x3 VALUES LESS THAN (720) ENGINE = ndbcluster) */
|
|
SHOW CREATE TABLE test.t4;
|
|
Table Create Table
|
|
t4 CREATE TABLE `t4` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` varchar(180) NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) ENGINE=ndbcluster AUTO_INCREMENT=251 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`c3`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE test.t5;
|
|
Table Create Table
|
|
t5 CREATE TABLE `t5` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` text NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) ENGINE=ndbcluster AUTO_INCREMENT=251 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (pk1)
|
|
(PARTITION p0 ENGINE = ndbcluster,
|
|
PARTITION p1 ENGINE = ndbcluster) */
|
|
SHOW CREATE TABLE test.t6;
|
|
Table Create Table
|
|
t6 CREATE TABLE `t6` (
|
|
`pk1` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`c2` varchar(220) NOT NULL,
|
|
`c3` int(11) NOT NULL,
|
|
`c4` bit(1) NOT NULL,
|
|
PRIMARY KEY (`pk1`,`c3`)
|
|
) ENGINE=ndbcluster AUTO_INCREMENT=251 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`pk1`)
|
|
(PARTITION x1 VALUES LESS THAN (333) ENGINE = ndbcluster,
|
|
PARTITION x2 VALUES LESS THAN (720) ENGINE = ndbcluster) */
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't1';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION
|
|
t1 p0 1 HASH `c3`
|
|
t1 p1 2 HASH `c3`
|
|
t1 p2 3 HASH `c3`
|
|
t1 p3 4 HASH `c3`
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't2';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION
|
|
t2 p0 1 KEY `c3`
|
|
t2 p1 2 KEY `c3`
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't3';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION PARTITION_DESCRIPTION
|
|
t3 x1 1 RANGE `c3` 105
|
|
t3 x2 2 RANGE `c3` 333
|
|
t3 x3 3 RANGE `c3` 720
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't4';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION
|
|
t4 p0 1 HASH `c3`
|
|
t4 p1 2 HASH `c3`
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't5';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION
|
|
t5 p0 1 KEY `pk1`
|
|
t5 p1 2 KEY `pk1`
|
|
SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't6';
|
|
TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION PARTITION_METHOD PARTITION_EXPRESSION PARTITION_DESCRIPTION
|
|
t6 x1 1 RANGE `pk1` 333
|
|
t6 x2 2 RANGE `pk1` 720
|
|
SELECT COUNT(*) FROM test.t1;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t1 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 Sweden, Texas 2 0
|
|
249 Sweden, Texas 4 0
|
|
248 Sweden, Texas 6 0
|
|
247 Sweden, Texas 8 0
|
|
246 Sweden, Texas 10 0
|
|
SELECT COUNT(*) FROM test.t2;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t2 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 1 1
|
|
249 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 3 1
|
|
248 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 5 1
|
|
247 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 7 1
|
|
246 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 9 1
|
|
SELECT COUNT(*) FROM test.t3;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t3 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 0 1
|
|
249 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 2 1
|
|
248 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 4 1
|
|
247 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 6 1
|
|
246 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 8 1
|
|
SELECT COUNT(*) FROM test.t4;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t4 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 Sweden, Texas 2 0
|
|
249 Sweden, Texas 4 0
|
|
248 Sweden, Texas 6 0
|
|
247 Sweden, Texas 8 0
|
|
246 Sweden, Texas 10 0
|
|
SELECT COUNT(*) FROM test.t5;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t5 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 1 1
|
|
249 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 3 1
|
|
248 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 5 1
|
|
247 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 7 1
|
|
246 Sweden, Texas, ITALY, Kyle, JO, JBM,TU 9 1
|
|
SELECT COUNT(*) FROM test.t6;
|
|
COUNT(*)
|
|
250
|
|
SELECT pk1, c2, c3, hex(c4) FROM test.t6 ORDER BY c3 LIMIT 5;
|
|
pk1 c2 c3 hex(c4)
|
|
250 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 0 1
|
|
249 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 2 1
|
|
248 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 4 1
|
|
247 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 6 1
|
|
246 TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU 8 1
|
|
DROP TABLE test.t1;
|
|
DROP TABLE test.t2;
|
|
DROP TABLE test.t3;
|
|
DROP TABLE test.t4;
|
|
DROP TABLE test.t5;
|
|
DROP TABLE test.t6;
|
|
ALTER TABLESPACE table_space1 DROP DATAFILE './table_space1/datafile.dat';
|
|
ALTER TABLESPACE table_space2 DROP DATAFILE './table_space2/datafile.dat';
|
|
DROP TABLESPACE table_space1;
|
|
DROP TABLESPACE table_space2;
|
|
DROP LOGFILE GROUP log_group1 ENGINE = NDB;
|