Bug #70733 Partition examples should include a primary key
Submitted: 26 Oct 2013 14:55 Modified: 30 Oct 2013 8:12
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[26 Oct 2013 14:55] Daniël van Eeden 
Description:
Page: http://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html

The following table definition suggests that there is a primary key, but there isn't.
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

It's now possible to have duplicates in the id column (different employees with the same id/employeenumber).

missing primary keys can hurt performance for InnoDB, replication and some dba tools.

How to repeat:
See description.

Suggested fix:
Either upgrade id to a fullblown surrogate primary key or remove it and use a natural primary key.

A different primary key may be used depending on the partitioning type (e.g. (store_id,id) to allow partitioning on store_id)
[26 Oct 2013 14:55] Daniël van Eeden 
Changed severity to S4 as that seems more appropriate
[30 Oct 2013 6:16] Jon Stephens 
This could be misleading or confusing. We should at least mention that we're ignoring the PK requirements to simplify the discussion/examples at this point.

This should not be a particularly difficult or lengthy fix. I'll try to take care of it soon.

Thanks for pointing this out.
[30 Oct 2013 8:12] 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 products.

I don't want to clutter up or complicate the discussion so I have added a note about this, with a reference to http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-k...

Fixed in mysqldoc rev 36548. Closed.