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'
partition by key (a,b,c,d) (
partition pa1 max_rows=20 min_rows=2,
partition pa2 max_rows=30 min_rows=3,
partition pa3 max_rows=30 min_rows=4,
partition pa4 max_rows=40 min_rows=2);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` date NOT NULL,
`b` varchar(50) NOT NULL,
`c` varchar(50) NOT NULL,
`d` enum('m','w') NOT NULL DEFAULT 'm',
PRIMARY KEY (`a`,`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a,b,c,d)
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
insert into t1 values
('1975-01-01', 'abcde', 'abcde','m'),
('1983-12-31', 'cdef', 'srtbvsr', 'w'),
('1980-10-14', 'fgbbd', 'dtzndtz', 'w'),
('2000-06-15', 'jukg','zikhuk','m');
select * from t1;
a b c d
1983-12-31 cdef srtbvsr w
1980-10-14 fgbbd dtzndtz w
1975-01-01 abcde abcde m
2000-06-15 jukg zikhuk m
select * from t1 where a<19851231;
a b c d
1975-01-01 abcde abcde m
1980-10-14 fgbbd dtzndtz w
1983-12-31 cdef srtbvsr w
drop table t1;
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'
partition by key(a,b,c,d,e,f,g,h) (
partition pa1 max_rows=20 min_rows=2,
partition pa2 max_rows=30 min_rows=3,
partition pa3 max_rows=30 min_rows=4,
partition pa4 max_rows=40 min_rows=2);
show create table t1;
Table Create Table
t1 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(11) NOT NULL,
`f` decimal(18,2) NOT NULL,
`g` bigint(20) NOT NULL,
`h` tinyint(4) NOT NULL,
`i` char(255) DEFAULT NULL,
PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a,b,c,d,e,f,g,h)
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
insert into t1 values
('1975-01-01', 'abcde', 'abcde','m', 1234, 123.45, 32412341234, 113, 'tbhth nrzh ztfghgfh fzh ftzhj fztjh'),
('1983-12-31', 'cdef', 'srtbvsr', 'w', 45634, 13452.56, 3452346456, 127, 'liuugbzvdmrlti b itiortudirtfgtibm dfi'),
('1980-10-14', 'fgbbd', 'dtzndtz', 'w', 67856, 5463354.67, 3567845333, 124, 'd,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr'),
('2000-06-15', 'jukg','zikhuk','m', 45675, 6465754.13, 435242623462, 18, 'pib mdotkbm.m' );
select * from t1;
a b c d e f g h i
1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m
select * from t1 where a<19851231;
a b c d e f g h i
1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh
1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f söierugsig msireg siug ei5ggth lrutluitgzeöjrtnb.rkjthuekuhzrkuthgjdnffjmbr
1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi
drop table t1;
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'
partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1) (
partition pa1 max_rows=20 min_rows=2,
partition pa2 max_rows=30 min_rows=3,
partition pa3 max_rows=30 min_rows=4,
partition pa4 max_rows=40 min_rows=2);
show create table t1;
Table Create Table
t1 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(11) NOT NULL,
`f` decimal(18,2) NOT NULL,
`g` bigint(20) NOT NULL,
`h` tinyint(4) NOT NULL,
`a1` date NOT NULL,
`b1` varchar(50) NOT NULL,
`c1` varchar(50) NOT NULL,
`d1` enum('m','w') NOT NULL,
`e1` int(11) NOT NULL,
`f1` decimal(18,2) NOT NULL,
`g1` bigint(20) NOT NULL,
`h1` tinyint(4) NOT NULL,
`i` char(255) DEFAULT NULL,
PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`,`a1`,`b1`,`c1`,`d1`,`e1`,`f1`,`g1`,`h1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1)
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
insert into t1 values
('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'),
('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'),
('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'),
('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');
select * from t1;
a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i
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
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
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
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
select * from t1 where a<19851231;
a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i
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
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
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
drop table t1;
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'
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) (
partition pa1 max_rows=20 min_rows=2,
partition pa2 max_rows=30 min_rows=3,
partition pa3 max_rows=30 min_rows=4,
partition pa4 max_rows=40 min_rows=2);
ERROR HY000: Too many fields in 'list of partition fields'
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'
partition by key(a,b,c,d,e,f,g,h) (
partition pa1 max_rows=20 min_rows=2,
partition pa2 max_rows=30 min_rows=3,
partition pa3 max_rows=30 min_rows=4,
partition pa4 max_rows=40 min_rows=2);
show create table t1;
Table Create Table
t1 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(11) NOT NULL,
`f` decimal(18,2) NOT NULL,
`g` bigint(20) NOT NULL,
`h` tinyint(4) NOT NULL,
`a1` date NOT NULL,
`b1` varchar(50) NOT NULL,
`c1` varchar(50) NOT NULL,
`d1` enum('m','w') NOT NULL,
`e1` int(11) NOT NULL,
`f1` decimal(18,2) NOT NULL,
`g1` bigint(20) NOT NULL,
`h1` tinyint(4) NOT NULL,
`a2` date NOT NULL,
`b2` varchar(50) NOT NULL,
`c2` varchar(50) NOT NULL,
`d2` enum('m','w') NOT NULL,
`e2` int(11) NOT NULL,
`f2` decimal(18,2) NOT NULL,
`g2` bigint(20) NOT NULL,
`h2` tinyint(4) NOT NULL,
`a3` date NOT NULL,
`b3` varchar(50) NOT NULL,
`c3` varchar(50) NOT NULL,
`d3` enum('m','w') NOT NULL,
`e3` int(11) NOT NULL,
`f3` decimal(18,2) NOT NULL,
`g3` bigint(20) NOT NULL,
`h3` tinyint(4) NOT NULL,
`i` char(255) DEFAULT NULL,
PRIMARY KEY (`a`,`b`,`c`,`d`,`e`,`f`,`g`,`h`,`a1`,`b1`,`c1`,`d1`,`e1`,`f1`,`g1`,`h1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (a,b,c,d,e,f,g,h)
(PARTITION pa1 MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
PARTITION pa2 MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
PARTITION pa3 MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
PARTITION pa4 MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB) */
insert into t1 values
('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'),
('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'),
('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'),
('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');
select * from t1;
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
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
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
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
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
select * from t1 where a<19851231;
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
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
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
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
drop table t1;
# Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end
CREATE TABLE t1 (
a INT AUTO_INCREMENT,
b VARCHAR(255),
PRIMARY KEY (a))
ENGINE = InnoDB
PARTITION BY HASH (a)
PARTITIONS 2;
SET autocommit=OFF;
START TRANSACTION;
INSERT INTO t1 VALUES (NULL, 'first row t2');
SET autocommit=OFF;
SET SESSION lock_wait_timeout= 1;
ALTER TABLE t1 AUTO_INCREMENT = 10;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DROP TABLE t1;
#
# Bug#53676: Unexpected errors and possible table corruption on
# ADD PARTITION and LOCK TABLE
CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
INSERT INTO t1 VALUES (2, 2), (3, 3), (4, 4), (5, 5);
SET lock_wait_timeout = 2;
#Connection 1 locks the table
LOCK TABLE t1 READ;
# Connection 2 tries to add partitions:
# First attempt: lock wait timeout (as expected)
ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Second attempt: says that partition already exists
ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Check that we only can select, not insert/update/delete.
INSERT INTO t1 VALUES (NULL, 6), (NULL, 7), (10, 10), (11, 11);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UPDATE t1 SET i = 5 WHERE f = 2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM t1 WHERE i = 10;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM t1;
i f
2 2
3 3
4 4
5 5
# Connection 1 unlocks the table and locks it again:
UNLOCK TABLES;
LOCK TABLE t1 READ;
# Connection 2 tries again to add partitions:
# Third attempt: says that the table does not exist
ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Check table returns the same (not after fixing bug#56172!)
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t2 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
SET lock_wait_timeout = 2;
LOCK TABLE t2 READ;
ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE t2 ADD PARTITION PARTITIONS 2;
UNLOCK TABLES;
CHECK TABLE t2;
Table Op Msg_type Msg_text
test.t2 check status OK
SELECT * FROM t2;
i f
DROP TABLE t2;
CREATE TABLE t3 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
SET lock_wait_timeout = 2;
# Connection 1 locks the table
LOCK TABLE t3 READ;
# Connection 2 tries to add partitions (timeout):
ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET lock_wait_timeout = 2;
# Connection 3 tries to add partitions (partition already exists):
ALTER TABLE t3 ADD PARTITION PARTITIONS 2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Connection 4 tries to rename the table:
RENAME TABLE t3 TO t4;
# Connection 1 unlocks the table:
UNLOCK TABLES;
# Connection 4 gets error on rename:
# SHOW TABLES returns the table (not renamed):
SHOW TABLES;
Tables_in_test
t4
# Connection 5 attempts to read from the table (table does not exist):
SELECT * FROM t3;
ERROR 42S02: Table 'test.t3' doesn't exist
DROP TABLE t4;
# Test WRITE LOCK.
CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT )
ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2;
INSERT INTO t1 VALUES (3, 3), (4, 4);
SET lock_wait_timeout = 2;
#Connection 1 locks the table
LOCK TABLE t1 WRITE;
# Check that we still can SELECT, but not insert/update/delete.
# Check that we only can select, not insert/update/delete.
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (10, 10), (11, 11);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UPDATE t1 SET i = 5 WHERE f = 2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM t1 WHERE i = 10;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM t1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UNLOCK TABLES;
DROP TABLE t1;