Hello,
Say I have a function with 5 MySQL queries that all insert or update the
database. Now let's assume that during execution, the first 3 queries
are executed fine but then the 4th one dies for some reason - problem
with the database, connection, whatever.
Obviously I can test the $result variable to see if it returns true or
false, but given the case above, the database is now left in a "corrupt"
state (assuming all 5 queries have to succeed in order for the database
to be correct).
So, if I receive false for any of the results, is there an efficient way
to "roll-back" the database to its original state? Specifically in the
above example, is there a way to undo the first 3 queries?
I thought of setting flags in an array so that, for example, if query 4
dies, I can automatically undo queries 1 through 3 with new queries that
would return the database to its original form, but if there is a
problem that prevented query 4 from executing, I don't see how I can
guarantee that I would be able to successfully execute new queries to
undo the first 3.
Thanks in advance. 9 1721
Marcus (Ju********@aol .com) wrote:
: Hello,
: Say I have a function with 5 MySQL queries that all insert or update the
: database. Now let's assume that during execution, the first 3 queries
: are executed fine but then the 4th one dies for some reason - problem
: with the database, connection, whatever.
: Obviously I can test the $result variable to see if it returns true or
: false, but given the case above, the database is now left in a "corrupt"
: state (assuming all 5 queries have to succeed in order for the database
: to be correct).
: So, if I receive false for any of the results, is there an efficient way
: to "roll-back" the database to its original state? Specifically in the
: above example, is there a way to undo the first 3 queries?
: I thought of setting flags in an array so that, for example, if query 4
: dies, I can automatically undo queries 1 through 3 with new queries that
: would return the database to its original form, but if there is a
: problem that prevented query 4 from executing, I don't see how I can
: guarantee that I would be able to successfully execute new queries to
: undo the first 3.
(*) The "easiest" solution is to use a database like Oracle ($) or
PostgreSQL (free), or MSSQL (if you're on a windows server that has it).
This sort of thing is handled automatically by simply defining a
transaction for the set of related queries.
The mysql web pages had (probably still has) suggestions for how to best
do this sort of thing in mysql, though it's all kludgey.
I suppose you could do something like write all your steps to a process
specific file ahead of time, and then log each step as it runs, then the
last step would be to remove the file. If any files are left around then
you would know something went wrong, and also know what was supposed to
have happened. The processes could also query the original data ahead of
time and save that in the file too which might be useful. If that sounds
inefficient or kludgey then goto (*) above.
--
This space not for rent.
>Say I have a function with 5 MySQL queries that all insert or update the database. Now let's assume that during execution, the first 3 queries are executed fine but then the 4th one dies for some reason - problem with the database, connection, whatever.
Use MySQL transactions, however, that is unlikely to help with
problems with the database CONNECTION. It does help with
errors such as the user name the user wanted is already taken
(and inserting the record fails due to a unique index), or
detected problems like a bank balance going negative.
You need to be using a high enough MySQL version and appropriate table
type to use transactions (e.g. MySQL 4.1 and InnoDB table type
works, although I believe there are some versions earlier than 4.1
which also work).
Obviously I can test the $result variable to see if it returns true or false, but given the case above, the database is now left in a "corrupt" state (assuming all 5 queries have to succeed in order for the database to be correct).
So, if I receive false for any of the results, is there an efficient way to "roll-back" the database to its original state? Specifically in the above example, is there a way to undo the first 3 queries?
Start a transaction, proceed with the queries. If you are satisfied
with the result, commit the result. If you are not, roll it back.
Assuming you have autocommit turned off (normal when using transactions),
if the connection breaks, the last (partial) transaction is rolled back.
I thought of setting flags in an array so that, for example, if query 4 dies, I can automatically undo queries 1 through 3 with new queries that would return the database to its original form, but if there is a problem that prevented query 4 from executing, I don't see how I can guarantee that I would be able to successfully execute new queries to undo the first 3.
Transactions will not protect you against things like bad disk sectors
on the MySQL server, network interruptions, or system crashes. It
does allow you to roll back partially-completed transactions due to
things like bad data entered, unique key violations, etc.
Gordon L. Burditt
Good Man wrote: yf***@vtn1.vict oria.tc.ca (Malcolm Dew-Jones) wrote in news:42******@n ews.victoria.tc .ca:
The mysql web pages had (probably still has) suggestions for how to best do this sort of thing in mysql, though it's all kludgey.
Kludgey? Not at all.
To the original poster, yes you can do this in MySQL. It is called using "transactio ns", and has been available from MySQL 4 onwards. All that is involved is using InnoDB tables instead of MyISAM tables.
http://dev.mysql.com/doc/mysql/en/an...nsactions.html
http://www.devarticles.com/c/a/MySQL...MySQL-4.0-and- PHP/
http://www.databasejournal.com/featu...le.php/3382171
Hello,
Thank you all for the replies. I have been reading up on InnoDB tables
per your suggestions, and it looks like this is exactly what I was
looking for.
I was wondering if anyone has had any experience with InnoDB tables and
if anyone knows if they are more efficient in terms of
storage/speed/security or any other major aspects than MyISAM. I
remember reading somewhere awhile ago that MyISAM was the most efficient
table type available to MySQL, but that was obviously before 4.1 onwards.
Lastly, I am running 4.1 and was just wondering if changing existing
table types to InnoDB would have any adverse affects, or if the DB will
rearrange all the data on disk without any complications. I just want
to be sure I don't mess up data on my live server by changing the structure.
Thank you again in advance.
On Thu, 21 Apr 2005 02:59:49 +0000, Marcus wrote: Good Man wrote: yf***@vtn1.vict oria.tc.ca (Malcolm Dew-Jones) wrote in news:42******@n ews.victoria.tc .ca:
The mysql web pages had (probably still has) suggestions for how to best do this sort of thing in mysql, though it's all kludgey.
Kludgey? Not at all.
To the original poster, yes you can do this in MySQL. It is called using "transactio ns", and has been available from MySQL 4 onwards. All that is involved is using InnoDB tables instead of MyISAM tables.
http://dev.mysql.com/doc/mysql/en/an...nsactions.html
http://www.devarticles.com/c/a/MySQL...MySQL-4.0-and- PHP/
http://www.databasejournal.com/featu...le.php/3382171
Hello,
Thank you all for the replies. I have been reading up on InnoDB tables per your suggestions, and it looks like this is exactly what I was looking for.
I was wondering if anyone has had any experience with InnoDB tables and if anyone knows if they are more efficient in terms of storage/speed/security or any other major aspects than MyISAM. I remember reading somewhere awhile ago that MyISAM was the most efficient table type available to MySQL, but that was obviously before 4.1 onwards.
Lastly, I am running 4.1 and was just wondering if changing existing table types to InnoDB would have any adverse affects, or if the DB will rearrange all the data on disk without any complications. I just want to be sure I don't mess up data on my live server by changing the structure.
Thank you again in advance.
They are more efficient in terms of they are capable of recovering from
failed transactions.
This comes at a cost
Table and row locks are held longer.
More potential for race conditions and deadlocking.
More IO/Memory required.
On Wed, 20 Apr 2005 21:24:50 -0500, Good Man <he***@letsgo.c om> wrote: To the original poster, yes you can do this in MySQL. It is called using "transactions" , and has been available from MySQL 4 onwards. All that is involved is using InnoDB tables instead of MyISAM tables.
It's been available since the later versions of 3.2, as well, in theory
anyway. 3.23.17 according to the manual.
--
Andy Hassall / <an**@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool
Andy Hassall wrote: To the original poster, yes you can do this in MySQL. It is called using "transactions ", and has been available from MySQL 4 onwards. All that is involved is using InnoDB tables instead of MyISAM tables.
Any way of converting a MyISAM tableto an InnoDB?
-- http://www.petezilla.co.uk
>>>To the original poster, yes you can do this in MySQL. It is called using "transaction s", and has been available from MySQL 4 onwards. All that is involved is using InnoDB tables instead of MyISAM tables.
Any way of converting a MyISAM tableto an InnoDB?
ALTER TABLE foo TYPE=InnoDB;
or
ALTER TABLE foo ENGINE=InnoDB;
Don't convert the tables in the 'mysql' database.
Gordon L. Burditt This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: DB2 Convert |
last post by:
Hi,
Correct me if I am wrong? Why should I specify at the restore
statement such as Restore Database ABC ... "WITHOUT ROLLING FOWARD"?
My understand is when I am using circular logging, automatically I
CAN'T do a rollforward. If I can't do rollforward due to the circular
logging, why should I
specify "WITHOUT ROLLING FORWARD"?
|
by: db2group88 |
last post by:
we are using db2 udb v8.2 on windows. All our tables are created with
"not logged initially" parameter. Our application with auto commit on.
I would like to do an online backup and rolling forward recovery, is
this allowed? because in the document, i read these: You cannot recover
these tables when rolling forward. If the rollforward operation
encounters a table that was created or altered with the NOT LOGGED
INITIALLY option, the table is...
|
by: david.monaghan |
last post by:
I have a table with the following fields - Location, Manager,
CostCentre, Month and Headcount. What I am trying to do is a monthly
rolling average headcount by Location, Manager, CostCentre. To verify
the calculations I am using sum rather then average and the query below
is showing the sum of all records in the month rather then breaking it
down by the Location, Manager, CostCentre combinations.
SELECT test.Loc, test., test.CC,...
|
by: Elijah Cardon |
last post by:
Let's say I have m dice having n sides, such that n^m is not going to bust
int as a datatype. With m=4 and n=6, an outcome might be {2, 5, 1, 2}.
What is a good way to represent this in c so that I can check cases by brute
force? EC
|
by: Jose Durazo |
last post by:
Hello, I'm doing an exercise to simulate rolling a pair of dice 36,000
times, then count and display how many times the simulation rolls each
possible sum.
For some reason each time I run my simulation, one or more of my sums is
zero, which is highly unlikely. So I'm sure there's a bug in my code. But
I can't tell what it is, even after re-reading it several times. Can
someone give me a hint?
Here is my code:
| |
by: BurtonBach |
last post by:
I have a small database that mostly keeps track of material quantities going into jobs we do. I have been able to make this work well for me. I now want to add the tracking of a rolling balance of one particular material that we keep a small amount in inventory. Is there a straight forward way to track a rolling balance in Access other than simply tracking the sums of the increases and decreases forever?
|
by: cricketweb |
last post by:
I have a stored procedure that calls another stored procedure with the
first stored procedure opening a transaction:
BEGIN
SET XACT_ABORT ON
BEGIN TRANSACTION
does various updates/inserts
|
by: brendan.wong |
last post by:
hello. i'm trying to incorporate error handling into my application,
but i've run into a dilemma.
i've already performed 10 successful INSERTS, but on the 11th INSERT,
the application fails for some reason (say for example, i tried to
perform an INSERT into a table that doesn't exist). logically, i
should stop execution and display some sort of error message. but,
i've already ran a bunch of INSERTS so what do i do? thanks
|
by: valentin tihomirov |
last post by:
Once file is open, it should be closed:
Stream fs = new FileStream(filename, FileMode.Create);
try {
// write, exceptions may raise here
} finally {
fs.Close();
}
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: 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...
| |