How can I check the SQL syntax in a .sql file?

Question

How can I check the SQL syntax in a .sql file?

Federalism

This needs a lot of clarification to be useful (and to stay open). Are you asking for tips on visually inspecting your SQL? Are you looking for a script that will help you check the syntax? Are you running the script and you need help understanding a particular error? – Adventuresome

I think they want to verify that the file has valid mySql syntax. – Sharell

visually inspecting SQL file or script will also do to check the syntax – Federalism

@Federalism said "visually inspecting SQL file or script will also do to check the syntax". in my experience, humans are not 100% accurate checking syntax, but the DB is! – Sharell

In an automation that accepts a set of migration/rollback scripts, knowing that they at least pass basic syntax sanity and failing the commit if they don't makes a lot of sense. – Secundine

By Executing It? Is This Such A Mystery? – Palaeozoic

Answer 1

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.

http://dev.mysql.com/downloads/gui-tools/5.0.html

Flowage

Answer 2

SELECT STATEMENT_DIGEST_TEXT in MySQL 8.0 can be used for MySQL query syntax validation.

8.0.4>SELECT STATEMENT_DIGEST_TEXT('FLUSH TABLES')\G
STATEMENT_DIGEST_TEXT('FLUSH TABLES'): FLUSH TABLES 

8.0.4>SELECT STATEMENT_DIGEST_TEXT("SET GLOBAL second_cache.key_buffer_size=128*1024;")\G
STATEMENT_DIGEST_TEXT("SET GLOBAL second_cache.key_buffer_size=128*1024;"): SET GLOBAL `second_cache` . `key_buffer_size` = ? * ? ;

8.0.4>SELECT STATEMENT_DIGEST_TEXT("create TABLE t1 ( a2 int unsigned not null, b2 int unsigned not null, c2 int unsigned not null, primary key (a2), index b2x (b2), index c2x (c2) ) ENGINE=MEMORY;")\G
STATEMENT_DIGEST_TEXT("create TABLE t1 ( a2 int unsigned not null, b2 int unsigned not null, c2 int unsigned not null, primary key (a2), index b2x (b2), index c2x (c2) ) ENGINE=MEMORY;"): CREATE TABLE `t1` ( `a2` INTEGER UNSIGNED NOT NULL , `b2` INTEGER UNSIGNED NOT NULL , `c2` INTEGER UNSIGNED NOT NULL , PRIMARY KEY ( `a2` ) , INDEX `b2x` ( `b2` ) , INDEX `c2x` ( `c2` ) ) ENGINE = MEMORY ; 

If the SQL is not supported, you'll get an error. Like the next one, but there is something special about this response;

8.0.4>SELECT STATEMENT_DIGEST_TEXT('HELP SELECT')\G
ERROR 3676 (HY000): Could not parse argument to digest function: "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 'SELECT' at line 1".

Did you see what was special? It is the fact that 'HELP' is a valid, but client-side only keyword - i.e. not a server keyword. In any case, an invalid SQL statement will produce a similar situation; an ERROR.

Thus, you can check based on ERROR vs NO ERROR to know whether the passed SQL syntax is valid or not (excluding the very limited set of client-side-only commands, but those would not be of interest to most people).

Summary; SELECT STATEMENT_DIGEST_TEXT is a comprehensive SQL parser (while that may not be it's direct/intended function) which can be used in all cases to check the validity of statements quickly and without actually executing them. This is huge progress as far as SQL validity validation is concerned.

Note that you need to have a MySQL server up and running for this. You can pass queries using the mysql -e client, or use a pipe to mysql etc.

Mendy

Answer 3

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.

edit: For MySQL 8.0+ see How can I check the SQL syntax in a .sql file?

Humo

This kind of programmatic solution would be very useful to, for example, validate that a mysqldump result is at least syntactically complete. – Gossipry

Yes, but doing so would be complex to do. An easier approach (with the same outcome); https://mcmap.net/q/444990/-how-can-i-check-the-sql-syntax-in-a-sql-file – Mendy

The question is about syntax. Your answer is about lexical analysis. What building your own parser has to do with testing the syntax of existing queries as against existing implementations is another mystery. – Palaeozoic

To me the bigger mystery is how you know this 15 years after the fact from a single, vague sentence. Hindsight really must be 20-20. – Humo

Answer 4

TLDR:

>awk '{print "EXPLAIN " $0}' statements.sql | mysql --force -u user -p database | grep "ERROR"

---

Strangely, mysql does not have a built-in switch for this, but you can check syntax by adding the EXPLAIN statement in front of your queries.

If you have a statements.sql file with each statement on one line, prepend EXPLAIN in front of all lines with:

>awk '{print "EXPLAIN " $0}' statements.sql > check.sql

You can then run the statements with the mysql command-line tool and use --force to have it continue on error. It will print an error for any statements with incorrect syntax.

>mysql --force -u user -p database < check.sql

Or to only view the lines with errors:

>mysql --force -u user -p database < check.sql | grep "ERROR"

You can do all of this on one line without creating an intermediate file:

>awk '{print "EXPLAIN " $0}' statements.sql | mysql --force -u user -p database | grep "ERROR"

Cola

I would add this to make it so the file doesn't need to be a single line. awk '{if (sub(/;$/,";\n")) printf "EXPLAIN %s", $0; else print $0}' statements.sql Combine lines ending in semicolon. – Shadowy

Answer 5

You can try using SQLFluff cli tool, "The SQL Linter for Humans".

Simple use

This tool is a linter, so the main way of using it is to show all kinds of code style issues, but you can force it just parse the script to check for syntax errors, like this:

sqlfluff parse --dialect mysql <myscript>.sql

...where --dialect is set to one of the options from here.

If your script is valid it will result in a long output of the parsing and if it's not valid it will also add a section about the errors at the end.

The exit code will be 1 on errors, so this is CI/CD-friendly.

Output only the errors

For a concise output of only showing the errors you can run it like this:

sqlfluff parse --dialect mysql <myscript>.sql | (! grep -A100 "==== parsing violations ====")

(the ! is for keeping the 1 exit code in case of a validation error, credits to this comment)

Erelong

Answer 6

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.

Esophagus

Answer 7

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.

Eisk

The script may contain statements that cause an implicit commit anyway. – Jennifferjennilee

True, this makes some assumptions about the content of the .sql file. – Eisk

Answer 8

just run it....

begin transaction

run it

rollback

Sharell

runing it will give the answer , but is there anything which shows me syntax errors in that file .... – Federalism

Can't roll back if tables are MyISAM, or if script runs implicit-commit statements (e.g. DDL), or if script contains COMMIT statements. – Jennifferjennilee

@Bill Karwin, their question is quite brief and doesn't mention any of that ;-) – Sharell

Prefixing the statement with 'EXPLAIN ' will cause the statement to be parsed and parser errors to be reported without changing the data or hogging too much resource on the database. – Nacre

DO NOT RUN THIS ON PRODUCTION! Some statements implicitly and immediately commit. There's quite a few. Ref mariadb.com/kb/en/library/… for a list – Mendy