1

There are times that when you are importing a mySQL dump, it would break due to syntax, etc. Currently working on a very large database, running:

mysql -u root -p database < import.sql

will only verbose

ERROR 1064 (42000) at line 116238: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 389

looking at line 389

https://i.stack.imgur.com/aZAJX.png

won't be much help since its not the actual line the SQL is currently executing (it ignores some comments, etc.)

So, to be in general, what is the best way to debug? How to know the actual line causing the issue?

Hope somebody can help thanks!

arvil
  • 840
  • 11
  • 27
  • Not sure how to debug, I would open the file and search for `''` two consecutive single quotes you may find the problem... – Jorge Campos Apr 06 '17 at 04:26
  • The consecutive single quotes doesn't refer to an empty string in this error message. For example: `ERROR 1064 (42000): 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 'WHERE id=101 SET name='foo'' at line 1` you see the part of the sql is surrounded by single quotes. It may refer to everywhere. – Taha Paksu Apr 06 '17 at 05:47
  • 1
    Look again... the error is at line 116238 of the *file*, but line 389 within that specific *statement*. – Michael - sqlbot Apr 06 '17 at 13:45
  • @Michael-sqlbot you are right, but I think would be great if there's really a software like that – arvil Apr 06 '17 at 22:27
  • Agreed, but I haven't finished writing it yet. – Michael - sqlbot Apr 07 '17 at 00:21
  • @TahaPaksu, in `...the right syntax to use near ''` the quoted empty string *always* refers to **end of statement**. The `''` means the parser ran out of tokens at the end of a statement in a situation where end-of-statement was impossible. For example, `SELECT * FROM t1 WHERE;` will generate this error, because this query can't be valid without *something* after `WHERE`. The `;` (or whatever the current delimiter is, at the end of the statement) is actually stripped from the query by the `mysql` cli before the statement is sent to the server, so it doesn't appear in the error message. – Michael - sqlbot Apr 07 '17 at 00:29
  • @Michael-sqlbot, I commented to the first comment of Jorge Campos as he suggests searching for consecutive single quotes in the SQL file. – Taha Paksu Apr 07 '17 at 05:39
  • @TahaPaksu you did, and you were correct. I think I might have misunderstood your intention when you said the error can be "everywhere." My intention was to clarify that the `''` can only represent an unexpected end of statement, since in any other case (such as your example error), the parse buffer won't be empty, so the error will include part of the query beginning with the point where something unexpected was encountered, shown inside the quotes. Everything the server thinks it understood in the statement has already been parsed and consumed, leaving `''` (nothing remaining to parse). – Michael - sqlbot Apr 07 '17 at 06:46

0 Answers0