473,320 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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?

Jun 13 '06 #1
10 1438
>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
Jun 13 '06 #2
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.

Jun 13 '06 #3
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.


Jun 13 '06 #4
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
==================
Jun 14 '06 #5

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.

Jun 14 '06 #6
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
==================
Jun 14 '06 #7
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.

Jun 14 '06 #8
Oh, and feel free to have the last word. I'm done here.

Jun 14 '06 #9
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
==================
Jun 15 '06 #10
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

Jun 16 '06 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Trevor Morrison | last post by:
Hi, I am new to using the Control Center-0.9.2-beta. My problem is that I cannot seem to display more than 1000 rows in my result set when I know that there is 1534 rows. I know that it is a...
0
by: Johannes B. Ullrich | last post by:
--=-WKgoK98ejo9BZyGYc3N/ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable I am having problems with MySQL 4.0.12 on RedHat Advanced Server 2.1 using a dual Xeon with 8...
0
by: Phil Powell | last post by:
Retracing my problem leads me to believe I never successfully created fulltext indexes for MySQL 3.23.58 MyISAM tables. I went to the MySQL manual and was able - or so I thought - to create them,...
0
by: Plymouth Acclaim | last post by:
Hi guys, We have a problem with Dual AMD64 Opteron/MySQL 4.0.18/Mandrake 10 for a very high volume site. We are evaluating the performance on our new server AMD64 and it seems it's slow compared...
11
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time...
1
by: PowerLifter1450 | last post by:
I've been having a very rough time installinig mySQL on Linux. I have been following the instructions form here: http://www.hostlibrary.com/installing_apache_mysql_php_on_linux Everytime I get to...
13
by: Ciaran | last post by:
Hi All, Is it faster to have mySql look up as much data as possible in one complex query or to have php do all the complex processing and submit lots of simple queries to the mysql database? ...
2
by: =?iso-8859-1?B?Sm/jbyBNb3JhaXM=?= | last post by:
Hi there guys, My doubt is related to PHP and MySQL usage, and it's related to check if performed queries are performed with success, since we know that: Each time we perform an sql query,...
9
by: =?iso-8859-1?B?Sm/jbyBNb3JhaXM=?= | last post by:
Hi there guys, My doubt is related with MySQL and Transactions (InnoDB) so here it goes: I have a query like this: $query = 'START TRANSACTION; '; $query .= 'UPDATE sections '; $query .=...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.