MySQL Bugs: #30826: Falcon - OPTIMIZE PARTITION not supported.

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

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`;

GDB back trace

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

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>

I can easily reproduce this on Windows.  It shows a divide by zero error.

Simple one line fix is being tested.

The fix is in...

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

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.

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.

It fails, because partition engine  does not support "optimize" until WL#4176  is implemented. Falcon is not called here at all.

It appears a general server behavior, not falcon specific.
ha_partition::handle_opt_partitions currently always returns HA_ADMIN_NOT_IMPLEMENTED

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.

Not sure that you are waiting feedback from me. If yes, how can I help?

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.

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.