1
DROP TABLE IF EXISTS t1;
2
CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b,c))
4
PARTITION BY KEY (a,b);
5
insert into t1 values (1,1,1,1);
9
update t1 set d = 2 where a = 1 and b = 1 and c = 1;
17
CREATE TABLE t1 (a int, b int, c int, d int, PRIMARY KEY(a,b))
20
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
21
CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY(a,b))
25
(1,1,3),(1,2,3),(1,3,3),(1,4,3),(1,5,3),(1,6,3),
26
(1,7,3),(1,8,3),(1,9,3),(1,10,3),(1,11,3),(1,12,3);
27
select * from t1 order by b;
41
select max(b) from t1 where a = 1;
44
select b from t1 where a = 1 order by b desc;
59
CREATE TABLE t1 (a INT, b CHAR(10) COLLATE latin1_bin, c INT, d INT,
60
PRIMARY KEY (a,b,c) USING HASH)
62
DEFAULT CHARSET=latin1
64
insert into t1 values (1,"a",1,1),(2,"a",1,1),(3,"a",1,1);
72
Number of attributes: 4
73
Number of primary keys: 3
79
TableStatus: Retrieved
81
a Int PRIMARY KEY AT=FIXED ST=MEMORY
82
b Char(10;latin1_bin) PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
83
c Int PRIMARY KEY AT=FIXED ST=MEMORY
84
d Int NULL AT=FIXED ST=MEMORY
87
PRIMARY KEY(a, b, c) - UniqueHashIndex
90
NDBT_ProgramExit: 0 - OK
94
t1 CREATE TABLE `t1` (
95
`a` int(11) NOT NULL DEFAULT '0',
96
`b` char(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
97
`c` int(11) NOT NULL DEFAULT '0',
98
`d` int(11) DEFAULT NULL,
99
PRIMARY KEY (`a`,`b`,`c`) USING HASH
100
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
101
/*!50100 PARTITION BY KEY (b) */
103
CREATE TABLE t1 (a int not null primary key)
105
(PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
107
CREATE TABLE t1 (a int not null primary key);
111
(PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
113
CREATE TABLE t1 (a int not null primary key) ENGINE = NDB;
116
(PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);
118
create table t1 (a int)
121
(partition p0, partition p1);
122
show create table t1;
124
t1 CREATE TABLE `t1` (
125
`a` int(11) DEFAULT NULL
126
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
127
/*!50100 PARTITION BY KEY (a)
128
(PARTITION p0 ENGINE = ndbcluster,
129
PARTITION p1 ENGINE = ndbcluster) */
130
alter table t1 engine=heap;
131
show create table t1;
133
t1 CREATE TABLE `t1` (
134
`a` int(11) DEFAULT NULL
135
) ENGINE=MEMORY DEFAULT CHARSET=latin1
136
/*!50100 PARTITION BY KEY (a)
137
(PARTITION p0 ENGINE = MEMORY,
138
PARTITION p1 ENGINE = MEMORY) */
139
alter table t1 engine=ndb;
140
show create table t1;
142
t1 CREATE TABLE `t1` (
143
`a` int(11) DEFAULT NULL
144
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
145
/*!50100 PARTITION BY KEY (a)
146
(PARTITION p0 ENGINE = ndbcluster,
147
PARTITION p1 ENGINE = ndbcluster) */
148
alter table t1 engine=heap remove partitioning;
149
show create table t1;
151
t1 CREATE TABLE `t1` (
152
`a` int(11) DEFAULT NULL
153
) ENGINE=MEMORY DEFAULT CHARSET=latin1
154
alter table t1 engine=ndb
156
(partition p0, partition p1 engine = ndb);
157
show create table t1;
159
t1 CREATE TABLE `t1` (
160
`a` int(11) DEFAULT NULL
161
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
162
/*!50100 PARTITION BY KEY (a)
163
(PARTITION p0 ENGINE = ndbcluster,
164
PARTITION p1 ENGINE = ndbcluster) */
167
(partition p0 engine=ndb, partition p1 engine=ndb);
168
show create table t1;
170
t1 CREATE TABLE `t1` (
171
`a` int(11) DEFAULT NULL
172
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
173
/*!50100 PARTITION BY KEY (a)
174
(PARTITION p0 ENGINE = ndbcluster,
175
PARTITION p1 ENGINE = ndbcluster) */
176
alter table t1 remove partitioning;
177
show create table t1;
179
t1 CREATE TABLE `t1` (
180
`a` int(11) DEFAULT NULL
181
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
184
(partition p0 engine=ndb, partition p1);
188
(partition p0 engine=ndb, partition p1 engine = ndb);
189
show create table t1;
191
t1 CREATE TABLE `t1` (
192
`a` int(11) DEFAULT NULL
193
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
194
/*!50100 PARTITION BY KEY (a)
195
(PARTITION p0 ENGINE = ndbcluster,
196
PARTITION p1 ENGINE = ndbcluster) */
199
c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
208
PARTITION BY KEY(c3) PARTITIONS 5;
209
ALTER TABLE t1 COALESCE PARTITION 4;
211
CREATE TABLE t1 (a int primary key)
215
Table Op Msg_type Msg_text
216
test.t1 analyze note The storage engine for the table doesn't support analyze
218
Table Op Msg_type Msg_text
219
test.t1 check note The storage engine for the table doesn't support check
221
Table Op Msg_type Msg_text
222
test.t1 optimize note The storage engine for the table doesn't support optimize
224
Table Op Msg_type Msg_text
225
test.t1 repair note The storage engine for the table doesn't support repair
226
ALTER TABLE t1 OPTIMIZE PARTITION p0;
227
Table Op Msg_type Msg_text
228
test.t1 optimize note The storage engine for the table doesn't support optimize
229
ALTER TABLE t1 CHECK PARTITION p0;
230
Table Op Msg_type Msg_text
231
test.t1 check note The storage engine for the table doesn't support check
232
ALTER TABLE t1 REPAIR PARTITION p0;
233
Table Op Msg_type Msg_text
234
test.t1 repair note The storage engine for the table doesn't support repair
235
ALTER TABLE t1 ANALYZE PARTITION p0;
236
Table Op Msg_type Msg_text
237
test.t1 analyze note The storage engine for the table doesn't support analyze
238
ALTER TABLE t1 REBUILD PARTITION p0;
239
ERROR HY000: Table storage engine for 't1' doesn't have this option
242
c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
247
PARTITION BY KEY(c3) PARTITIONS 5;
248
ALTER TABLE t1 ADD COLUMN c4 INT AFTER c1;
251
c1 MEDIUMINT NOT NULL AUTO_INCREMENT,
257
(PARTITION p0 NODEGROUP 0, PARTITION p1 NODEGROUP 0);
258
ALTER TABLE t1 ADD COLUMN c4 INT AFTER c1;
259
SELECT NODEGROUP,PARTITION_NAME FROM information_schema.partitions WHERE
261
NODEGROUP PARTITION_NAME
266
a tinyint unsigned NOT NULL,
267
b bigint(20) unsigned NOT NULL,
270
) ENGINE ndb DEFAULT CHARSET=latin1 PARTITION BY KEY (a);
271
insert into t1 values(1,1,'1'), (2,2,'2'), (3,3,'3'), (4,4,'4'), (5,5,'5');
272
select * from t1 where a = 1;
275
select * from t1 where a = 2;
278
select * from t1 where a = 3;
281
select * from t1 where a = 4;
284
select * from t1 where a = 5;
287
delete from t1 where a = 1;
288
select * from t1 order by 1;
294
delete from t1 where a = 2;
295
select * from t1 order by 1;
300
delete from t1 where a = 3;
301
select * from t1 order by 1;
305
delete from t1 where a = 4;
306
select * from t1 order by 1;
309
delete from t1 where a = 5;
310
select * from t1 order by 1;