1
# Creating database MySQL_TEST_DB
2
CREATE DATABASE MySQL_Test_DB;
4
# 1.0 KEY partitioning mgm
5
# Creating KEY partitioned table
6
CREATE TABLE TableA (a INT)
13
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
14
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
29
# Test of ADD/COALESCE PARTITIONS
30
# expecting duplicate partition name
31
ALTER TABLE TableA ADD PARTITION
35
ERROR HY000: Duplicate partition name parta
36
ALTER TABLE TableA ADD PARTITION
54
SHOW CREATE TABLE TableA;
56
TableA CREATE TABLE `tablea` (
57
`a` int(11) DEFAULT NULL
58
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
59
/*!50100 PARTITION BY KEY (a)
60
(PARTITION parta ENGINE = ndbcluster,
61
PARTITION partB ENGINE = ndbcluster,
62
PARTITION Partc ENGINE = ndbcluster,
63
PARTITION PartD ENGINE = ndbcluster,
64
PARTITION partE ENGINE = ndbcluster,
65
PARTITION Partf ENGINE = ndbcluster,
66
PARTITION PartG ENGINE = ndbcluster) */
67
ALTER TABLE TableA COALESCE PARTITION 4;
82
SHOW CREATE TABLE TableA;
84
TableA CREATE TABLE `tablea` (
85
`a` int(11) DEFAULT NULL
86
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
87
/*!50100 PARTITION BY KEY (a)
88
(PARTITION parta ENGINE = ndbcluster,
89
PARTITION partB ENGINE = ndbcluster,
90
PARTITION Partc ENGINE = ndbcluster) */
91
# Test of REORGANIZE PARTITIONS
92
# Should not work on HASH/KEY
93
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
96
ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
97
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
100
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
101
ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
102
(PARTITION partB COMMENT="Previusly named parta",
103
PARTITION parta COMMENT="Previusly named partB");
104
SELECT * FROM TableA;
118
SHOW CREATE TABLE TableA;
120
TableA CREATE TABLE `tablea` (
121
`a` int(11) DEFAULT NULL
122
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
123
/*!50100 PARTITION BY KEY (a)
124
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ndbcluster,
125
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ndbcluster,
126
PARTITION Partc ENGINE = ndbcluster) */
127
# Test of RENAME TABLE
128
RENAME TABLE TableA to TableB;
129
SELECT * FROM TableB;
143
RENAME TABLE TableB to TableA;
144
SELECT * FROM TableA;
158
# Checking name comparision Upper vs Lower case
159
# Error if lower_case_table_names != 0
160
# lower_case_table_names: 1
161
CREATE TABLE tablea (a INT)
162
ENGINE = 'NDBCluster'
168
ERROR 42S01: Table 'tablea' already exists
170
Tables_in_mysql_test_db
172
RENAME TABLE TableA to tablea;
173
ERROR 42S01: Table 'tablea' already exists
174
RENAME TABLE tablea to TableA;
175
ERROR 42S01: Table 'tablea' already exists
176
SELECT * FROM tablea;
190
SHOW CREATE TABLE tablea;
192
tablea CREATE TABLE `tablea` (
193
`a` int(11) DEFAULT NULL
194
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
195
/*!50100 PARTITION BY KEY (a)
196
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ndbcluster,
197
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ndbcluster,
198
PARTITION Partc ENGINE = ndbcluster) */
199
# Test of REMOVE PARTITIONING
200
ALTER TABLE TableA REMOVE PARTITIONING;
201
SELECT * FROM TableA;
215
SHOW CREATE TABLE TableA;
217
TableA CREATE TABLE `tablea` (
218
`a` int(11) DEFAULT NULL
219
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
220
# Cleaning up after KEY PARTITIONING test
222
# Cleaning up before exit
224
DROP DATABASE MySQL_Test_DB;