| 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: | |
| 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 | CPU Architecture: | Any |
[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!
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.