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

Question

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

Not possible you have to figure it out and near is usually helpful — P.Salmon
– P.Salmon, Commented Apr 1, 2021 at 15:33
if mysql workbench can underline in red issues with the syntax, it means there must be some way to retrieve the column where the error is detected. Near is not helpful in my case, I have numerous times '' appearing without issues. — nathan raynal
– nathan raynal, Commented Apr 1, 2021 at 15:37
the code is too long to be checked manually. I tried to scroll but it would take me hours to find it most probably. The red underline is useful for up to a few hundred characters, maybe even a few thousands, but here I'm speaking about lines with over one million character. not to mention it crashed mysql workbench after a few minutes of scrolling. — nathan raynal
– nathan raynal, Commented Apr 1, 2021 at 15:47
If you have a single query that has over a million characters, I think we've found the problem. The empty string for near could just mean that mysql couldn't load the beast at all. If the script is many queries, then it's time to knuckle down and test them one at a time. It might be helpful to know where this script came from. — Jerry
– Jerry, Commented Apr 2, 2021 at 1:22

Mike Lischke · Accepted Answer · 2021-04-02 09:08:10Z

1

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.

answered Apr 2, 2021 at 9:08

Mike Lischke

54.3k18 gold badges142 silver badges210 bronze badges

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

1 Comment

nathan raynal Over a year ago

I happened to find the error, it was an added comma in the end of the query. So it really was a syntax error, that I could find manually luckily. However, I'm still surprised that mysql can't be more verbose about syntax error, is there a way I could suggest the devellopers to add this feature ?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related