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? 10 1481
>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
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.
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.c om 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.
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.c om wrote:
Gordon Burditt wrote:
Often times when writing a php script to handle some mysql DB transaction s, 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*******@attgl obal.net
=============== ===
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. tu*****@gmail.c om 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*******@attgl obal.net
=============== ===
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.
Oh, and feel free to have the last word. I'm done here. tu*****@gmail.c om 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*******@attgl obal.net
=============== === This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 setting somewhere, but is it in
MySql or in the Control Center somewhere. I am running this on a Windows
2000 machine.
TIA
|
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 GByte of RAM.
I have a database collecting logs. Each day, a new table is created. In
order to allow for queries across more than one day, I use 'MERGE'
|
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, however, my
fulltext search queries fail in 3.23.58 but the exact queries (with
same data) work perfectly in 4.0.10.
--...
|
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 to Dual Xeon/MySQL
4.0.15/RedHat8 and Dual Xeon/MySQL 4.0.18/Mandrake 10.
And it seems there are zombie threads. 570 threads in 1 hour and we
didn't even use JDBC connection pooling at all. These threads are
supposed to be gone within 60...
|
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 to load making any kind linkage
with my Access data virtually useless.
I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting
out on a server and the Access database is running locally. The network
connection is very...
| |
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 #./configure it goes through all of the preparing
tables and starting mysqlServer and daemon, but than immediaetly says
"mysql ended" -- I try to do #make right after anyway, but I get the
error "No targets specified and no makefile found" -- Any...
|
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?
Cheers,
Ciarán
|
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, we can check if it was performed
with success like this:
|
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 .= 'SET position=position-%d ';
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |