|
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.
|