1062 Duplicate entry but there are no duplicates?

Question

I keep getting this error:

failed to INSERT: [1062] Duplicate entry 'Upping Your Type Game-http://jessicahische.is/talkingtype' for key 'PRIMARY'.

The problem is, is that there are no duplicate entries. Which is why I'm confused when receiving that error.

Is there any way to check using InnoDB?

I have tried exporting the table and reading the SQL code and ran a search for any duplicates but there wasn't any.

Answer 1

It isn't saying that there is a duplicate entry in the table already, it is saying that there is already one entry in there with that value for the primary key and it is refusing to insert a second for that reason.

I suspect if you run:

SELECT * 
FROM   <table> 
WHERE  <keyfield> = 'Upping Your Type Game-http://jessicahische.is/talkingtype'

or if that key is a composite

SELECT * 
FROM   <table> 
WHERE  <keyfield1> = 'Upping Your Type Game' 
AND    <keyfield2> = 'http://jessicahische.is/talkingtype'

You will find one matching row, and that your code at the point of the error is trying to insert a second.

Dealing with duplicates on insert:

If you try INSERT a duplicate values for a primary key (or a unique index) you will always get that error. There are a couple of ways around it: check before you insert and either do an update (if something might have changed) or just don't do anything.

There is also the mysql specific ON DUPLICATE KEY UPDATE option (see https://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) if you are happy to sacrifice compatibility with other RDBMSs.

As of version 9.5 Postgres supports a similar feature with slightly different syntax (ON CONFLICT DO UPDATE/NOTHING - see https://wiki.postgresql.org/wiki/UPSERT).

MS SQL Server, Oracle and some other systems support MERGE statements as defined in the SQL:2003 standard (see https://en.wikipedia.org/wiki/Merge_(SQL)) which is intended to achieve the same functionality and more. Be careful with any of these options if cross database compatibility is (or is likely to be in future) one of your goals.

Answer 2

You might have reached the limit of your id column (the PRIMARY KEY). For instance, if your column is defined as tinyint(4), once you reached the value of 127, you can't get higher. When you try to insert to that column, the higher value is still 127, but you already have an entry with that value, so the error will tell you that you try to enter a duplicate entry ('127').

Solution: change the definition of your PRIMARY KEY to a higher INT.

Answer 3

I have just been getting the same error. My table only has 1 row, and there is not duplicate.

TLDR: check your foreign keys, make sure the value exists in the parent table. MySQL 5.6.3 apparently can't tell you the real reason for the error.

mysql> select * from users_sessions;
+---------+----------------------------+---------------------+
| user_id | session_id                 | last_accessed       |
+---------+----------------------------+---------------------+
|       3 | 6n02k8catt2kdn30b92ljtrpc6 | 2019-01-13 23:30:53 |
+---------+----------------------------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> INSERT INTO `users_sessions` VALUES(3,"fbfibdog1qumlj5mg4kstbagu7","2019-01-14 18:37:15") ON DUPLICATE KEY UPDATE last_accessed = "2019-01-14 18:37:15";
ERROR 1062 (23000): Duplicate entry '3-fbfibdog1qumlj5mg4kstbagu7' for key 'PRIMARY'

There is no such duplicate key!

I tried running the same insert without the ON DUPLICATE KEY ... clause, and got a better clue of what was happening.

mysql> INSERT INTO `users_sessions` VALUES(3,"fbfibdog1qumlj5mg4kstbagu7","2019-01-14 18:37:15");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`login`.`users_sessions`, CONSTRAINT `fk_sessions_id` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Now we're onto something. So I tried running the original query with a different value that does have a matching value in the foreign key table. The query ran without issue.

Why does MySQL seem to issue the wrong error? I'm not sure, but it probably has to do with the way ON DUPLICATE KEY UPDATE is implemented. I tried running the same insert query as a REPLACE INTO query and got the same error.

mysql> REPLACE INTO `users_sessions` VALUES(3,"fbfibdog1qumlj5mg4kstbagu7","2019-01-14 18:37:15");
ERROR 1062 (23000): Duplicate entry '3-fbfibdog1qumlj5mg4kstbagu7' for key 'PRIMARY'

If you run into this error for no apparent reason, check your foreign keys. Try a regular INSERT query (instead of REPLACE INTO or INSERT ... ON DUPLICATE KEY UPDATE...) as well.

Answer 4

This can also be triggered if one have a trigger which creates a conflict.

Having table A and B. When a new record is inserted into A, table B is updated by trigger.

INSERT INTO foo (id) VALUES(new.id) 

Now truncate A and forget to truncate B.

Inserting into A will now fail for any query where id exists in B (when B.id is unique.)

---

Had a test table that I forgot about, hence this error, which at first was a bit cryptic considering A was empty ;)

Answer 5

I have such error if I have system shutdown or network problem. You really don't have a duplicate in your db. That is mysql db error...all you need to do is that, if you do your insertion and it is not true, just alter one of the columns of your table you want to insert into either from varchar to text or bigint and then redo the insertion. That will solve the problem.

If(!$insert){
$alter=Mysql_query("alter table `table_name` 
                    change `table_name` `table_name` bigint(255) not null");
If($alter){
//you then redo your insertion.
}
}