68

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?

Steve
  • 2,066
  • 13
  • 60
  • 115

3 Answers3

100

try

mysql --force < sample_data.sql

Mysql help section says

 -f, --force         Continue even if we get an sql error.
Zimbabao
  • 8,150
  • 3
  • 29
  • 36
48

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
Lukas Ignatavičius
  • 3,496
  • 2
  • 24
  • 29
  • 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 https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict – CLAbeel Nov 12 '18 at 20:53
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

KCD
  • 9,873
  • 5
  • 66
  • 75
Steve
  • 2,066
  • 13
  • 60
  • 115
  • Thank you for the tip! Saved a lot of time. – André Luiz Müller Mar 10 '13 at 03:02
  • 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. – BuvinJ Jul 06 '21 at 20:46