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 10:30	Modified:	2 Sep 2006 7:28
Reporter:	Jon Stephens
Status:	Closed
Category:	Server: Partition	Severity:	S3 (Non-critical)
Version:	5.1.12-BK, 5.1	OS:	Any (All)
Assigned to:	Sergey Gluhov	Target Version:

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!