CODEWITHSUNDEEP
mysqldatabasesyntax-errormysql-workbench
nathan raynal
nathan raynal

Reputation: 161

how to get precise locations of syntax errors in a .sql script?

I'm trying to execute a .sql script which inserts values into a database. Problem is, there's some issues in the syntax of the script.

Error Code: 1064. 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 '' at line 1    0.078 sec

But the script is pretty large, a few Mos, so I can't check manually where the error is. According to the error message it should be close to quotes, but sadly all the values inserted are on one line in the script file so the indication "on line 1" is not helpful at all.

Is there a way to get a precise position for the error ? I'd like, in particular, the column number where the syntax is wrong. Is that possible ?

I'm using command of the type :

mysql -u root -p  Wikicategory < path\to\script\script.sql

Upvotes: 0

Views: 1386

Answers (1)

Mike Lischke
Mike Lischke

Reputation: 53337

MySQL Workbench uses a different parser (ANTLR4 based) than the MySQL server (yacc based). ANTLR4 based parsers often (but not always) can report errors with a precise location.

I don't think the query is too large. If it were you would get a different error (because the connection buffer would not be large enough).

So, you best option is to reformat the query. For SELECTs you can use MySQL Workbench, but better try Visual Studio Code with the SQLTools plugin. Not the best results there either, but it seems to be able to reformat all types of queries.

Then run the script again to see if you get a better error location.

Upvotes: 1

Related Questions