Error while importing a database in MySQL Workbench

Question

I did a backup file for an existing database and when I want to import this file to a new database I'm getting this error and the import process is stopped:

ERROR 1062 (23000) at line 38: Duplicate entry '86' for key 'PRIMARY'

Do you know how to ignore this error in the import process? I'm using MySQL Workbench, I know that this error is because a primary key is duplicated in some records and should not to be this way.

Are you importing the data to a different schema? – RiggsFolly Feb 22 '19 at 15:25 — RiggsFolly, Feb 22 '19 at 15:25
Yes, into an empty schema – Pahoran Alanis Feb 22 '19 at 15:41 — Pahoran Alanis, Feb 22 '19 at 15:41

D.Christodoulou · Accepted Answer · 2019-02-22T17:55:35.303

0

You could enable the ignore_dup_key option on the primary key.

This will give you a warning instead of an error and instead of failing, the query will discard the row which triggered the error (in your case, the row with the primary key value '86' that you're trying to import).

In the query you are using to import the database, in the specific part about the table:

CREATE TABLE db.mytable(
id NOT NULL,
PRIMARY KEY (id ASC) 
WITH (IGNORE_DUP_KEY = ON));

Edit for MySQL after Shadow's comment

To ignore duplicates in MySQL you could use the INSERT IGNORE statement or the INSERT ... ON DUPLICATE KEY UPDATE.

For more information on the statements, you can check the official documentation
( https://dev.mysql.com/doc/refman/5.5/en/insert.html )

edited Feb 22 '19 at 17:55

answered Feb 22 '19 at 15:31

D.Christodoulou

181
8

This solution may work for ms sql server, but MySQL does not support IGNORE_DUP_KEY option. – Shadow Feb 22 '19 at 16:11
@Shadow You are correct, I have edited my answer accordingly. Thank you. – D.Christodoulou Feb 22 '19 at 16:46

Error while importing a database in MySQL Workbench

1 Answers1