# Creating database MySQL_TEST_DB
CREATE DATABASE MySQL_Test_DB;
USE MySQL_Test_DB;
# 1.0 KEY partitioning mgm
# Creating KEY partitioned table
CREATE TABLE TableA (a INT)
ENGINE = 'NDBCluster'
PARTITION BY KEY (a)
(PARTITION parta ,
PARTITION partB ,
PARTITION Partc ,
PARTITION PartD );
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
# Test of ADD/COALESCE PARTITIONS
# expecting duplicate partition name
ALTER TABLE TableA ADD PARTITION
(PARTITION partA,
PARTITION Parta,
PARTITION PartA);
ERROR HY000: Duplicate partition name parta
ALTER TABLE TableA ADD PARTITION
(PARTITION partE,
PARTITION Partf,
PARTITION PartG);
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE TableA;
Table	Create Table
TableA	CREATE TABLE `tablea` (
  `a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
(PARTITION parta ENGINE = ndbcluster,
 PARTITION partB ENGINE = ndbcluster,
 PARTITION Partc ENGINE = ndbcluster,
 PARTITION PartD ENGINE = ndbcluster,
 PARTITION partE ENGINE = ndbcluster,
 PARTITION Partf ENGINE = ndbcluster,
 PARTITION PartG ENGINE = ndbcluster) */
ALTER TABLE TableA COALESCE PARTITION 4;
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE TableA;
Table	Create Table
TableA	CREATE TABLE `tablea` (
  `a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
(PARTITION parta ENGINE = ndbcluster,
 PARTITION partB ENGINE = ndbcluster,
 PARTITION Partc ENGINE = ndbcluster) */
# Test of REORGANIZE PARTITIONS
# Should not work on HASH/KEY
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
(PARTITION PARTA ,
PARTITION partc );
ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
(PARTITION partB ,
PARTITION parta );
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
(PARTITION partB  COMMENT="Previusly named parta",
PARTITION parta  COMMENT="Previusly named partB");
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE TableA;
Table	Create Table
TableA	CREATE TABLE `tablea` (
  `a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ndbcluster,
 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ndbcluster,
 PARTITION Partc ENGINE = ndbcluster) */
# Test of RENAME TABLE
RENAME TABLE TableA to TableB;
SELECT * FROM TableB;
a
1
10
11
12
2
3
4
5
6
7
8
9
RENAME TABLE TableB to TableA;
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
# Checking name comparision Upper vs Lower case
# Error if lower_case_table_names != 0
# lower_case_table_names: 1
CREATE TABLE tablea (a INT)
ENGINE = 'NDBCluster'
PARTITION BY KEY (a)
(PARTITION parta ,
PARTITION partB ,
PARTITION Partc ,
PARTITION PartD );
ERROR 42S01: Table 'tablea' already exists
SHOW TABLES;
Tables_in_mysql_test_db
tablea
RENAME TABLE TableA to tablea;
ERROR 42S01: Table 'tablea' already exists
RENAME TABLE tablea to TableA;
ERROR 42S01: Table 'tablea' already exists
SELECT * FROM tablea;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE tablea;
Table	Create Table
tablea	CREATE TABLE `tablea` (
  `a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a)
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ndbcluster,
 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ndbcluster,
 PARTITION Partc ENGINE = ndbcluster) */
# Test of REMOVE PARTITIONING
ALTER TABLE TableA REMOVE PARTITIONING;
SELECT * FROM TableA;
a
1
10
11
12
2
3
4
5
6
7
8
9
SHOW CREATE TABLE TableA;
Table	Create Table
TableA	CREATE TABLE `tablea` (
  `a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
# Cleaning up after KEY PARTITIONING test
DROP TABLE TableA;
# Cleaning up before exit
USE test;
DROP DATABASE MySQL_Test_DB;