Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this.
Requirement: To create an exact replica 'db4' of an existing DB 'db3'.
Procedure followed:
- mysqldump -uuser -ppass db3 > db3.sql (size is 6G)
- mysql -uuser -ppass db4 < db3.sql (db4 was a newly created blank database)
The 2nd step throws in the error:
ERROR 1062 (23000) at line 5524: Duplicate entry '600806' for key 1"
I ran the 2nd step again with –force. The restore completed but with 2 additional similar errors:
ERROR 1062 (23000) at line 6309: Duplicate entry '187694' for key 1
ERROR 1062 (23000) at line 6572: Duplicate entry '1567400' for key 1
On completion when I queried certain tables of db4 database, I was able to see missing records.
Question:
-
Does this indicate a corrupted/problematic db3 database?
-
How to proceed to create a 'consistent/working' replica (db4) of db3?
-
If (2) fails, how to possibly troubleshoot and find the reason behind why it occurs?
Thanks,
Best Answer
I would suggest that this is indeed indicative of a problem with
db3
. By default, mysqldump generates "extended" insert statements, containing more than one row's worth of insert per line.This is an optimization, since multiple inserts in a single statement are much faster than executing individual insert statements.
But, you can disable this behavior using the
--skip-extended-insert
option.Using that option for backups isn't a good idea, since they restore much more slowly, but this option does make dump files that are much easier to read with your eyeballs and much easier to search through for a specific record using
grep
or a similar tool.Dump the database using this option and then search through the file for the duplicate keys that are throwing errors and it seems likely that you'll find duplicate rows or rows with duplicate keys that should have been unique... which means underlying table trouble in
db3
.I can't think of a way this could happen with
InnoDB
but withMyISAM
it's distinctly possible.There are a couple of other
mysqldump
options that might also be useful:--replace
generates a file writing the statements asREPLACE INTO
instead ofINSERT INTO
which would result in the duplicate key records occurring later in the file replacing the conflicting records that occurred earlier in the file, without generating an error--insert-ignore
generates a file with the statements written asINSERT IGNORE INTO
instead ofINSERT INTO
, which would cause the duplicate key records occurring earlier in the file to be persisted in the restored tables, as the later conflicting records would be ignored, not inserted, and would not generate an error.