161

I am importing a fairly large database. The .sql file has almost 1,000,000 lines in it. Problem is that I am getting a syntax error when trying to import the database. It says:

ERROR 1064 (42000) at line 8428420: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
Fatal error: Maximum execution time of 600 seconds exceeded in

Normally I'd just open the .sql file and fix the error. But my computer is really struggling to open this file.

Is there any way I can ignore errors when importing a MySQL database?

A.L
  • 10,259
  • 10
  • 67
  • 98
Dustin
  • 4,314
  • 12
  • 53
  • 91
  • 1
    Use an editor that does not open the entire file into memory (this is the problem your having to try to edit it) and uses Seek based display for editing on Windows I can Recommend Notepad++ for this task – Barkermn01 Apr 30 '18 at 08:58

1 Answers1

380

Use the --force (-f) flag on your mysql import. Rather than stopping on the offending statement, MySQL will continue and just log the errors to the console.

For example:

mysql -u userName -p -f -D dbName < script.sql
luchaninov
  • 6,792
  • 6
  • 60
  • 75
Craig Boobar
  • 3,801
  • 1
  • 10
  • 3
  • 9
    if anyone else wondered about the `-D` in `-D dbName`, it's the same as specifying `--database=dbName ` – Aamnah Mar 12 '19 at 16:49
  • I've tested both `-D dbName` and `--database=dbName` and it worked @Aamnah – FanoFN Sep 05 '19 at 03:49
  • I have a question about the -f flag. If there is a syntax error in the timestamp of an insert statement, will it ignore that insert statement alone or that entire block of insert and move on? Kindly advise. – Raja Jun 18 '23 at 03:36