How to skip row when importing bad MySQL dump

Question

Given bad mysqldump that causes error on import:

namtar backups # mysql -p < 2010-12-01.sql
Enter password: 
ERROR 1062 (23000) at line 8020: Duplicate entry 'l�he?' for key 'wrd_txt'

Is there an easy way to tell import to just skip given row and continue?

(Yes, I know I can manually edit the file or parse output, but it's not very convinient)

I was also having this issue I created a new database and then imported the data from .sql file into my newly created database. It worked for me. — Waqar Detho, Jan 10 '17 at 21:27
In my case, triggers were causing duplicate entry error while import. — M Rostami, Jun 22 '20 at 13:59

score 52 · Accepted Answer · edited Feb 05 '18 at 10:09

52

If you can make the dump again you could add --insert-ignore to the command-line when dumping.

Or you can try using the mysqlimport command with --force,which will continue even if it encounters MySQL Errors.

edited Feb 05 '18 at 10:09

AliRNazari

732
11
24

answered Oct 01 '11 at 18:56

jmlsteele

1,128
8
6

I cannot create export again...and how do I use mysqlimport agains output from mysqldump --all-databases ? – Almad Oct 01 '11 at 19:23
2

I just tried using --force with mysql and it causes the insertion to stop when the error occurs. mysqlimport doesn't work with an --all-databases dump, so that's out of the question as well. The easiest way would be to edit the file, not to remove the offending line(s) which could be tedious, but to turn the "INSERT" commands into "INSERT IGNORE" commands. A simple find/replace should suffice. I realise it's not an ideal solution, but it's better than having to dig through the file replacing offending inserts manually when you find out about them. – jmlsteele Oct 01 '11 at 19:57

score 38 · Answer 2 · answered Apr 05 '12 at 13:35

38

mysql -f -p < 2010-12-01.sql

the -f (force) being the operative option here, worked for me.

answered Apr 05 '12 at 13:35

Patrick Klingemann

8,548
4
41
49

In my case, I restored .sql file in mysql cluster 5.5.29-7.2.10, -f also works. It lists all the ERROR lines, and this can also ignore the error like: ERROR 1528 (HY000) at line 22: Failed to create LOGFILE GROUP, and reuse the LOGFILE already created. – zhihong Mar 18 '13 at 11:15

Ben · Answer 3 · 2013-07-09T01:13:29.573

23

Following the advice from jmlsteele's answer and comment, here's how to turn the inserts into INSERT IGNORE on the fly.

If you're importing from an sql file:

sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" < 2010-12-01.sql | mysql -p

If you're importing from a gz file, just pipe the output from gunzip into sed instead of using the file input:

gunzip < 2010-12-01.sql.gz | sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" | mysql -p

edited Jul 09 '13 at 01:13

answered Jul 03 '13 at 16:11

Ben

2,373
1
15
10

1

Wouldn't this also replace "INSERT INTO" texts inside text fields? – Almad Jul 04 '13 at 13:15
2

@Almad - It would only change the first INSERT INTO, and any additional on the same line would not be changed. As far as I'm aware, any time you'd have "INSERT INTO" in a text field it would be following an INSERT INTO statement -- But, I changed the answer to make sure its only replaced when its at the beginning of a new line. – Ben Jul 09 '13 at 01:19
A dump can have multiple INSERT statements (even thousands or more) per-row: in that case the solution wouldn't affect the other statements after the first. Maybe with a more complex regex it's possible to achieve the result, substituting only the INSERT INTO not in text fields. – reallynice Aug 25 '15 at 08:15

score 3 · Answer 4 · answered Feb 22 '19 at 10:51

3

Great tip. I did it a little different but same result.

perl -pi -e 's/INSERT INTO/INSERT IGNORE INTO/g' filename.sql

answered Feb 22 '19 at 10:51

Robert Saylor

769
6
8

score 2 · Answer 5 · answered Mar 17 '17 at 14:01

2

The other options certainly are viable options, but another solution would be to simply edit the .sql file obtained from the mysqldump.

Change:

INSERT INTO table_name ...

TO

INSERT IGNORE INTO table_name ...

answered Mar 17 '17 at 14:01

PromInc

1,044
7
12

@Almad the solution you linked allows you to make the same change I proposed from the command line at the time of importing and does not modify the .sql file. My suggestion here is to actually open the file, edit its contents and save the file before importing. While similar I don't feel these are duplicate answers. – PromInc Mar 18 '17 at 09:38

score 1 · Answer 6 · answered Mar 06 '13 at 13:33

Just a thought did you delete the MySQL directives at the top of dump? (I unintentionally did when I restarted a restore after deleting all the records/tables I'd already inserted with a sed command). These directives tell MySQL ,among other things, not to do unique tests, foreign key tests etc)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

How to skip row when importing bad MySQL dump

6 Answers6

Linked