Bug #32932 alter table .. optimize partitions error -7 from storage engine

• Submitted: 3 Dec 2007 14:44
• Modified: 6 Dec 2007 10:10
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Duplicate
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.1.23
• OS: Any
• CPU Architecture: Any

[3 Dec 2007 14:44] Shane Bester

Description:
mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table `t1` (`a` date) engine=innodb partition by range (to_days(`a`))(partition p1 values less than (733230) engine = innodb);
Query OK, 0 rows affected (0.13 sec)

mysql> alter table `t1` optimize partition `p1`;
ERROR 1030 (HY000): Got error -7 from storage engine

#define HA_ADMIN_TRY_ALTER   -7

How to repeat:
drop table if exists `t1`;
create table `t1` (`a` date) engine=innodb partition by range (to_days(`a`))(partition p1 values less than (733230) engine = innodb);
alter table `t1` optimize partition `p1`;

[3 Dec 2007 15:31] Valeriy Kravchuk

Thank you for a bug report. Verified just as described.

[3 Dec 2007 17:53] Philip Stoev

Bug #32781 has been marked as a duplicate to this one.

[6 Dec 2007 10:10] Mattias Jonsson

Duplicate of Bug#27429. (Please verify after fixing that bug)

[10 Dec 2007 4:34] Lu Jingdong

The following is the executing process of the statement 
"alter table t1 optimize partition p1".
...
mysql_execute_command
|-- mysql_alter_table
    |-- fast_alter_partition_table
        |-- ha_partition::optimize_partitions
            |-- ha_partition::handle_opt_partitions
                |-- handle_opt_part
                    |-- ha_innobase::optimize          (engine=innodb)
The function "ha_innobase::optimize" in .../storage/innobase/handler/ha_innodb.cc returns error code "HA_ADMIN_TRY_ALTER" directly. So we get the wrong result.
For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.
I think we should consider the steps of the statement "OPTIMIZE TABLE" and do some operations according to the error code just like the function "mysql_admin_table" in .../sql/sql_table.cc.
mysql_admin_table
|-- ha_partition::optimize
    |-- ha_partition::handle_opt_partitions
        ...
|-- send_result_message:
    ...
    |-- swith (result_code)
        ...
        case HA_ADMIN_TRY_ALTER:
        {...
          result_code= mysql_recreate_table(thd, table);
         ...