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 ?