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:
None 
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
Description:
using ndb_dd_disk2memory.test

CREATE TABLE test.t1
(pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
TABLESPACE table_space1 STORAGE DISK
ENGINE=NDB;

CREATE TABLE test.t2
(pk2 INT NOT NULL PRIMARY KEY, b2 INT NOT NULL, c2 INT NOT NULL)
ENGINE=NDB;

Shows:
SHOW CREATE TABLE test.t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `pk1` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY  (`pk1`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY ()

SHOW CREATE TABLE test.t2;
Table   Create Table
t2      CREATE TABLE `t2` (
  `pk2` int(11) NOT NULL,
  `b2` int(11) NOT NULL,
  `c2` int(11) NOT NULL,
  PRIMARY KEY  (`pk2`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY KEY ()

should show:
SHOW CREATE TABLE test.t2;
Table   Create Table
t2      CREATE TABLE `t2` (
  `pk2` int(11) NOT NULL,
  `b2` int(11) NOT NULL,
  `c2` int(11) NOT NULL,
  PRIMARY KEY  (`pk2`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `pk1` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY  (`pk1`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1

How to repeat:
See above
[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.)