MySQL duplicate entry error even though there is no duplicate entry

Question

I am using MySQL 5.1.56, MyISAM. My table looks like this:

CREATE TABLE IF NOT EXISTS `my_table` (
  `number` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `money` int(11) NOT NULL,
  PRIMARY KEY (`number`,`name`)
) ENGINE=MyISAM;

It contains these two rows:

INSERT INTO `my_table` (`number`, `name`, `money`) VALUES
(1, 'S. Name', 150), (2, 'Another Name', 284);

Now I am trying to insert another row:

INSERT INTO `my_table` (`number`, `name`, `money`) VALUES
(2, 'S. Name', 240);

And MySQL just won't insert it while telling me this:

#1062 - Duplicate entry '2-S. Name' for key 'PRIMARY'

I really don't understand it. The primary key is on the first two columns (both of them), so the row I am trying to insert HAS a unique primary key, doesn't it?

I tried to repair the table, I tried to optimize the table, all to no avail. Also please note that I cannot change from MyISAM to InnoDB.

Am I missing something or is this a bug of MySQL or MyISAM? Thanks.

To summarize and point out where I think is the problem (even though there shouldn't be): Table has primary key on two columns. I am trying to insert a row with a new combination of values in these two columns, but value in column one is already in some row and value in column two is already in another row. But they are not anywhere combined, so I believe this is supposed to work and I am very confused to see that it doesn't.

Hebraic

Are those the exact schema and exact INSERTs? If not, we may be barking up the wrong trees! Please provide a reproducible test case. – Toting

My problem was that my INSERT query wasn't specifying which database to use, and the default database had a table with the same name. – Mixture

Answer 1

Your code and schema are OK. You probably trying on previous version of table.

http://sqlfiddle.com/#!2/9dc64/1/0

Your table even has no UNIQUE, so that error is impossible on that table.

Backup data from that table, drop it and re-create.

Maybe you tried to run that CREATE TABLE IF NOT EXIST. It was not created, you have old version, but there was no error because of IF NOT EXIST.

You may run SQL like this to see current table structure:

DESCRIBE my_table;

---

Edit - added later:

Try to run this:

DROP TABLE `my_table`; --make backup - it deletes table

CREATE TABLE `my_table` (
  `number` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `money` int(11) NOT NULL,
  PRIMARY KEY (`number`,`name`),
  UNIQUE (`number`, `name`) --added unique on 2 rows
) ENGINE=MyISAM;

Ashla

Thanks, I re-created the table, inserted data from the old table and tried again. There was no change in anything, but after I ran the insert query on the new table, it worked. So the old table was probably somehow corrupted, even though I have no idea how. – Hebraic

It was probably old version of table. Your script that was supposed to update schema did nothing, because there was "IF (table) NOT EXIST". – Ashla

I didn't try the thing with adding UNIQUE, it worked with just re-creating and re-filling the table. – Hebraic

UNIQUE is optional - if you want to keep unique value combination in these fields. Now - without UNIQUE - you should be able to insert duplicate values for that key. – Ashla

A PRIMARY KEY (in MySQL) is UNIQUE. So adding the UNIQUE key is totally redundant (and wasteful). This applies to any Engine, not must MyISAM. – Toting

All I did was drop the id column and recreated a new id column as primary AI – Riesman

@nawissor Yea, but if this column had relationships, you would also have to delete foreign keys from related tables. – Ashla

Answer 2

I know this wasn't the problem in this case, but I had a similar issue of "Duplicate Entry" when creating a composite primary key:

ALTER TABLE table ADD PRIMARY KEY(fieldA,fieldB); 

The error was something like:

#1062 Duplicate entry 'valueA-valueB' for key 'PRIMARY'

So I searched:

select * from table where fieldA='valueA' and fieldB='valueB'

And the output showed just 1 row, no duplicate!

After some time I found out that if you have NULL values in these field you receive these errors. In the end the error message was kind of misleading me.

Cobwebby

In my case, I had a decimal field and it told me I had a duplicate value, but it wasn't. I did run a check for other duplicate values, though, using this trick and did find 3. I resolved those and was able to create the index. – Dalton

NULL values were the reason for my case too, works after adding allow null with unique – Eberto

Answer 3

I had a similar issue, but in my case it turned out that I used case insensitive collation - utf8_general_ci.

Thus, when I tried to insert two strings which were different in a case-sensitive comparison, but the same in the case-insensitive one, MySQL fired the error and I couldn't understand what a problem, because I used a case-sensitive search.

The solution is to change the collation of a table, e.g. I used utf8_bin which is case-sensitive (or utf8_general_cs should be appropriate one too).

Liana

great answer, save a lot time – Boschvark

Answer 4

In case this helps anyone besides the OP, I had a similar problem using InnoDB.

For me, what was really going on was a foreign key constraint failure. I was referencing a foreign key that did not exist.

In other words, the error was completely off. The primary key was fine, and inserting the foreign key first fixed the problem. No idea why MySQL got this wrong suddenly.

Cheyenne

FOREIGN KEYs are not supported by MyISAM. Any, none is specified. – Toting

Apologies, I overlooked that. Let me specify InnoDB and leave this around in case anyone using InnoDB runs into this problem. – Cheyenne

Answer 5

Less common cases, but keep in mind that according to DOC https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

Constituency

Answer 6

In my case the error was caused by the outdated schema, one column was originally varchar(50) but the dump I was trying to import was created from a modified version of the schema that has varchar(70) for that column (and some of the entries of that field where using more than 50 chars).

During the import some keys were truncated and the truncated version was not unique anymore. Took a while to figure that out, I was like "but this supposedly duplicated key doesn't even exist!".

Brittaney

VARCHAR(50) and VARCHAR(70) are compatible enough so that this cannot be the answer. – Toting

@RickJames what do you mean with "compatible enough"? Let's say the dump has two keys: "my_key_0" and "my_key_1". If you reduce the key size by 1 they both become "my_key_" breaking the uniqueness of the key. – Brittaney

If your strings are too long for the datatype, they will truncated. Such data loss can lead to a variety of problems. But if all your strings are shorter than 50, then (70) and (50) both work, and JOINing will be happy. – Toting

@RickJames ok, some of my strings were bigger than 50 and got truncated causing the loss of uniqueness – Brittaney

Then clearly "(50)" was an error. Since the example did not have a long string, we floundered on the question. Please edit the question to use a longer string in the example. Or 'delete' the question entirely. flag19 == user1763581 ?? – Toting

Answer 7

Another reason you may be getting this error is because the same restriction exists in another related table, and they Keyname on the related table has the exact same name. I've had this happen once and it was quite difficult to identify.

i.e. if you have a trigger that inserts data to a different table (the "related" table) with the same restriction and same Keyname, MySQL will not include the name of the table throwing the error, only the Keyname.

Mackenzie

Answer 8

MySQL is responding with misleading error and I got mine solved with trick below. Since I have only about ten rows of data, and I was performing the insert on an empty table, I can be pretty sure duplication is not possible.

INSERT INTO TABLE_NAME VALUES
(...),
(...),
(...)
AS
    new 
ON 
    DUPLICATE KEY
UPDATE
    col = new.col;

mysql --version # Ver 8.0.35-0ubuntu0.22.04.1

Mauer

Answer 9

Try with auto increment:

CREATE TABLE IF NOT EXISTS `my_table` (
   `number` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(50) NOT NULL,
   `money` int(11) NOT NULL,
    PRIMARY KEY (`number`,`name`)
) ENGINE=MyISAM;

Tourcoing

I can't do this, the 'number' column cannot have auto_increment. – Hebraic

Answer 10

Your code is work well on this demo:

http://sqlfiddle.com/#!8/87e10/1/0

I think you are doing second query (insert...) twice. Try

select * from my_table

before insert new row and you will get that your data already exist or not.

Reimport

The new row doesn't exist, I checked. – Hebraic

Answer 11

i have just tried, and if you have data and table recreation wouldnt work, just alter table to InnoDB and try again, it would fix the problem

Flex

Answer 12

In case anyone else finds this thread with my problem -- I was using an "integer" column type in MySQL. The row I was attempting to insert had a primary key with a value larger than allowed by integer. Switching to "bigint" fixed the problem.

Papaw

He has INT, which allows a range of about -2 billion to 2 billion. 2 is not too big. – Toting

Answer 13

As per your code your "number" and "Name" are primarykey and you are inserting S.NAME in both row so it will make a conflict. we are using primarykey for accessing complete data. here you cant access the data using the primarykey 'name'.

im a beginner and i think it might be the error.

Panorama

The PRIMARY KEY is a composite of two columns; so your answer does not apply. – Toting

Answer 14

In my case the error was very misleading. The problem was that PHPMyAdmin uses "ALTER TABLE" when you click on the "make unique" button instead of "ALTER IGNORE TABLE", so I had to do it manually, like in:

ALTER TABLE mytbl ADD UNIQUE (columnName);

Histoplasmosis

Answer 15

This problem is often created when adding a column or using an existing column as a primary key. It is not created due to a primary key existing that was never actually created or due to damage to the table.

What the error actually denotes is that a pending key value is blank.

The solution is to populate the column with unique values and then try to create the primary key again. There can be no blank, null or duplicate values, or this misleading error will appear.

Constitute

Answer 16

For me a noop on table has been enough (was already InnoDB):

ALTER TABLE $tbl ENGINE=InnoDB;

Copious

Answer 17

tl;dr: my view showed my table was empty but the view excluded existing rows.

I had the same problem but mine was because I was inserting the same test rows I had used before. When I checked to see if my table was empty, I used a view that excluded different tenants so the search came back empty. When I checked the actual table, the previous records were still there.

Once I had deleted the existing records, the insert worked. Only half a day of frustration lost to this one...

Roxy

Answer 18

Had this error, when adding a composite primary key that is ADD PRIMARY KEY (column1, column2, ...) The value of all the columns in that row must not be duplicated.

For Example: You do ADD PRIMARY KEY (name, country, number)

name	country	number
collin	Uk	5
collin	Uk	5

This will throw an error #1062 - Duplicate entry 'collin-UK-5' for key 'PRIMARY' because the columns combined have duplicate

So if you see this format of error just check and ensure that the columns you want to add a composite primary key to combined don't have duplicates.

Salsify

Answer 19

In my case, it was caused by special characters. For example, I got

foó@dummy.com and [email protected]

When I tried to search with LIKE email = '%[email protected]%' the results were empty.

I had to apply regex to fix this special case.

Gyimah

Answer 20

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.

Brottman

I really need the primary key to be on both columns - on combination of number and name. I am trying to insert a new combination of this primary key but my MySQL database just won't let me. – Hebraic

you can use this to put primary key on both Fields number and name but when you use name same in two name field it show duplicate error so avoid to make name column as a primary key . you can put it not null. – Brottman

You wrote "it is saying that you use primary key in your number field" - but that's not the case. It clearly shows values from both 'number' and 'name' columns as primary key and claims they are duplicate even though they are not. But I already solved it, my table was probably somehow corrupt. Thanks for your help anyway. – Hebraic