Developer Forum

Hi,

I'm using MySQL 5.0. I'm trying to write a script where if an error
occurs at any point in the script I do not want any of the previous
statements before the error to be committed, but if the script runs
all the way through, I would like to commit everything. I'm not using
any other program (e.g. PHP) to execute the MySQL script. Given this,
how could I achieve the above?

Thanks, - Dave

Put BEGIN; at the top and COMMIT; at the end.

Thanks. I've tried this but even if a statement fails somewhere in
between, the previous statements are getting saved, prompting my
question.

Which engine are you using?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================

InnoDB

- Dave

Ok, do you have autocommit turned off (default is usually on)?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================

That shouldn't make any difference. The whole point of using
transactions is to implement ACID where either everything goes in
or nothing goes in. Any sort of autocommit that violates this
rule is a serious bug.

I thought perhaps using "start transaction" instead of "begin"
might prevent it from mistaking it for the "begin" that goes
with "begin ... end", but that sort of confusion would also be
a bug. I would prefer "start transaction" in any event, because
it's clearer and more obvious for human readers.

The OP should tell us a little more about what he's attempting to
do that's causing this behavior.

For example, he mentions using a "script". What kind of script,
a shell script? In this script, is there more than one database
connection? Generally a transaction must be contained within a
single connection.

What kind of error is occurring? A database error? If it's some
other error outside of the database, it is beyond the control of
the database engine. There is no way it can know to rollback a
transaction unless it hits a database error.

\:-\

Not at all. It makes all the difference in the world. Autocommit() is
designed to automatically perform a COMMIT after every request to change
the database, so you don't have to.


That's doesn't change how AUTOCOMMIT works.



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================

I should have mentioned:

If indeed it is a non-database error inside your script, you
can send MySQL the "rollback" statement to throw away all updates
up to the beginning of the transaction.

But if it's a database error, it should rollback automatically.

/:-/

According to:
http://www.yqcomputer.com/

Quote:
To disable autocommit mode for a single series of statements, use the
START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the
transaction with COMMIT or ROLLBACK. The autocommit mode then reverts
to its previous state.
:End quote

This is exactly how I would expect it to behave. It makes
no practical sense to use "start transaction" AND turn off
autocommit in two separate statements when the *semantics*
behind starting a transaction is to commit when you tell it.

I'll say again; if this isn't what the database is doing,
it's a serious bug.

\:-\

Then I would recommend you report it as such.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================

I just checked on mine. The table has to be created with
"engine=innodb" for transactions to work correctly. And,
after a careful reading of the manual, it (more or less)
says just that.

Otherwise, the "start transaction" and "rollback" have
absolutely no effect whatsoever. And I'm not 100% sure
whether "commit" does anything either; I have yet to get
an update to a table with the default engine to *not*
commit.

I'm sorry, I'm just not used to this. My background is
with Sybase and Oracle databases, and they don't give you
the option to arbitrarily break the standard database
rules if you feel like it. Transactions ALWAYS work the
way they're supposed to.

That's the next question for the OP; how are your tables
being created? If InnoDB, then using the start transaction,
rollback and commit statements should work as advertised.
If not InnoDB, convert them.

/:-/

Well, I just reviewed the thread, and the OP says he is
using InnoDB.

So.

The transactions work on mine. Double check with the
"show create table {tablename}" statement. If indeed all
of your tables are InnoDB, and the transactions still don't
work for you, then you have some other issue going on.

/:-/

hanksButNo wrote:


I agree with ThanksButNo -- A change transaction is not just a single
statement (insert update or delete), but the way most web-based apps
seem to work, it appears to work that way - especially if they use
AUTOCOMMIT. The problem has always been that because of this, if
someone elects to bail out of the transaction, you need cleanup
procedures to remove the garbage data.

Maybe now that Oracle is buying Sun and therefor MySQL, maybe the real
database engine developers can fix this stuff...

Hi Dave,

A couple of things:

1. A DDL statement will implicitly commit any transactions, even if
you are not in autocommit mode, or have begun a transaction. As such,
you won't be able to roll-back those statements, since they are non-
transactional.
2. If you have a script that does Insert, Update, and Delete
operations, and you are using a transactional storage engine (e.g.,
InnoDB) then you have the option of putting a 'BEGIN' before the group
of Insert/Update/Delete statements that should occur as a group, and a
commit at the end. If an error occurs in a transaction, all
subsequent statements will be ignored until a COMMIT or ROLLBACK, and
a COMMIT will be treated as a ROLLBACK in such cases.
3. Transactions aren't broken in MySQL (as has been implied in this
thread), it's just that they only work with transactional storage
engines, and you need to understand that DDL is non-transactional (in
general).

I suspect the issue you have is either related to you not using a
transactional storage engine, or the fact that you have DDL in your
script, which is not transactional, and will cause an implicit
commit. This would include statements like CREATE TABLE, ALTER TABLE,
CREATE INDEX, etc.

Hope that helps.

Chander Ganesan
Open Technology Group, Inc < http://www.yqcomputer.com/ >
Ask me about MySQL, PHP, and Python training delivered worldwide!