Debian Codegraph @

mysql-test/suite/parts/r/partition_special_innodb.result

raw
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);
7 show create table t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `a` date NOT NULL,
11 `b` varchar(50) NOT NULL,
12 `c` varchar(50) NOT NULL,
13 `d` enum('m','w') NOT NULL,
14 PRIMARY KEY (`a`,`b`,`c`,`d`)
15 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
16 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)
21 insert into t1 values
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');
26 select * from t1;
27 a b c d
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;
33 a b c d
34 1975-01-01 abcde abcde m
35 1980-10-14 fgbbd dtzndtz w
36 1983-12-31 cdef srtbvsr w
37 drop table t1;
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);
44 show create table t1;
45 Table Create Table
46 t1 CREATE TABLE `t1` (
47 `a` date NOT NULL,
48 `b` varchar(50) NOT NULL,
49 `c` varchar(50) NOT NULL,
50 `d` enum('m','w') NOT NULL,
51 `e` int(11) 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
58 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)
63 insert into t1 values
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' );
68 select * from t1;
69 a b c d e f g h i
70 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
71 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
72 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
73 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m
74 select * from t1 where a<19851231;
75 a b c d e f g h i
76 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
77 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
78 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
79 drop table t1;
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);
86 show create table t1;
87 Table Create Table
88 t1 CREATE TABLE `t1` (
89 `a` date NOT NULL,
90 `b` varchar(50) NOT NULL,
91 `c` varchar(50) NOT NULL,
92 `d` enum('m','w') NOT NULL,
93 `e` int(11) NOT NULL,
94 `f` decimal(18,2) NOT NULL,
95 `g` bigint(20) NOT NULL,
96 `h` tinyint(4) NOT NULL,
97 `a1` date 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
108 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');
118 select * from t1;
119 a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i
120 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
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 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
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 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
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 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
129 drop table t1;
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,a4)) 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,a4) (
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 HY000: Too many fields in 'list of partition fields'
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,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3)) engine='InnoDB'
138 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) (
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 drop table t1;
144 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'
145 partition by key(a,b,c,d,e,f,g,h) (
146 partition pa1 max_rows=20 min_rows=2,
147 partition pa2 max_rows=30 min_rows=3,
148 partition pa3 max_rows=30 min_rows=4,
149 partition pa4 max_rows=40 min_rows=2);
150 show create table t1;
151 Table Create Table
152 t1 CREATE TABLE `t1` (
153 `a` date NOT NULL,
154 `b` varchar(50) NOT NULL,
155 `c` varchar(50) NOT NULL,
156 `d` enum('m','w') NOT NULL,
157 `e` int(11) NOT NULL,
158 `f` decimal(18,2) NOT NULL,
159 `g` bigint(20) NOT NULL,
160 `h` tinyint(4) NOT NULL,
161 `a1` date NOT NULL,
162 `b1` varchar(50) NOT NULL,
163 `c1` varchar(50) NOT NULL,
164 `d1` enum('m','w') NOT NULL,
165 `e1` int(11) NOT NULL,
166 `f1` decimal(18,2) NOT NULL,
167 `g1` bigint(20) NOT NULL,
168 `h1` tinyint(4) NOT NULL,
169 `a2` date NOT NULL,
170 `b2` varchar(50) NOT NULL,
171 `c2` varchar(50) NOT NULL,
172 `d2` enum('m','w') NOT NULL,
173 `e2` int(11) NOT NULL,
174 `f2` decimal(18,2) NOT NULL,
175 `g2` bigint(20) NOT NULL,
176 `h2` tinyint(4) NOT NULL,
177 `a3` date NOT NULL,
178 `b3` varchar(50) NOT NULL,
179 `c3` varchar(50) NOT NULL,
180 `d3` enum('m','w') NOT NULL,
181 `e3` int(11) NOT NULL,
182 `f3` decimal(18,2) NOT NULL,
183 `g3` bigint(20) NOT NULL,
184 `h3` tinyint(4) NOT NULL,
185 `i` char(255) DEFAULT NULL,
186 PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`,`a1`,`b1`,`c1`,`d1`,`e1`,`f1`,`g1`,`h1`)
187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
188 PARTITION BY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`)
189 (PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
190 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
191 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
192 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
193 insert into t1 values
194 ('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'),
195 ('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'),
196 ('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'),
197 ('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');
198 select * from t1;
199 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
200 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
201 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
202 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
203 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
204 select * from t1 where a<19851231;
205 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
206 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
207 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
208 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
209 drop table t1;
210 # Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end
211 CREATE TABLE t1 (
212 a INT AUTO_INCREMENT,
213 b VARCHAR(255),
214 PRIMARY KEY (a))
215 ENGINE = InnoDB
216 PARTITION BY HASH (a)
217 PARTITIONS 2;
218 connect con1, localhost, root,,;
219 SET autocommit=OFF;
220 START TRANSACTION;
221 INSERT INTO t1 VALUES (NULL, 'first row t2');
222 connect con2, localhost, root,,;
223 SET autocommit=OFF;
224 SET SESSION lock_wait_timeout= 0;
225 ALTER TABLE t1 AUTO_INCREMENT = 10;
226 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
227 disconnect con2;
228 disconnect con1;
229 connection default;
230 DROP TABLE t1;
231 #
232 # Bug#53676: Unexpected errors and possible table corruption on
233 # ADD PARTITION and LOCK TABLE
234 CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
235 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
236 INSERT INTO t1 VALUES (2, 2), (3, 3), (4, 4), (5, 5);
237 LOCK TABLE t1 READ;
238 connect con2,localhost,root,,;
239 SET lock_wait_timeout = 0;
240 # First attempt: lock wait timeout (as expected)
241 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
242 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
243 # Second attempt: says that partition already exists
244 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
245 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
246 # Check that we only can select, not insert/update/delete.
247 INSERT INTO t1 VALUES (NULL, 6), (NULL, 7), (10, 10), (11, 11);
248 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
249 UPDATE t1 SET i = 5 WHERE f = 2;
250 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
251 DELETE FROM t1 WHERE i = 10;
252 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
253 SELECT * FROM t1;
254 i f
255 2 2
256 3 3
257 4 4
258 5 5
259 connection default;
260 UNLOCK TABLES;
261 LOCK TABLE t1 READ;
262 connection con2;
263 # Third attempt: says that the table does not exist
264 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
265 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
266 # Check table returns the same (not after fixing bug#56172!)
267 CHECK TABLE t1;
268 Table Op Msg_type Msg_text
269 test.t1 check status OK
270 connection default;
271 UNLOCK TABLES;
272 SET GLOBAL innodb_max_purge_lag_wait=0;
273 connection con2;
274 DROP TABLE t1;
275 connection default;
276 CREATE TABLE t2 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
277 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
278 LOCK TABLE t2 READ;
279 connection con2;
280 SET lock_wait_timeout = 0;
281 ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
282 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
283 SET lock_wait_timeout = 2;
284 ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
285 connection default;
286 UNLOCK TABLES;
287 connection con2;
288 connect con3,localhost,root,,;
289 CHECK TABLE t2;
290 Table Op Msg_type Msg_text
291 test.t2 check status OK
292 SELECT * FROM t2;
293 i f
294 DROP TABLE t2;
295 connection default;
296 CREATE TABLE t3 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
297 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
298 LOCK TABLE t3 READ;
299 connection con2;
300 SET lock_wait_timeout = 0;
301 ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
302 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
303 connection con3;
304 SET lock_wait_timeout = 0;
305 ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
306 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
307 connect con4,localhost,root,,;
308 RENAME TABLE t3 TO t4;
309 connection default;
310 UNLOCK TABLES;
311 connection con4;
312 connect con5,localhost,root,,;
313 # SHOW TABLES returns the table (not renamed):
314 SHOW TABLES;
315 Tables_in_test
316 t4
317 # Connection 5 attempts to read from the table (table does not exist):
318 SELECT * FROM t3;
319 ERROR 42S02: Table 'test.t3' doesn't exist
320 DROP TABLE t4;
321 disconnect con5;
322 disconnect con4;
323 disconnect con3;
324 disconnect con2;
325 connection default;
326 CREATE TABLE t1(
327 f1 INT, f2 VARCHAR(10) CHARSET ascii,
328 f3 CHAR(150) CHARSET ascii,
329 f4 TEXT CHARSET ascii)ENGINE=InnoDB
330 PARTITION BY RANGE(f1) (PARTITION p1 VALUES LESS THAN(10),
331 PARTITION p2 VALUES LESS THAN (100));
332 ALTER TABLE t1 convert to charset ascii collate ascii_bin, ALGORITHM=INSTANT;
333 DROP TABLE t1;
334 # Test WRITE LOCK.
335 CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
336 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
337 INSERT INTO t1 VALUES (3, 3), (4, 4);
338 LOCK TABLE t1 WRITE;
339 connect con2,localhost,root,,;
340 SET lock_wait_timeout = 0;
341 # Check that we only can select, not insert/update/delete.
342 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (10, 10), (11, 11);
343 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
344 UPDATE t1 SET i = 5 WHERE f = 2;
345 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
346 DELETE FROM t1 WHERE i = 10;
347 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
348 SELECT * FROM t1;
349 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
350 connection default;
351 UNLOCK TABLES;
352 SET GLOBAL innodb_max_purge_lag_wait=0;
353 connection con2;
354 DROP TABLE t1;
355 disconnect con2;
356 connection default;