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.

up vote 4 down vote accepted

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 * FORM <table> WHERE <keyfield>='Upping Your Type Game-http://jessicahische.is/talkingtype', or if that key is a composite SELECT * FORM <table> WHERE <keyfield1>='Upping Your Type Game' AND <keyfield1>='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.

Update: 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 (or with extensions) 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.

  • Instead of grabbing all the articles from my feed, I wonder if I should just get the last article in the feed and let it INSERT or check the DB first and only INSERT what isn't there. Thoughts? – Chris Burton Jul 22 '13 at 19:25
  • 2
    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 stackoverflow.com/questions/1218905/…) if you are happy to sacrifice compatibility with other RDBMSs. – David Spillett Jul 23 '13 at 10:57

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.

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.
}
}

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.