2125 lines
66 KiB
Plaintext
2125 lines
66 KiB
Plaintext
#
|
|
# Bug#11894100: EXCHANGE PARTITION CAN'T BE EXECUTED IF
|
|
# ROW_FORMAT WAS SET EXPLICITLY
|
|
#
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
# Same definition (both have ROW_FORMAT set)
|
|
SET GLOBAL innodb_default_row_format=compact;
|
|
CREATE TABLE t1 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB ROW_FORMAT=COMPACT
|
|
PARTITION BY HASH (id)
|
|
PARTITIONS 2;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 LIKE t1;
|
|
ALTER TABLE t2 REMOVE PARTITIONING;
|
|
SHOW CREATE TABLE t1;
|
|
Table t1
|
|
Create Table CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
|
|
/*!50100 PARTITION BY HASH (`id`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE t2;
|
|
Table t2
|
|
Create Table CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
|
|
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2');
|
|
TABLE_NAME t1
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS row_format=COMPACT partitioned
|
|
TABLE_NAME t2
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS row_format=COMPACT
|
|
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table t1
|
|
Create Table CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
|
|
/*!50100 PARTITION BY HASH (`id`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE t2;
|
|
Table t2
|
|
Create Table CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
|
|
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2');
|
|
TABLE_NAME t1
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS row_format=COMPACT partitioned
|
|
TABLE_NAME t2
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS row_format=COMPACT
|
|
DROP TABLE t2;
|
|
# Only the partitioned table have ROW_FORMAT set.
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table t1
|
|
Create Table CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
|
|
/*!50100 PARTITION BY HASH (`id`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE t2;
|
|
Table t2
|
|
Create Table CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2');
|
|
TABLE_NAME t1
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS row_format=COMPACT partitioned
|
|
TABLE_NAME t2
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS
|
|
# Only the non partitioned table have ROW_FORMAT set.
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB
|
|
PARTITION BY HASH (id)
|
|
PARTITIONS 2;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB ROW_FORMAT = COMPACT;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table t1
|
|
Create Table CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`id`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE t2;
|
|
Table t2
|
|
Create Table CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
|
|
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2');
|
|
TABLE_NAME t1
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS partitioned
|
|
TABLE_NAME t2
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS row_format=COMPACT
|
|
# No table have ROW_FORMAT set.
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB
|
|
PARTITION BY HASH (id)
|
|
PARTITIONS 2;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table t1
|
|
Create Table CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`id`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE t2;
|
|
Table t2
|
|
Create Table CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2');
|
|
TABLE_NAME t1
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS partitioned
|
|
TABLE_NAME t2
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS
|
|
# Not same ROW_FORMAT as default (but same).
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB ROW_FORMAT = REDUNDANT
|
|
PARTITION BY HASH (id)
|
|
PARTITIONS 2;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB ROW_FORMAT = REDUNDANT;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table t1
|
|
Create Table CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
|
|
/*!50100 PARTITION BY HASH (`id`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE t2;
|
|
Table t2
|
|
Create Table CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
|
|
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2');
|
|
TABLE_NAME t1
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Redundant
|
|
CREATE_OPTIONS row_format=REDUNDANT partitioned
|
|
TABLE_NAME t2
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Redundant
|
|
CREATE_OPTIONS row_format=REDUNDANT
|
|
# Not same ROW_FORMAT as default (tables differs).
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB
|
|
PARTITION BY HASH (id)
|
|
PARTITIONS 2;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB ROW_FORMAT = REDUNDANT;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
|
|
ERROR HY000: Non matching attribute 'ROW_FORMAT' between partition and table
|
|
SHOW CREATE TABLE t1;
|
|
Table t1
|
|
Create Table CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`id`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE t2;
|
|
Table t2
|
|
Create Table CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
|
|
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2');
|
|
TABLE_NAME t1
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS partitioned
|
|
TABLE_NAME t2
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Redundant
|
|
CREATE_OPTIONS row_format=REDUNDANT
|
|
# Different than default (forced ROW_TYPE)
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB ROW_FORMAT = COMPACT
|
|
PARTITION BY HASH (id)
|
|
PARTITIONS 2;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
year year(4) DEFAULT NULL,
|
|
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB ROW_FORMAT = REDUNDANT;
|
|
Warnings:
|
|
Level Warning
|
|
Code 1681
|
|
Message Integer display width is deprecated and will be removed in a future release.
|
|
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
|
|
ERROR HY000: Non matching attribute 'ROW_FORMAT' between partition and table
|
|
SHOW CREATE TABLE t1;
|
|
Table t1
|
|
Create Table CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
|
|
/*!50100 PARTITION BY HASH (`id`)
|
|
PARTITIONS 2 */
|
|
SHOW CREATE TABLE t2;
|
|
Table t2
|
|
Create Table CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`year` year(4) DEFAULT NULL,
|
|
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
|
|
SELECT TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, CREATE_OPTIONS
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME IN ('t1', 't2');
|
|
TABLE_NAME t1
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Compact
|
|
CREATE_OPTIONS row_format=COMPACT partitioned
|
|
TABLE_NAME t2
|
|
TABLE_TYPE BASE TABLE
|
|
ENGINE InnoDB
|
|
ROW_FORMAT Redundant
|
|
CREATE_OPTIONS row_format=REDUNDANT
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug #28235668: ASSERTION AT UT_DBG_ASSERTION_FAILED (EXPR=0X5173C5C "J < NCOL"
|
|
#
|
|
# Create a partitioned table with virtual columns
|
|
CREATE TABLE t1_1 (
|
|
`col1` int(11) DEFAULT NULL,
|
|
`col2` int(11) DEFAULT NULL,
|
|
`col3` int(11) DEFAULT NULL,
|
|
`col4` text,
|
|
`col5` varchar(500) GENERATED ALWAYS AS (substr(`col4`,1,500)) VIRTUAL,
|
|
`col6` text GENERATED ALWAYS AS (substr(`col4`,1,500)) VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE ((`col1` * 2))
|
|
(PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
|
|
PARTITION p3 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.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
# "Clone" table using AS SELECT ...
|
|
CREATE TABLE temp25058 AS SELECT * FROM t1_1 WHERE 1 <> 1;
|
|
SHOW CREATE TABLE temp25058;
|
|
Table Create Table
|
|
temp25058 CREATE TABLE `temp25058` (
|
|
`col1` int(11) DEFAULT NULL,
|
|
`col2` int(11) DEFAULT NULL,
|
|
`col3` int(11) DEFAULT NULL,
|
|
`col4` text,
|
|
`col5` varchar(500) DEFAULT NULL,
|
|
`col6` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
# Select from this table is ok - no error
|
|
SELECT * FROM temp25058;
|
|
col1 col2 col3 col4 col5 col6
|
|
# Exchanging dissimilar tables is not a supported action for
|
|
# tables with generated columns.
|
|
ALTER TABLE t1_1 ALGORITHM DEFAULT , LOCK SHARED ,
|
|
EXCHANGE PARTITION p2 WITH TABLE temp25058;
|
|
ERROR HY000: 'Exchanging partitions for non-generated columns' is not supported for generated columns.
|
|
# No viritual columns in Innodb or DD
|
|
SHOW CREATE TABLE temp25058;
|
|
Table Create Table
|
|
temp25058 CREATE TABLE `temp25058` (
|
|
`col1` int(11) DEFAULT NULL,
|
|
`col2` int(11) DEFAULT NULL,
|
|
`col3` int(11) DEFAULT NULL,
|
|
`col4` text,
|
|
`col5` varchar(500) DEFAULT NULL,
|
|
`col6` text
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
# No viritual columns in Innodb or DD
|
|
SELECT * FROM temp25058;
|
|
col1 col2 col3 col4 col5 col6
|
|
DROP TABLE temp25058;
|
|
DROP TABLE t1_1;
|
|
#
|
|
# Bug#56484: !table || (!table->read_set ||
|
|
# bitmap_is_set(table->read_set, field_index))
|
|
# After WL#8971, the test case was rewritten to use InnoDB.
|
|
#
|
|
CREATE TABLE t1 (a INT NOT NULL, b TIME NOT NULL DEFAULT '00:00:00')
|
|
ENGINE=InnoDB
|
|
PARTITION BY HASH (a) PARTITIONS 2;
|
|
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
|
|
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
|
|
ERROR HY000: Tables have different definitions
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug#55784: Foreign key integrity broken by alter table
|
|
#
|
|
CREATE TABLE t1 (s1 INT PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (s1 INT, FOREIGN KEY (s1) REFERENCES t1 (s1)) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
INSERT INTO t2 VALUES (1),(2),(3);
|
|
CREATE TABLE t3 (s1 INT PRIMARY KEY)
|
|
ENGINE=InnoDB
|
|
PARTITION BY LIST (s1)
|
|
(PARTITION p1 VALUES IN (1,2,3));
|
|
ALTER TABLE t3 EXCHANGE PARTITION p1 WITH TABLE t1;
|
|
ERROR HY000: Table to exchange with partition has foreign key references: 't1'
|
|
DROP TABLE t2, t1, t3;
|
|
# Tests for WL#4445
|
|
CREATE TABLE t (a INT,
|
|
b VARCHAR(55),
|
|
PRIMARY KEY (a));
|
|
CREATE TABLE tp (a INT,
|
|
b VARCHAR(55),
|
|
PRIMARY KEY (a))
|
|
PARTITION BY RANGE (a)
|
|
(PARTITION p0 VALUES LESS THAN (100),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
CREATE TABLE tsp (a INT,
|
|
b VARCHAR(55),
|
|
PRIMARY KEY (a))
|
|
PARTITION BY RANGE (a)
|
|
SUBPARTITION BY HASH(a)
|
|
(PARTITION p0 VALUES LESS THAN (100)
|
|
(SUBPARTITION sp0,
|
|
SUBPARTITION sp1),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION sp2,
|
|
SUBPARTITION sp3));
|
|
INSERT INTO t VALUES (1, "First value"), (3, "Three"), (5, "Five"), (99, "End of values");
|
|
INSERT INTO tp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"), (200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"), (63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"), (163, "163"), (164, "164");
|
|
INSERT INTO tsp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"), (200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"), (63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"), (163, "163"), (164, "164");
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
SELECT * FROM tp;
|
|
a b
|
|
10 Ten
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
2 First value
|
|
200 Two hundred, end of values
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT * FROM t;
|
|
a b
|
|
10 Ten
|
|
2 First value
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
SELECT * FROM tp;
|
|
a b
|
|
1 First value
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
200 Two hundred, end of values
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
SELECT * FROM tp;
|
|
a b
|
|
10 Ten
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
2 First value
|
|
200 Two hundred, end of values
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t;
|
|
ERROR HY000: Found a row that does not match the partition
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
SELECT * FROM tp;
|
|
a b
|
|
10 Ten
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
2 First value
|
|
200 Two hundred, end of values
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
# Test list of partitions
|
|
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t IGNORE;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE' at line 1
|
|
ALTER TABLE tp EXCHANGE PARTITION p0,p1 WITH TABLE t IGNORE;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',p1 WITH TABLE t IGNORE' at line 1
|
|
ALTER TABLE tp EXCHANGE PARTITION p0,p1 WITH TABLE t;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',p1 WITH TABLE t' at line 1
|
|
ALTER TABLE tp EXCHANGE PARTITION (p0,p1) WITH TABLE t;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p0,p1) WITH TABLE t' at line 1
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE (t,t2);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(t,t2)' at line 1
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t,t2;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',t2' at line 1
|
|
ALTER TABLE tp EXCHANGE PARTITION non_existent WITH TABLE t;
|
|
ERROR HY000: Unknown partition 'non_existent' in table 'tp'
|
|
ALTER TABLE tsp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
ERROR HY000: Subpartitioned table, use subpartition instead of partition
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp0 WITH TABLE tp;
|
|
ERROR HY000: Table to exchange with partition is partitioned: 'tp'
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SHOW CREATE TABLE tsp;
|
|
Table Create Table
|
|
tsp CREATE TABLE `tsp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100)
|
|
(SUBPARTITION sp0 ENGINE = InnoDB,
|
|
SUBPARTITION sp1 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION sp2 ENGINE = InnoDB,
|
|
SUBPARTITION sp3 ENGINE = InnoDB)) */
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
SELECT * FROM tp;
|
|
a b
|
|
10 Ten
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
2 First value
|
|
200 Two hundred, end of values
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
# Test exchange partition
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT * FROM t;
|
|
a b
|
|
10 Ten
|
|
2 First value
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
SELECT * FROM tp;
|
|
a b
|
|
1 First value
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
200 Two hundred, end of values
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
SELECT * FROM tp;
|
|
a b
|
|
10 Ten
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
2 First value
|
|
200 Two hundred, end of values
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
# Test exchange subpartition
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp1 WITH TABLE t;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tsp;
|
|
Table Create Table
|
|
tsp CREATE TABLE `tsp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100)
|
|
(SUBPARTITION sp0 ENGINE = InnoDB,
|
|
SUBPARTITION sp1 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION sp2 ENGINE = InnoDB,
|
|
SUBPARTITION sp3 ENGINE = InnoDB)) */
|
|
SELECT * FROM t;
|
|
a b
|
|
61 Sixty one
|
|
63 Sixty three
|
|
SELECT * FROM tsp;
|
|
a b
|
|
1 First value
|
|
10 Ten
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
2 First value
|
|
200 Two hundred, end of values
|
|
3 Three
|
|
5 Five
|
|
50 Fifty
|
|
62 Sixty two
|
|
64 Sixty four
|
|
99 End of values
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp1 WITH TABLE t;
|
|
ALTER TABLE t ENGINE = InnoDB;
|
|
ALTER TABLE tp ENGINE = InnoDB;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
SELECT * FROM tp;
|
|
a b
|
|
10 Ten
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
2 First value
|
|
200 Two hundred, end of values
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT * FROM t;
|
|
a b
|
|
10 Ten
|
|
2 First value
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
SELECT * FROM tp;
|
|
a b
|
|
1 First value
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
200 Two hundred, end of values
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
# Test different charsets
|
|
CREATE TABLE tmp LIKE t;
|
|
INSERT INTO tmp SELECT * FROM t;
|
|
RENAME TABLE t TO tmp2, tmp TO t;
|
|
ALTER TABLE t CHARACTER SET = koi8r COLLATE koi8r_general_ci;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
ERROR HY000: Non matching attribute 'CHARACTER SET' between partition and table
|
|
DROP TABLE t;
|
|
# Test multiple different table options
|
|
CREATE TABLE t (a INT,
|
|
b VARCHAR(55),
|
|
PRIMARY KEY (a))
|
|
MAX_ROWS = 100000 MIN_ROWS = 1000;
|
|
INSERT INTO t SELECT * FROM tmp2;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MIN_ROWS=1000 MAX_ROWS=100000
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
ERROR HY000: Non matching attribute 'MAX_ROWS' between partition and table
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1731 Non matching attribute 'MAX_ROWS' between partition and table
|
|
Error 1731 Non matching attribute 'MIN_ROWS' between partition and table
|
|
DROP TABLE t;
|
|
RENAME TABLE tmp2 TO t;
|
|
ALTER TABLE t ADD KEY ba_key (b, a);
|
|
ALTER TABLE tp ADD KEY ba_key (b, a);
|
|
ALTER TABLE tsp ADD KEY ba_key (b, a);
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `ba_key` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `ba_key` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT * FROM t;
|
|
a b
|
|
10 Ten
|
|
2 First value
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
SELECT * FROM tp;
|
|
a b
|
|
1 First value
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
200 Two hundred, end of values
|
|
3 Three
|
|
5 Five
|
|
99 End of values
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
ALTER TABLE t DROP KEY ba_key;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
ERROR HY000: Tables have different definitions
|
|
ALTER TABLE t ADD KEY b_key (b);
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp1 WITH TABLE t;
|
|
ERROR HY000: Tables have different definitions
|
|
ALTER TABLE t ADD KEY ba_key (b, a);
|
|
ALTER TABLE t DROP KEY b_key;
|
|
ALTER TABLE t CHANGE a c INT;
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp1 WITH TABLE t;
|
|
ERROR HY000: Tables have different definitions
|
|
ALTER TABLE t CHANGE c a INT;
|
|
# test temporary table
|
|
CREATE TEMPORARY TABLE tmp LIKE t;
|
|
INSERT INTO tmp SELECT * FROM t;
|
|
ALTER TABLE t RENAME TO tmp2;
|
|
ALTER TABLE tmp RENAME TO t;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TEMPORARY TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `ba_key` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `ba_key` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
ERROR HY000: Table to exchange with partition is temporary: 't'
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TEMPORARY TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `ba_key` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `ba_key` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
DROP TEMPORARY TABLE t;
|
|
ALTER TABLE tmp2 RENAME TO t;
|
|
# Test non partitioned table
|
|
ALTER TABLE tp REMOVE PARTITIONING;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
ERROR HY000: Partition management on a not partitioned table is not possible
|
|
DROP TABLE t, tp, tsp;
|
|
# Test with LOCK TABLE
|
|
CREATE TABLE tp
|
|
(a VARCHAR(24),
|
|
b DATETIME,
|
|
PRIMARY KEY (a,b))
|
|
PARTITION BY RANGE COLUMNS (a, b)
|
|
(PARTITION p0 VALUES LESS THAN ("Middle", '0000-00-00'),
|
|
PARTITION p1 VALUES LESS THAN (MAXVALUE, '9999-12-31 23:59:59'));
|
|
CREATE TABLE t LIKE tp;
|
|
ALTER TABLE t REMOVE PARTITIONING;
|
|
CREATE TABLE t2 LIKE t;
|
|
INSERT INTO tp VALUES ("First in tp", '2000-01-02 03:04:25'), ("Zebra in tp", '0000-00-00 00:00:00'), ("Second in tp", '2010-01-01 05:12:24');
|
|
INSERT INTO t VALUES ("First in t", '2000-01-02 03:04:25'), ("a test in t", '0000-00-00 00:00:00'), ("Echo in t", '2010-01-01 05:12:24');
|
|
# tp_lock '0' t_lock 'READ' t2_lock 'READ' count '17'
|
|
LOCK TABLE t READ, t2 READ;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
# tp_lock '0' t_lock 'WRITE' t2_lock 'READ' count '16'
|
|
LOCK TABLE t WRITE, t2 READ;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
# tp_lock 'READ' t_lock '0' t2_lock 'READ' count '15'
|
|
LOCK TABLE tp READ, t2 READ;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
# tp_lock 'READ' t_lock 'READ' t2_lock 'READ' count '14'
|
|
LOCK TABLE t READ, tp READ, t2 READ;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
# tp_lock 'READ' t_lock 'WRITE' t2_lock 'READ' count '13'
|
|
LOCK TABLE t WRITE, tp READ, t2 READ;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
# tp_lock 'WRITE' t_lock '0' t2_lock 'READ' count '12'
|
|
LOCK TABLE tp WRITE, t2 READ;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 't' was not locked with LOCK TABLES
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 't2' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
# tp_lock 'WRITE' t_lock 'READ' t2_lock 'READ' count '11'
|
|
LOCK TABLE t READ, tp WRITE, t2 READ;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 't' was locked with a READ lock and can't be updated
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 't2' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
# tp_lock 'WRITE' t_lock 'WRITE' t2_lock 'READ' count '10'
|
|
LOCK TABLE t WRITE, tp WRITE, t2 READ;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 't2' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
# tp_lock '0' t_lock '0' t2_lock 'WRITE' count '9'
|
|
LOCK TABLE t2 WRITE;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
# tp_lock '0' t_lock 'READ' t2_lock 'WRITE' count '8'
|
|
LOCK TABLE t READ, t2 WRITE;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
# tp_lock '0' t_lock 'WRITE' t2_lock 'WRITE' count '7'
|
|
LOCK TABLE t WRITE, t2 WRITE;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 'tp' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
# tp_lock 'READ' t_lock '0' t2_lock 'WRITE' count '6'
|
|
LOCK TABLE tp READ, t2 WRITE;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
# tp_lock 'READ' t_lock 'READ' t2_lock 'WRITE' count '5'
|
|
LOCK TABLE t READ, tp READ, t2 WRITE;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
# tp_lock 'READ' t_lock 'WRITE' t2_lock 'WRITE' count '4'
|
|
LOCK TABLE t WRITE, tp READ, t2 WRITE;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 'tp' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
# tp_lock 'WRITE' t_lock '0' t2_lock 'WRITE' count '3'
|
|
LOCK TABLE tp WRITE, t2 WRITE;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1100 Table 't' was not locked with LOCK TABLES
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
UNLOCK TABLES;
|
|
# tp_lock 'WRITE' t_lock 'READ' t2_lock 'WRITE' count '2'
|
|
LOCK TABLE t READ, tp WRITE, t2 WRITE;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Error 1099 Table 't' was locked with a READ lock and can't be updated
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
UNLOCK TABLES;
|
|
# tp_lock 'WRITE' t_lock 'WRITE' t2_lock 'WRITE' count '1'
|
|
LOCK TABLE t WRITE, tp WRITE, t2 WRITE;
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
UNLOCK TABLES;
|
|
DROP TABLE t, t2, tp;
|
|
#
|
|
# Tests for WL#5630: Allow EXCHANGE PARTITION WITHOUT VALIDATION
|
|
#
|
|
CREATE TABLE t (a INT,
|
|
b VARCHAR(55),
|
|
PRIMARY KEY (a));
|
|
CREATE TABLE tp (a INT,
|
|
b VARCHAR(55),
|
|
PRIMARY KEY (a))
|
|
PARTITION BY RANGE (a)
|
|
(PARTITION p0 VALUES LESS THAN (100),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
CREATE TABLE tsp (a INT,
|
|
b VARCHAR(55),
|
|
PRIMARY KEY (a))
|
|
PARTITION BY RANGE (a)
|
|
SUBPARTITION BY HASH(a)
|
|
(PARTITION p0 VALUES LESS THAN (100)
|
|
(SUBPARTITION sp0,
|
|
SUBPARTITION sp1),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION sp2,
|
|
SUBPARTITION sp3));
|
|
INSERT INTO t VALUES (1, "First value"), (3, "Three"), (5, "Five"),
|
|
(6, "Six"), (8, "Eight"), (99, "End of values");
|
|
INSERT INTO tp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"),
|
|
(200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"),
|
|
(63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"),
|
|
(163, "163"), (164, "164");
|
|
INSERT INTO tsp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"),
|
|
(200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"),
|
|
(63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"),
|
|
(163, "163"), (164, "164");
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
6 Six
|
|
8 Eight
|
|
99 End of values
|
|
SELECT * FROM tp PARTITION (p0);
|
|
a b
|
|
10 Ten
|
|
2 First value
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
SELECT * FROM tp PARTITION (p1);
|
|
a b
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
200 Two hundred, end of values
|
|
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t;
|
|
ERROR HY000: Found a row that does not match the partition
|
|
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t WITH VALIDATION;
|
|
ERROR HY000: Found a row that does not match the partition
|
|
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t WITHOUT VALIDATION;
|
|
SELECT * FROM t;
|
|
a b
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
200 Two hundred, end of values
|
|
SELECT * FROM tp PARTITION (p0);
|
|
a b
|
|
10 Ten
|
|
2 First value
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
SELECT * FROM tp PARTITION (p1);
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
6 Six
|
|
8 Eight
|
|
99 End of values
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
# No need to use WITHOUT VALIDATION on the original partition
|
|
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t;
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
6 Six
|
|
8 Eight
|
|
99 End of values
|
|
SELECT * FROM tp PARTITION (p0);
|
|
a b
|
|
10 Ten
|
|
2 First value
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
SELECT * FROM tp PARTITION (p1);
|
|
a b
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
200 Two hundred, end of values
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tp;
|
|
Table Create Table
|
|
tp CREATE TABLE `tp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
# Test exchange subpartition WITHOUT VALIDATION
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
6 Six
|
|
8 Eight
|
|
99 End of values
|
|
SELECT * FROM tsp PARTITION (sp3);
|
|
a b
|
|
161 161
|
|
163 163
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp3 WITH TABLE t WITHOUT VALIDATION;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tsp;
|
|
Table Create Table
|
|
tsp CREATE TABLE `tsp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100)
|
|
(SUBPARTITION sp0 ENGINE = InnoDB,
|
|
SUBPARTITION sp1 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION sp2 ENGINE = InnoDB,
|
|
SUBPARTITION sp3 ENGINE = InnoDB)) */
|
|
SELECT * FROM t;
|
|
a b
|
|
161 161
|
|
163 163
|
|
SELECT * FROM tsp PARTITION (sp3);
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
6 Six
|
|
8 Eight
|
|
99 End of values
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp3 WITH TABLE t;
|
|
SELECT * FROM t;
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
6 Six
|
|
8 Eight
|
|
99 End of values
|
|
SELECT * FROM tsp PARTITION (sp3);
|
|
a b
|
|
161 161
|
|
163 163
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE tsp;
|
|
Table Create Table
|
|
tsp CREATE TABLE `tsp` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(55) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100)
|
|
(SUBPARTITION sp0 ENGINE = InnoDB,
|
|
SUBPARTITION sp1 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION sp2 ENGINE = InnoDB,
|
|
SUBPARTITION sp3 ENGINE = InnoDB)) */
|
|
# Test MANAGEMENT of partition with misplaced rows
|
|
call mtr.add_suppression("row in wrong partition:");
|
|
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t WITHOUT VALIDATION;
|
|
SELECT * FROM tp PARTITION (p0);
|
|
a b
|
|
10 Ten
|
|
2 First value
|
|
50 Fifty
|
|
61 Sixty one
|
|
62 Sixty two
|
|
63 Sixty three
|
|
64 Sixty four
|
|
SELECT * FROM tp PARTITION (p1);
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
6 Six
|
|
8 Eight
|
|
99 End of values
|
|
ALTER TABLE tp REBUILD PARTITION p1;
|
|
ERROR HY000: Found a row in wrong partition from REBUILD/REORGANIZED partition: 0 to non included partition (new definition): 0 a:1 b:First value
|
|
ALTER TABLE tp REORGANIZE PARTITION p1 INTO
|
|
(PARTITION p1 VALUES LESS THAN (162),
|
|
PARTITION p2 VALUES LESS THAN MAXVALUE);
|
|
ERROR HY000: Found a row in wrong partition from REBUILD/REORGANIZED partition: 0 to non included partition (new definition): 0 a:1 b:First value
|
|
ALTER TABLE tp ANALYZE PARTITION p1;
|
|
Table Op Msg_type Msg_text
|
|
test.tp analyze status OK
|
|
ALTER TABLE tp OPTIMIZE PARTITION p1;
|
|
Table Op Msg_type Msg_text
|
|
test.tp optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
|
|
test.tp optimize status OK
|
|
# Test REBUILD of partition (with subpartitions) with misplaced rows
|
|
# half of the rows match sp3 and the other half match sp2
|
|
# with duplicates!
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp3 WITH TABLE t WITHOUT VALIDATION;
|
|
SELECT * FROM tsp PARTITION (sp2);
|
|
a b
|
|
162 162
|
|
164 164
|
|
200 Two hundred, end of values
|
|
SELECT * FROM tsp PARTITION (sp3);
|
|
a b
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
200 Two hundred, end of values
|
|
call mtr.add_suppression("Cannot find index .PRIMARY. in InnoDB index translation table.");
|
|
ALTER TABLE tsp REBUILD PARTITION p1;
|
|
Got one of the listed errors
|
|
ALTER TABLE tsp REORGANIZE PARTITION p1 INTO
|
|
(PARTITION p1 VALUES LESS THAN (162),
|
|
PARTITION p2 VALUES LESS THAN MAXVALUE);
|
|
Got one of the listed errors
|
|
ALTER TABLE tsp ANALYZE PARTITION sp3;
|
|
Table Op Msg_type Msg_text
|
|
test.tsp analyze status OK
|
|
ALTER TABLE tsp OPTIMIZE PARTITION sp3;
|
|
Table Op Msg_type Msg_text
|
|
test.tsp optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
|
|
test.tsp optimize status OK
|
|
SELECT * FROM tsp PARTITION (sp2);
|
|
a b
|
|
162 162
|
|
164 164
|
|
200 Two hundred, end of values
|
|
SELECT * FROM tsp PARTITION (sp3);
|
|
a b
|
|
161 161
|
|
162 162
|
|
163 163
|
|
164 164
|
|
200 Two hundred, end of values
|
|
# Test with REBUILD where the rows does not match the current partition
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp3 WITH TABLE t;
|
|
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t;
|
|
ALTER TABLE tsp EXCHANGE PARTITION sp3 WITH TABLE t WITHOUT VALIDATION;
|
|
SELECT * FROM tsp PARTITION (sp2);
|
|
a b
|
|
162 162
|
|
164 164
|
|
200 Two hundred, end of values
|
|
SELECT * FROM tsp PARTITION (sp3);
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
6 Six
|
|
8 Eight
|
|
99 End of values
|
|
ALTER TABLE tsp REBUILD PARTITION p1;
|
|
ERROR HY000: Found a row in wrong partition from REBUILD/REORGANIZED partition: 1 to non included partition (new definition): 1 a:1 b:First value
|
|
ALTER TABLE tsp REORGANIZE PARTITION p1 INTO
|
|
(PARTITION p1 VALUES LESS THAN (162),
|
|
PARTITION p2 VALUES LESS THAN MAXVALUE);
|
|
ERROR HY000: Found a row in wrong partition from REBUILD/REORGANIZED partition: 1 to non included partition (new definition): 1 a:1 b:First value
|
|
ALTER TABLE tsp REORGANIZE PARTITION p1 INTO
|
|
(PARTITION p01 VALUES LESS THAN (162)
|
|
(SUBPARTITION sp01,
|
|
SUBPARTITION sp02),
|
|
PARTITION p02 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION sp03,
|
|
SUBPARTITION sp04));
|
|
ERROR HY000: Found a row in wrong partition from REBUILD/REORGANIZED partition: 1 to non included partition (new definition): 1 a:1 b:First value
|
|
ALTER TABLE tsp ANALYZE PARTITION sp3;
|
|
Table Op Msg_type Msg_text
|
|
test.tsp analyze status OK
|
|
ALTER TABLE tsp OPTIMIZE PARTITION sp3;
|
|
Table Op Msg_type Msg_text
|
|
test.tsp optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
|
|
test.tsp optimize status OK
|
|
SELECT * FROM tsp PARTITION (sp2);
|
|
a b
|
|
162 162
|
|
164 164
|
|
200 Two hundred, end of values
|
|
SELECT * FROM tsp PARTITION (sp3);
|
|
a b
|
|
1 First value
|
|
3 Three
|
|
5 Five
|
|
6 Six
|
|
8 Eight
|
|
99 End of values
|
|
# Test with REBUILD where the rows does not match the current partition
|
|
DROP TABLE t, tp, tsp;
|
|
SET sql_mode = default;
|
|
SET GLOBAL innodb_default_row_format=default;
|
|
#
|
|
# Bug#19730200: EXCHANGE PARTITION FOR PARTITION WITH DATA DIRECTORY
|
|
#
|
|
CREATE TABLE t1 (a int unsigned auto_increment primary key, b varchar(64))
|
|
ENGINE = InnoDB
|
|
DATA DIRECTORY = 'MYSQL_TMP_DIR/t1_dir';
|
|
CREATE TABLE t2 (a int unsigned auto_increment primary key, b varchar(64))
|
|
ENGINE = InnoDB
|
|
PARTITION BY LIST (a)
|
|
(PARTITION p1 VALUES IN (1,3,5,7,9) DATA DIRECTORY = 'MYSQL_TMP_DIR/p1_dir',
|
|
PARTITION p2 VALUES IN (0,2,4,6,8) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir');
|
|
INSERT INTO t1 VALUES (1, "t1"), (5, "t1");
|
|
INSERT INTO t2 VALUES (3, "p1"), (4, "p2"), (7, "p1"), (8, "p2");
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/t1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION p1 VALUES IN (1,3,5,7,9) DATA DIRECTORY = 'MYSQL_TMP_DIR/p1_dir/' ENGINE = InnoDB,
|
|
PARTITION p2 VALUES IN (0,2,4,6,8) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir/' ENGINE = InnoDB) */
|
|
# List files from t1_dir/test
|
|
t1.ibd
|
|
# List files from p1_dir/test
|
|
t2#P#p1.ibd
|
|
# List files from p2_dir/test
|
|
t2#P#p2.ibd
|
|
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
|
|
SELECT * FROM t1;
|
|
a b
|
|
3 p1
|
|
7 p1
|
|
SELECT * FROM t2;
|
|
a b
|
|
1 t1
|
|
5 t1
|
|
4 p2
|
|
8 p2
|
|
# List files from t1_dir/test
|
|
t2#P#p1.ibd
|
|
# List files from p1_dir/test
|
|
t1.ibd
|
|
# List files from p2_dir/test
|
|
t2#P#p2.ibd
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/p1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION p1 VALUES IN (1,3,5,7,9) DATA DIRECTORY = 'MYSQL_TMP_DIR/t1_dir/' ENGINE = InnoDB,
|
|
PARTITION p2 VALUES IN (0,2,4,6,8) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir/' ENGINE = InnoDB) */
|
|
FLUSH TABLES t1, t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/p1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION p1 VALUES IN (1,3,5,7,9) DATA DIRECTORY = 'MYSQL_TMP_DIR/t1_dir/' ENGINE = InnoDB,
|
|
PARTITION p2 VALUES IN (0,2,4,6,8) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir/' ENGINE = InnoDB) */
|
|
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/t1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION p1 VALUES IN (1,3,5,7,9) DATA DIRECTORY = 'MYSQL_TMP_DIR/p1_dir/' ENGINE = InnoDB,
|
|
PARTITION p2 VALUES IN (0,2,4,6,8) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir/' ENGINE = InnoDB) */
|
|
# List files from t1_dir/test
|
|
t1.ibd
|
|
# List files from p1_dir/test
|
|
t2#P#p1.ibd
|
|
# List files from p2_dir/test
|
|
t2#P#p2.ibd
|
|
DROP TABLE t1, t2;
|
|
# exchage partition with data dir and range
|
|
CREATE TABLE t1 (a int unsigned auto_increment primary key, b varchar(64))
|
|
ENGINE = InnoDB
|
|
DATA DIRECTORY = 'MYSQL_TMP_DIR/t1_dir';
|
|
CREATE TABLE t2 (a int unsigned auto_increment primary key, b varchar(64))
|
|
ENGINE = InnoDB
|
|
PARTITION BY range (a)
|
|
(PARTITION p1 VALUES less than (10) DATA DIRECTORY = 'MYSQL_TMP_DIR/p1_dir',
|
|
PARTITION p2 VALUES less than (20) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir');
|
|
INSERT INTO t1 VALUES (1, "t1"), (5, "t1");
|
|
INSERT INTO t2 VALUES (3, "p1"), (4, "p2"), (17, "p1"), (12, "p2");
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/t1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p1 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQL_TMP_DIR/p1_dir/' ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (20) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir/' ENGINE = InnoDB) */
|
|
# List files from t1_dir/test
|
|
t1.ibd
|
|
# List files from p1_dir/test
|
|
t2#P#p1.ibd
|
|
# List files from p2_dir/test
|
|
t2#P#p2.ibd
|
|
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
|
|
SELECT * FROM t1;
|
|
a b
|
|
3 p1
|
|
4 p2
|
|
SELECT * FROM t2;
|
|
a b
|
|
1 t1
|
|
5 t1
|
|
12 p2
|
|
17 p1
|
|
# List files from t1_dir/test
|
|
t2#P#p1.ibd
|
|
# List files from p1_dir/test
|
|
t1.ibd
|
|
# List files from p2_dir/test
|
|
t2#P#p2.ibd
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/p1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p1 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQL_TMP_DIR/t1_dir/' ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (20) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir/' ENGINE = InnoDB) */
|
|
FLUSH TABLES t1, t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/p1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p1 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQL_TMP_DIR/t1_dir/' ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (20) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir/' ENGINE = InnoDB) */
|
|
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/t1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p1 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQL_TMP_DIR/p1_dir/' ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (20) DATA DIRECTORY = 'MYSQL_TMP_DIR/p2_dir/' ENGINE = InnoDB) */
|
|
# List files from t1_dir/test
|
|
t1.ibd
|
|
# List files from p1_dir/test
|
|
t2#P#p1.ibd
|
|
# List files from p2_dir/test
|
|
t2#P#p2.ibd
|
|
DROP table t1,t2;
|
|
exchange subpartition
|
|
CREATE TABLE t1 (id INT, purchased DATE)
|
|
ENGINE = InnoDB
|
|
DATA DIRECTORY = 'MYSQL_TMP_DIR/t1_dir';
|
|
CREATE TABLE t2 (id INT, purchased DATE)
|
|
PARTITION BY RANGE( YEAR(purchased) )
|
|
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
|
|
PARTITION p0 VALUES LESS THAN (1990) (
|
|
SUBPARTITION s0 data directory='MYSQL_TMP_DIR/sp0_dir',
|
|
SUBPARTITION s1),
|
|
PARTITION p1 VALUES LESS THAN (2000) (
|
|
SUBPARTITION s2,
|
|
SUBPARTITION s3 data directory='MYSQL_TMP_DIR/sp3_dir'
|
|
));
|
|
insert into t2 values(1,'1987-05-12 '),(2,'1987-05-14');
|
|
insert into t2 values(3,'1997-05-10 '),(4,'1997-05-18');
|
|
insert into t1 values(1,'1987-05-10 '),(2,'1987-05-08');
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/t1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (year(`purchased`))
|
|
SUBPARTITION BY HASH (to_days(`purchased`))
|
|
(PARTITION p0 VALUES LESS THAN (1990)
|
|
(SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/sp0_dir/' ENGINE = InnoDB,
|
|
SUBPARTITION s1 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN (2000)
|
|
(SUBPARTITION s2 ENGINE = InnoDB,
|
|
SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/sp3_dir/' ENGINE = InnoDB)) */
|
|
# List files from t1_dir/test
|
|
t1.ibd
|
|
# List files from sp0_dir/test
|
|
t2#P#p0#SP#s0.ibd
|
|
# List files from sp3_dir/test
|
|
t2#P#p1#SP#s3.ibd
|
|
ALTER TABLE t2 EXCHANGE PARTITION s0 WITH TABLE t1;
|
|
SELECT * FROM t1;
|
|
id purchased
|
|
1 1987-05-12
|
|
2 1987-05-14
|
|
SELECT * FROM t2;
|
|
id purchased
|
|
1 1987-05-10
|
|
2 1987-05-08
|
|
3 1997-05-10
|
|
4 1997-05-18
|
|
# List files from t1_dir/test
|
|
t2#P#p0#SP#s0.ibd
|
|
# List files from sp0_dir/test
|
|
t1.ibd
|
|
# List files from sp3_dir/test
|
|
t2#P#p1#SP#s3.ibd
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/sp0_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (year(`purchased`))
|
|
SUBPARTITION BY HASH (to_days(`purchased`))
|
|
(PARTITION p0 VALUES LESS THAN (1990)
|
|
(SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/t1_dir/' ENGINE = InnoDB,
|
|
SUBPARTITION s1 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN (2000)
|
|
(SUBPARTITION s2 ENGINE = InnoDB,
|
|
SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/sp3_dir/' ENGINE = InnoDB)) */
|
|
ALTER TABLE t2 EXCHANGE PARTITION s0 WITH TABLE t1;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/t1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (year(`purchased`))
|
|
SUBPARTITION BY HASH (to_days(`purchased`))
|
|
(PARTITION p0 VALUES LESS THAN (1990)
|
|
(SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/sp0_dir/' ENGINE = InnoDB,
|
|
SUBPARTITION s1 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN (2000)
|
|
(SUBPARTITION s2 ENGINE = InnoDB,
|
|
SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/sp3_dir/' ENGINE = InnoDB)) */
|
|
# List files from t1_dir/test
|
|
t1.ibd
|
|
# List files from sp0_dir/test
|
|
t2#P#p0#SP#s0.ibd
|
|
# List files from sp3_dir/test
|
|
t2#P#p1#SP#s3.ibd
|
|
delete from t1;
|
|
insert into t1 values(3,'1997-05-8 '),(4,'1997-05-16');
|
|
ALTER TABLE t2 EXCHANGE PARTITION s3 WITH TABLE t1;
|
|
SELECT * FROM t1;
|
|
id purchased
|
|
3 1997-05-10
|
|
4 1997-05-18
|
|
SELECT * FROM t2;
|
|
id purchased
|
|
1 1987-05-12
|
|
2 1987-05-14
|
|
3 1997-05-08
|
|
4 1997-05-16
|
|
# List files from t1_dir/test
|
|
t2#P#p1#SP#s3.ibd
|
|
# List files from sp0_dir/test
|
|
t2#P#p0#SP#s0.ibd
|
|
# List files from sp3_dir/test
|
|
t1.ibd
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/sp3_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (year(`purchased`))
|
|
SUBPARTITION BY HASH (to_days(`purchased`))
|
|
(PARTITION p0 VALUES LESS THAN (1990)
|
|
(SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/sp0_dir/' ENGINE = InnoDB,
|
|
SUBPARTITION s1 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN (2000)
|
|
(SUBPARTITION s2 ENGINE = InnoDB,
|
|
SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/t1_dir/' ENGINE = InnoDB)) */
|
|
ALTER TABLE t2 EXCHANGE PARTITION s3 WITH TABLE t1;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/t1_dir/'
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`purchased` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (year(`purchased`))
|
|
SUBPARTITION BY HASH (to_days(`purchased`))
|
|
(PARTITION p0 VALUES LESS THAN (1990)
|
|
(SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/sp0_dir/' ENGINE = InnoDB,
|
|
SUBPARTITION s1 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN (2000)
|
|
(SUBPARTITION s2 ENGINE = InnoDB,
|
|
SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/sp3_dir/' ENGINE = InnoDB)) */
|
|
# List files from t1_dir/test
|
|
t1.ibd
|
|
# List files from sp0_dir/test
|
|
t2#P#p0#SP#s0.ibd
|
|
# List files from sp3_dir/test
|
|
t2#P#p1#SP#s3.ibd
|
|
DROP table t1,t2;
|
|
check table_id after exchange partition
|
|
CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b VARCHAR(64)) ENGINE = INNODB ;
|
|
CREATE TABLE t2 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b VARCHAR(64)) ENGINE = INNODB PARTITION BY LIST (a) (PARTITION p1 VALUES IN (1,3,5,7,9) ,PARTITION p2 VALUES IN (0,2,4,6,8) );
|
|
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
|
|
include/assert.inc [Table id of t2 exchanged with t1]
|
|
add column after exchange partition
|
|
ALTER TABLE t2 ADD COLUMN j INT,ALGORITHM=INSTANT;
|
|
ALTER TABLE t2 ADD COLUMN k INT,ALGORITHM=COPY;
|
|
DROP TABLE t1,t2;
|
|
exchange second partition
|
|
CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b VARCHAR(64)) ENGINE = INNODB ;
|
|
CREATE TABLE t2 (a INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b VARCHAR(64)) ENGINE = INNODB PARTITION BY LIST (a) (PARTITION p1 VALUES IN (1,3,5,7,9) ,PARTITION p2 VALUES IN (0,2,4,6,8) );
|
|
ALTER TABLE t2 EXCHANGE PARTITION p2 WITH TABLE t1;
|
|
include/assert.inc [Table id of t2 is same after exchange it's 2nd partition with table t1]
|
|
add column after exchange partition
|
|
ALTER TABLE t2 ADD COLUMN j INT,ALGORITHM=INSTANT;
|
|
ALTER TABLE t2 ADD COLUMN k INT,ALGORITHM=COPY;
|
|
DROP TABLE t1,t2;
|
|
#
|
|
Bug#28687608 exchange partition with different row_format
|
|
#
|
|
SET @orig_default_row_format = @@global.innodb_default_row_format;
|
|
SET GLOBAL innodb_default_row_format=COMPACT;
|
|
CREATE TABLE t1 (
|
|
col1 INT UNSIGNED,
|
|
col2 DATETIME
|
|
)
|
|
ENGINE=INNODB
|
|
PARTITION BY RANGE COLUMNS(col2)
|
|
(
|
|
PARTITION p0 VALUES LESS THAN ('1960-01-01'),
|
|
PARTITION p1 VALUES LESS THAN ('1980-01-01')
|
|
);
|
|
INSERT INTO t1 VALUES ('1','1958-02-17 06:25:34');
|
|
INSERT INTO t1 VALUES ('2','1962-11-23 11:06:06');
|
|
SET GLOBAL innodb_default_row_format=DYNAMIC;
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN ('2000-01-01'));
|
|
INSERT INTO t1 VALUES ('3','1999-10-13 05:28:46'), ('4', '1996-09-26 20:00:00');
|
|
SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
|
|
WHERE NAME LIKE '%t1%' ORDER BY TABLE_ID;
|
|
NAME ROW_FORMAT
|
|
test/t1#P#p0 Compact
|
|
test/t1#P#p1 Compact
|
|
test/t1#P#p2 Dynamic
|
|
CREATE TABLE t2 LIKE t1;
|
|
ALTER TABLE t2 REMOVE PARTITIONING;
|
|
exchange compact partition with dynamic;
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
|
|
ERROR HY000: Non matching attribute 'ROW_FORMAT' between partition and table
|
|
ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE t2;
|
|
DROP TABLE t1,t2;
|
|
SET GLOBAL innodb_default_row_format=COMPACT;
|
|
CREATE TABLE t1 (a INT,
|
|
b VARCHAR(55),
|
|
PRIMARY KEY (a))
|
|
PARTITION BY RANGE (a)
|
|
SUBPARTITION BY HASH(a)
|
|
(PARTITION p0 VALUES LESS THAN (100)
|
|
(SUBPARTITION sp0,
|
|
SUBPARTITION sp1),
|
|
PARTITION p1 VALUES LESS THAN (200)
|
|
(SUBPARTITION sp2,
|
|
SUBPARTITION sp3));
|
|
SET GLOBAL innodb_default_row_format=DYNAMIC;
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (300)
|
|
(SUBPARTITION sp4,
|
|
SUBPARTITION sp5));
|
|
INSERT INTO t1(b,a) VALUES('first',40),('second',91),('third',140),
|
|
('forth',191);
|
|
SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
|
|
WHERE NAME LIKE '%t1%' ORDER BY TABLE_ID;
|
|
NAME ROW_FORMAT
|
|
test/t1#P#p0#SP#sp0 Compact
|
|
test/t1#P#p0#SP#sp1 Compact
|
|
test/t1#P#p1#SP#sp2 Compact
|
|
test/t1#P#p1#SP#sp3 Compact
|
|
test/t1#P#p2#SP#sp4 Dynamic
|
|
test/t1#P#p2#SP#sp5 Dynamic
|
|
CREATE TABLE t2 like t1;
|
|
ALTER TABLE t2 REMOVE PARTITIONING;
|
|
ALTER TABLE t1 EXCHANGE PARTITION sp2 WITH TABLE t2;
|
|
ERROR HY000: Non matching attribute 'ROW_FORMAT' between partition and table
|
|
ALTER TABLE t1 EXCHANGE PARTITION sp4 WITH TABLE t2;
|
|
DROP TABLE t1,t2;
|
|
SET @@global.innodb_default_row_format=@orig_default_row_format;
|