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

[12 Jun 2015 11:13] Sergei Golubchik
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;
[12 Jun 2015 13:34] Umesh Shastry
Hello Sergei Golubchik,

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

Thanks,
Umesh
[3 Dec 2015 18:14] Jon Stephens
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.