MySQL Bugs: #77333: ALTER TABLE ... PARTITION is not reexecution safe

Bug #77333	ALTER TABLE ... PARTITION is not reexecution safe
Submitted:	12 Jun 2015 11:13	Modified:	3 Dec 2015 18:14
Reporter:	Sergei Golubchik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.5, 5.5.45, 5.6.26, 5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
See "how to repeat". Note that after the second execution of SP or PS, the partitioning definition is incorrect.

How to repeat:
CREATE TABLE `t1` (
       `hid` bigint(20) unsigned NOT NULL,
       `itid` bigint(20) unsigned NOT NULL,
       `clocktime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       `values` double(16,4) NOT NULL,
       PRIMARY KEY (`hid`,`itid`,`clocktime`)
     ) ;
CREATE PROCEDURE `create_part_max`()
     alter table `t1`
     partition by range(unix_timestamp(clocktime)) (
     partition partMAX values less than MAXVALUE
     );

call create_part_max(); show create table t1;
call create_part_max(); show create table t1;

drop procedure create_part_max;

prepare stmt from "alter table `t1`
     partition by range(unix_timestamp(clocktime)) (
     partition partMAX values less than MAXVALUE
     )";

execute stmt; show create table t1;
execute stmt; show create table t1;

deallocate prepare stmt;

drop table t1;

Hello Sergei Golubchik,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

Hi Sergei,

This was fixed as part of another bug in MySQL 5.5.46, 5.6.27, and 5.7.9. See the relevant changelogs for documentation (now also tagged with this bug # for your reference).

cheers,

jon.