mysql duplicate entry error 1062 when restoring backup

Question

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:

1. Does this indicate a corrupted/problematic db3 database?

2. How to proceed to create a 'consistent/working' replica (db4) of db3?

3. If (2) fails, how to possibly troubleshoot and find the reason behind why it occurs?

Thanks,

Answer 1

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.

INSERT INTO table_name VALUES (...), (...), (...), ...;

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 with MyISAM it's distinctly possible.

There are a couple of other mysqldump options that might also be useful:

• --replace generates a file writing the statements as REPLACE INTO instead of INSERT 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 as INSERT IGNORE INTO instead of INSERT 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.

Answer 2

One can even search and replace each occurrence of Insert into with Insert Ignore into in the mysqldump file.

Answer 3

Preface

So I came across this issue after one of our replication servers went into an inconsistent state. We couldn't get it back on line with a quick fix and decided to reinitialize the replication with a new backup.

The question stated above is not the same problem but does return the same error therefore I decided to add this answer in case other people are looking for it.

It is also an exact duplicate of this question on Server Fault but because that question has a higher ranking in (my) Google results I also posted it there.

What we did

We deleted all our databases on the instance with MySQL Workbench, I restarted the instance and double checked that there where no databases left on the instance. On the master server we had already started creating a new backup with the script we always use.

Then once the backup was on the failed server we started the import and after a couple of hours it had imported most of the data and failed with these kinds of errors:

ERROR 1062 (23000) at line XXX: Duplicate entry 'dbName-tblName' for key 'PRIMARY'
Operation failed with exitcode 1

We thought okay, maybe something went wrong when creating the backup, I checked out the data in the backup but couldn't find anything wrong. We retried all the steps again just to be sure we didn't miss anything but unfortunately there was no cigar..

The actual problem

The error that is displayed is not the actual insert of the data but the creation of an index. This is why the data was inserted without a problem (we checked) but still generated an error. Apparently deleting all the tables is not sufficient, index data still exists on the server (this might be due to a change in MySQL 5.6 as we never had this problem before). And because the import of these index is somewhere in the middle of the import file the rest of the data didn't get imported.

The fix

We deleted the ibdata1 file and all the innodb_index_stats and innodb_table_stats files in the mysql database folder and then started the instance. MySQL will then tell you that some system tables are missing you can find more info on that here

This solved our issue and our server is now replicating as expected.

Hopefully this save some hours for someone somewhere :)

Answer 4

For those of you who stumbled on this question through a search engine and have the same error but for different reasons: The same error just happened to me on MySQLWorkbench because I usually "Dump structure and data" and for some reason the default selection changed to just "Dump data". Re-exporting both structure and data helped me.