MySQL Bugs: #50300: ALTER TABLE REORGANIZE PARTITION .... changes row

Bug #50300	ALTER TABLE REORGANIZE PARTITION .... changes row_format of table
Submitted:	13 Jan 2010 8:31	Modified:	1 Sep 2010 19:00
Reporter:	Joakim von Brandis	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.41-enterprise-gpl-advanced-log, 5.5.1-m2	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	ALTER TABLE, reorganize partition, ROW_FORMAT

Description:
When reorganizing a compressed partitioned table, the row_format is changed, requiring a new "alter table .. row_format=compressed", which is _very_ heavy.

How to repeat:
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION pnew VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

mysql> show table status;

| Name                         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options                    | Comment |
+------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------------------+---------+
| test                         | InnoDB |      10 | Compressed |    3 |          16384 |       49152 |               0 |            0 |         0 |           NULL | NULL                | NULL        | NULL       | latin1_swedish_ci |     NULL | row_format=COMPRESSED partitioned |         |

alter table test reorganize partition pnew into (partition p3 values less than (30) engine=innodb,  PARTITION pnew VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

mysql> show table status;

| Name                         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options                    | Comment |
+------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+-----------------------------------+---------+
| test                         | InnoDB |      10 | Fixed      |    4 |          16384 |       65536 |               0 |            0 |         0 |           NULL | NULL                | NULL        | NULL       | latin1_swedish_ci |     NULL | row_format=COMPRESSED partitioned |         |

On my system, with a real table, the row_format becomes "Dynamic" or "Compact".
I cannot specify row_format during the "alter table reorganize..." statement.

Suggested fix:
Make alter table reorganize retain row_format.

Verified just as described, also - with 5.5.1-m2

openxs@suse:/home2/openxs/dbs/trunk> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.1-m2-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table test;
Query OK, 0 rows affected (0.54 sec)

mysql> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test` (   `id` int(11) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED PARTITION BY RANGE (id) (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,  PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,  PARTITION pnew VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.23 sec)

mysql> show table status like 'test%'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 3
 Avg_row_length: 16384
    Data_length: 49152
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED partitioned
        Comment:
1 row in set (0.01 sec)

mysql> alter table test reorganize partition pnew into (partition p3 values less than (30)
    -> engine=innodb,  PARTITION pnew VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'test%'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Fixed
           Rows: 4
 Avg_row_length: 16384
    Data_length: 65536
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED partitioned
        Comment:
1 row in set (0.01 sec)

To me, this looks like a bug in MySQL partitioning code, which cannot be fixed in a storage engine. MySQL should be preserving the table attributes for the partition tables. Besides, what is Row_format=Fixed in the SHOW TABLE STATUS output? InnoDB should report one of Redundant, Compact, Dynamic, or Compressed. In my opinion, the partitioning code should report the Row_format of one of the underlying tables instead of reporting a bogus value.

Duplicate of bug#54679.

Wrote WL#5549 to allow ROW_FORMAT per partition.

The reason Row_format Fixed was returned was that the old partitions had Compressed and the new ones had Compact (bug#54679), resulting in partitioning could not return a correct Row_format, and returned ROW_FORMAT_NOT_USED in get_row_type() on the table level, which in I_S.tables/show table status interpret as Fixed/Dynamic or compressed depending on HA_OPTION_PACK_RECORD and HA_OPTION_COMPRESS_RECORD.