Bug #48276 Partitions: can't add column if subpartition exists
Submitted: 23 Oct 2009 22:37 Modified: 15 Mar 2010 5:22
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.41 OS:Any (SUSE 11.1 64-bit)
Assigned to: Mattias Jonsson CPU Architecture:Any

[23 Oct 2009 22:37] Peter Gulutzan
Description:
I create a table with a subpartition.
I try to alter and add a new column.
I fail.

How to repeat:
mysql> create table t (s1 int, s2 int) partition by list (s1)
    -> subpartition by hash (s2) (partition p1 values in (1));
Query OK, 0 rows affected (0.09 sec)

mysql> alter table t add column s3 int;
ERROR 1499 (HY000): Too many partitions (including subpartitions) were defined
[24 Oct 2009 0:44] MySQL Verification Team
Thank you for the bug report. Which exactly source tree it was used to build the server?. I couldn't repeat with latest source of mysql-5.1 on Ubuntu 64-bit:

miguel@laras:~/dbs$ 5.1/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-debug Source distribution

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

mysql> create database qw;
Query OK, 1 row affected (0.00 sec)

mysql> use qw
Database changed
mysql> create table t (s1 int, s2 int) partition by list (s1)
    -> subpartition by hash (s2) (partition p1 values in (1));
Query OK, 0 rows affected (0.12 sec)

mysql> alter table t add column s3 int;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thanks in advance.
[24 Oct 2009 14:15] Peter Gulutzan
bzr branch bzr+ssh://pgulutzan@bk-internal.mysql.com/bzrroot/server/mysql-5.1 mysql-5.1
...
BUILD/compile-pentium-max
[25 Oct 2009 16:14] Valeriy Kravchuk
Verified just as described with recent 5.1.41 from bzr on Mac OS X:

77-52-222-60:5.1 openxs$ bin/mysql -uroot -pmysql 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 2
Server version: 5.1.41-debug Source distribution

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

mysql> create table t (s1 int, s2 int) partition by list (s1)
    -> subpartition by hash (s2) (partition p1 values in (1));
Query OK, 0 rows affected (0.43 sec)

mysql> alter table t add column s3 int;
ERROR 1499 (HY000): Too many partitions (including subpartitions) were defined
[9 Nov 2009 10:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89756

3202 Mattias Jonsson	2009-11-09
      Bug#48276: can't add column if subpartition exists
      
      Bug when setting up default partitioning,
      used an uninitialized variabe.
     @ mysql-test/r/partition.result
        Bug#48276: can't add column if subpartition exists
        
        Added result
     @ mysql-test/t/partition.test
        Bug#48276: can't add column if subpartition exists
        
        Added test
     @ sql/sql_partition.cc
        Bug#48276: can't add column if subpartition exists
        
        even if is_create_table_ind was set, one tried to set no_subparts
        with the unitialized no_parts local variable.
        
        Fixed by rearrange the code to be to only execute
        the statements when is_create_table_ind was not set.
[18 Nov 2009 11:21] Mattias Jonsson
Pushed into mysql-5.1-bugteam and mysql-pe
[2 Dec 2009 8:07] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[3 Dec 2009 15:41] Jon Stephens
Documented bugfix in the 5.1.42 changelog as follows:

        In some cases, it was not possible to add a new column to a
        table that had subpartitions.

Closed.
[16 Dec 2009 8:39] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:46] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:52] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[16 Dec 2009 15:39] Jon Stephens
Also documented in the 5.5.1, 5.6.0, and 6.0.14 changelogs. Closed.
[7 Mar 2010 23:55] Paul DuBois
5.6.0 changelog entry unneeded.
[12 Mar 2010 14:13] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:29] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:44] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[15 Mar 2010 5:22] Jon Stephens
No additional changelog entries required. Returning to Closed state.