Description:
Hi,
this is a variation on Bug#95478 and a collaboration with Kristofer Grahn ([1]).
[1]: https://mysqlcommunity.slack.com/archives/C8R1336M7/p1558597468017000?thread_ts=1558557714...
In Bug#95478, I showed that EXCHANGE PARTITION does not work when table and partition does not have the same ROW_FORMAT. However, I think this should work. My logic behind this is that it is possible to have partitions of different ROW_FORMAT in the same partitioned table. See How to repeat for details.
Also, in How to repeat, I will show that is is actually possible, in a specific case, to exchange a partition with a table of different ROW_FORMAT. I understand that there is a test that is wrong somewhere.
Thanks for looking into that,
JFG
How to repeat:
# Using dbdeployer, create a test instance:
dbdeployer deploy single mysql_5.7.26
-- First, I will show that it is possible to have a partitioned table with partitions of different ROW_FORMAT.
mysql [localhost:5726] {msandbox} (information_schema) > CREATE DATABASE test_jfg;
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5726] {msandbox} (information_schema) > SET GLOBAL innodb_default_row_format=dynamic;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5726] {msandbox} (information_schema) > CREATE TABLE test_jfg.t1 (
-> id bigint unsigned NOT NULL AUTO_INCREMENT,
-> created_at datetime NOT NULL,
-> str1 varchar(50) DEFAULT NULL,
-> PRIMARY KEY (id,created_at)
-> ) PARTITION BY RANGE ( MONTH(`created_at`))
-> (PARTITION p1 VALUES LESS THAN (2),
-> PARTITION p2 VALUES LESS THAN (3));
Query OK, 0 rows affected (0.05 sec)
mysql [localhost:5726] {msandbox} (information_schema) > ALTER TABLE test_jfg.t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (4));
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5726] {msandbox} (information_schema) > SET GLOBAL innodb_default_row_format=compact;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5726] {msandbox} (information_schema) > ALTER TABLE test_jfg.t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (5));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5726] {msandbox} (information_schema) > SET GLOBAL innodb_default_row_format=redundant;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:5726] {msandbox} (information_schema) > ALTER TABLE test_jfg.t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (6));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5726] {msandbox} (information_schema) > SELECT TABLE_SCHEMA, TABLE_NAME, ROW_FORMAT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_jfg";
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT |
+--------------+------------+------------+
| test_jfg | t1 | Dynamic |
+--------------+------------+------------+
1 row in set (0.00 sec)
mysql [localhost:5726] {msandbox} ((none)) > SELECT NAME, FILE_FORMAT, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE "test_jfg%t1%";
+------------------+-------------+------------+
| NAME | FILE_FORMAT | ROW_FORMAT |
+------------------+-------------+------------+
| test_jfg/t1#P#p1 | Barracuda | Dynamic |
| test_jfg/t1#P#p2 | Barracuda | Dynamic |
| test_jfg/t1#P#p3 | Barracuda | Dynamic |
| test_jfg/t1#P#p4 | Antelope | Compact |
| test_jfg/t1#P#p5 | Antelope | Redundant |
+------------------+-------------+------------+
5 rows in set (0.00 sec)
-- So here, we have a partitioned table with different ROW_FORMAT for different partitions and with I_S.TABLES reporting the format as ROW_FORMAT=Dynamic.
-- Let's continue with a CREATE TABLE LIKE this table...
mysql [localhost:5726] {msandbox} ((none)) > CREATE TABLE test_jfg.t2 LIKE test_jfg.t1;
Query OK, 0 rows affected (0.09 sec)
mysql [localhost:5726] {msandbox} ((none)) > SELECT NAME, FILE_FORMAT, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE "test_jfg%t2%";
+------------------+-------------+------------+
| NAME | FILE_FORMAT | ROW_FORMAT |
+------------------+-------------+------------+
| test_jfg/t2#P#p1 | Antelope | Redundant |
| test_jfg/t2#P#p2 | Antelope | Redundant |
| test_jfg/t2#P#p3 | Antelope | Redundant |
| test_jfg/t2#P#p4 | Antelope | Redundant |
| test_jfg/t2#P#p5 | Antelope | Redundant |
+------------------+-------------+------------+
5 rows in set (0.00 sec)
-- Here, we have an example of Bug#95478 where t2 is not strictly identical of t1...
-- Now let's try to EXCHANGE PARTITION...
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5726] {msandbox} ((none)) > SELECT NAME, FILE_FORMAT, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE "test_jfg%t2%";
+-------------+-------------+------------+
| NAME | FILE_FORMAT | ROW_FORMAT |
+-------------+-------------+------------+
| test_jfg/t2 | Antelope | Redundant |
+-------------+-------------+------------+
1 row in set (0.00 sec)
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t1 EXCHANGE PARTITION p2 WITH TABLE test_jfg.t2;
ERROR 1731 (HY000): Non matching attribute 'ROW_FORMAT' between partition and table
-- Hum... we cannot exchange a partition of ROW_FORMAT=Dynamic with a table of ROW_FORMAT=Redundant.
-- I would expect this to be possible as I shown above that it is possible to have a partitioned table with partitions of different ROW_FORMAT.
-- But it gets weirder...
-- Now let's try to exchange p5 and t2, and I expect this to work as the ROW_FORMAT are the same, but...
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t1 EXCHANGE PARTITION p5 WITH TABLE test_jfg.t2;
ERROR 1731 (HY000): Non matching attribute 'ROW_FORMAT' between partition and table
-- Hum... why is this not working... Maybe the test is done with the ROW_FORMAT of the partitioned table, not of the actual partition...
-- Let's ALTER t2 to ROW_FORMAT=Dynamic, and now we should be able to exchange p2 with t2 as they are the same ROW_FORMAT...
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t2 ROW_FORMAT=Dynamic;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t1 EXCHANGE PARTITION p2 WITH TABLE test_jfg.t2;
Query OK, 0 rows affected (0.02 sec)
-- Ok, this worked, let's try to exchange p5 (ROW_FORMAT=Redundant) with t2 (ROW_FORMAT=Dynamic)...
mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t1 EXCHANGE PARTITION p5 WITH TABLE test_jfg.t2;
Query OK, 0 rows affected (0.02 sec)
-- So exchanging a partition with a table with different ROW_FORMAT actually worked here.
-- I understand the test is done with the ROW_FORMAT of the partitioned table, but it looks like this test just should not be done !
Suggested fix:
Allow exchanging partition with table even if the ROW_FORMAT is not matching.
