CODEWITHSUNDEEP
mysql
Mike
Mike

Reputation:

Validate MySql Query

How can I validate a MySql query before executing it?

Upvotes: 9

Views: 7533

Answers (5)

georgepsarakis
georgepsarakis

Reputation: 1957

I think I have figured out a way. You can run PREPARE and create a statement.

I guess in pseudocode I would do something like this:

foreach list_of_queries as query
  try
    MySQL_API::run('PREPARE validate_sql FROM ' + quote(query))
  catch MySQL::Error as e
    print 'Query ' + query + ' has errors'
    print e.errno + ' -> ' + e.error
  finally
    MySQL_API::run('DEALLOCATE PREPARE validate_sql')
  end   
end

Example


 CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text,
  `doc_id` int(11) DEFAULT '1',
  `date_inserted` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

-- DELETE
mysql> PREPARE validate_sql FROM 
'DELETE FROM `comments` WHERE gid = ?';
ERROR 1054 (42S22): Unknown column 'gid' in 'where clause'

-- SELECT
mysql> PREPARE validate_sql FROM 
'SELECT doc_id, content, date_modified FROM `comments` WHERE id = ?';
ERROR 1054 (42S22): Unknown column 'date_modified' in 'field list'

-- INSERT
mysql> PREPARE validate_sql FROM 
'INSERT INTO comments VALUES(?,?,?)';
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> PREPARE validate_sql FROM 
'INSERT INTO comments(id, contents, doc_id) VALUES(?,?,?)';
ERROR 1054 (42S22): Unknown column 'contents' in 'field list'

-- ALTER TABLE
mysql> PREPARE validate_sql FROM 
'ALTER TABLE `comments` ADD COLUMN `my_col` bint UNSIGNED 
NOT NULL DEFAULT "0" AFTER `content`';
ERROR 1064 (42000): 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 
'bint unsigned not null default "0" after `content`' at line 1
-- Unfortunately for ALTER TABLE statements 
-- you do not get more specific syntax errors
-- such as "Wrong/missing field type"

Upvotes: 2

shantanuo
shantanuo

Reputation: 32316

Add a slave with "blackhole" as default table type.

Now run any query on that slave.

Upvotes: 0

rooskie
rooskie

Reputation: 491

There isn't a good way! Basically, you need to execute the statement; there's no -l flag.

Two common methods are:

  1. Preceding a SELECT with EXPLAIN. This only works for SELECT statements, so it's not a general solution
  2. Use Transactions. This is only good in 5.x Innodb tables, so it's not a general solution. This also doesn't help for SELECTS which you don't want to take time to execute.

Neither of these work for me. The only decent general solution I've found is to create a test suite that creates temp tables in the likeness of the real ones, and then executes the queries against them:

CREATE TEMPORARY TABLE users_test LIKE users;
CREATE TEMPORARY TABLE auth_test LIKE auth;

You can actually forget about making those temps and keep them around in a '_test' database and just change your DSN when appropriate.

Otherwise, you need to parametrize your queries so you can tell it to use the '_test' tables when necessary.

This is far from ideal, but is the best solution I've found since it executes the queries lightning fast (no data to join/decide on) and doesn't affect the DB.

I would love if someone proved me wrong and pointed to a MySQL Parser that accepts a string and returns either TRUE or an error message.

Upvotes: 7

Sinan Taifour
Sinan Taifour

Reputation: 10795

You could also include the query in a transaction, and then cancel the transaction. This way you can also see the results of the query (even if it is an update or delete query), and then act accordingly, without affecting the database (unless you commit the transaction).

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425371

Run EXPLAIN SELECT …

It will parse your query and show you the execution plan (which algorithms will it use to execute your query, and in which order).

It's a good thing by itself, i. e. always do it, not only for validating.

This will help you to understand what's going on behind the curtains and build more efficient queries.

Upvotes: 1

Related Questions