rolling back a database

[Marcus]

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.

[Malcolm Dew-Jones]

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.

[Good Man]

yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in
news:42******@news.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
"transactions", 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

[Gordon Burditt]

>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

[Marcus]

Good Man wrote:

yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in
news:42******@news.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
"transactions", 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.

[Malcolm Dew-Jones]

Good Man (he***@letsgo.com) wrote:
: yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in
: news:42******@news.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
: "transactions", 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
Well, by bad.

A welcome addition to mysql.
--

This space not for rent.

[CJ Llewellyn]

On Thu, 21 Apr 2005 02:59:49 +0000, Marcus wrote:

Good Man wrote:

yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote in
news:42******@news.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
"transactions", 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.

[Andy Hassall]

On Wed, 20 Apr 2005 21:24:50 -0500, Good Man <he***@letsgo.com> 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.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Peter Chant]

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

[Gordon Burditt]

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

ALTER TABLE foo TYPE=InnoDB;
or
ALTER TABLE foo ENGINE=InnoDB;

Don't convert the tables in the 'mysql' database.

Gordon L. Burditt