1
create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w'), primary key(a,b,c,d)) engine='InnoDB'
2
partition by key (a,b,c,d) (
3
partition pa1 max_rows=20 min_rows=2,
4
partition pa2 max_rows=30 min_rows=3,
5
partition pa3 max_rows=30 min_rows=4,
6
partition pa4 max_rows=40 min_rows=2);
11
`b` varchar(50) NOT NULL,
12
`c` varchar(50) NOT NULL,
13
`d` enum('m','w') NOT NULL DEFAULT 'm',
14
PRIMARY KEY (`a`,`b`,`c`,`d`)
15
) ENGINE=InnoDB DEFAULT CHARSET=latin1
16
/*!50100 PARTITION BY KEY (a,b,c,d)
17
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
18
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
19
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
20
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
22
('1975-01-01', 'abcde', 'abcde','m'),
23
('1983-12-31', 'cdef', 'srtbvsr', 'w'),
24
('1980-10-14', 'fgbbd', 'dtzndtz', 'w'),
25
('2000-06-15', 'jukg','zikhuk','m');
28
1975-01-01 abcde abcde m
29
1980-10-14 fgbbd dtzndtz w
30
1983-12-31 cdef srtbvsr w
31
2000-06-15 jukg zikhuk m
32
select * from t1 where a<19851231;
34
1983-12-31 cdef srtbvsr w
35
1980-10-14 fgbbd dtzndtz w
36
1975-01-01 abcde abcde m
38
create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h)) engine='InnoDB'
39
partition by key(a,b,c,d,e,f,g,h) (
40
partition pa1 max_rows=20 min_rows=2,
41
partition pa2 max_rows=30 min_rows=3,
42
partition pa3 max_rows=30 min_rows=4,
43
partition pa4 max_rows=40 min_rows=2);
46
t1 CREATE TABLE `t1` (
48
`b` varchar(50) NOT NULL,
49
`c` varchar(50) NOT NULL,
50
`d` enum('m','w') NOT NULL,
52
`f` decimal(18,2) NOT NULL,
53
`g` bigint(20) NOT NULL,
54
`h` tinyint(4) NOT NULL,
55
`i` char(255) DEFAULT NULL,
56
PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`)
57
) ENGINE=InnoDB DEFAULT CHARSET=latin1
58
/*!50100 PARTITION BY KEY (a,b,c,d,e,f,g,h)
59
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
60
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
61
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
62
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
64
('1975-01-01', 'abcde', 'abcde','m', 1234, 123.45, 32412341234, 113, 'tbhth nrzh ztfghgfh fzh ftzhj fztjh'),
65
('1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, 'liuugbzvdmrlti b itiortudirtfgtibm dfi'),
66
('1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, 'd,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr'),
67
('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m' );
70
1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
71
1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
72
1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
73
2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m
74
select * from t1 where a<19851231;
76
1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
77
1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
78
1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
80
create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1)) engine='InnoDB'
81
partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1) (
82
partition pa1 max_rows=20 min_rows=2,
83
partition pa2 max_rows=30 min_rows=3,
84
partition pa3 max_rows=30 min_rows=4,
85
partition pa4 max_rows=40 min_rows=2);
88
t1 CREATE TABLE `t1` (
90
`b` varchar(50) NOT NULL,
91
`c` varchar(50) NOT NULL,
92
`d` enum('m','w') NOT NULL,
94
`f` decimal(18,2) NOT NULL,
95
`g` bigint(20) NOT NULL,
96
`h` tinyint(4) NOT NULL,
98
`b1` varchar(50) NOT NULL,
99
`c1` varchar(50) NOT NULL,
100
`d1` enum('m','w') NOT NULL,
101
`e1` int(11) NOT NULL,
102
`f1` decimal(18,2) NOT NULL,
103
`g1` bigint(20) NOT NULL,
104
`h1` tinyint(4) NOT NULL,
105
`i` char(255) DEFAULT NULL,
106
PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`,`a1`,`b1`,`c1`,`d1`,`e1`,`f1`,`g1`,`h1`)
107
) ENGINE=InnoDB DEFAULT CHARSET=latin1
108
/*!50100 PARTITION BY KEY (a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1)
109
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
110
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
111
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
112
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
113
insert into t1 values
114
('1975-01-01', 'abcde', 'abcde','m', 1234, 123.45, 32412341234, 113,'1975-01-01', 'abcde', 'abcde','m', 1234, 123.45, 32412341234, 113, 'tbhth nrzh ztfghgfh fzh ftzhj fztjh'),
115
('1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127,'1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, 'liuugbzvdmrlti b itiortudirtfgtibm dfi'),
116
('1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124,'1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, 'd,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr'),
117
('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m');
119
a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i
120
1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
121
1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
122
1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
123
2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m
124
select * from t1 where a<19851231;
125
a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i
126
1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
127
1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
128
1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
130
create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, a2 date not null, b2 varchar(50) not null, c2 varchar(50) not null, d2 enum('m', 'w') not null, e2 int not null, f2 decimal (18,2) not null, g2 bigint not null, h2 tinyint not null, a3 date not null, b3 varchar(50) not null, c3 varchar(50) not null, d3 enum('m', 'w') not null, e3 int not null, f3 decimal (18,2) not null, g3 bigint not null, h3 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3)) engine='InnoDB'
131
partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3) (
132
partition pa1 max_rows=20 min_rows=2,
133
partition pa2 max_rows=30 min_rows=3,
134
partition pa3 max_rows=30 min_rows=4,
135
partition pa4 max_rows=40 min_rows=2);
136
ERROR 42000: Too many key parts specified; max 16 parts allowed
137
create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, a2 date not null, b2 varchar(50) not null, c2 varchar(50) not null, d2 enum('m', 'w') not null, e2 int not null, f2 decimal (18,2) not null, g2 bigint not null, h2 tinyint not null, a3 date not null, b3 varchar(50) not null, c3 varchar(50) not null, d3 enum('m', 'w') not null, e3 int not null, f3 decimal (18,2) not null, g3 bigint not null, h3 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1)) engine='InnoDB'
138
partition by key(a,b,c,d,e,f,g,h) (
139
partition pa1 max_rows=20 min_rows=2,
140
partition pa2 max_rows=30 min_rows=3,
141
partition pa3 max_rows=30 min_rows=4,
142
partition pa4 max_rows=40 min_rows=2);
143
show create table t1;
145
t1 CREATE TABLE `t1` (
147
`b` varchar(50) NOT NULL,
148
`c` varchar(50) NOT NULL,
149
`d` enum('m','w') NOT NULL,
150
`e` int(11) NOT NULL,
151
`f` decimal(18,2) NOT NULL,
152
`g` bigint(20) NOT NULL,
153
`h` tinyint(4) NOT NULL,
155
`b1` varchar(50) NOT NULL,
156
`c1` varchar(50) NOT NULL,
157
`d1` enum('m','w') NOT NULL,
158
`e1` int(11) NOT NULL,
159
`f1` decimal(18,2) NOT NULL,
160
`g1` bigint(20) NOT NULL,
161
`h1` tinyint(4) NOT NULL,
163
`b2` varchar(50) NOT NULL,
164
`c2` varchar(50) NOT NULL,
165
`d2` enum('m','w') NOT NULL,
166
`e2` int(11) NOT NULL,
167
`f2` decimal(18,2) NOT NULL,
168
`g2` bigint(20) NOT NULL,
169
`h2` tinyint(4) NOT NULL,
171
`b3` varchar(50) NOT NULL,
172
`c3` varchar(50) NOT NULL,
173
`d3` enum('m','w') NOT NULL,
174
`e3` int(11) NOT NULL,
175
`f3` decimal(18,2) NOT NULL,
176
`g3` bigint(20) NOT NULL,
177
`h3` tinyint(4) NOT NULL,
178
`i` char(255) DEFAULT NULL,
179
PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`,`a1`,`b1`,`c1`,`d1`,`e1`,`f1`,`g1`,`h1`)
180
) ENGINE=InnoDB DEFAULT CHARSET=latin1
181
/*!50100 PARTITION BY KEY (a,b,c,d,e,f,g,h)
182
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
183
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
184
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
185
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
186
insert into t1 values
187
('1975-01-01', 'abcde', 'abcde','m', 1234, 123.45, 32412341234, 113,'1975-01-01', 'abcde', 'abcde','m', 1234, 123.45, 32412341234, 113,'1975-01-01', 'abcde', 'abcde','m', 1234, 123.45, 32412341234, 113, '1975-01-01', 'abcde', 'abcde','m', 1234, 123.45, 32412341234, 113, 'tbhth nrzh ztfghgfh fzh ftzhj fztjh'),
188
('1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127,'1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, '1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, '1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, 'liuugbzvdmrlti b itiortudirtfgtibm dfi'),
189
('1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, '1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, '1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, '1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, 'd,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr'),
190
('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, '2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m');
192
a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 a2 b2 c2 d2 e2 f2 g2 h2 a3 b3 c3 d3 e3 f3 g3 h3 i
193
1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
194
1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
195
1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
196
2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m
197
select * from t1 where a<19851231;
198
a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 a2 b2 c2 d2 e2 f2 g2 h2 a3 b3 c3 d3 e3 f3 g3 h3 i
199
1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
200
1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
201
1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
203
# Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end
205
a INT AUTO_INCREMENT,
209
PARTITION BY HASH (a)
213
INSERT INTO t1 VALUES (NULL, 'first row t2');
215
ALTER TABLE t1 AUTO_INCREMENT = 10;
216
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
217
INSERT INTO t1 VALUES (NULL, 'second row t2');
218
SELECT a,b FROM t1 ORDER BY a;