Thank you for the response.
There are no partitioning involved at all... I will try the suggestion though and see if we get anything... Regards ________________________________ From: shawn l.green <shawn.l.gr...@oracle.com> Sent: 13 February 2018 09:51:33 PM To: mysql@lists.mysql.com 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 https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-31.html 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. example: # 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 values. Then you can modify a SELECT command to search each partition or subpartition individually until you find the rows that are in the wrong spots. https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html Yours, -- Shawn Green 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/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
