17

I'm making a simple tool that will get a string of MySQL commands and run it (on several DB servers sequentially). I trust the users to be sensible, but mistakes happen, and I'm looking for a way to prevent basic typos:

Is there a way to validate, at runtime, (relatively simple) MySQL queries to see if they're syntactically correct?

I'm not looking for semantic correctness, e.g. table names or join applicability; just something like a spellcheck for SQL queries.

In other words, 

SELECT * FROM x;

or

INSERT INTO x SET id=1,bar="foo";

would be marked valid, whereas any of those would not:

SELECT FROM x;
SECLET * RFOM x;
ISNETR INTO x SET id=1;
HJBGYGCRYTCY;

For SELECTs, I could bend EXPLAIN to my needs - run EXPLAIN SELECT (...) and watch for errors, but is there a way to check for other commands as well?

Improve this question

6 Answers 6

Reset to default
21

To verify a query i use the EXPLAIN command. You can take any SQL query and add EXPLAIN before it and execute. If query is wrong, error will be returned.

Examples:

explain select * from users;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

And wrong query

explain select * from users2;
ERROR 1146 (42S02): Table 'test.users2' doesn't exist

P.S. Explain works for insert, update, delete too. Not only select

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is the correct answer. Not sure why a non-answer is marked as the correct answer, and the correct answer is at the bottom.
explain select * from users; MySQL says ERROR 1146 (42S02): Table 'sakila.users' doesn't exist Now select * from users; gives ERROR 1146 (42S02): Table 'sakila.users' doesn't exist So whats wrong?
I think the table 'sakila.users' doesn't exist :)
9

Not without knowledge of the schema (for example, is 'x' a table?) and writing a SQL parser. Your MySQL query tool should be able to do that kind of validation (intellisense if you like) but I know from first hand experience, most of the (free) MySQL tools are abysmal.

'Preparing' the query would do what you want, but is a runtime check, not a compile time check - you seem to be looking for a compile time/offline solution.

Improve this answer

4 Comments

I was looking for a quick way of checking the queries before the script executes them. Writing a parser would be way overboard - if it breaks on the first server, it'll just exit with an error (supposedly the script users (system admins) are sane enough to know what they're doing).
+1 For prepare. That's the only way to do it to my knowledge.
It seems that MySQL can't run PREPARE on an empty db. And some syntactical errors are not shown up, for example two field with the same name.
Using phpMyAdmin, I copied some SQL into the query box. I changed the parameters to match my own schema, but needed to adjust the copied query by adding a WHERE clause to limit its effects. I didn't notice there was a ; at the end of the copied query, so it executed on the entire table. Then I got an error message from my standalone WHERE clause. If there were a way to check the validity of all SQL statements before executing any of them, phpMyAdmin could have warned me of the mistake before executing anything. Or, perhaps phpMyAdmin could wrap them in a transaction to solve this issue!
6

Depending on your MySQL engine and settings, you could start a transaction, try the query, and then do a rollback. Assuming dirty reads are off, that should work.

Improve this answer

1 Comment

Oh wow, I just posted a comment on James Ogden's answer with this same idea. I think phpMyAdmin, and any other RDMS front-end that allows multiple queries should automatically enclose them in a transaction by default.
4

You could create a temporary table to circumvent side effects of the query:

CREATE TEMPORARY TABLE users SELECT * FROM users;
INSERT INTO users(name) VALUES('UniqueName');
DROP TABLE users;
SELECT * FROM users WHERE name='UniqueName'; -- Should return 0 results
Improve this answer

Comments

0

You can temporarily create a stored procedure with your code and unique name. Then you can drop it immediately. Transactions can't rollback SP creation. But be careful, because it just syntax validation, so it doesn't check if an object (in your query) exists or not.

Improve this answer

Comments

-2

delete from employee where columnname='salary';

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.