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...... Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/ 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. Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368230 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?! Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368288 Share on other sites More sharing options...
Christian F. Posted August 10, 2012 Share Posted August 10, 2012 Yes. Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368341 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 ... Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368366 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. Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368375 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 Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368379 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. Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368385 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. Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368420 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. Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368437 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 Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368444 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 Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368549 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. Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368608 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. Link to comment https://forums.phpfreaks.com/topic/266883-need-help-importing-a-large-db/#findComment-1368873 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.