Continue SQL query even on errors in MySQL workbench

Question

I'm using MySQL workbench to import a Joomla sample_data.sql file into my local database. I want it to continue importing, even if an error occurs, by skipping the line that caused the error.

Is there something I can prefix the SQL with to prevent the query from halting at any errors?

Zimbabao · Accepted Answer · 2011-03-20 02:52:52Z

100

try

mysql --force < sample_data.sql

Mysql help section says

 -f, --force         Continue even if we get an sql error.

answered Mar 20, 2011 at 2:52

Zimbabao

8,2803 gold badges32 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukas Ignatavičius · Accepted Answer · 2014-04-17 14:46:58Z

49

You could also use INSERT IGNORE

INSERT IGNORE INTO mytable
 (primaryKey, field1, field2)
VALUES
 ('1', 1, 2),
 ('1', 3, 4), //will not be inserted
 ('2', 5, 6); //will be inserted

answered Apr 17, 2014 at 14:46

Lukas Ignatavičius

3,6652 gold badges28 silver badges30 bronze badges

1 Comment

CLAbeel Over a year ago

This can have other downstream impacts depending on the use case, such as inserting implicit default values into columns. "If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings (see Section 13.7.5.41, “SHOW WARNINGS Syntax”). In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE." See dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

KCD · Accepted Answer · 2012-03-29 02:19:17Z

44

In MySQL Workbench, I unticked the option under Query to "Stop Script Execution on Errors":

enter image description here

It looks like Zimbabao's answer will work also.

In newer versions use 'Toggle whether execution of SQL script should continue after failed statements'

Toggle whether execution of SQL script should continue after failed statements

edited Mar 29, 2012 at 2:19

KCD

10.4k5 gold badges69 silver badges76 bronze badges

answered Mar 20, 2011 at 3:20

Steve

1,87915 gold badges71 silver badges136 bronze badges

1 Comment

BuvinJ Over a year ago

While this answer is definitely correct, and super useful, I found it doesn't work quite perfectly. Using Workbench v6.3 against an old v5.1 MySQL Server, and calling a series of stored procedures, when one of these procedures encounters an error, execution is halted regardless of this setting in Workbench! I didn't isolate which of those factors might matter. Just reporting the hole I found here, in case that information could be useful to others.

Collectives™ on Stack Overflow

Continue SQL query even on errors in MySQL workbench

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related