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
|
,
0 Comments
)