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 charset latin1
3 partition by key (a,b,c,d) (
4 partition pa1 max_rows=20 min_rows=2,
5 partition pa2 max_rows=30 min_rows=3,
6 partition pa3 max_rows=30 min_rows=4,
7 partition pa4 max_rows=40 min_rows=2);
8 show create table t1;
9 Table Create Table
10 t1 CREATE TABLE `t1` (
11 `a` date NOT NULL,
12 `b` varchar(50) NOT NULL,
13 `c` varchar(50) NOT NULL,
14 `d` enum('m','w') NOT NULL,
15 PRIMARY KEY (`a`,`b`,`c`,`d`)
16 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
17 /*!50100 PARTITION BY KEY (a,b,c,d)
18 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
19 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
20 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
21 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
22 insert into t1 values
23 ('1975-01-01', 'abcde', 'abcde','m'),
24 ('1983-12-31', 'cdef', 'srtbvsr', 'w'),
25 ('1980-10-14', 'fgbbd', 'dtzndtz', 'w'),
26 ('2000-06-15', 'jukg','zikhuk','m');
27 select * from t1;
28 a b c d
29 1983-12-31 cdef srtbvsr w
30 1980-10-14 fgbbd dtzndtz w
31 1975-01-01 abcde abcde m
32 2000-06-15 jukg zikhuk m
33 select * from t1 where a<19851231;
34 a b c d
35 1975-01-01 abcde abcde m
36 1980-10-14 fgbbd dtzndtz w
37 1983-12-31 cdef srtbvsr w
38 drop table t1;
39 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'
40 charset latin1
41 partition by key(a,b,c,d,e,f,g,h) (
42 partition pa1 max_rows=20 min_rows=2,
43 partition pa2 max_rows=30 min_rows=3,
44 partition pa3 max_rows=30 min_rows=4,
45 partition pa4 max_rows=40 min_rows=2);
46 show create table t1;
47 Table Create Table
48 t1 CREATE TABLE `t1` (
49 `a` date NOT NULL,
50 `b` varchar(50) NOT NULL,
51 `c` varchar(50) NOT NULL,
52 `d` enum('m','w') NOT NULL,
53 `e` int NOT NULL,
54 `f` decimal(18,2) NOT NULL,
55 `g` bigint NOT NULL,
56 `h` tinyint NOT NULL,
57 `i` char(255) DEFAULT NULL,
58 PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`)
59 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
60 /*!50100 PARTITION BY KEY (a,b,c,d,e,f,g,h)
61 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
62 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
63 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
64 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
65 insert into t1 values
66 ('1975-01-01', 'abcde', 'abcde','m', 1234, 123.45, 32412341234, 113, 'tbhth nrzh ztfghgfh fzh ftzhj fztjh'),
67 ('1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, 'liuugbzvdmrlti b itiortudirtfgtibm dfi'),
68 ('1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, 'd,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr'),
69 ('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m' );
70 select * from t1;
71 a b c d e f g h i
72 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
73 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
74 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
75 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m
76 select * from t1 where a<19851231;
77 a b c d e f g h i
78 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
79 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
80 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
81 drop table t1;
82 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'
83 charset latin1
84 partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1) (
85 partition pa1 max_rows=20 min_rows=2,
86 partition pa2 max_rows=30 min_rows=3,
87 partition pa3 max_rows=30 min_rows=4,
88 partition pa4 max_rows=40 min_rows=2);
89 show create table t1;
90 Table Create Table
91 t1 CREATE TABLE `t1` (
92 `a` date NOT NULL,
93 `b` varchar(50) NOT NULL,
94 `c` varchar(50) NOT NULL,
95 `d` enum('m','w') NOT NULL,
96 `e` int NOT NULL,
97 `f` decimal(18,2) NOT NULL,
98 `g` bigint NOT NULL,
99 `h` tinyint NOT NULL,
100 `a1` date NOT NULL,
101 `b1` varchar(50) NOT NULL,
102 `c1` varchar(50) NOT NULL,
103 `d1` enum('m','w') NOT NULL,
104 `e1` int NOT NULL,
105 `f1` decimal(18,2) NOT NULL,
106 `g1` bigint NOT NULL,
107 `h1` tinyint NOT NULL,
108 `i` char(255) DEFAULT NULL,
109 PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`,`a1`,`b1`,`c1`,`d1`,`e1`,`f1`,`g1`,`h1`)
110 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
111 /*!50100 PARTITION BY KEY (a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1)
112 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
113 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
114 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
115 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
116 insert into t1 values
117 ('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'),
118 ('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'),
119 ('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'),
120 ('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');
121 select * from t1;
122 a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i
123 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
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
125 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
126 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
127 select * from t1 where a<19851231;
128 a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i
129 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 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
131 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
132 drop table t1;
133 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'
134 charset latin1
135 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) (
136 partition pa1 max_rows=20 min_rows=2,
137 partition pa2 max_rows=30 min_rows=3,
138 partition pa3 max_rows=30 min_rows=4,
139 partition pa4 max_rows=40 min_rows=2);
140 ERROR HY000: Too many fields in 'list of partition fields'
141 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'
142 charset latin1
143 partition by key(a,b,c,d,e,f,g,h) (
144 partition pa1 max_rows=20 min_rows=2,
145 partition pa2 max_rows=30 min_rows=3,
146 partition pa3 max_rows=30 min_rows=4,
147 partition pa4 max_rows=40 min_rows=2);
148 show create table t1;
149 Table Create Table
150 t1 CREATE TABLE `t1` (
151 `a` date NOT NULL,
152 `b` varchar(50) NOT NULL,
153 `c` varchar(50) NOT NULL,
154 `d` enum('m','w') NOT NULL,
155 `e` int NOT NULL,
156 `f` decimal(18,2) NOT NULL,
157 `g` bigint NOT NULL,
158 `h` tinyint NOT NULL,
159 `a1` date NOT NULL,
160 `b1` varchar(50) NOT NULL,
161 `c1` varchar(50) NOT NULL,
162 `d1` enum('m','w') NOT NULL,
163 `e1` int NOT NULL,
164 `f1` decimal(18,2) NOT NULL,
165 `g1` bigint NOT NULL,
166 `h1` tinyint NOT NULL,
167 `a2` date NOT NULL,
168 `b2` varchar(50) NOT NULL,
169 `c2` varchar(50) NOT NULL,
170 `d2` enum('m','w') NOT NULL,
171 `e2` int NOT NULL,
172 `f2` decimal(18,2) NOT NULL,
173 `g2` bigint NOT NULL,
174 `h2` tinyint NOT NULL,
175 `a3` date NOT NULL,
176 `b3` varchar(50) NOT NULL,
177 `c3` varchar(50) NOT NULL,
178 `d3` enum('m','w') NOT NULL,
179 `e3` int NOT NULL,
180 `f3` decimal(18,2) NOT NULL,
181 `g3` bigint NOT NULL,
182 `h3` tinyint NOT NULL,
183 `i` char(255) DEFAULT NULL,
184 PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`,`a1`,`b1`,`c1`,`d1`,`e1`,`f1`,`g1`,`h1`)
185 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
186 /*!50100 PARTITION BY KEY (a,b,c,d,e,f,g,h)
187 (PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
188 PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
189 PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
190 PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
191 insert into t1 values
192 ('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'),
193 ('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'),
194 ('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'),
195 ('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');
196 select * from t1;
197 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
198 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
199 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
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 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
202 select * from t1 where a<19851231;
203 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
204 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
205 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
206 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
207 drop table t1;
208 # Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end
209 CREATE TABLE t1 (
210 a INT AUTO_INCREMENT,
211 b VARCHAR(255),
212 PRIMARY KEY (a))
213 ENGINE = InnoDB
214 PARTITION BY HASH (a)
215 PARTITIONS 2;
216 SET autocommit=OFF;
217 START TRANSACTION;
218 INSERT INTO t1 VALUES (NULL, 'first row t2');
219 SET autocommit=OFF;
220 SET SESSION lock_wait_timeout= 1;
221 ALTER TABLE t1 AUTO_INCREMENT = 10;
222 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
223 DROP TABLE t1;
224 #
225 # Bug#53676: Unexpected errors and possible table corruption on
226 # ADD PARTITION and LOCK TABLE
227 CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
228 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
229 INSERT INTO t1 VALUES (2, 2), (3, 3), (4, 4), (5, 5);
230 SET lock_wait_timeout = 2;
231 #Connection 1 locks the table
232 LOCK TABLE t1 READ;
233 # Connection 2 tries to add partitions:
234 # First attempt: lock wait timeout (as expected)
235 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
236 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
237 # Second attempt: says that partition already exists
238 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
239 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
240 # Check that we only can select, not insert/update/delete.
241 INSERT INTO t1 VALUES (NULL, 6), (NULL, 7), (10, 10), (11, 11);
242 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
243 UPDATE t1 SET i = 5 WHERE f = 2;
244 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
245 DELETE FROM t1 WHERE i = 10;
246 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
247 SELECT * FROM t1;
248 i f
249 2 2
250 3 3
251 4 4
252 5 5
253 # Connection 1 unlocks the table and locks it again:
254 UNLOCK TABLES;
255 LOCK TABLE t1 READ;
256 # Connection 2 tries again to add partitions:
257 # Third attempt: says that the table does not exist
258 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
259 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
260 # Check table returns the same (not after fixing bug#56172!)
261 CHECK TABLE t1;
262 Table Op Msg_type Msg_text
263 test.t1 check status OK
264 UNLOCK TABLES;
265 DROP TABLE t1;
266 CREATE TABLE t2 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
267 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
268 SET lock_wait_timeout = 2;
269 LOCK TABLE t2 READ;
270 ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
271 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
272 ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
273 UNLOCK TABLES;
274 CHECK TABLE t2;
275 Table Op Msg_type Msg_text
276 test.t2 check status OK
277 SELECT * FROM t2;
278 i f
279 DROP TABLE t2;
280 CREATE TABLE t3 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
281 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
282 SET lock_wait_timeout = 2;
283 # Connection 1 locks the table
284 LOCK TABLE t3 READ;
285 # Connection 2 tries to add partitions (timeout):
286 ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
287 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
288 SET lock_wait_timeout = 2;
289 # Connection 3 tries to add partitions (partition already exists):
290 ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
291 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
292 # Connection 4 tries to rename the table:
293 RENAME TABLE t3 TO t4;
294 # Connection 1 unlocks the table:
295 UNLOCK TABLES;
296 # Connection 4 gets error on rename:
297 # SHOW TABLES returns the table (not renamed):
298 SHOW TABLES;
299 Tables_in_test
300 t4
301 # Connection 5 attempts to read from the table (table does not exist):
302 SELECT * FROM t3;
303 ERROR 42S02: Table 'test.t3' doesn't exist
304 DROP TABLE t4;
305 # Test WRITE LOCK.
306 CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
307 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
308 INSERT INTO t1 VALUES (3, 3), (4, 4);
309 SET lock_wait_timeout = 2;
310 #Connection 1 locks the table
311 LOCK TABLE t1 WRITE;
312 # Check that we still can SELECT, but not insert/update/delete.
313 # Check that we only can select, not insert/update/delete.
314 INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (10, 10), (11, 11);
315 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
316 UPDATE t1 SET i = 5 WHERE f = 2;
317 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
318 DELETE FROM t1 WHERE i = 10;
319 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
320 SELECT * FROM t1;
321 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
322 UNLOCK TABLES;
323 DROP TABLE t1;
324 #
325 # Bug#53770: Server crash at handler.cc:2076 on LOAD DATA
326 # after timed out COALESCE PARTITION.
327 # Rewritten to use InnoDB after WL#8971.
328 CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
329 ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 3;
330 # Connection 1 starts transaction and gets lock
331 START TRANSACTION;
332 SELECT * FROM t1;
333 i f
334 SET lock_wait_timeout = 2;
335 # Connection 2 tries to coalesce partitions (timeout):
336 ALTER TABLE t1 COALESCE PARTITION 2;
337 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
338 # Connection 3 tries to load into the table:
339 LOAD DATA INFILE 'load.in' INTO TABLE t1 (f);
340 # Connection 1 commits the transaction
341 COMMIT;
342 # Connection 3...
343 DROP TABLE t1;