Mysql – Can not add list partitions

MySQLpartitioning

Following is the create table statement that is working as expected. But if I have a table without any partitions and if I try add partitions, it seems that I can not add more than 16 values in it.

   CREATE TABLE `mypart` (
      `part_id` int(11) DEFAULT NULL
    ) ENGINE=MyISAM 
    PARTITION BY LIST (part_id)
    (
    PARTITION Jan10 VALUES IN (1,2,3,4,5) ENGINE = MyISAM,
    PARTITION Mar10 VALUES IN (734201,734202,734203,734204,734205,734206,734207,734208,734209,734210,734211,734212,734213,734214,734215,734216, 734217) ENGINE = MyISAM
    );

All the following statements should execute with success and create a table as shown above.

drop table mypart;
create table mypart (`part_id` int) ENGINE=MyISAM ;
alter table mypart partition by list(part_id) (PARTITION Jan10 VALUES IN (1, 2, 3, 4, 5));

mysql> alter table mypart add PARTITION (PARTITION Mar10 VALUES IN (734201, 734202, 734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213,  734214, 734215,734216, 734217  ) ) ;
ERROR 1657 (HY000): Cannot have more than one value for this type of LIST partitioning

At this point, the second alter table statement does not work and hence I can not have the 17 values for Mar10 partition as shown in the first example. If I delete the last value 734217 then the alter table statement is successful!

mysql> alter table mypart add PARTITION (PARTITION Mar10 VALUES IN (734201, 734202, 734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213,  734214, 734215,734216));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Is there some limit in the alter table statement that does not applies to create table statement ?

Best Answer

This is a bug that's been fixed in MySQL 5.6.5.

See http://bugs.mysql.com/bug.php?id=62505

Related Question