CODEWITHSUNDEEP
mysqlvalidationsyntax
maxjackie
maxjackie

Reputation: 23282

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

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

Upvotes: 33

Views: 47248

Answers (8)

Greg Dubicki
Greg Dubicki

Reputation: 6940

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)

Upvotes: 2

Roel Van de Paar
Roel Van de Paar

Reputation: 2228

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.

Upvotes: 8

VolkerK
VolkerK

Reputation: 96159

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?

Upvotes: 5

culix
culix

Reputation: 10616

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"

Upvotes: 4

Hardwareguy
Hardwareguy

Reputation: 2981

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

Upvotes: 1

Christopher Klein
Christopher Klein

Reputation: 2793

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.

Upvotes: 1

Travis Beale
Travis Beale

Reputation: 5644

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.

Upvotes: 0

KM.
KM.

Reputation: 103597

just run it....

begin transaction

run it

rollback

Upvotes: -4

Related Questions