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 ?