Why is MySQL-add unique index fails with duplicate entry error?

Why is MySQL-add unique index fails with duplicate entry error?

Attempt 1: If I try to create the index after the data is in the table it fails with duplicate key error. A select on the failed key returns only one (!) row. returns 1 (!) Attempt 2: If I create the index on an empty table or make a combined private key and then put the data into the table, the table contains only 27 of 51 Mio. rows (!).

How to create a unique index on a field with duplicates?

You are trying to create a unique index on a field with duplicates. You will need to resolve the duplicate data first then add the index. This is 3rd time i am looking for solution to this problem so for the reference I am posting the answer here.

How to ignore duplicates on a unique key?

If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value. The IGNORE keyword extension to MySQL seems to have a bug in the InnoDB version on some version of MySQL.

Can a duplicate value be inserted into a unique column?

It is possible to insert duplicate values in a column declared as UNIQUE by disabling the index. However, at the time of rebuilding (enabling) the index, it will give the “duplicate value” error.

How many rows are in MySQL unique index?

Data size is about 51 Mio. rows. Following problem: Attempt 1: If I try to create the index after the data is in the table it fails with duplicate key error. A select on the failed key returns only one (!) row. returns 1 (!)

How to create unique index in SQL Server?

Cannot insert duplicate key row in object ‘dbo.MSmerge_genhistory’ with unique index ‘unc1MSmerge_genhistory’. The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.MSmerge_genhistory’ and the index name ‘unc1MSmerge_genhistory’.

Why was the CREATE UNIQUE INDEX statement terminated?

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.MSmerge_contents’ and the index name ‘uc1SycContents’. The duplicate key value is (7696031, 08703987-557d-e111-9888-e61f13c44f03).

Why is MySQL error 1062 showing duplicate entry?

As looking on your error #1062 – Duplicate entry ‘2-S. Name’ for key ‘PRIMARY’ it is saying that you use primary key in your number field that’s why it is showing duplicate Error on Number Field. So Remove this primary Key then it inset duplicate also. Share.

How to get rid of duplicate searches in MySQL?

The only suggestion I can make is to go to your MySQL console and truncate all the search-related tables. Don’t drop the tables themselves — just empty them out. Then run cron until your site is fully indexed. I imagine those entries were put in there by your D5 site. Those issues you referenced were fixed many many months ago.

Why are there 27 duplicate entries in MySQL?

If a single value of the insert violated the unique constraint, all other values where also not inserted and caused 27 Mio rows instead of 50 Mio. Approach 1: However the bug with the wrong duplicate entry message still there even after the database were dropped and recreated, but this is an evil I can/must live with.