Bug #95486 Allow to ALTER the ROW_FORMAT of a partition.
Submitted: 23 May 2019 9:54 Modified: 23 May 2019 13:17
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:5.7.26 OS:Any
Assigned to: CPU Architecture:Any

[23 May 2019 9:54] Jean-François Gagné
Description:
Hi,

this is related to Bug#95478 and Bug#95484, and it is a collaboration with Kristofer Grahn ([1]).

[1]: https://mysqlcommunity.slack.com/archives/C8R1336M7/p1558597468017000?thread_ts=1558557714...

In Bug#95484, I showed that it is possible to have a partitioned table with different ROW_FORMAT for different partitions.  But from what I can find in the manual ([2]), there is no syntax for ALTERing the ROW_FORMAT of a single partition (only for the whole table).

[2]: https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

Please consider adding syntax for ALTERing the ROW_FORMAT of a partition.

Many thanks for looking into that,

JFG

How to repeat:
-- BEGIN copy of Bug#95484.

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

-- END copy of Bug#95484.

# I have that in my Linux filesystem.

test_jfg$ ls -l
total 496
-rw-r----- 1 jgagne jgagne    65 May 23 09:47 db.opt
-rw-r----- 1 jgagne jgagne  8628 May 23 09:47 t1.frm
-rw-r----- 1 jgagne jgagne 98304 May 23 09:47 t1#P#p1.ibd
-rw-r----- 1 jgagne jgagne 98304 May 23 09:47 t1#P#p2.ibd
-rw-r----- 1 jgagne jgagne 98304 May 23 09:47 t1#P#p3.ibd
-rw-r----- 1 jgagne jgagne 98304 May 23 09:47 t1#P#p4.ibd
-rw-r----- 1 jgagne jgagne 98304 May 23 09:47 t1#P#p5.ibd

-- If I now want all partitions to be ROW_FORMAT=Dynamic, I could do...

mysql [localhost:5726] {msandbox} ((none)) > ALTER TABLE test_jfg.t1 ROW_FORMAT=Dynamic;
Query OK, 0 rows affected (0.13 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%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 | Barracuda   | Dynamic    |
| test_jfg/t1#P#p5 | Barracuda   | Dynamic    |
+------------------+-------------+------------+
5 rows in set (0.00 sec)

# But this looks like all partitions were rebuilt as shown with the content of the filesystem (see updated date of all files).

test_jfg$ ls -l
total 496
-rw-r----- 1 jgagne jgagne    65 May 23 09:47 db.opt
-rw-r----- 1 jgagne jgagne  8628 May 23 09:48 t1.frm
-rw-r----- 1 jgagne jgagne 98304 May 23 09:48 t1#P#p1.ibd
-rw-r----- 1 jgagne jgagne 98304 May 23 09:48 t1#P#p2.ibd
-rw-r----- 1 jgagne jgagne 98304 May 23 09:48 t1#P#p3.ibd
-rw-r----- 1 jgagne jgagne 98304 May 23 09:48 t1#P#p4.ibd
-rw-r----- 1 jgagne jgagne 98304 May 23 09:48 t1#P#p5.ibd

-- If would be be much better to be able to do something like...

ALTER TABLE test_jfg.t1 ALTER PARTITION p4 ROW_FORMAT=Dynamic, ALTER PARTITION p5 ROW_FORMAT=Dynamic;

Suggested fix:
Allow some sort of syntax like below:

ALTER TABLE test_jfg.t1 ALTER PARTITION p4 ROW_FORMAT=Dynamic, ALTER PARTITION p5 ROW_FORMAT=Dynamic;
[23 May 2019 13:17] MySQL Verification Team
Salut Jean-Francois,

i agree with you 100 %.

Verified as reported.