phpmyadmin error "#1062 - Duplicate entry '1' for key 1"

Question

I dropped all tables from a database then restore(import) a backup. Afterwards I am getting error #1062 - Duplicate entry '1' for key 1.

Should i repeat the process. Or something else? Why this error is coming?

You will need to remove the duplicate entry from your database (yes from the backup SQL). — Richard Knop, Jul 19 '10 at 08:29
Not true, this is a problem with the way it interprets the imported SQL and you should not remove anything. — FarhadA, Oct 22 '13 at 07:31

score 7 · Answer 1 · answered Aug 28 '13 at 12:52

7

When you export your sql from php admin

enter image description here

Select "custom" as export method"

enter image description here

then, instead of 'insert', choose "update"

This will perform update-statements and prevent duplicated inserts.

answered Aug 28 '13 at 12:52

Gary Tsui

1,755
14
18

Thank you very much Gary, this fixed it for me. I was struggling with it for over a day! – FarhadA Oct 22 '13 at 10:59

thomasrutter · Answer 2 · 2013-03-31T00:17:44.677

5

This indicates that you have a UNIQUE or PRIMARY index on a table, and there is a duplicate value on one of the values that will be inserted into one of these indexes.

You'll need to look at which particular operation caused this error to find out which table and which row it was trying to write. Hopefully, phpMyAdmin should tell you which row of data caused the problem, shouldn't it?

One guess is that you're importing data that duplicates some data already in a table, ie you may not have removed the existing data like you thought you had. But it could be any number of things.

edited Mar 31 '13 at 00:17

answered Jul 19 '10 at 04:50

thomasrutter

114,488
30
148
167

i want to remove all previous tables from database and want to import from another sql file. – Jitendra Vyas Jul 19 '10 at 04:52
2

I selected all tables then dropped. Is it not enough? – Jitendra Vyas Jul 19 '10 at 05:37
1

This happened to me as well once, I too dropped the tables and tried importing the csv, and ended up in this error. After reading this post of @thomasrutter, I checked my table data and saw that for some reason the csv headers alone got inserted as one record and that caused unique constraint violation next time I tried to import the csv data. I deleted all records and again tried, and yup it worked! – spiderman May 17 '15 at 04:11
Indeed, I've had that happen too. Can also happen if you have cruft (ie mostly blank lines with just notes or random characters in them) at the bottom of the CSV – thomasrutter May 17 '15 at 05:02

Noam · Answer 3 · 2014-11-02T17:22:10.663

From the sounds if it, the dump has a duplicate entry inside the queries it holds.

Although this shouldn't happen, it happened to me in the past. In order to solve this, I would advise two options:

Manually remove the ADD UNIQUE INDEX and/or PRIMARY KEY at the start of each table dump. Then create a same structure table, add the missing index, and INSERT IGNORE INTO new_tbl (SELECT * FROM tbl)
Adding INSERT IGNORE for the insert statement in the log

score -1 · Answer 4 · answered Aug 19 '13 at 02:23

-1

To fix this, when you want to export DB you can try to untick "Do not use AUTO_INCREMENT for zero values" under "Format-Specific Options:" , see image below :

enter image description here

answered Aug 19 '13 at 02:23

rusly

1,504
6
28
62

phpmyadmin error "#1062 - Duplicate entry '1' for key 1"

4 Answers4

Linked