MySQL Bugs: #62505: ALTER TABLE ADD PARTITION fails for LIST partitions with more than 16 items

Bug #62505	ALTER TABLE ADD PARTITION fails for LIST partitions with more than 16 items
Submitted:	22 Sep 2011 21:19	Modified:	20 Dec 2011 15:45
Reporter:	Nicholas Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.5.15, 5.5.17	OS:	Any (Windows 7 64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, list, partition, regression

Description:
Adding a partition to an already existing LIST partitioned table does not work if the number of items in the new partition is greater than 16.  I have tested this to affect MYISAM and INNODB engines.  The error is:

[Error Code: 1657, SQL State: HY000]  Cannot have more than one value for this type of LIST partitioning

Obviously you can have more than one item in a LIST partition.  Also, it works fine when the number of items is less than or equal to 16.  This was not happening in MySQL 5.1.

The same error occurs if you try to add a partition through REORGANIZE when number of items in one of the partitions is greater than 16.

This 16 item limit for some reason does not apply in the initial PARTITION BY LIST command which can be done either in the CREATE TABLE or ALTER TABLE command.

Tests were performed from application Java code directly and from DbVisualizer.  JDBC connectors 5.1.11 and 5.1.17 (latest available) were tested with same result.  MySQL versions 5.1.41 and 5.5.15 were tested.  The problem only affects MySQL 5.5.15.

How to repeat:
CREATE TABLE p_test (int_id int, timeslice_id smallint, period_duration int, kpiValue float, primary key (int_id)) ENGINE=MYISAM
-- SUCCESS

ALTER TABLE p_test PARTITION BY LIST(int_id) (PARTITION part_1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20));
-- SUCCESS with 20 items because this is initial partitioning action

ALTER TABLE p_test ADD PARTITION (PARTITION part_2 VALUES in (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40))
-- FAIL, because number of items > 16 during partition add
-- [Error Code: 1657, SQL State: HY000]  Cannot have more than one value for this type of LIST partitioning

ALTER TABLE p_test ADD PARTITION (PARTITION part_2 VALUES in (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35))
-- SUCCESS because number of items is <= 16

ALTER TABLE p_test ADD PARTITION (PARTITION part_3 VALUES in (40,41))
-- SUCCESS because number of items is <= 16

Suggested fix:
Get rid of the limit.

Thank you for the bug report. Indeed, in 5.1 it works:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE p_test (int_id int, timeslice_id smallint, period_duration int, kpiValue
    -> float, primary key (int_id)) ENGINE=MYISAM
    -> ;
Query OK, 0 rows affected (0.19 sec)

mysql> ALTER TABLE p_test PARTITION BY LIST(int_id) (PARTITION part_1 VALUES IN
    -> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20));
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE p_test ADD PARTITION (PARTITION part_2 VALUES in
    -> (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40))
    -> ;
Query OK, 0 rows affected (0.99 sec)
Records: 0  Duplicates: 0  Warnings: 0

While in 5.5 it does not work:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.17-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE p_test (int_id int, timeslice_id smallint, period_duration int, kpiValue float, primary key (int_id)) ENGINE=MYISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE p_test PARTITION BY LIST(int_id) (PARTITION part_1 VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20));
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE p_test ADD PARTITION (PARTITION part_2 VALUES in (21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40));
ERROR 1657 (HY000): Cannot have more than one value for this type of LIST partitioning

Looks like a regression bug to me.

I see this issue has not been assigned or given a target fix version.  Can you tell me when I might be able to expect that this issue will be fixed and available?

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix as follows in the 5.6.5 changelog:

        Adding a partition to an existing LIST-partitioned table
        did not work correctly if the number of items in the new
        partition was greater than 16. This could happen when trying to
        add a partition using an ALTER TABLE ... ADD PARTITION
        statement, or an ALTER TABLE ... REORGANIZE PARTITION statement.

        This 16-item limit was not apparent when using either CREATE
        TABLE ... PARTITION BY LIST or ALTER TABLE ... PARTITION BY
        LIST.

Closed.