MySQL Bugs: #48276: Partitions: can't add column if subpartition exists

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

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

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.

bzr branch bzr+ssh://pgulutzan@bk-internal.mysql.com/bzrroot/server/mysql-5.1 mysql-5.1
...
BUILD/compile-pentium-max

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

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.

Pushed into mysql-5.1-bugteam and mysql-pe

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)

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.

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)

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)

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)

Also documented in the 5.5.1, 5.6.0, and 6.0.14 changelogs. Closed.

5.6.0 changelog entry unneeded.

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)

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)

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)

No additional changelog entries required. Returning to Closed state.