Bug #95484 EXCHANGE PARTITION works wrong/werid with different ROW_FORMAT.
Submitted: 23 May 9:36 Modified: 23 Jul 6:59
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.7.26 OS:Any
Assigned to: CPU Architecture:Any

[23 May 9:36] Jean-François Gagné
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.
[23 May 9:55] Jean-François Gagné
Related Bug#95486.
[23 May 13:13] Sinisa Milivojevic
Hi,

Thank you for your report.

I do not think that this is a bug, but a very good feature request.

Verified as a feature request.
[23 Jul 6:59] Jean-François Gagné
I have not tested, but this looked fixed in 5.7.27.

From https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-27.html

I can read:

Partitioning: ALTER TABLE ... EXCHANGE PARTITION failed with the error Non matching attribute 'ROW_FORMAT' between partition and table when the partitioned table had partitions using different row formats, even when the partition to be exchanged used the same row format as the non-partitioned table. (Bug #28687608)
[23 Jul 13:50] Sinisa Milivojevic
Salut Jean-Francois,

Bug that was fixed was internal. Hence , this bug will remain verified until it is declared duplicate or until someone checks that it is fixed with the same patch.
[6 Aug 8:32] Erlend Dahl
I checked this with the responsible team, and got the following comment from Dmitry L:

"Although the issue seems pretty similar I think it is actually different.

The old bug report (Bug#28687608) was about incorrect check that didn't allow to swap partition and table in the same row format in partitioned tables with a mix of row formats, while the new bug report is about exchanging partition and tables in different formats, i.e. the request is to abolish this check altogether."

Based on this I think this report should remain in 'Verified'.
[6 Aug 18:16] Sinisa Milivojevic
Erlend,

Thanks a lot ......