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 l.green <shawn.l.gr...@oracle.com>
Sent: 13 February 2018 09:51:33 PM
Subject: Re: Optimize fails due to duplicate rows error but no duplicates found
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
> Any idea on why optimize would still be failing ?
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
> 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
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql