N4XX Posted August 9, 2012 Share Posted August 9, 2012 Hello, I try to import a large database in PHPMYADMIN. Ive run the command mysql -u root DBNAME < path.sql, thus created some tables, but i got the error - "ERROR 1062 (23000) at line xx: Duplicate entry 'xx' for key 'PRIMARY". How can i fix this? ? :/ i dunno what to do...... Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted August 9, 2012 Share Posted August 9, 2012 The field defined as the PRIMARY KEY, or any field (or field group) with a UNIQUE index can't contain duplicate values. If you've attempted the import more than once, it could be that some data was inserted, and you're now trying to import the same data again. If not, you'll have to examine the data you're trying to import and see where the duplication is. Quote Link to comment Share on other sites More sharing options...
N4XX Posted August 10, 2012 Author Share Posted August 10, 2012 Ah, so if i try to import the db, abort it and try it again without clearing the db this error occurs?! Quote Link to comment Share on other sites More sharing options...
Christian F. Posted August 10, 2012 Share Posted August 10, 2012 Yes. Quote Link to comment Share on other sites More sharing options...
N4XX Posted August 10, 2012 Author Share Posted August 10, 2012 Well, i created a new db and tried to import it again but i get the same error ... Quote Link to comment Share on other sites More sharing options...
Christian F. Posted August 10, 2012 Share Posted August 10, 2012 Then there's a duplicated index in the dump. Which means you'll have to go through it, identify the problematic line, and manually alter it. You may also need to change any references to it, if it is used as a foreign key in some other table. Quote Link to comment Share on other sites More sharing options...
N4XX Posted August 10, 2012 Author Share Posted August 10, 2012 ah ;P and how can i do this? :3 Quote Link to comment Share on other sites More sharing options...
Christian F. Posted August 10, 2012 Share Posted August 10, 2012 Opening the file in a text editor, and read. Afraid there are no other solutions. Quote Link to comment Share on other sites More sharing options...
N4XX Posted August 10, 2012 Author Share Posted August 10, 2012 how to open a xx GB file with notepa? ;P. Quote Link to comment Share on other sites More sharing options...
Christian F. Posted August 10, 2012 Share Posted August 10, 2012 Oh.... Then I'd try to export the data again, using mysqldump. That should give you a correct replica of the database. If there still are duplicate keys then the problem lies in the original database, and you'll need to fix that before exporting. Quote Link to comment Share on other sites More sharing options...
xyph Posted August 10, 2012 Share Posted August 10, 2012 Replace all INSERTs to INSERT IGNORE This isn't necessarily the 'right' way to do it, but it'll get rid of the error, and the insert will simply skip if there's a duplicate key Quote Link to comment Share on other sites More sharing options...
Barand Posted August 11, 2012 Share Posted August 11, 2012 To find duplicates: Recreate table without the unique constraint Load the data SELECT keyfield, COUNT(*) FROM thetable GROUP BY keyfield HAVING COUNT(*) > 1 Quote Link to comment Share on other sites More sharing options...
cpd Posted August 11, 2012 Share Posted August 11, 2012 Whilst its a solution I really wouldn't go down the line xyph suggested. You could loose a chunk of data you need very easily. Its faster than Barands suggestion but has consequences. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 13, 2012 Share Posted August 13, 2012 I'll have to second that -- IGNORE is just asking for trouble -- drop the constaint, then look at what damage you're about to do. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.