Bug #30826 Falcon - OPTIMIZE PARTITION not supported.
Submitted: 5 Sep 2007 11:32 Modified: 20 Sep 2008 11:53
Reporter: Santo Leto Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:6.0.2-alpha OS:Any
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, falcon, OPTIMIZE PARTITION
Triage: D3 (Medium)

[5 Sep 2007 11:32] Santo Leto
Description:
ALTER TABLE...OPTIMIZE PARTITION on a Falcon Table causes the Server to crash.

How to repeat:
Test 1) [All works fine]

DROP DATABASE IF EXISTS falcon_test2;

CREATE DATABASE falcon_test2;

CREATE TABLE  `falcon_test2`.`falcon_table4` (
	PRIMARY KEY  (`id`), 
	`id` BIGINT NOT NULL  AUTO_INCREMENT ,
	`field1` CHAR(50) NOT NULL 
)ENGINE = FALCON
PARTITION BY Hash (id) 
PARTITIONS 4;

INSERT INTO `falcon_test2`.`falcon_table4` (`id`, `field1`) VALUES('3','value3');
INSERT INTO `falcon_test2`.`falcon_table4` (`id`, `field1`) VALUES('5','value5');
INSERT INTO `falcon_test2`.`falcon_table4` (`id`, `field1`) VALUES('7','value7');

ALTER TABLE `falcon_test2`.`falcon_table4` OPTIMIZE PARTITION `p1`;

Test 2) [The server crashes]

DROP DATABASE IF EXISTS falcon_test2;

CREATE DATABASE falcon_test2;

CREATE TABLE  `falcon_test2`.`falcon_table5` (
	PRIMARY KEY  (`id`), 
	`id` BIGINT NOT NULL  AUTO_INCREMENT ,
	`field1` CHAR(50) NOT NULL 
)ENGINE = FALCON
PARTITION BY Hash (id) 
PARTITIONS 4;

ALTER TABLE `falcon_test2`.`falcon_table5` OPTIMIZE PARTITION `p1`;
[5 Sep 2007 12:05] Miguel Solorzano
GDB back trace

Attachment: bt-falcon-30826.txt (text/plain), 17.74 KiB.

[5 Sep 2007 12:06] Miguel Solorzano
Thank you for the bug report.

[miguel@skybr 6.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.3-alpha-debug Source distribution

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

mysql> DROP DATABASE IF EXISTS falcon_test2;
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> 
mysql> CREATE DATABASE falcon_test2;
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> CREATE TABLE  `falcon_test2`.`falcon_table4` (
    -> PRIMARY KEY  (`id`), 
    -> `id` BIGINT NOT NULL  AUTO_INCREMENT ,
    -> `field1` CHAR(50) NOT NULL 
    -> )ENGINE = FALCON
    -> PARTITION BY Hash (id) 
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.61 sec)

mysql> 
mysql> INSERT INTO `falcon_test2`.`falcon_table4` (`id`, `field1`) VALUES('3','value3');
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO `falcon_test2`.`falcon_table4` (`id`, `field1`) VALUES('5','value5');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `falcon_test2`.`falcon_table4` (`id`, `field1`) VALUES('7','value7');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> ALTER TABLE `falcon_test2`.`falcon_table4` OPTIMIZE PARTITION `p1`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DROP DATABASE IF EXISTS falcon_test2;
Query OK, 1 row affected (0.80 sec)

mysql> 
mysql> CREATE DATABASE falcon_test2;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> CREATE TABLE  `falcon_test2`.`falcon_table5` (
    -> PRIMARY KEY  (`id`), 
    -> `id` BIGINT NOT NULL  AUTO_INCREMENT ,
    -> `field1` CHAR(50) NOT NULL 
    -> )ENGINE = FALCON
    -> PARTITION BY Hash (id) 
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> ALTER TABLE `falcon_test2`.`falcon_table5` OPTIMIZE PARTITION `p1`;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[5 Sep 2007 21:06] Kevin Lewis
I can easily reproduce this on Windows.  It shows a divide by zero error.
[5 Sep 2007 21:27] Kevin Lewis
Simple one line fix is being tested.
[6 Sep 2007 3:50] Kevin Lewis
The fix is in...
[30 Nov 2007 17:23] Hakan Küçükyılmaz
Works now with latest code

