Thank you for the response.

  There are no partitioning involved at all...

  I will try the suggestion though and see if we get anything...


From: shawn <>
Sent: 13 February 2018 09:51:33 PM
Subject: Re: Optimize fails due to duplicate rows error but no duplicates found

Hello Machiel,

On 2/13/2018 3:02 AM, Machiel Richards wrote:
> Good day guys,
>      I am hoping this mail finds you well.
>     I am at a bit of a loss here...
>      We are trying to run optimize against a table in order to reclaim disk 
> space from archived data which has been removed.
>      However, after running for over an hour , the optimize fails stating 
> there is a duplicate entry in the table.
>       We have now spent 2 days using various methods but we are unable to 
> find any duplicates in the primary key and also nothing on the unique key 
> fields.
> Any idea on why optimize would still be failing ?
> Regards

Is it possible that the duplicate keys were the result of
re-partitioning your data where one of the "older" copies was in the
wrong partition as part of an upgrade from an earlier version?

See the entry in that
start with...
> Incompatible Change; Partitioning: Changes in the KEY partitioning hashing 
> functions used with numeric, date and time, ENUM, and SET columns in MySQL 
> 5.5 makes tables using partitioning or subpartitioning by KEY on any of the 
> affected column types and created on a MySQL 5.5 or later server incompatible 
> with a MySQL 5.1 server. This is because the partition IDs as calculated by a 
> MySQL 5.5 or later server almost certainly differ from those calculated by a 
> MySQL 5.1 server for the same table definition and data as a result of the 
> changes in these functions.

A normal indexed lookup against a partitioned table will use
(particularly for a PK value) "partition pruning" .  To see all of your
PK values regardless of which partition they are in, you need to scan
the table and avoid all indexes.

# for a numeric PK column
CREATE TABLE myPK_list SELECT pk from sourcetable WHERE pk+0 > 0;

Then you can check the list in the generated table to find any duplicate

Then you can modify a SELECT command to search each partition or
subpartition individually until you find the rows that are in the wrong

Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit
for details.

MySQL General Mailing List
For list archives:
To unsubscribe:

Reply via email to