Home > Archive > PHP SQL > June 2006 > Damage Control: When 1 of 2+ Interdependent mySQL Queries Fail...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Damage Control: When 1 of 2+ Interdependent mySQL Queries Fail...
|
|
| tuco357@gmail.com 2006-06-17, 8:10 am |
| This problem has been vexing me for some time and I thought I should
consult the group....
Often times when writing a php script to handle some mysql DB
transactions, I must write code that performs, say, an insert into a
MySQL DB, then retrieves the last item's index, and makes a new
insertion into another table on the DB that requires the previously
obtained index. Both queries must be executed and completed - if query
one succeeds and query two fails, I roll back the changes of query one.
Obviously, there will be times when many interdependent queries are
involved - rolling back in such cases is a headache, albeit a necessary
one. We all know that a robust web system must have a lot of error
checking, and the intergrity of the databases must be protected at all
times.
Now, for the big problem that I cannot find an *eloquent* solution
to...
Suppose that in the original example, query one is executed and
completed. However, just before query two is made by the PHP script,
the whole darn server goes down. Thus, query one is complete, query
two never took place, and when the server is restarted, the database is
corrupt!
An obvious, but IMHO, clunky, solution, is to use a set of scripts that
can be run every few hours or days that go through the database and
verify that everything makes sense - e.g. there is no row in PROFILES
with a globally unique ID that cannot be found in the MEMBERS table (a
user has a profile but no basic account info in members). As
problems are discovered, they can be automatically corrected, or an
alert can be sent to an admin.
Obviously, my solution is the pits. What would you do?
| |
| Gordon Burditt 2006-06-17, 8:10 am |
| >Often times when writing a php script to handle some mysql DB
>transactions, I must write code that performs, say, an insert into a
>MySQL DB, then retrieves the last item's index, and makes a new
>insertion into another table on the DB that requires the previously
>obtained index. Both queries must be executed and completed - if query
>one succeeds and query two fails, I roll back the changes of query one.
Of course, you are using transactions here.
>Obviously, there will be times when many interdependent queries are
>involved - rolling back in such cases is a headache, albeit a necessary
>one. We all know that a robust web system must have a lot of error
>checking, and the intergrity of the databases must be protected at all
>times.
>
>Now, for the big problem that I cannot find an *eloquent* solution
>to...
>
>Suppose that in the original example, query one is executed and
>completed. However, just before query two is made by the PHP script,
>the whole darn server goes down. Thus, query one is complete, query
>two never took place, and when the server is restarted, the database is
>corrupt!
Isn't an *UNCOMMITTED* transaction supposed to be rolled back, if it
in fact ever made it to disk in the first place, on a server restart?
I believe MySQL with InnoDB tables is capable of this.
Gordon L. Burditt
| |
| tuco357@gmail.com 2006-06-17, 8:10 am |
| Gordon Burditt wrote:
>
> Of course, you are using transactions here.
>
>
> Isn't an *UNCOMMITTED* transaction supposed to be rolled back, if it
> in fact ever made it to disk in the first place, on a server restart?
>
> I believe MySQL with InnoDB tables is capable of this.
>
> Gordon L. Burditt
Perfect! This is the problem with being a self-trained web developer -
your answer is probably DB 101, but I never took that class!
Thanks, Gordon.
-B.
| |
|
| For help with transactions, check out ADOdb or ADOdb lite along with
the section on transactions in the following article:
http://www.databasejournal.com/feat...cle.php/2234861
Tom
tuco357@gmail.com wrote:
> Gordon Burditt wrote:
>
> Perfect! This is the problem with being a self-trained web developer -
> your answer is probably DB 101, but I never took that class!
>
> Thanks, Gordon.
> -B.
| |
| Jerry Stuckle 2006-06-17, 8:10 am |
| Tom wrote:
> For help with transactions, check out ADOdb or ADOdb lite along with
> the section on transactions in the following article:
>
> http://www.databasejournal.com/feat...cle.php/2234861
>
> Tom
>
>
> tuco357@gmail.com wrote:
>
>
>
And try asking in a MySQL newsgroup - such as comp.databases.mysql - instead of
one for PHP programming. You'll get better answers in general (although there
is nothing wrong with the answers you got here).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
| |
|
| tuco357@gmail.com wrote:
>
> This problem has been vexing me for some time and I thought
> I should consult the group....
I think reading the MySQL manual coould be even more helpful.
> Often times when writing a php script to handle some mysql DB
> transactions, I must write code that performs, say, an insert into a
> MySQL DB, then retrieves the last item's index, and makes a new
> insertion into another table on the DB that requires the previously
> obtained index. Both queries must be executed and completed -
> if query one succeeds and query two fails, I roll back the changes
> of query one.
So you should use transactions...
> Suppose that in the original example, query one is executed and
> completed. However, just before query two is made by the PHP
> script, the whole darn server goes down. Thus, query one is
> complete, query two never took place, and when the server is
> restarted, the database is corrupt!
If you use transactions, this is not going to happen, since nothing
will be commited until the second query is successfully executed.
> Obviously, my solution is the pits. What would you do?
Learn about transactions:
http://dev.mysql.com/doc/refman/4.1/en/commit.html
Cheers,
NC
|
|
|
|
|