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;