How can I check (My)SQL statements for syntactical correctness [closed]

Question

we're currently setting up out integration server and during that process we've set up pre-commit hooks on the SVN so that our developers can't check in files that are syntactically invalid (primarily PHP and XML).

We also have a bunch of .sql files (for MySQL) which I'd like to lint as well. Unfortunately, Google didn't turn up anything useful for this task.

Any ideas?

Golub

Exactly the same issue - and the system must cope with DDL (in fact the SQL files are all DDL), and with MySQL enhancements. – Trotskyite

Answer 1

The commercial version of MySQL Workbench has a syntax checker for MySQL statements, but of course that would only cover the database aspects. See http://mysql.com/products/workbench/ (though I found the factoid in the help index for the free app).

Chantal

Thanks! But unfortunately I don't think I can talk my boss into buying that just for having another neat toy for our integration server :) – Golub

Answer 2

After searching for a CLI tool for syntax linting in Mysql to use in Jenkins and didn't find anything quickly (this Stackoverflow question is one of the first results - LOL) I came up with the following solution (OS: Linux, but should be feasible with Windows too):

Something like the follwoing:

lint_result=`mysql mysql_test -B -f -e 'select asdf s where x;' 2>&1`; if [ `echo $lint_result | sed -r "s/ERROR ([0-9]*).*/\1/g"` -eq 1064 ]; then echo -e "Syntax error:\n${lint_result}"; fi
Syntax error:
ERROR 1064 (42000) at line 1: 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 x' at line 1

(To check sql files you can use "< filename.sql" instead of -b -e 'statement')

If the syntax of the query can not be parsed by mysql it claims: ERROR 1064 (42000) at line 1: 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

Only if the syntax is correct it tries to execute the query and realize that the table don't exists but this isn't interesting anymore:

ERROR 1146 (42S02) at line 1: Table 'mysql_test.s' doesn't exist

Therefor Error 1064 is the invalid syntax. You only need to create an empty test database because otherwise only errors with a wrong FROM part would appear (here for example the database is needed in order to get a valid syntax check result: 'select asdf from s where x and if;).

As far as i tested it works fine (Version Mysql 5.5).

Here a complete bash script vesion:

#!/bin/bash

source_dir=${1};
database="mysql_test";
mysql_args="-h127.0.0.1";

mysql $mysql_args -B -e "DROP DATABASE IF EXISTS $database; CREATE DATABASE $database;";
for file in `find $source_dir -name "*.sql"`; do
    lint_result=`mysql $mysql_args $database -f -b < $file 2>&1`;
    if [ "`echo $lint_result | sed -r \"s/ERROR ([0-9]*).*/\1/g\"`" = "1064" ]; then
        echo -e "Syntax error in file ${file}:\n${lint_result}" && exit 1;
    fi;
done

Freehearted

Excellent solution !! Works like a charm. – Rufena

Answer 3

Here is a validator, which validates against SQL 92/99/2003 standards however the fact you mention MySQL leads me to believe you are using MySQL specific syntax in your SQL queries.

One option is move to an agnostic approach in dealing with your database layer, writing agnostic SQL code. You will obviously need to get in contact with Mimer to see if you can take it offline and integrate it within your CI environment.

Couple of "ifs" in the overall approach and this is all contingent on the fact you are willing/able in the current state to write agnostic SQL code.

Shivers

Intersting link! Unfortunately, writing agnostic SQL is not an option. Out system has a lot of traffic and we need to squeeze every last bit of performance out of MySQL (in some places, at least). – Golub

also link is dead – Duran

Answer 4

I've written an CLI tool to syntax check SQL files using the phpMyAdmin SQL parser: php-sqllint.

Contumacy

How to install on windows and use php-sqllint? – Florineflorio

my antivirus says the link to php-sqllint is malicious/infested with spam/ads :( – Lavin

Answer 5

Disclaimer: I have not yet tried out any of these tools! YMMV!

PHPMyAdmin's validating SQL parser is an actively developed open source project and would be my best bet right now in 2021.

The other more or less current project I've found doesn't look as mature, but here it is:

Persist SQL Query "The best and most complete query builder/parser for MySQL (PHP)" according to its Github Tagline, but it's not explicitly validating, but it from the source you can see it does throw Exceptions if a query can't be parsed properly.

Just to mention it: PHP-SQL-Parser is non-validating, so it does not qualify for linting.

Lavin

Answer 6

I suggest trying out SQLFluff cli tool, "The SQL Linter for Humans".

It has a documentation section specifically about using it as a pre-commit hook.

Please see more info about using it in my other answer, here.

What