MySQL Bugs: #21862: Misleading error message 1490: "A PRIMARY KEY need to include all fields..."

Bug #21862	Misleading error message 1490: "A PRIMARY KEY need to include all fields..."
Submitted:	28 Aug 2006 8:30	Modified:	2 Sep 2006 5:28
Reporter:	Jon Stephens	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.12-BK, 5.1	OS:	Any (All)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
The error message displayed when attempting to define a unique key that's not valid for a partitioned table is misleading.

What it says is, "A PRIMARY KEY need to include all fields in the partition function"

What it should say is, "A unique key (including a primary key) must include all columns in the table's partitioning expression"

The current error message is also grammatically incorrect, and refers to "fields" instead of "columns" (per PeterG we don't refer to columns as "fields" in the Manual) and "partitioning function" instead of "partitioning expression" (I think the latter is preferable).

How to repeat:
Execute any CREATE TABLE or ALTER TABLE statement that would result in a partitioned table having at least one unique key (or primary key) that does not use all columns referenced in the table's partitioning expression.

My main concern here is that the current error message doesn't make clear that this limitation covers ALL unique keys, and not just primary keys.

Suggested fix:
Included in Description.

Explicit example:

mysql> CREATE TABLE t1 (
    ->     col1 INT NOT NULL,
    ->     col2 DATE NOT NULL,
    ->     col3 INT NOT NULL,
    ->     col4 INT NOT NULL,
    ->     UNIQUE KEY(col1, col2)
    -> )
    -> PARTITION BY HASH(col3)
    -> PARTITIONS 4;
ERROR 1490 (HY000): A PRIMARY KEY need to include all fields in the partition function

Since the proposed table definition has no primary key, this error message is confusing at best.

Verified just as described with 5.1.12-BK on Linux:

openxs@suse:~/dbs/5.1> bin/mysql -uroot 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 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE tp1 (
    -> col1 INT NOT NULL,
    -> col2 DATE NOT NULL,
    -> col3 INT NOT NULL,
    -> col4 INT NOT NULL,
    ->  UNIQUE KEY(col1, col2)
    -> ) PARTITION BY HASH(col3) PARTITIONS 4;
ERROR 1490 (HY000): A PRIMARY KEY need to include all fields in the partition fu
nction

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/10991

ChangeSet@1.2286, 2006-08-29 15:15:07+05:00, gluh@mysql.com +4 -0
  Bug#21862 Misleading error message 1490: "A PRIMARY KEY need to include all fields..."
  fixed error message

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/11144

ChangeSet@1.2288, 2006-08-31 13:43:26+05:00, gluh@mysql.com +3 -0
  Bug#21862 Misleading error message 1490: "A PRIMARY KEY need to include all fields..."
  fixed error message

Patch will appear in 5.1.12

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.12 changelog. Thanks, guys!