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
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[13 Jan 2010 8:31] Joakim von Brandis
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.
[13 Jan 2010 9:41] Valeriy Kravchuk
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)
[14 Jan 2010 7:15] Marko Mäkelä
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.
[1 Sep 2010 19:00] Mattias Jonsson
Duplicate of bug#54679.
[2 Sep 2010 6:58] Mattias Jonsson
Wrote WL#5549 to allow ROW_FORMAT per partition.
[2 Sep 2010 7:04] Mattias Jonsson
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.