Bug #32781 ALTER TABLE OPTIMIZE|REPAIR PARTITION fails on innodb tables
Submitted: 27 Nov 2007 17:51 Modified: 3 Dec 2007 17:53
Reporter: Philip Stoev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.23-BK OS:Any
Assigned to: CPU Architecture:Any

[27 Nov 2007 17:51] Philip Stoev
Description:
ALTER TABLE OPTIMIZE PARTITION appears to not work on innodb tables.

Multiple-partition tables are also affected; other partition types are also affected; tables with actual rows are also affected; myisam works as expected; 

How to repeat:
mysql> CREATE TABLE part1_range (f1 integer) TYPE=innodb PARTITION BY RANGE (f1) ( PARTITION p0 VALUES LESS THAN (MAXVALUE));
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

Suggested fix:
Make sure OPTIMIZE PARTITION works.
[27 Nov 2007 19:57] Miguel Solorzano
Thank you for the bug report.

[miguel@skybr 5.1]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.23-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE part1_range (f1 integer) TYPE=innodb PARTITION BY RANGE (f1) (
    -> PARTITION p0 VALUES LESS THAN (MAXVALUE));
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> alter table part1_range optimize partition p0;
ERROR 1030 (HY000): Got error -7 from storage engine
mysql> alter table part1_range engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table part1_range optimize partition p0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
[28 Nov 2007 9:34] Philip Stoev
REPAIR PARTITION is also affected
[3 Dec 2007 17:53] Philip Stoev
This bug is a duplicate to BUG#32932