Damage Control: When 1 of 2+ Interdependent mySQL Queries Fail...

[tuco357]

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]

>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]

Gordon Burditt wrote:

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

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.

[Tom]

For help with transactions, check out ADOdb or ADOdb lite along with
the section on transactions in the following article:

http://www.databasejournal.com/featu...le.php/2234861

Tom
tu*****@gmail.com wrote:

Gordon Burditt wrote:

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

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]

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/featu...le.php/2234861

Tom
tu*****@gmail.com wrote:

Gordon Burditt wrote:

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

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.

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.
js*******@attglobal.net
==================

[tuco357]

Jerry Stuckle 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).

No, Jerry.

I received a great answer from this group. I did not know the problem
was fully solved by MySQL/DB choices; I thought PHP would play the lead
role, hence I asked the PHP group.

But thanks for the flame anyway, dog.

[Jerry Stuckle]

tu*****@gmail.com wrote:

Jerry Stuckle 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).

No, Jerry.

I received a great answer from this group. I did not know the problem
was fully solved by MySQL/DB choices; I thought PHP would play the lead
role, hence I asked the PHP group.

But thanks for the flame anyway, dog.

It was not a flame. It was a gentle suggestion. If I would have wanted to
flame you, you would have known you were flamed.

Look at your question. Everything in it had to do with mysql - there were no
questions on PHP syntax or operations. In fact, there was nothing PHP specific
in it at all. You could have posted the same thing in a C++ newsgroup, a Perl
newsgroup or a bunch of others.

That in itself should have told you it was language independent - but database
dependent, and a mysql newsgroup would be more applicable.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[tuco357]

Jerry Stuckle wrote:

If I would have wanted to flame you, you would have known you were flamed.

You're one of those Internet tough guys, huh? Jerry, it's possible to
be right, and an a$$hole. Just watch The Big Lebowski.

[tuco357]

Oh, and feel free to have the last word. I'm done here.

[Jerry Stuckle]

tu*****@gmail.com wrote:

Jerry Stuckle wrote:

If I would have wanted to flame you, you would have known you were flamed.

You're one of those Internet tough guys, huh? Jerry, it's possible to
be right, and an a$$hole. Just watch The Big Lebowski.

No, I'm not an "Internet tough guy". I just try to steer people to the
appropriate place to get the best answer to their questions.

But obviously you're an asshole.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[NC]

tu*****@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