# 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: REORGANISE PARTITION can only be used to reorganise partitions not to change their numbers
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
(PARTITION partB ,
PARTITION parta );
ERROR HY000: When reorganising 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: 2
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;