Bug #14327 PARTITIONS clause not shown in SHOW CREATE TABLE output
Submitted: 26 Oct 2005 11:22 Modified: 19 Jan 2006 9:21
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.2 (pulled 2005-10-20) OS:Linux (SuSE 9.3, SuSE 10)
Assigned to: Mikael Ronström

[26 Oct 2005 11:22] Jon Stephens
Description:
The PARTITIONS clause is not shown in the output of SHOW CREATE TABLE for a table partitioned by HASH or KEY. Thus is there is currently no way to determine how many partitions there are for such a table other than by direct inspection of the filesystem.

How to repeat:
Example:

mysql> CREATE TABLE t1 (
    ->   id INT,
    ->   somedata VARCHAR(50),
    ->   startdate DATE
    -> )
    -> PARTITION BY HASH( YEAR(startdate) )
    -> PARTITIONS 5;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) default NULL,
  `somedata` varchar(50) default NULL,
  `startdate` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH ( YEAR(startdate))
1 row in set (0.00 sec)

Suggested fix:
Include the PARTITIONS clause in the SHOW CREATE TABLE output for hash and key partitioned tables. Default should be PARTITIONS 1.
[26 Oct 2005 21:35] Jorge del Conde
I was able to reproduce this bug under FC4 in a recent 5.1 clone
[2 Nov 2005 17:44] Mikael Ronström
Worked in partition management clone in review now
[20 Dec 2005 16:25] Matthias Leich
Another example:
CREATE TABLE t1 ( f1 BIGINT, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2
( PARTITION part1
(SUBPARTITION subpart11 STORAGE ENGINE = 'MYISAM' ,
SUBPARTITION subpart12 STORAGE ENGINE = 'MYISAM' ),
PARTITION part2 STORAGE ENGINE = 'MYISAM'
(SUBPARTITION subpart21 STORAGE ENGINE = 'MYISAM' ,
SUBPARTITION subpart22 STORAGE ENGINE = 'MYISAM' )
);
SHOW CREATE TABLE t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `f1` bigint(20) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 PARTITION BY HASH (f1) 
(PARTITION part1  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)

!! The complete SUBPARTITION part is missing.
My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
         bk-internal.mysql.com:/home/bk/mysql-5.1-wl2604-new
              last ChangeSet@1.1981, 2005-12-15
[23 Dec 2005 12:21] Matthias Leich
Please forget my last comment.
The output of SHOW CREATE TABLE was correct and fits to
the created files (there were no files for the subpartitions.)
So the reason for the observed effect is
   Bug#15961 Partitioning: Creation of subpartioned table without subpartioning
                     not rejected
The server accepts the CREATE TABLE STATEMENT and creates a table
without subpartitions instead of rejecting the statement.
[19 Jan 2006 8:44] Jon Stephens
This bug appears to be fixed in the 5.1 tree, but not in the 5.1.5 release.

May I document this as fixed in 5.1.6?
[19 Jan 2006 8:56] Mikael Ronström
Yes, the fix for this bug has been pushed and will appear in 5.1.6
[19 Jan 2006 9:21] Jon Stephens
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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.1.6 changelog and Partitioning chapter of 5.1 Manual. Thanks!