How to skip rows with errors such as duplicate key when importing a SQL file from mysqldump? (Doc ID 1023317.1)

Last updated on MARCH 12, 2015

Applies to:

MySQL Server - Version 4.0 and later
All Platforms

Goal

When restoring a dump file generated by mysqldump, there sometimes could be problems with duplicate rows. This can happen if a dump file is being restored over existing data while attempting to restore rows that were deleted or if a corrupt table was dumped that incorrectly held a duplicate row.  A problem arises when running the restore because a mutliple row INSERT will be stopped and an error message will be displayed like the following:

The result of this error is dependant on the storage engine being used. When a duplicate key error occurs while performing a multi-row insert into a MyISAM table, all rows up to the row that causes the duplicate key error are inserted into the table. When such an error occurs while performing a multi-row insert into an InnoDB table, the statement is aborted and none of the rows are inserted into the table. This is because MyISAM is not able to rollback the portion of the statement that has already been processed. InnoDB is a transactional storage engine, so it is able to rollback the statement and therefore avoid inserting any of the rows. 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms