How can I check the SQL syntax in a .sql file?
runing it will give the answer , but is there anything which shows me syntax errors in that file ....
maxjackie
2009-06-05 15:37:32
you can have valid syntax and still have runtime errors
KM
2009-06-05 15:41:11
Can't roll back if tables are MyISAM, or if script runs implicit-commit statements (e.g. DDL), or if script contains COMMIT statements.
Bill Karwin
2009-06-05 15:45:37
@Bill Karwin, their question is quite brief and doesn't mention any of that ;-)
KM
2009-06-05 16:07:05
+1
A:
There are a few free/try-ware products out there that will allow you to connect to a MySQL database or just paste in the script to validate it. Google is your friend here. Mimer will check ANSI-Standard syntax validation but probably not handle any MySQL specifics.
SomeMiscGuy
2009-06-05 15:40:58
A:
There are a couple of possiblities. If you are using InnoDB tables that support transactions, you can simply execute a start transaction; at the beginning of your .sql file and a rollback; at the end. MySQL will output any syntax errors.
If you are testiing UPDATE or DELETE statements, you could add LIMIT 0 to the end to prevent these queries from making any database changes, and still have MySQL check the syntax.
Travis Beale
2009-06-05 15:43:00
The script may contain statements that cause an implicit commit anyway.
Bill Karwin
2009-06-05 15:46:44
True, this makes some assumptions about the content of the .sql file.
Travis Beale
2009-06-05 15:48:10
A:
The basic lexer seems to be implemented in sql/sql_lex.cc. You could use/salvage this to build your own test parser. But this would only check for syntax but not any runtime errors.
VolkerK
2009-06-05 21:48:24
+1
A:
You could paste it into a query browser like the MySQL Query Browser (part of the GUI Tools package) and visually inspect how the keywords and string literals are colored to more easily see if you've made any syntax errors.
Hardwareguy
2009-06-05 21:53:41