You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1920 lines
57 KiB
1920 lines
57 KiB
drop table if exists t1, t2;
|
|
#
|
|
#BUG 18618561: FAILED ALTER TABLE ENGINE CHANGE WITH PARTITIONS
|
|
# CORRUPTS FRM
|
|
# After WL#8971, the test case was rewritten to use InnoDB.
|
|
CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE= INNODB PARTITION BY HASH(fld1)
|
|
PARTITIONS 5;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`fld1` int(11) NOT NULL,
|
|
PRIMARY KEY (`fld1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`fld1`)
|
|
PARTITIONS 5 */
|
|
ALTER TABLE t1 ENGINE= ARCHIVE;
|
|
ERROR 42000: The storage engine for the table doesn't support native partitioning
|
|
#After the patch, the ENGINE is correctly displayed as InnoDB
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`fld1` int(11) NOT NULL,
|
|
PRIMARY KEY (`fld1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`fld1`)
|
|
PARTITIONS 5 */
|
|
#Cleanup.
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#11763825/56590: PARTITIONS: FULLTEXT SEARCHES FAIL
|
|
# (bad/confusing error message)
|
|
# After WL#8971, the test case was rewritten to use InnoDB.
|
|
# The errors returned are different.
|
|
#
|
|
CREATE TABLE t1
|
|
(a INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
b VARCHAR(200),
|
|
c TEXT)
|
|
ENGINE=InnoDB
|
|
PARTITION BY HASH(a) PARTITIONS 1;
|
|
CREATE FULLTEXT INDEX msg ON t1 (b, c);
|
|
ERROR HY000: The used table type doesn't support FULLTEXT indexes
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1
|
|
(a INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
b VARCHAR(200),
|
|
c TEXT,
|
|
FULLTEXT (b, c))
|
|
ENGINE=InnoDB
|
|
PARTITION BY HASH(a) PARTITIONS 1;
|
|
ERROR HY000: The used table type doesn't support FULLTEXT indexes
|
|
#
|
|
# BUG#11933226 - 60681: CHECKSUM TABLE RETURNS 0 FOR PARTITIONED TABLE
|
|
# After WL#8971, the test case was rewritten to use InnoDB.
|
|
#
|
|
CREATE TABLE t1 (
|
|
i INT
|
|
)
|
|
ENGINE=InnoDB
|
|
PARTITION BY RANGE (i)
|
|
(PARTITION p3 VALUES LESS THAN (3),
|
|
PARTITION p5 VALUES LESS THAN (5),
|
|
PARTITION pMax VALUES LESS THAN MAXVALUE);
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6);
|
|
CHECKSUM TABLE t1;
|
|
Table Checksum
|
|
test.t1 2653438147
|
|
ALTER TABLE t1 CHECKSUM = 1;
|
|
CHECKSUM TABLE t1 EXTENDED;
|
|
Table Checksum
|
|
test.t1 2653438147
|
|
CHECKSUM TABLE t1;
|
|
Table Checksum
|
|
test.t1 2653438147
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`i` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CHECKSUM=1
|
|
/*!50100 PARTITION BY RANGE (`i`)
|
|
(PARTITION p3 VALUES LESS THAN (3) ENGINE = InnoDB,
|
|
PARTITION p5 VALUES LESS THAN (5) ENGINE = InnoDB,
|
|
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
DROP TABLE t1;
|
|
# Same test without partitioning
|
|
CREATE TABLE t1 (
|
|
i INT
|
|
) ENGINE=InnoDB;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`i` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6);
|
|
CHECKSUM TABLE t1;
|
|
Table Checksum
|
|
test.t1 2653438147
|
|
ALTER TABLE t1 CHECKSUM = 1;
|
|
CHECKSUM TABLE t1 EXTENDED;
|
|
Table Checksum
|
|
test.t1 2653438147
|
|
CHECKSUM TABLE t1;
|
|
Table Checksum
|
|
test.t1 2653438147
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`i` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CHECKSUM=1
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST
|
|
# INNODB PARTITION STATISTICS
|
|
#
|
|
CREATE TABLE t1
|
|
(a INT,
|
|
b varchar(64),
|
|
PRIMARY KEY (a),
|
|
KEY (b))
|
|
ENGINE = InnoDB
|
|
PARTITION BY RANGE (a)
|
|
SUBPARTITION BY HASH (a) SUBPARTITIONS 10
|
|
(PARTITION pNeg VALUES LESS THAN (0),
|
|
PARTITION p0 VALUES LESS THAN (1000),
|
|
PARTITION pMAX VALUES LESS THAN MAXVALUE);
|
|
# Only one row in the first 10 subpartitions
|
|
INSERT INTO t1 VALUES (-1, 'Only negative pk value');
|
|
INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'),
|
|
(10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'),
|
|
(20, '0'), (21, '1'), (22, '2'), (23, '3'),
|
|
(4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9');
|
|
INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 pNeg_pNegsp0,pNeg_pNegsp1,pNeg_pNegsp2,pNeg_pNegsp3,pNeg_pNegsp4,pNeg_pNegsp5,pNeg_pNegsp6,pNeg_pNegsp7,pNeg_pNegsp8,pNeg_pNegsp9,p0_p0sp0,p0_p0sp1,p0_p0sp2,p0_p0sp3,p0_p0sp4,p0_p0sp5,p0_p0sp6,p0_p0sp7,p0_p0sp8,p0_p0sp9,pMAX_pMAXsp0,pMAX_pMAXsp1,pMAX_pMAXsp2,pMAX_pMAXsp3,pMAX_pMAXsp4,pMAX_pMAXsp5,pMAX_pMAXsp6,pMAX_pMAXsp7,pMAX_pMAXsp8,pMAX_pMAXsp9 range PRIMARY,b b 263 NULL 8 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` between 'L' and 'N') and (`test`.`t1`.`a` > <cache>(-(100))))
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#13007154: Crash in keys_to_use_for_scanning with ORDER BY
|
|
# and PARTITIONING
|
|
#
|
|
CREATE TABLE t1 (a INT, KEY(a))
|
|
ENGINE = InnoDB
|
|
PARTITION BY KEY (a) PARTITIONS 1;
|
|
SELECT 1 FROM t1 WHERE a > (SELECT LAST_INSERT_ID() FROM t1 LIMIT 0)
|
|
ORDER BY a;
|
|
1
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#56287: crash when using Partition datetime in sub in query
|
|
#
|
|
CREATE TABLE t1
|
|
(c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
c2 varchar(40) not null default '',
|
|
c3 datetime not NULL,
|
|
PRIMARY KEY (c1,c3),
|
|
KEY partidx(c3))
|
|
ENGINE=InnoDB
|
|
PARTITION BY RANGE (TO_DAYS(c3))
|
|
(PARTITION p200912 VALUES LESS THAN (to_days('2010-01-01')),
|
|
PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),
|
|
PARTITION p201912 VALUES LESS THAN MAXVALUE);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
insert into t1(c2,c3) values ("Test row",'2010-01-01 00:00:00');
|
|
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test';
|
|
PARTITION_NAME TABLE_ROWS
|
|
p200912 0
|
|
p201103 1
|
|
p201912 0
|
|
SELECT count(*) FROM t1 p where c3 in
|
|
(SELECT c3 FROM t1 t WHERE t.c3 < TIMESTAMP'2011-04-26 19:19:44'
|
|
AND t.c3 > TIMESTAMP'2011-04-26 19:18:44') ;
|
|
count(*)
|
|
0
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#54747: Deadlock between REORGANIZE PARTITION and
|
|
# SELECT is not detected
|
|
#
|
|
SET @old_innodb_thread_concurrency := @@innodb_thread_concurrency;
|
|
SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay;
|
|
SET GLOBAL innodb_thread_concurrency = 2;
|
|
CREATE TABLE t1
|
|
(user_num BIGINT,
|
|
hours SMALLINT,
|
|
KEY user_num (user_num))
|
|
ENGINE = InnoDB
|
|
PARTITION BY RANGE COLUMNS (hours)
|
|
(PARTITION hour_003 VALUES LESS THAN (3),
|
|
PARTITION hour_004 VALUES LESS THAN (4),
|
|
PARTITION hour_005 VALUES LESS THAN (5),
|
|
PARTITION hour_last VALUES LESS THAN (MAXVALUE));
|
|
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
|
|
BEGIN;
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
5
|
|
# con1
|
|
# SEND a ALTER PARTITION which waits on the ongoing transaction.
|
|
ALTER TABLE t1
|
|
REORGANIZE PARTITION hour_003, hour_004 INTO
|
|
(PARTITION oldest VALUES LESS THAN (4));
|
|
# Connection default wait until the ALTER is in 'waiting for table...'
|
|
# state and then continue the transaction by trying a SELECT
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
5
|
|
COMMIT;
|
|
# con1, reaping ALTER.
|
|
# Disconnecting con1 and switching to default. Cleaning up.
|
|
SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency;
|
|
SET GLOBAL innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#50418: DROP PARTITION does not interact with transactions
|
|
#
|
|
CREATE TABLE t1 (
|
|
id INT AUTO_INCREMENT NOT NULL,
|
|
name CHAR(50) NOT NULL,
|
|
myDate DATE NOT NULL,
|
|
PRIMARY KEY (id, myDate),
|
|
INDEX idx_date (myDate)
|
|
) ENGINE=InnoDB
|
|
PARTITION BY RANGE ( TO_DAYS(myDate) ) (
|
|
PARTITION p0 VALUES LESS THAN (734028),
|
|
PARTITION p1 VALUES LESS THAN (734029),
|
|
PARTITION p2 VALUES LESS THAN (734030),
|
|
PARTITION p3 VALUES LESS THAN MAXVALUE
|
|
) ;
|
|
INSERT INTO t1 VALUES
|
|
(NULL, 'Lachlan', '2009-09-13'),
|
|
(NULL, 'Clint', '2009-09-13'),
|
|
(NULL, 'John', '2009-09-14'),
|
|
(NULL, 'Dave', '2009-09-14'),
|
|
(NULL, 'Jeremy', '2009-09-15'),
|
|
(NULL, 'Scott', '2009-09-15'),
|
|
(NULL, 'Jeff', '2009-09-16'),
|
|
(NULL, 'Joe', '2009-09-16');
|
|
SET AUTOCOMMIT=0;
|
|
SELECT * FROM t1 FOR UPDATE;
|
|
id name myDate
|
|
1 Lachlan 2009-09-13
|
|
2 Clint 2009-09-13
|
|
3 John 2009-09-14
|
|
4 Dave 2009-09-14
|
|
5 Jeremy 2009-09-15
|
|
6 Scott 2009-09-15
|
|
7 Jeff 2009-09-16
|
|
8 Joe 2009-09-16
|
|
UPDATE t1 SET name = 'Mattias' WHERE id = 7;
|
|
SELECT * FROM t1 WHERE id = 7;
|
|
id name myDate
|
|
7 Mattias 2009-09-16
|
|
# Connection con1
|
|
SET lock_wait_timeout = 1;
|
|
# After the patch it will wait and fail on timeout.
|
|
ALTER TABLE t1 DROP PARTITION p3;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1205 Lock wait timeout exceeded; try restarting transaction
|
|
# Connection default
|
|
SELECT * FROM t1;
|
|
id name myDate
|
|
1 Lachlan 2009-09-13
|
|
2 Clint 2009-09-13
|
|
3 John 2009-09-14
|
|
4 Dave 2009-09-14
|
|
5 Jeremy 2009-09-15
|
|
6 Scott 2009-09-15
|
|
7 Mattias 2009-09-16
|
|
8 Joe 2009-09-16
|
|
# No changes.
|
|
COMMIT;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#51830: Incorrect partition pruning on range partition (regression)
|
|
#
|
|
CREATE TABLE t1 (a INT NOT NULL)
|
|
ENGINE = InnoDB
|
|
PARTITION BY RANGE(a)
|
|
(PARTITION p10 VALUES LESS THAN (10),
|
|
PARTITION p30 VALUES LESS THAN (30),
|
|
PARTITION p50 VALUES LESS THAN (50),
|
|
PARTITION p70 VALUES LESS THAN (70),
|
|
PARTITION p90 VALUES LESS THAN (90));
|
|
INSERT INTO t1 VALUES (10),(30),(50);
|
|
INSERT INTO t1 VALUES (70);
|
|
INSERT INTO t1 VALUES (80);
|
|
INSERT INTO t1 VALUES (89);
|
|
INSERT INTO t1 VALUES (90);
|
|
ERROR HY000: Table has no partition for value 90
|
|
INSERT INTO t1 VALUES (100);
|
|
ERROR HY000: Table has no partition for value 100
|
|
insert INTO t1 VALUES (110);
|
|
ERROR HY000: Table has no partition for value 110
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT * FROM t1 WHERE a > 90;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 90)
|
|
EXPLAIN SELECT * FROM t1 WHERE a >= 90;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` >= 90)
|
|
EXPLAIN SELECT * FROM t1 WHERE a = 90;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 90)
|
|
EXPLAIN SELECT * FROM t1 WHERE a = 89;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 89)
|
|
EXPLAIN SELECT * FROM t1 WHERE a >= 89;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` >= 89)
|
|
EXPLAIN SELECT * FROM t1 WHERE a > 89;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 89)
|
|
EXPLAIN SELECT * FROM t1 WHERE a = 100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 100)
|
|
EXPLAIN SELECT * FROM t1 WHERE a >= 100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` >= 100)
|
|
EXPLAIN SELECT * FROM t1 WHERE a > 100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 100)
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#50104: Partitioned table with just 1 partion works with fk
|
|
#
|
|
CREATE TABLE t2 (
|
|
id INT,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB ;
|
|
CREATE TABLE t1 (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
parent_id INT DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
KEY parent_id (parent_id)
|
|
) ENGINE=InnoDB;
|
|
ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 1;
|
|
ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id);
|
|
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
|
|
ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 2;
|
|
ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id);
|
|
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
|
|
DROP TABLE t1, t2;
|
|
create table t1 (a varchar(5), b int signed, c varchar(10), d datetime)
|
|
partition by range columns(b,c)
|
|
subpartition by hash(to_seconds(d))
|
|
( partition p0 values less than (2, 'b'),
|
|
partition p1 values less than (4, 'd'),
|
|
partition p2 values less than (10, 'za'));
|
|
insert into t1 values ('a', 3, 'w', '2001-10-27 04:34:00');
|
|
insert into t1 values ('r', 7, 'w', '2001-10-27 05:34:00');
|
|
insert into t1 values ('g', 10, 'w', '2001-10-27 06:34:00');
|
|
update t1 set a = 'c' where a > 'f';
|
|
drop table t1;
|
|
create table t1 (a varchar(5))
|
|
engine=innodb
|
|
partition by range columns(a)
|
|
( partition p0 values less than ('m'),
|
|
partition p1 values less than ('za'));
|
|
insert into t1 values ('j');
|
|
update t1 set a = 'z' where (a >= 'j');
|
|
drop table t1;
|
|
create table t1 (a int not null,
|
|
b datetime not null,
|
|
primary key (a,b))
|
|
engine=innodb
|
|
partition by range (to_days(b))
|
|
subpartition by hash (a)
|
|
subpartitions 2
|
|
( partition p0 values less than (to_days('2009-01-01')),
|
|
partition p1 values less than (to_days('2009-02-01')),
|
|
partition p2 values less than (to_days('2009-03-01')),
|
|
partition p3 values less than maxvalue);
|
|
alter table t1 reorganize partition p1,p2 into
|
|
( partition p2 values less than (to_days('2009-03-01')));
|
|
drop table t1;
|
|
CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB
|
|
PARTITION BY RANGE(id) (
|
|
PARTITION p0 VALUES LESS THAN (5),
|
|
PARTITION p1 VALUES LESS THAN (10),
|
|
PARTITION p2 VALUES LESS THAN MAXVALUE
|
|
);
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8),
|
|
(9,9), (10,10), (11,11);
|
|
SET @old_transaction_isolation := @@session.transaction_isolation;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
SET autocommit = 0;
|
|
UPDATE t1 SET DATA = data*2 WHERE id = 3;
|
|
UPDATE t1 SET data = data*2 WHERE data = 2;
|
|
SET @@session.transaction_isolation = @old_transaction_isolation;
|
|
SET autocommit = default;
|
|
DROP TABLE t1;
|
|
# Bug#37721, test of ORDER BY on PK and WHERE on INDEX
|
|
CREATE TABLE t1 (
|
|
a INT,
|
|
b INT,
|
|
PRIMARY KEY (a),
|
|
INDEX (b))
|
|
ENGINE InnoDB
|
|
PARTITION BY HASH(a)
|
|
PARTITIONS 3;
|
|
INSERT INTO t1 VALUES (0,0),(4,0),(2,0);
|
|
SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC;
|
|
a
|
|
0
|
|
2
|
|
4
|
|
SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC;
|
|
a
|
|
4
|
|
2
|
|
0
|
|
ALTER TABLE t1 DROP INDEX b;
|
|
SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC;
|
|
a
|
|
0
|
|
2
|
|
4
|
|
SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC;
|
|
a
|
|
4
|
|
2
|
|
0
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
a VARCHAR(600),
|
|
b VARCHAR(600),
|
|
PRIMARY KEY (a),
|
|
INDEX (b))
|
|
ENGINE InnoDB
|
|
PARTITION BY KEY(a)
|
|
PARTITIONS 3;
|
|
INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'1'),repeat('0',257));
|
|
INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'3'),repeat('0',257));
|
|
INSERT INTO t1 VALUES (concat(repeat('MySQL',100),'2'),repeat('0',257));
|
|
SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC;
|
|
right(a,1)
|
|
1
|
|
2
|
|
3
|
|
SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC;
|
|
right(a,1)
|
|
3
|
|
2
|
|
1
|
|
ALTER TABLE t1 DROP INDEX b;
|
|
SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a ASC;
|
|
right(a,1)
|
|
1
|
|
2
|
|
3
|
|
SELECT right(a,1) FROM t1 WHERE b = repeat('0',257) ORDER BY a DESC;
|
|
right(a,1)
|
|
3
|
|
2
|
|
1
|
|
DROP TABLE t1;
|
|
create table t1 (a int) engine=innodb partition by hash(a) ;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show table status like 't1';
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 0 # # 0 0 # NULL # # NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
engine = innodb
|
|
partition by key (a);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 0 # # 0 0 # NULL # # NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
insert into t1 values (0), (1), (2), (3);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 4 # # 0 0 # NULL # # NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
drop table t1;
|
|
create table t1 (a int auto_increment primary key)
|
|
engine = innodb
|
|
partition by key (a);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 0 # # 0 0 # 1 # # NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
insert into t1 values (NULL), (NULL), (NULL), (NULL);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 4 # # 0 0 # 5 # # NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
insert into t1 values (NULL), (NULL), (NULL), (NULL);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 8 # # 0 0 # 9 # # NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by key (a)
|
|
(partition p1 engine = innodb);
|
|
alter table t1 rebuild partition p1;
|
|
alter table t1 rebuild partition p1;
|
|
alter table t1 rebuild partition p1;
|
|
alter table t1 rebuild partition p1;
|
|
alter table t1 rebuild partition p1;
|
|
alter table t1 rebuild partition p1;
|
|
alter table t1 rebuild partition p1;
|
|
drop table t1;
|
|
create table t1 (a date)
|
|
engine = innodb
|
|
partition by range (year(a))
|
|
(partition p0 values less than (2006),
|
|
partition p1 values less than (2007));
|
|
explain select * from t1
|
|
where a between '2006-01-01' and '2007-06-01';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` between '2006-01-01' and '2007-06-01')
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
engine = x
|
|
partition by key (a);
|
|
ERROR 42000: Unknown storage engine 'x'
|
|
create table t1 (a int)
|
|
engine = innodb
|
|
partition by list (a)
|
|
(partition p0 values in (0));
|
|
alter table t1 engine = x;
|
|
ERROR 42000: Unknown storage engine 'x'
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
create table t1
|
|
(
|
|
id int unsigned auto_increment,
|
|
time datetime not null,
|
|
first_name varchar(40),
|
|
last_name varchar(50),
|
|
primary key (id, time),
|
|
index first_index (first_name),
|
|
index last_index (last_name)
|
|
) engine=Innodb partition by range (to_days(time)) (
|
|
partition p1 values less than (to_days('2007-02-07')),
|
|
partition p2 values less than (to_days('2007-02-08')),
|
|
partition p3 values less than MAXVALUE
|
|
);
|
|
insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert'),
|
|
('2007-02-07', 'Mark', 'Nate'), ('2007-02-07', 'Nate', 'Oscar'),
|
|
('2007-02-07', 'Zack', 'Alice'), ('2007-02-07', 'Jack', 'Kathy'),
|
|
('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Brian', 'Charles'),
|
|
('2007-02-06', 'Charles', 'David'), ('2007-02-06', 'David', 'Eric'),
|
|
('2007-02-07', 'Hector', 'Isaac'), ('2007-02-07', 'Oscar', 'Patricia'),
|
|
('2007-02-07', 'Patricia', 'Q'), ('2007-02-07', 'X', 'Yuri'),
|
|
('2007-02-07', 'Robert', 'Shawn'), ('2007-02-07', 'Kathy', 'Lois'),
|
|
('2007-02-07', 'Eric', 'Francis'), ('2007-02-06', 'Shawn', 'Theron'),
|
|
('2007-02-06', 'U', 'Vincent'), ('2007-02-06', 'Francis', 'George'),
|
|
('2007-02-06', 'George', 'Hector'), ('2007-02-06', 'Vincent', 'Walter'),
|
|
('2007-02-06', 'Walter', 'X'), ('2007-02-07', 'Lois', 'Mark'),
|
|
('2007-02-07', 'Yuri', 'Zack'), ('2007-02-07', 'Isaac', 'Jack'),
|
|
('2007-02-07', 'Sharon', 'Mark'), ('2007-02-07', 'Michael', 'Michelle'),
|
|
('2007-02-07', 'Derick', 'Nathan'), ('2007-02-07', 'Peter', 'Xavier'),
|
|
('2007-02-07', 'Fred', 'Harold'), ('2007-02-07', 'Katherine', 'Lisa'),
|
|
('2007-02-07', 'Tom', 'Rina'), ('2007-02-07', 'Jerry', 'Victor'),
|
|
('2007-02-07', 'Alexander', 'Terry'), ('2007-02-07', 'Justin', 'John'),
|
|
('2007-02-07', 'Greg', 'Ernest'), ('2007-02-07', 'Robert', 'Q'),
|
|
('2007-02-07', 'Nate', 'Mark'), ('2007-02-07', 'Oscar', 'Nate'),
|
|
('2007-02-07', 'Alice', 'Zack'), ('2007-02-07', 'Kathy', 'Jack'),
|
|
('2007-02-06', 'Alice', 'Alice'), ('2007-02-06', 'Charles', 'Brian'),
|
|
('2007-02-06', 'David', 'Charles'), ('2007-02-06', 'Eric', 'David'),
|
|
('2007-02-07', 'Isaac', 'Hector'), ('2007-02-07', 'Patricia', 'Oscar'),
|
|
('2007-02-07', 'Q', 'Patricia'), ('2007-02-07', 'Yuri', 'X'),
|
|
('2007-02-07', 'Shawn', 'Robert'), ('2007-02-07', 'Lois', 'Kathy'),
|
|
('2007-02-07', 'Francis', 'Eric'), ('2007-02-06', 'Theron', 'Shawn'),
|
|
('2007-02-06', 'Vincent', 'U'), ('2007-02-06', 'George', 'Francis'),
|
|
('2007-02-06', 'Hector', 'George'), ('2007-02-06', 'Walter', 'Vincent'),
|
|
('2007-02-06', 'X', 'Walter'), ('2007-02-07', 'Mark', 'Lois'),
|
|
('2007-02-07', 'Zack', 'Yuri'), ('2007-02-07', 'Jack', 'Isaac'),
|
|
('2007-02-07', 'Mark', 'Sharon'), ('2007-02-07', 'Michelle', 'Michael'),
|
|
('2007-02-07', 'Nathan', 'Derick'), ('2007-02-07', 'Xavier', 'Peter'),
|
|
('2007-02-07', 'Harold', 'Fred'), ('2007-02-07', 'Lisa', 'Katherine'),
|
|
('2007-02-07', 'Rina', 'Tom'), ('2007-02-07', 'Victor', 'Jerry'),
|
|
('2007-02-07', 'Terry', 'Alexander'), ('2007-02-07', 'John', 'Justin'),
|
|
('2007-02-07', 'Ernest', 'Greg');
|
|
SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake';
|
|
id time first_name last_name
|
|
drop table t1;
|
|
CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB
|
|
PARTITION BY KEY(a) PARTITIONS 10;
|
|
INSERT INTO t1 VALUES(1),(2);
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
2
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB
|
|
PARTITION BY list(a) (PARTITION p1 VALUES IN (1));
|
|
CREATE INDEX i1 ON t1 (a);
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#54783: optimize table crashes with invalid timestamp default value and NO_ZERO_DATE
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT '0000-00-00 00:00:00')
|
|
ENGINE=INNODB PARTITION BY LINEAR HASH (a) PARTITIONS 1;
|
|
SET @old_mode = @@sql_mode;
|
|
SET SESSION sql_mode = '';
|
|
OPTIMIZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t1 optimize status OK
|
|
SET SESSION sql_mode = @old_mode;
|
|
DROP TABLE t1;
|
|
SET sql_mode = default;
|
|
#
|
|
# Bug#57985 "ONLINE/FAST ALTER PARTITION can fail and leave the
|
|
# table unusable".
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a bigint not null, b int not null, PRIMARY KEY (a))
|
|
ENGINE = InnoDB PARTITION BY KEY(a) PARTITIONS 2;
|
|
INSERT INTO t1 values (0,1), (1,2);
|
|
# The below ALTER should fail. It should leave the
|
|
# table in its original, non-corrupted, usable state.
|
|
ALTER TABLE t1 ADD UNIQUE KEY (b);
|
|
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
|
|
# The below statements should succeed, as ALTER should
|
|
# have left table intact.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
PARTITIONS 2 */
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 2
|
|
0 1
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#16943907: FLUSH TABLES FOR EXPORT: ASSERTION IN HA_PARTITION::EXTRA
|
|
#
|
|
CREATE TABLE t1 (a int, PRIMARY KEY (a)) ENGINE=InnoDB
|
|
PARTITION BY HASH (a) PARTITIONS 2;
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
# List of files after EXPORT (should include a .cfg file for each part).
|
|
t1#P#p0.cfg
|
|
t1#P#p0.ibd
|
|
t1#P#p1.cfg
|
|
t1#P#p1.ibd
|
|
# Copying the .cfg and .ibd files as backup
|
|
UNLOCK TABLES;
|
|
# List of files after UNLOCK (no .cfg files).
|
|
t1#P#p0.ibd
|
|
t1#P#p1.ibd
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
# List of files after DISCARD (no .cfg/.ibd files).
|
|
# Moving the .cfg and .ibd files back from backup
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
# List of files after IMPORT (.cfg files still there).
|
|
t1#P#p0.cfg
|
|
t1#P#p0.ibd
|
|
t1#P#p1.cfg
|
|
t1#P#p1.ibd
|
|
DROP TABLE t1;
|
|
# List of files after DROP (.cfg files should also be removed).
|
|
#
|
|
# Bug#13737949: CRASH IN HA_PARTITION::INDEX_INIT
|
|
# Bug#18694052: SERVER CRASH IN HA_PARTITION::INIT_RECORD_PRIORITY_QUEUE
|
|
#
|
|
CREATE TABLE t1
|
|
(a INT,
|
|
b INT,
|
|
PRIMARY KEY (a))
|
|
ENGINE = InnoDB
|
|
PARTITION BY HASH (a) PARTITIONS 3;
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
# con1
|
|
ALTER TABLE t1 ADD INDEX idx1 (b);
|
|
# con default
|
|
SELECT b FROM t1 WHERE b = 0;
|
|
ERROR HY000: Table definition has changed, please retry transaction
|
|
SELECT b FROM t1 WHERE b = 0;
|
|
ERROR HY000: Table definition has changed, please retry transaction
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Table definition has changed, please retry transaction
|
|
DROP TABLE t1;
|
|
# Same test without partitioning
|
|
CREATE TABLE t1
|
|
(a INT,
|
|
b INT,
|
|
PRIMARY KEY (a))
|
|
ENGINE = InnoDB;
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
# con1
|
|
ALTER TABLE t1 ADD INDEX idx1 (b);
|
|
# con default
|
|
SELECT b FROM t1 WHERE b = 0;
|
|
ERROR HY000: Table definition has changed, please retry transaction
|
|
SELECT b FROM t1 WHERE b = 0;
|
|
ERROR HY000: Table definition has changed, please retry transaction
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Table definition has changed, please retry transaction
|
|
DROP TABLE t1;
|
|
Bug 17896265 PARTITIONED TABLE HAS MISPLACED ROWS, AFTER INPLACE ALTER
|
|
CREATE TABLE t1 (
|
|
f1 INT(11) NOT NULL,
|
|
f2 INT(11) NOT NULL
|
|
)
|
|
ENGINE=InnoDB
|
|
PARTITION BY KEY (f1,f2) PARTITIONS 2;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES (9585,5);
|
|
ALTER TABLE t1 CHANGE f1 f1 INT AFTER f2, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
CREATE TABLE t2 (
|
|
f1 INT(11) NOT NULL,
|
|
f2 INT(11) NOT NULL,
|
|
f3 INT(11) NOT NULL,
|
|
f4 INT(11) NOT NULL
|
|
)
|
|
ENGINE=InnoDB
|
|
PARTITION BY KEY (f2,f3) PARTITIONS 2;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t2 VALUES (10,9585,5,20);
|
|
ALTER TABLE t2 CHANGE f3 f3 INT AFTER f4, ALGORITHM=INPLACE;
|
|
CHECK TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
ALTER TABLE t2 CHANGE f3 f3 INT AFTER f1, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
ALTER TABLE t2 CHANGE f4 f4 INT AFTER f3, ALGORITHM=INPLACE;
|
|
CHECK TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
ALTER TABLE t2 CHANGE f1 f1 INT AFTER f4, ALGORITHM=INPLACE;
|
|
CHECK TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
ALTER TABLE t2 CHANGE f1 f1 INT AFTER f2, ALGORITHM=INPLACE;
|
|
CHECK TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
ALTER TABLE t2 CHANGE f2 f2 INT AFTER f4, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
CREATE TABLE t3 (f1 INT,f2 INT) ENGINE=INNODB
|
|
PARTITION BY RANGE(f1) (
|
|
PARTITION p0 VALUES LESS THAN (100),
|
|
PARTITION p1 VALUES LESS THAN (200),
|
|
PARTITION p2 VALUES LESS THAN (600),
|
|
PARTITION p3 VALUES LESS THAN MAXVALUE
|
|
);
|
|
insert into t3 values (90,120);
|
|
insert into t3 values (120,300);
|
|
ALTER TABLE t3 CHANGE f1 f1 int AFTER f2, ALGORITHM=INPLACE;
|
|
CHECK TABLE t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t3 check status OK
|
|
CREATE TABLE t4 (
|
|
f1 INT(11) NOT NULL,
|
|
f2 INT(11) NOT NULL
|
|
)
|
|
ENGINE=InnoDB
|
|
PARTITION BY HASH (MOD(f1,f2)) PARTITIONS 2;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t4 VALUES (9585,5);
|
|
ALTER TABLE t4 CHANGE f1 f1 INT AFTER f2, ALGORITHM=INPLACE;
|
|
CHECK TABLE t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t4 check status OK
|
|
CREATE TABLE t5 (
|
|
f1 INT,
|
|
f2 INT
|
|
)
|
|
ENGINE=InnoDB
|
|
PARTITION BY RANGE COLUMNS(f1,f2) (
|
|
PARTITION p0 VALUES LESS THAN (10000,12),
|
|
PARTITION p1 VALUES LESS THAN (MAXVALUE, MAXVALUE)
|
|
);
|
|
INSERT INTO t5 VALUES (1,20000);
|
|
ALTER TABLE t5 CHANGE f1 f1 INT AFTER f2, ALGORITHM=INPLACE;
|
|
CHECK TABLE t5;
|
|
Table Op Msg_type Msg_text
|
|
test.t5 check status OK
|
|
CREATE TABLE t6 (
|
|
a INT,
|
|
b INT
|
|
)
|
|
ENGINE=InnoDB
|
|
PARTITION BY RANGE COLUMNS(a,b)
|
|
SUBPARTITION BY KEY(a,b)
|
|
SUBPARTITIONS 2 (
|
|
PARTITION p0 VALUES LESS THAN (10000,12),
|
|
PARTITION p1 VALUES LESS THAN (MAXVALUE, MAXVALUE)
|
|
);
|
|
INSERT INTO t6 VALUES (9585,5);
|
|
ALTER TABLE t6 CHANGE a a INT AFTER b, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
CREATE TABLE t7 (
|
|
f1 INT(11) NOT NULL,
|
|
f2 INT(11) NOT NULL,
|
|
f3 INT(11) NOT NULL,
|
|
f4 INT(11) NOT NULL,
|
|
f5 INT(11) NOT NULL
|
|
)
|
|
ENGINE=InnoDB
|
|
PARTITION BY KEY (f1,f5) PARTITIONS 2;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t7 VALUES (9585,10,20,10,5);
|
|
ALTER TABLE t7 CHANGE f5 f5 INT AFTER f3, ALGORITHM=INPLACE;
|
|
CHECK TABLE t7;
|
|
Table Op Msg_type Msg_text
|
|
test.t7 check status OK
|
|
ALTER TABLE t7 CHANGE f5 f5 INT AFTER f2, ALGORITHM=INPLACE;
|
|
CHECK TABLE t7;
|
|
Table Op Msg_type Msg_text
|
|
test.t7 check status OK
|
|
ALTER TABLE t7 CHANGE f1 f1 INT AFTER f4, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
DROP TABLE t1,t2,t3,t4,t5,t6,t7;
|
|
# Coverage tests for Native InnoDB Partitioning
|
|
#
|
|
# Test enable/disable keys.
|
|
#
|
|
CREATE TABLE t1 (a int, b int, primary key (a), key (b))
|
|
ENGINE = InnoDB
|
|
PARTITION BY HASH (a) PARTITIONS 3;
|
|
INSERT INTO t1 VALUES (1,1),(2,1),(3,3),(4,1),(5,3),(6,1),(7,1),(8,1),(9,4),
|
|
(10,1),(11,3),(12,1),(13,3),(14,1),(15,1),(16,3),(17,1),(18,1),(19,1),(20,3);
|
|
EXPLAIN SELECT * FROM t1 WHERE b = 4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2 ref b b 5 const 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` = 4)
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
Warnings:
|
|
Note 1031 Table storage engine for 't1' doesn't have this option
|
|
INSERT INTO t1 VALUES (21,1),(22,1),(23,3),(24,1);
|
|
EXPLAIN SELECT * FROM t1 WHERE b = 4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2 ref b b 5 const 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` = 4)
|
|
ALTER TABLE t1 ENABLE KEYS;
|
|
Warnings:
|
|
Note 1031 Table storage engine for 't1' doesn't have this option
|
|
EXPLAIN SELECT * FROM t1 WHERE b = 4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2 ref b b 5 const 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` = 4)
|
|
DROP TABLE t1;
|
|
#
|
|
# Test with different key sizes
|
|
#
|
|
CREATE TABLE t1
|
|
(a int NOT NULL,
|
|
b int NOT NULL,
|
|
c varchar(10) NOT NULL,
|
|
INDEX(a),
|
|
UNIQUE KEY (c(5), a, b)
|
|
)
|
|
ENGINE=InnoDB
|
|
PARTITION BY HASH (b) PARTITIONS 2;
|
|
SELECT * FROM t1 WHERE a = '92' AND c = '0.73';
|
|
a b c
|
|
SELECT * FROM t1 WHERE a = '1224';
|
|
a b c
|
|
DROP TABLE t1;
|
|
#
|
|
# Test with index_merge using PK
|
|
#
|
|
CREATE TABLE t1
|
|
(
|
|
a int NOT NULL,
|
|
b int NOT NULL DEFAULT 2,
|
|
c int NOT NULL DEFAULT 3,
|
|
PRIMARY KEY (a),
|
|
INDEX i2(b),
|
|
INDEX i3(c)
|
|
)
|
|
ENGINE = InnoDB
|
|
PARTITION BY HASH (a) PARTITIONS 3;
|
|
INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8);
|
|
INSERT INTO t1 (a) SELECT a+8 FROM t1;
|
|
UPDATE t1 SET b=a,c=a;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT * FROM t1 WHERE a=3 OR b=4;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2 index_merge PRIMARY,i2 PRIMARY,i2 4,4 NULL # 100.00 Using union(PRIMARY,i2); Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 3) or (`test`.`t1`.`b` = 4))
|
|
SELECT * FROM t1 WHERE a=3 OR b=4;
|
|
a b c
|
|
3 3 3
|
|
4 4 4
|
|
DROP TABLE t1;
|
|
#
|
|
# Test error handling in mysql_admin
|
|
#
|
|
CREATE TABLE t1 (a int)
|
|
PARTITION BY LINEAR HASH (a) PARTITIONS 8;
|
|
LOAD INDEX INTO CACHE t1 PARTITION (ALL);
|
|
Table Op Msg_type Msg_text
|
|
test.t1 preload_keys note The storage engine for the table doesn't support preload_keys
|
|
ALTER TABLE t1 COALESCE PARTITION 2;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug #17299181 CREATE_TIME AND UPDATE_TIME ARE
|
|
# WRONG FOR PARTITIONED TABLES
|
|
#
|
|
CREATE TABLE t1 (a int, PRIMARY KEY (a)) ENGINE=InnoDB
|
|
PARTITION BY HASH (a) PARTITIONS 2;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE
|
|
CREATE_TIME IS NOT NULL AND TABLE_NAME='t1';
|
|
COUNT(*)
|
|
1
|
|
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE
|
|
CREATE_TIME IS NOT NULL AND UPDATE_TIME IS NOT NULL
|
|
AND TABLE_NAME='t1' OR FALSE;
|
|
COUNT(*)
|
|
1
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#20160327 OPTIMIZE TABLE REMOVES THE DATA DIRECTORY IN PARTITIONS
|
|
#
|
|
CREATE TABLE `t1` (
|
|
`f1` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`f2` MEDIUMTEXT NOT NULL,
|
|
`f3` CHAR(100) NOT NULL,
|
|
`f4` TINYINT(1) unsigned NOT NULL,
|
|
PRIMARY KEY (`f1`,`f4`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1
|
|
PARTITION BY LIST (`f4`)
|
|
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES IN (1) DATA DIRECTORY = 'MYSQL_TMP_DIR/temp_dir' ENGINE = InnoDB);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`f2` mediumtext NOT NULL,
|
|
`f3` char(100) NOT NULL,
|
|
`f4` tinyint(1) unsigned NOT NULL,
|
|
PRIMARY KEY (`f1`,`f4`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY LIST (`f4`)
|
|
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES IN (1) DATA DIRECTORY = 'MYSQL_TMP_DIR/temp_dir/' ENGINE = InnoDB) */
|
|
OPTIMIZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t1 optimize status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`f2` mediumtext NOT NULL,
|
|
`f3` char(100) NOT NULL,
|
|
`f4` tinyint(1) unsigned NOT NULL,
|
|
PRIMARY KEY (`f1`,`f4`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY LIST (`f4`)
|
|
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES IN (1) DATA DIRECTORY = 'MYSQL_TMP_DIR/temp_dir/' ENGINE = InnoDB) */
|
|
t1#p#p1.ibd
|
|
t1#p#p0.ibd
|
|
ALTER TABLE t1 OPTIMIZE PARTITION p0;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
|
|
test.t1 optimize status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`f2` mediumtext NOT NULL,
|
|
`f3` char(100) NOT NULL,
|
|
`f4` tinyint(1) unsigned NOT NULL,
|
|
PRIMARY KEY (`f1`,`f4`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY LIST (`f4`)
|
|
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES IN (1) DATA DIRECTORY = 'MYSQL_TMP_DIR/temp_dir/' ENGINE = InnoDB) */
|
|
t1#p#p1.ibd
|
|
t1#p#p0.ibd
|
|
ALTER TABLE t1 OPTIMIZE PARTITION p1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
|
|
test.t1 optimize status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`f2` mediumtext NOT NULL,
|
|
`f3` char(100) NOT NULL,
|
|
`f4` tinyint(1) unsigned NOT NULL,
|
|
PRIMARY KEY (`f1`,`f4`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY LIST (`f4`)
|
|
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES IN (1) DATA DIRECTORY = 'MYSQL_TMP_DIR/temp_dir/' ENGINE = InnoDB) */
|
|
t1#p#p1.ibd
|
|
t1#p#p0.ibd
|
|
ALTER TABLE t1 REBUILD PARTITION ALL;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`f2` mediumtext NOT NULL,
|
|
`f3` char(100) NOT NULL,
|
|
`f4` tinyint(1) unsigned NOT NULL,
|
|
PRIMARY KEY (`f1`,`f4`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY LIST (`f4`)
|
|
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES IN (1) DATA DIRECTORY = 'MYSQL_TMP_DIR/temp_dir/' ENGINE = InnoDB) */
|
|
t1#p#p1.ibd
|
|
t1#p#p0.ibd
|
|
ALTER TABLE t1 ADD extracol VARCHAR(32) NULL;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`f1` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`f2` mediumtext NOT NULL,
|
|
`f3` char(100) NOT NULL,
|
|
`f4` tinyint(1) unsigned NOT NULL,
|
|
`extracol` varchar(32) DEFAULT NULL,
|
|
PRIMARY KEY (`f1`,`f4`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY LIST (`f4`)
|
|
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES IN (1) DATA DIRECTORY = 'MYSQL_TMP_DIR/temp_dir' ENGINE = InnoDB) */
|
|
t1#p#p1.ibd
|
|
t1#p#p0.ibd
|
|
DROP TABLE t1;
|
|
|
|
Bug #25942592 INNODB: FAILING ASSERTION: INDEX->PAGE != 0XFFFFFFFF
|
|
IN BTR0CUR.CC LINE 816
|
|
|
|
call mtr.add_suppression("Missing .ibd file for table");
|
|
CREATE TABLE t1(c1 INT,c2 CHAR (1),c3 DATE) ENGINE=InnoDB PARTITION BY HASH
|
|
(TO_DAYS(c3)) PARTITIONS 12;
|
|
ALTER TABLE t1 ADD INDEX(c1);
|
|
ALTER TABLE t1 DISCARD PARTITION p2 TABLESPACE;
|
|
SELECT COUNT(*)FROM t1,t1 AS b WHERE t1.c1=''AND t1.c2=b.c1;
|
|
ERROR HY000: Tablespace has been discarded for table 't1'
|
|
CREATE TABLE t2 (c1 int,c2 CHAR (1),c3 date,key(c1)) ENGINE=InnoDB
|
|
PARTITION BY RANGE (TO_DAYS(c3))
|
|
(
|
|
PARTITION p0 VALUES LESS THAN (TO_DAYS('1979-01-01')),
|
|
PARTITION p1 VALUES LESS THAN (TO_DAYS('1989-01-01')),
|
|
PARTITION p2 VALUES LESS THAN (TO_DAYS('1999-01-01'))
|
|
);
|
|
ALTER TABLE t2 DISCARD PARTITION p2 TABLESPACE;
|
|
SELECT COUNT(*)FROM t2,t2 AS b WHERE t2.c1=''AND t2.c2=b.c1;
|
|
ERROR HY000: Tablespace has been discarded for table 't2'
|
|
DROP TABLE t1,t2;
|
|
|
|
Bug #26034430 ASSERTION `PART_REC_BUF_PTR' FAILED.
|
|
|
|
|
|
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, PRIMARY KEY(a,b)) PARTITION BY RANGE (a)(PARTITION x1 VALUES LESS THAN (1));
|
|
SELECT * FROM t1 WHERE (a = 1 and b = 1 and c = 'b') OR (a > 2) ORDER BY a DESC;
|
|
a b c
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'b'
|
|
CREATE TABLE t(id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, dttm DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, msg
|
|
TEXT,PRIMARY KEY (id,dttm))ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 ROW_FORMAT=DYNAMIC PARTITION BY RANGE
|
|
COLUMNS(dttm) (PARTITION pf_201612 VALUES LESS THAN ('20170101') ENGINE = INNODB);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
SELECT * FROM t WHERE dttm > '2017-01-19' ORDER BY id DESC;
|
|
id dttm msg
|
|
DROP TABLE t,t1;
|
|
|
|
Bug #26731025 INNODB PARTITION TABLE WILL LOCK INTO THE NEAR RECORD AS A CONDITION IN THE USE
|
|
|
|
|
|
create table test12 (name varchar(30),age bigint,grade bigint,date datetime
|
|
not null,partition_id int not null,
|
|
PRIMARY KEY (`name`,`PARTITION_ID`),
|
|
KEY `IDX_DATE` (`date`,`name`,`partition_id`),
|
|
KEY `IDX_AGE` (`age`,`partition_id`) ) PARTITION BY RANGE
|
|
COLUMNS(PARTITION_ID)
|
|
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
|
|
PARTITION P1 VALUES LESS THAN (1) ENGINE = InnoDB,
|
|
PARTITION P2 VALUES LESS THAN (2) ENGINE = InnoDB,
|
|
PARTITION P3 VALUES LESS THAN (3) ENGINE = InnoDB,
|
|
PARTITION P4 VALUES LESS THAN (4) ENGINE = InnoDB,
|
|
PARTITION P5 VALUES LESS THAN (5) ENGINE = InnoDB,
|
|
PARTITION P6 VALUES LESS THAN (6) ENGINE = InnoDB,
|
|
PARTITION P7 VALUES LESS THAN (7) ENGINE = InnoDB,
|
|
PARTITION P8 VALUES LESS THAN (8) ENGINE = InnoDB,
|
|
PARTITION P9 VALUES LESS THAN (9) ENGINE = InnoDB);
|
|
insert into test12 values('Tom1',20,1,'2017-08-25
|
|
12:12:12',1),('Tom2',21,1,'2017-08-25 12:12:12',1),('Tom3',23,1,'2017-08-25
|
|
12:12:12',1);
|
|
begin;
|
|
update test12 set grade=3,date=now() where age=23 and
|
|
partition_id=1;
|
|
begin;
|
|
update test12 set grade=1,date=now() where age=21 and
|
|
partition_id=1;
|
|
begin;
|
|
update test12 set grade=0,date=now() where age=20 and
|
|
partition_id=1;
|
|
drop table test12;
|
|
#
|
|
# Bug#27073100: PARTITION UPDATE_TIME NOT CORRECT AFTER RESTART/REBUILD
|
|
#
|
|
# Set up.
|
|
CREATE TABLE t1 (fld1 INT(11) NOT NULL, fld2 INT(11) NOT NULL,
|
|
fld3 DATE NOT NULL, fld4 DATE NOT NULL,
|
|
PRIMARY KEY (fld1, fld3), KEY(fld1)) PARTITION
|
|
BY RANGE (YEAR(fld3))
|
|
(PARTITION p01 VALUES LESS THAN (1985) ENGINE = InnoDB,
|
|
PARTITION p02 VALUES LESS THAN (1986) ENGINE = InnoDB,
|
|
PARTITION p03 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES('11', '11111', '1984-11-16',
|
|
'2008-11-16'), ('12', '11112', '1985-11-16', '2008-11-16'),
|
|
('13', '11113', '1986-11-16', '2008-11-16');
|
|
# Fetch the count of 'update_time' which is not null.
|
|
SELECT TABLE_NAME, COUNT(UPDATE_TIME) FROM INFORMATION_SCHEMA.PARTITIONS
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1' ORDER BY
|
|
PARTITION_ORDINAL_POSITION;
|
|
TABLE_NAME COUNT(UPDATE_TIME)
|
|
t1 3
|
|
# Rebuild the table.
|
|
ALTER TABLE t1 ENGINE=INNODB;
|
|
# After table rebuild, the 'update_time' will be set to NULL.
|
|
# Hence the count would return 0.
|
|
SELECT TABLE_NAME, COUNT(UPDATE_TIME) FROM INFORMATION_SCHEMA.PARTITIONS
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1' ORDER BY
|
|
PARTITION_ORDINAL_POSITION;
|
|
TABLE_NAME COUNT(UPDATE_TIME)
|
|
t1 0
|
|
# Update the table where partition p02 is updated.
|
|
UPDATE t1 SET fld2 = 71000 WHERE fld1 = 12 AND fld3 = '1985-11-16';
|
|
# Without fix, the count for 'update_time' will be 3. After fix,
|
|
# it will be 1 since only partition 'p02' is updated.
|
|
SELECT TABLE_NAME, COUNT(UPDATE_TIME)
|
|
FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND
|
|
TABLE_NAME = 't1' ORDER BY PARTITION_ORDINAL_POSITION;
|
|
TABLE_NAME COUNT(UPDATE_TIME)
|
|
t1 1
|
|
DROP TABLE t1;
|
|
|
|
BUG#26553164 - INNODB PARTITION TABLE HAS UNEXPECTED ROW LOCK
|
|
|
|
CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL,
|
|
`dt` datetime NOT NULL,
|
|
`data` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`id`,`dt`),
|
|
KEY `idx_dt` (`dt`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY RANGE (to_days(dt))
|
|
(PARTITION p20170218 VALUES LESS THAN (736744) ENGINE = InnoDB,
|
|
PARTITION p20170219 VALUES LESS THAN (736745) ENGINE = InnoDB,
|
|
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES (1, '2018-04-25 10:18:00', '1');
|
|
INSERT INTO t1 VALUES (2, '2018-04-25 10:18:01', '2');
|
|
INSERT INTO t1 VALUES (3, '2018-04-25 10:18:02', '3');
|
|
SELECT * FROM t1;
|
|
id dt data
|
|
1 2018-04-25 10:18:00 1
|
|
2 2018-04-25 10:18:01 2
|
|
3 2018-04-25 10:18:02 3
|
|
begin;
|
|
UPDATE t1 SET data = '11' WHERE id = 1;
|
|
begin;
|
|
UPDATE t1 SET data = '22' WHERE id = 2;
|
|
commit;
|
|
SELECT * FROM t1;
|
|
id dt data
|
|
1 2018-04-25 10:18:00 11
|
|
2 2018-04-25 10:18:01 22
|
|
3 2018-04-25 10:18:02 3
|
|
DROP TABLE t1;
|
|
#
|
|
# WL#1074 - Descending index support.
|
|
#
|
|
CREATE TABLE t1
|
|
(a VARCHAR(10),
|
|
b VARCHAR(10),
|
|
PRIMARY KEY (a DESC, b DESC),
|
|
KEY ab_asc (a ASC, b ASC),
|
|
KEY a_asc_b_desc (a ASC, b DESC),
|
|
key a_desc_b_asc (a DESC, b ASC))
|
|
ENGINE = InnoDB
|
|
PARTITION BY KEY (a, b) PARTITIONS 3;
|
|
INSERT INTO t1 VALUES ("0", "0"), ("1", "1"), ("2", "2"), ("3", "3"),
|
|
("4", "4"), ("55", "55"), ("54", "54"), ("1", "2"), ("1", "4"), ("1", "3"),
|
|
("55", "54"), ("0", "1");
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
# Test non ordered index access
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 FORCE INDEX (`PRIMARY`);
|
|
a b
|
|
4 4
|
|
3 3
|
|
2 2
|
|
1 4
|
|
1 3
|
|
1 2
|
|
1 1
|
|
0 1
|
|
0 0
|
|
55 55
|
|
55 54
|
|
54 54
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 3
|
|
Handler_external_lock 6
|
|
Handler_read_first 3
|
|
Handler_read_key 5
|
|
Handler_read_next 12
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 FORCE INDEX (`ab_asc`);
|
|
a b
|
|
0 0
|
|
0 1
|
|
1 1
|
|
1 2
|
|
1 3
|
|
1 4
|
|
2 2
|
|
3 3
|
|
4 4
|
|
54 54
|
|
55 54
|
|
55 55
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 FORCE INDEX (`a_asc_b_desc`);
|
|
a b
|
|
0 1
|
|
0 0
|
|
1 4
|
|
1 3
|
|
1 2
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
54 54
|
|
55 55
|
|
55 54
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 FORCE INDEX (`a_desc_b_asc`);
|
|
a b
|
|
4 4
|
|
3 3
|
|
2 2
|
|
1 1
|
|
1 2
|
|
1 3
|
|
1 4
|
|
0 0
|
|
0 1
|
|
55 54
|
|
55 55
|
|
54 54
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
# Test sorted index access
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 ORDER BY a DESC, b DESC;
|
|
a b
|
|
55 55
|
|
55 54
|
|
54 54
|
|
4 4
|
|
3 3
|
|
2 2
|
|
1 4
|
|
1 3
|
|
1 2
|
|
1 1
|
|
0 1
|
|
0 0
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 ORDER BY a, b;
|
|
a b
|
|
0 0
|
|
0 1
|
|
1 1
|
|
1 2
|
|
1 3
|
|
1 4
|
|
2 2
|
|
3 3
|
|
4 4
|
|
54 54
|
|
55 54
|
|
55 55
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 ORDER BY a, b DESC;
|
|
a b
|
|
0 1
|
|
0 0
|
|
1 4
|
|
1 3
|
|
1 2
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
54 54
|
|
55 55
|
|
55 54
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 ORDER BY a DESC, b;
|
|
a b
|
|
55 54
|
|
55 55
|
|
54 54
|
|
4 4
|
|
3 3
|
|
2 2
|
|
1 1
|
|
1 2
|
|
1 3
|
|
1 4
|
|
0 0
|
|
0 1
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 ORDER BY a DESC, b DESC;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "X"
|
|
},
|
|
"ordering_operation": {
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"partitions": [
|
|
"p0",
|
|
"p1",
|
|
"p2"
|
|
],
|
|
"access_type": "index",
|
|
"key": "PRIMARY",
|
|
"used_key_parts": [
|
|
"a",
|
|
"b"
|
|
],
|
|
"key_length": "84",
|
|
"rows_examined_per_scan": "X",
|
|
"rows_produced_per_join": "X",
|
|
"filtered": "X",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "X",
|
|
"eval_cost": "X",
|
|
"prefix_cost": "X",
|
|
"data_read_per_join": "X"
|
|
},
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a` desc,`test`.`t1`.`b` desc
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 ORDER BY a, b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "X"
|
|
},
|
|
"ordering_operation": {
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"partitions": [
|
|
"p0",
|
|
"p1",
|
|
"p2"
|
|
],
|
|
"access_type": "index",
|
|
"key": "ab_asc",
|
|
"used_key_parts": [
|
|
"a",
|
|
"b"
|
|
],
|
|
"key_length": "84",
|
|
"rows_examined_per_scan": "X",
|
|
"rows_produced_per_join": "X",
|
|
"filtered": "X",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "X",
|
|
"eval_cost": "X",
|
|
"prefix_cost": "X",
|
|
"data_read_per_join": "X"
|
|
},
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b`
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 ORDER BY a, b DESC;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "X"
|
|
},
|
|
"ordering_operation": {
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"partitions": [
|
|
"p0",
|
|
"p1",
|
|
"p2"
|
|
],
|
|
"access_type": "index",
|
|
"key": "a_asc_b_desc",
|
|
"used_key_parts": [
|
|
"a",
|
|
"b"
|
|
],
|
|
"key_length": "84",
|
|
"rows_examined_per_scan": "X",
|
|
"rows_produced_per_join": "X",
|
|
"filtered": "X",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "X",
|
|
"eval_cost": "X",
|
|
"prefix_cost": "X",
|
|
"data_read_per_join": "X"
|
|
},
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` desc
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 ORDER BY a DESC, b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "X"
|
|
},
|
|
"ordering_operation": {
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"partitions": [
|
|
"p0",
|
|
"p1",
|
|
"p2"
|
|
],
|
|
"access_type": "index",
|
|
"key": "a_desc_b_asc",
|
|
"used_key_parts": [
|
|
"a",
|
|
"b"
|
|
],
|
|
"key_length": "84",
|
|
"rows_examined_per_scan": "X",
|
|
"rows_produced_per_join": "X",
|
|
"filtered": "X",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "X",
|
|
"eval_cost": "X",
|
|
"prefix_cost": "X",
|
|
"data_read_per_join": "X"
|
|
},
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a` desc,`test`.`t1`.`b`
|
|
# Test index access in different sort order
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 FORCE INDEX (`PRIMARY`) ORDER BY a, b;
|
|
a b
|
|
0 0
|
|
0 1
|
|
1 1
|
|
1 2
|
|
1 3
|
|
1 4
|
|
2 2
|
|
3 3
|
|
4 4
|
|
54 54
|
|
55 54
|
|
55 55
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_key 3
|
|
Handler_read_last 3
|
|
Handler_read_prev 12
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 FORCE INDEX (`PRIMARY`) ORDER BY a DESC, b;
|
|
a b
|
|
55 54
|
|
55 55
|
|
54 54
|
|
4 4
|
|
3 3
|
|
2 2
|
|
1 1
|
|
1 2
|
|
1 3
|
|
1 4
|
|
0 0
|
|
0 1
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 FORCE INDEX (`PRIMARY`) ORDER BY a, b DESC;
|
|
a b
|
|
0 1
|
|
0 0
|
|
1 4
|
|
1 3
|
|
1 2
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
54 54
|
|
55 55
|
|
55 54
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 FORCE INDEX (`PRIMARY`) ORDER BY a DESC, b;
|
|
a b
|
|
55 54
|
|
55 55
|
|
54 54
|
|
4 4
|
|
3 3
|
|
2 2
|
|
1 1
|
|
1 2
|
|
1 3
|
|
1 4
|
|
0 0
|
|
0 1
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 12
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 FORCE INDEX (`PRIMARY`) ORDER BY a, b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "X"
|
|
},
|
|
"ordering_operation": {
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"partitions": [
|
|
"p0",
|
|
"p1",
|
|
"p2"
|
|
],
|
|
"access_type": "index",
|
|
"key": "PRIMARY",
|
|
"used_key_parts": [
|
|
"a",
|
|
"b"
|
|
],
|
|
"key_length": "84",
|
|
"rows_examined_per_scan": "X",
|
|
"rows_produced_per_join": "X",
|
|
"filtered": "X",
|
|
"backward_index_scan": true,
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "X",
|
|
"eval_cost": "X",
|
|
"prefix_cost": "X",
|
|
"data_read_per_join": "X"
|
|
},
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (PRIMARY) order by `test`.`t1`.`a`,`test`.`t1`.`b`
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 FORCE INDEX (`PRIMARY`) ORDER BY a DESC, b;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "X"
|
|
},
|
|
"ordering_operation": {
|
|
"using_filesort": true,
|
|
"cost_info": {
|
|
"sort_cost": "X"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"partitions": [
|
|
"p0",
|
|
"p1",
|
|
"p2"
|
|
],
|
|
"access_type": "index",
|
|
"key": "PRIMARY",
|
|
"used_key_parts": [
|
|
"a",
|
|
"b"
|
|
],
|
|
"key_length": "84",
|
|
"rows_examined_per_scan": "X",
|
|
"rows_produced_per_join": "X",
|
|
"filtered": "X",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "X",
|
|
"eval_cost": "X",
|
|
"prefix_cost": "X",
|
|
"data_read_per_join": "X"
|
|
},
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (PRIMARY) order by `test`.`t1`.`a` desc,`test`.`t1`.`b`
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 FORCE INDEX (`PRIMARY`) ORDER BY a, b DESC;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "X"
|
|
},
|
|
"ordering_operation": {
|
|
"using_filesort": true,
|
|
"cost_info": {
|
|
"sort_cost": "X"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"partitions": [
|
|
"p0",
|
|
"p1",
|
|
"p2"
|
|
],
|
|
"access_type": "index",
|
|
"key": "PRIMARY",
|
|
"used_key_parts": [
|
|
"a",
|
|
"b"
|
|
],
|
|
"key_length": "84",
|
|
"rows_examined_per_scan": "X",
|
|
"rows_produced_per_join": "X",
|
|
"filtered": "X",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "X",
|
|
"eval_cost": "X",
|
|
"prefix_cost": "X",
|
|
"data_read_per_join": "X"
|
|
},
|
|
"used_columns": [
|
|
"a",
|
|
"b"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (PRIMARY) order by `test`.`t1`.`a`,`test`.`t1`.`b` desc
|
|
# Test HANDLER statements.
|
|
FLUSH STATUS;
|
|
HANDLER t1 OPEN;
|
|
HANDLER t1 READ `PRIMARY` FIRST;
|
|
a b
|
|
55 55
|
|
HANDLER t1 READ `PRIMARY` NEXT;
|
|
a b
|
|
55 54
|
|
HANDLER t1 READ `PRIMARY` NEXT;
|
|
a b
|
|
54 54
|
|
HANDLER t1 READ `PRIMARY` NEXT;
|
|
a b
|
|
4 4
|
|
HANDLER t1 READ `PRIMARY` NEXT;
|
|
a b
|
|
3 3
|
|
HANDLER t1 READ `PRIMARY` LAST;
|
|
a b
|
|
0 0
|
|
HANDLER t1 READ `PRIMARY` PREV;
|
|
a b
|
|
0 1
|
|
HANDLER t1 READ `PRIMARY` PREV;
|
|
a b
|
|
1 1
|
|
HANDLER t1 READ `PRIMARY` PREV;
|
|
a b
|
|
1 2
|
|
HANDLER t1 READ ab_asc FIRST;
|
|
a b
|
|
0 0
|
|
HANDLER t1 READ ab_asc NEXT;
|
|
a b
|
|
0 1
|
|
HANDLER t1 READ ab_asc NEXT;
|
|
a b
|
|
1 1
|
|
HANDLER t1 READ ab_asc NEXT;
|
|
a b
|
|
1 2
|
|
HANDLER t1 READ ab_asc NEXT;
|
|
a b
|
|
1 3
|
|
HANDLER t1 READ ab_asc LAST;
|
|
a b
|
|
55 55
|
|
HANDLER t1 READ ab_asc PREV;
|
|
a b
|
|
55 54
|
|
HANDLER t1 READ ab_asc PREV;
|
|
a b
|
|
54 54
|
|
HANDLER t1 READ ab_asc PREV;
|
|
a b
|
|
4 4
|
|
HANDLER t1 CLOSE;
|
|
SHOW STATUS WHERE Variable_name LIKE 'Handler%' AND Value >0;
|
|
Variable_name Value
|
|
Handler_commit 18
|
|
Handler_external_lock 36
|
|
Handler_read_first 6
|
|
Handler_read_key 12
|
|
Handler_read_last 6
|
|
Handler_read_next 8
|
|
Handler_read_prev 6
|
|
DROP TABLE t1;
|
|
|