[18:23] root@(none)>CREATE TABLE  `falcon_test2`.`falcon_table5` (
    -> PRIMARY KEY  (`id`),
    -> `id` BIGINT NOT NULL  AUTO_INCREMENT ,
    -> `field1` CHAR(50) NOT NULL
    -> )ENGINE = FALCON
    -> PARTITION BY Hash (id)
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.02 sec)

6.0.4-alpha-debug
[18:23] root@(none)>ALTER TABLE `falcon_test2`.`falcon_table5` OPTIMIZE PARTITION `p1`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
[3 Dec 2007 14:23] MC Brown
A note has been added to the 6.0.4 changelog: 

Running ALTER TABLE ... OPTIMIZE PARTITION on a Falcon table, a 'divide by zero' error would be reported during a server crash.
[28 Apr 2008 18:13] Kevin Lewis
Iven though the crash in this bug no longer occures, the testcase does not work yet because Falcon does not support Optimize Partition.

mysql> ALTER TABLE t1 OPTIMIZE PARTITION `p1`;
ERROR 1178 (42000): The storage engine for the table doesn't support optimize partition

After some discussion, we decided that we should support the API, but not actually do anything to the file or partition to optimize it.  The test will run, and any client application will also run without error.  

In the future, when we have some idea of what we will do to optimize a partition, we can add that effort in a completely backward compatible manner.
[29 Apr 2008 19:12] Vladislav Vaintroub
It fails, because partition engine  does not support "optimize" until WL#4176  is implemented. Falcon is not called here at all.
[24 Jul 2008 21:57] Vladislav Vaintroub
It appears a general server behavior, not falcon specific.
ha_partition::handle_opt_partitions currently always returns HA_ADMIN_NOT_IMPLEMENTED
[27 Aug 2008 8:59] Mikael Ronström
This should be fixed now after the push of Bug#20129 and WL#4176.
This fix is currently in 6.0-bugteam tree.
Please retry this using that tree.
[2 Sep 2008 9:13] Santo Leto
Not sure that you are waiting feedback from me. If yes, how can I help?
[4 Sep 2008 7:39] Mattias Jonsson
The patch is in a the bugteam tree, here is some results from both partitioned and non-partitioned falcon table which seems consistent to me:
create table t1 (a INT) engine falcon;
analyze table t1;
Table   Op      Msg_type        Msg_text
test.t1 analyze note    The storage engine for the table doesn't support analyze
check table t1;
Table   Op      Msg_type        Msg_text
test.t1 check   status  OK
optimize table t1;
Table   Op      Msg_type        Msg_text
test.t1 optimize        status  OK
repair table t1;
Table   Op      Msg_type        Msg_text
test.t1 repair  status  OK
drop table t1;
create table t1 (a INT) engine falcon partition by hash (a) partitions 2;
analyze table t1;
Table   Op      Msg_type        Msg_text
test.t1 analyze note    The storage engine for the table doesn't support analyze
alter table t1 analyze partition all;
Table   Op      Msg_type        Msg_text
test.t1 analyze note    The storage engine for the table doesn't support analyze
check table t1;
Table   Op      Msg_type        Msg_text
test.t1 check   status  OK
alter table t1 check partition all;
Table   Op      Msg_type        Msg_text
test.t1 check   status  OK
optimize table t1;
Table   Op      Msg_type        Msg_text
test.t1 optimize        status  OK
alter table t1 optimize partition all;
Table   Op      Msg_type        Msg_text
test.t1 optimize        status  OK
repair table t1;
Table   Op      Msg_type        Msg_text
test.t1 repair  status  OK
alter table t1 repair partition all;
Table   Op      Msg_type        Msg_text
test.t1 repair  status  OK
drop table t1;

It will be pushed into the mysql-6.0 main tree soon, and that is accessible from launchpad. I will ask for new feed back after that, and possibly set the status to 'Duplicate' of bug#20129 if acceptable.
[20 Sep 2008 11:53] Mattias Jonsson
This is a duplicate of bug#20129. Now the results are the same between partitioned and non-partitioned falcon table (see previous column). That fix is now in the main 6.0 tree now.