Description:
I have a partition table named par_xxx with dynamic row_format.
When I modify the table row_format to compressed; find the metadata information_schema.tables row_format column show the error value.
as the docs description: https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-options
`ROW_FORMAT`
> Defines the physical format in which the rows are stored.
But when I change the table row_format, The old value is still displayed in the information_schema.tables.row_format;
This issue was not found for non-partitioned tables
How to repeat:
MySQL [sbtest]> show variables like 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.35 |
+---------------+--------+
MySQL [sbtest]> CREATE TABLE par_xxx (id INT, amount DECIMAL(7,2), tr_date DATE)
-> ENGINE=INNODB
-> PARTITION BY HASH( MONTH(tr_date) )
-> PARTITIONS 6;
Query OK, 0 rows affected (0.092 sec)
MySQL [sbtest]>
MySQL [sbtest]> select * from information_schema.tables where table_schema = 'sbtest' and table_name = 'par_xxx';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
| def | sbtest | par_xxx | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 98304 | 0 | 0 | 0 | NULL | 2024-03-04 18:36:46 | NULL | NULL | utf8mb4_general_ci | NULL | partitioned | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
1 row in set (0.004 sec)
MySQL [sbtest]>
MySQL [sbtest]> alter table par_xxx row_format = compressed;
Query OK, 0 rows affected (0.236 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [sbtest]>
MySQL [sbtest]> select * from information_schema.tables where table_schema = 'sbtest' and table_name = 'par_xxx';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+-----------------------------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+-----------------------------------+---------------+
| def | sbtest | par_xxx | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 98304 | 0 | 0 | 0 | NULL | 2024-03-04 18:36:46 | NULL | NULL | utf8mb4_general_ci | NULL | row_format=COMPRESSED partitioned | |
Suggested fix:
When changing the row format, the metadata information of the tables info_schema view is corrected simultaneously
