I want to be able to pass any query to MySQL, have MySQL check whether
the query's syntax is valid, including whether non-existing tables or
columns have been referenced, and return an error if something is
wrong, WITHOUT actually executing the query. There are tricks that
accomplish this in some cases, such as using LIMIT 0 on UPDATE or
DELETE statements. However, I also need a way to test INSERT
statements so this hack is of limited use. I am looking for a general
solution.

---

Clarification of Question by vladkornea-ga on 19 May 2006 11:14 PDT

I am not interested in outside scripts that attempt to parse and
validate a query string and check for existence of referenced tables
and columns. Such a solution is too clunky, and opens the door to a
host of potential problems including performance issues, bugs, and
obsolescence. I'm hoping there's a simple SQL command to do this
(similar to EXPLAIN) that I just don't know about. However, lacking
such a statement, I'm open to other simple & reliable methods of doing
what I described. I am working in PHP, incidentally.

Hi, 

I've been working with MySQL for almost 10 years, and never heard of
such a setting. I checked my books to see if perhaps I just never
thought to look,  and there isn't even a connection switch you could
set to put the database in "test mode".

The way to do this for your Insert test, is to create a temporary
table, perform the insert, check for errors, and then drop the
temporary table.

mysql> CREATE TEMPORARY TABLE tmp_address SELECT * FROM address

While session is connected the temporary table will exist, unless you
DROP it. Once the session is closed, then the temporary table will be
dropped by MySQL. This isn't a hack, it is the real way to do what you
are trying to do. In fact you can set the temporary table name to the
same name as the original, and while the session is connected, the
temporary table will be used, instead of the original table:


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| employee       |
| rssnews        |
+----------------+
3 rows in set (0.01 sec)

mysql> create temporary table employee select * from employee;
Query OK, 10 rows affected (0.14 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> delete from employee;
Query OK, 10 rows affected (0.00 sec)

mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql>

What this does is allow you to put variable switches in your code to
set your program into "test" mode, by using a logic switch to see if
the temporary table should be created first before running your
queries. This way you don't have to have two sets of queries (one with
temp names and one with real names). Of course there are other ways to
do this, but this is by far the most effective.

You can learn more about temporary tables at these websites:

http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html



thanks, 

webadept-ga

---

Request for Answer Clarification by vladkornea-ga on 19 May 2006 12:09 PDT

In the case of tables such as zip codes containing 40,000+ rows or
orders containing 750,000+ thousand rows, this will result in a major
performance hit, correct?

---

Clarification of Answer by webadept-ga on 19 May 2006 12:33 PDT

Hi again, 

Not as much as you would think, MySQL is probably the best at doing
stuff like this, and ... you aren't going to be doing it very often..
right? Once you have the code written and tested, then you shouldn't
be doing it at all. If your system is so unstable that you need to
check the names of columns and table existence before every
transaction, then you have bigger problems than a few possible errors
:-)..

MySQL does have transactions now, so if you are worried about the
possibility of sending multiple inserts and the connection being
broken before all of them are done, then I would use transactions.
Otherwise, get your code and tables stable, then remove the testing
features, and setup error notifications and logging for future
maintenance.

You can also run a few checks using SHOW to make sure that tables
exists and have the expected columns before you run through a set of
procedures.

webadept-ga

---

Request for Answer Clarification by vladkornea-ga on 19 May 2006 12:42 PDT

Looks like what I'm looking for doesn't exist, which I suspected based
on my own searches but it seemed really odd that it wouldn't. You
confirmed that it doesn't exist, which is valuable. Thanks.

Incidentally, it just occured to me that this *might* be possible
using transactions--provided that SQL checks syntax before queuing
queries. (Start transaction, do INSERT statement, then ROLLBACK.) I
don't know whether MySQL validates the query before a COMMIT though.
Do you know?

---

Clarification of Answer by webadept-ga on 19 May 2006 14:26 PDT

Hi again, 

The basic run through is this (copied from article found on
http://www.samspublishing.com/articles/article.asp?p=29312&rl=1)


1.Begin transaction.
BEGIN WORK;

2.Check available inventory for a product with a specific ID, using a
table called inventory and a field called qty.
SELECT qty FROM inventory WHERE id = 'ABC-001' FOR UPDATE;

3.If the result is less than the amount ordered, rollback the
transaction to release the lock.
ROLLBACK;

4.If the result is greater than the amount ordered, continue issuing a
statement that reserves the required amount for the order.
UPDATE inventory SET qty = qty - [amount ordered] WHERE id =
'ABC-001';

5.Insert a master record into the master order table.

6.Retrieve the ID from the master order record you just entered.

7.Insert records into the line items table for each item ordered.

8.If steps 5 through 7 are successful, commit the transaction and release the lock.
COMMIT;

While the transaction remains uncommitted and the lock remains in
effect, no other users can access the record in the inventory table
for the product with the ID of ABC-001. If a user requests the current
quantity for the item with the ID of ABC-002, that row still operates
under the shared lock rules and can be read.

So that would do what you are suggesting you need done. Also in that
same article it talks about a server setting called AUTOCOMMIT which
is set at 0 for default. Make sure you understand that bit, because
from what you have said so far, you may not want that setting at the
default.

Something you may want to check out is the PHPMyAdmin project :
http://www.phpmyadmin.net/home_page/index.php

And the Pear Project
http://pear.php.net/

Those two have repositories of some of the tightest, best written
php/mysql code I've ever seen. It may save you some time and head
scratching to go through what they have developed.

Good Luck! And thanks for the question

webadept-ga