1create 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' 2partition by key (a,b,c,d) ( 3partition pa1 max_rows=20 min_rows=2, 4partition pa2 max_rows=30 min_rows=3, 5partition pa3 max_rows=30 min_rows=4, 6partition pa4 max_rows=40 min_rows=2); 7show create table t1; 8Table Create Table 9t1 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=latin1 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) 21insert 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'); 26select * from t1; 27a b c d 281975-01-01 abcde abcde m 291980-10-14 fgbbd dtzndtz w 301983-12-31 cdef srtbvsr w 312000-06-15 jukg zikhuk m 32select * from t1 where a<19851231; 33a b c d 341975-01-01 abcde abcde m 351980-10-14 fgbbd dtzndtz w 361983-12-31 cdef srtbvsr w 37drop table t1; 38create 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' 39partition by key(a,b,c,d,e,f,g,h) ( 40partition pa1 max_rows=20 min_rows=2, 41partition pa2 max_rows=30 min_rows=3, 42partition pa3 max_rows=30 min_rows=4, 43partition pa4 max_rows=40 min_rows=2); 44show create table t1; 45Table Create Table 46t1 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=latin1 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) 63insert 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' ); 68select * from t1; 69a b c d e f g h i 701980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr 711983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi 721975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 732000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m 74select * from t1 where a<19851231; 75a b c d e f g h i 761975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 771980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr 781983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi 79drop table t1; 80create 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' 81partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1) ( 82partition pa1 max_rows=20 min_rows=2, 83partition pa2 max_rows=30 min_rows=3, 84partition pa3 max_rows=30 min_rows=4, 85partition pa4 max_rows=40 min_rows=2); 86show create table t1; 87Table Create Table 88t1 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=latin1 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) 113insert 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'); 118select * from t1; 119a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i 1201983-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 1211980-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 1221975-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 1232000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m 124select * from t1 where a<19851231; 125a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i 1261975-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 1271980-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 1281983-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 129drop table t1; 130create 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' 131partition 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) ( 132partition pa1 max_rows=20 min_rows=2, 133partition pa2 max_rows=30 min_rows=3, 134partition pa3 max_rows=30 min_rows=4, 135partition pa4 max_rows=40 min_rows=2); 136ERROR HY000: Too many fields in 'list of partition fields' 137create 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' 138partition 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) ( 139partition pa1 max_rows=20 min_rows=2, 140partition pa2 max_rows=30 min_rows=3, 141partition pa3 max_rows=30 min_rows=4, 142partition pa4 max_rows=40 min_rows=2); 143drop table t1; 144create 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' 145partition by key(a,b,c,d,e,f,g,h) ( 146partition pa1 max_rows=20 min_rows=2, 147partition pa2 max_rows=30 min_rows=3, 148partition pa3 max_rows=30 min_rows=4, 149partition pa4 max_rows=40 min_rows=2); 150show create table t1; 151Table Create Table 152t1 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=latin1 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) 193insert 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'); 198select * from t1; 199a 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 2001980-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 2011983-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 2021975-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 2032000-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 204select * from t1 where a<19851231; 205a 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 2061975-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 2071980-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 2081983-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 209drop table t1; 210# Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end 211CREATE TABLE t1 ( 212a INT AUTO_INCREMENT, 213b VARCHAR(255), 214PRIMARY KEY (a)) 215ENGINE = InnoDB 216PARTITION BY HASH (a) 217PARTITIONS 2; 218connect con1, localhost, root,,; 219connect con2, localhost, root,,; 220connection con1; 221SET autocommit=OFF; 222START TRANSACTION; 223INSERT INTO t1 VALUES (NULL, 'first row t2'); 224connection con2; 225SET autocommit=OFF; 226SET SESSION lock_wait_timeout= 1; 227ALTER TABLE t1 AUTO_INCREMENT = 10; 228ERROR HY000: Lock wait timeout exceeded; try restarting transaction 229disconnect con2; 230disconnect con1; 231connection default; 232DROP TABLE t1; 233# 234# Bug#53676: Unexpected errors and possible table corruption on 235# ADD PARTITION and LOCK TABLE 236connect con1,localhost,root,,; 237CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) 238ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; 239connect con2,localhost,root,,; 240SET lock_wait_timeout = 2; 241connection con1; 242#Connection 1 locks the table 243LOCK TABLE t1 READ; 244connection con2; 245# Connection 2 tries to add partitions: 246# First attempt: lock wait timeout (as expected) 247ALTER TABLE t1 ADD PARTITION PARTITIONS 2; 248ERROR HY000: Lock wait timeout exceeded; try restarting transaction 249# Second attempt: says that partition already exists 250ALTER TABLE t1 ADD PARTITION PARTITIONS 2; 251ERROR HY000: Lock wait timeout exceeded; try restarting transaction 252connection con1; 253# Connection 1 unlocks the table and locks it again: 254UNLOCK TABLES; 255LOCK TABLE t1 READ; 256connection con2; 257# Connection 2 tries again to add partitions: 258# Third attempt: says that the table does not exist 259ALTER TABLE t1 ADD PARTITION PARTITIONS 2; 260ERROR HY000: Lock wait timeout exceeded; try restarting transaction 261# Check table returns the same (not after fixing bug#56172!) 262CHECK TABLE t1; 263Table Op Msg_type Msg_text 264test.t1 check status OK 265connection con1; 266UNLOCK TABLES; 267connection con2; 268DROP TABLE t1; 269connection con1; 270CREATE TABLE t2 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) 271ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; 272connection con2; 273SET lock_wait_timeout = 2; 274connection con1; 275LOCK TABLE t2 READ; 276connection con2; 277ALTER TABLE t2 ADD PARTITION PARTITIONS 2; 278ERROR HY000: Lock wait timeout exceeded; try restarting transaction 279ALTER TABLE t2 ADD PARTITION PARTITIONS 2; 280connection con1; 281UNLOCK TABLES; 282connection con2; 283connect con3,localhost,root,,; 284CHECK TABLE t2; 285Table Op Msg_type Msg_text 286test.t2 check status OK 287SELECT * FROM t2; 288i f 289DROP TABLE t2; 290connection con1; 291CREATE TABLE t3 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) 292ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; 293connection con2; 294SET lock_wait_timeout = 2; 295connection con1; 296# Connection 1 locks the table 297LOCK TABLE t3 READ; 298connection con2; 299# Connection 2 tries to add partitions (timeout): 300ALTER TABLE t3 ADD PARTITION PARTITIONS 2; 301ERROR HY000: Lock wait timeout exceeded; try restarting transaction 302connection con3; 303SET lock_wait_timeout = 2; 304# Connection 3 tries to add partitions (partition already exists): 305ALTER TABLE t3 ADD PARTITION PARTITIONS 2; 306ERROR HY000: Lock wait timeout exceeded; try restarting transaction 307connect con4,localhost,root,,; 308# Connection 4 tries to rename the table: 309RENAME TABLE t3 TO t4; 310connection con1; 311# Connection 1 unlocks the table: 312UNLOCK TABLES; 313connection con4; 314# Connection 4 gets error on rename: 315connect con5,localhost,root,,; 316# SHOW TABLES returns the table (not renamed): 317SHOW TABLES; 318Tables_in_test 319t4 320# Connection 5 attempts to read from the table (table does not exist): 321SELECT * FROM t3; 322ERROR 42S02: Table 'test.t3' doesn't exist 323DROP TABLE t4; 324disconnect con5; 325disconnect con4; 326disconnect con3; 327disconnect con2; 328disconnect con1; 329connection default; 330