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

n3rd picture n3rd · Oct 19, 2010 · Viewed 15.6k times · Source

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?

Answer

Georg Buske picture Georg Buske · Apr 25, 2013

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