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?

Itol

Answer 1

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

It looks like Zimbabao's answer will work also.

---

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

Itol

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. – Baffle

Answer 2

try

mysql --force < sample_data.sql 

Mysql help section says

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

Hove

Answer 3

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

Either

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 – Shurlocke