Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
I would like to raise FR for EXCHANGE PARTITION to work with different ROW_FORMAT. I found similar FR reported upstream here https://bugs.mysql.com/bug.php?id=95484
Currently, EXCHANGE PARTITION with different row format fails with error "Non matching attribute 'ROW_FORMAT' between partition and table" as shown below:
CREATE TABLE e (
|
id INT NOT NULL,
|
fname VARCHAR(30), |
lname VARCHAR(30) |
)
|
PARTITION BY RANGE (id) (
|
PARTITION p0 VALUES LESS THAN (50), |
PARTITION p1 VALUES LESS THAN (100), |
PARTITION p2 VALUES LESS THAN (150)); |
Query OK, 0 rows affected (0.034 sec) |
|
|
SET GLOBAL innodb_default_row_format=compact;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
ALTER TABLE e ADD PARTITION (PARTITION p4 VALUES LESS THAN (200)); |
Query OK, 0 rows affected (0.029 sec) |
|
|
SET GLOBAL innodb_default_row_format=dynamic;
|
Query OK, 0 rows affected (0.000 sec) |
|
|
CREATE TABLE e2 LIKE e;
|
Query OK, 0 rows affected (0.070 sec) |
|
|
SELECT TABLE_SCHEMA, TABLE_NAME, ROW_FORMAT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test" AND TABLE_NAME LIKE 'e%'; |
+--------------+------------+------------+
|
| TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT |
|
+--------------+------------+------------+
|
| test | e | Dynamic |
|
| test | e2 | Dynamic |
|
+--------------+------------+------------+
|
|
|
SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE "test/e%"; |
+--------------+------------+------------+
|
| NAME | ROW_FORMAT | SPACE_TYPE |
|
+--------------+------------+------------+
|
| test/e#P#p0 | Dynamic | Single |
|
| test/e#P#p1 | Dynamic | Single |
|
| test/e#P#p2 | Dynamic | Single |
|
| test/e#P#p4 | Compact | Single |
|
| test/e2#P#p0 | Dynamic | Single |
|
| test/e2#P#p1 | Dynamic | Single |
|
| test/e2#P#p2 | Dynamic | Single |
|
| test/e2#P#p4 | Dynamic | Single |
|
+--------------+------------+------------+
|
You can notice here that partition "p4" is created with "compact" row format while all others partitions are with default row format "dynamic"
ALTER TABLE e2 REMOVE PARTITIONING;
|
Query OK, 0 rows affected (0.107 sec) |
|
|
INSERT INTO e VALUES
|
(139, "Jim", "Smith"), |
(99, "Mary", "Jones"), |
(16, "Frank", "White"), |
(20, "Linda", "Black") |
(180, "irfan", "Brown"); |
Query OK, 5 rows affected (0.005 sec) |
|
|
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'; |
+----------------+------------+
|
| PARTITION_NAME | TABLE_ROWS |
|
+----------------+------------+
|
| p0 | 2 | |
| p1 | 1 | |
| p2 | 1 | |
| p4 | 1 | |
+----------------+------------+
|
4 rows in set (0.000 sec) |
So, each partition contains one row.
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
|
Query OK, 0 rows affected (0.043 sec) |
Partition exchange works as p0 partition is created with same row format as table i.e. dynamic row format.
ALTER TABLE e EXCHANGE PARTITION p4 WITH TABLE e2;
|
ERROR 1731 (HY000): Non matching attribute 'ROW_FORMAT' between partition and table |
However, p4 partition exchange fails because of different row format as p4 partition is created with COMPACT row format while table e2 is created with DYNAMIC row format.