Bug #16736 | Implicit partitioning key not displayed. | ||
---|---|---|---|
Submitted: | 23 Jan 2006 22:44 | Modified: | 16 Mar 2006 9:38 |
Reporter: | Jonathan Miller | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 5.1.6-alpha | OS: | Linux (Linux) |
Assigned to: | Jon Stephens | CPU Architecture: | Any |
[23 Jan 2006 22:44]
Jonathan Miller
[24 Jan 2006 8:12]
Mikael Ronström
This is not a bug. A change when pushing WL 2604 is that all NDB tables are treated as partitioned tables. This means that the two following CREATE TABLE statements produce exactly the same table. 1) CREATE TABLE (a int) PARTITION BY KEY() ENGINE = NDB; 2) CREATE TABLE (a int) ENGINE = NDB; There is no specific information kept that specifies whether the specified the first or the second variant.
[24 Jan 2006 11:43]
Jonathan Miller
I think that it will confuse users, but as it still creates the table it should then be heavly documented.
[24 Jan 2006 14:05]
Jon Stephens
I'm reopening this, because I believe that it is a bug. Or at least an implementation that can be and should be improved. The problem I have with this is that the user seems to have no (simple) way way of seeing what the partitioning key is. The statement CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY(); works, and this appears to be the case regardless of the storage engine used. However, the fact that the primary key is the partitioning key can be worked out only indirectly. (Remove the PRIMARY KEY attribute from the CREATE statement, and it fails.) When we examine INFORMATION_SCHEM.PARTITIONS, it literally tells us nothing: mysql> create schema part; Query OK, 1 row affected (0.00 sec) mysql> use part; Database changed mysql> CREATE TABLE k1 ( -> id INT NOT NULL PRIMARY KEY, -> name VARCHAR(20) -> ) PARTITION BY KEY(); Query OK, 0 rows affected (0.08 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_SCHEMA='part' AND TABLE_NAME='k1'\G *************************** 1. row *************************** TABLE_CATALOG: NULL TABLE_SCHEMA: part TABLE_NAME: k1 PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: KEY SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 0 MAX_DATA_LENGTH: 281474976710655 INDEX_LENGTH: 1024 DATA_FREE: 0 CREATE_TIME: 2006-01-24 23:35:01 UPDATE_TIME: 2006-01-24 23:35:01 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: default NODEGROUP: 0 TABLESPACE_NAME: default 1 row in set (0.01 sec) Even though it's evident that the primary key is the partitioning key, both SHOW CREATE TABLE and INFORMATION_SCHEMA.PARTITIONS should show this explicitly. The fact that we don't just looks sloppy, and users should not be required to deduce such basic info about a table. Let's fix it, please.
[24 Jan 2006 14:37]
Jon Stephens
I've updated the synopsis, since (a) this isn't unique to Cluster tables, and (b) what's displayed by SHOW CREATE TABLE is valid, but not intuitive.
[13 Mar 2006 15:47]
Jon Stephens
Reverted to myself/Documentation after discussion with Mikael.
[16 Mar 2006 9:38]
Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant product(s). Additional info: This info is now shown in the Partitioning By KEY section of the Partitioning chapter. (I actually took care of this some time ago as part of documenting a server bugfix, but I can't find the revision number.)