Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

DBI $dbh->rollback() NOT rolling back???

by puterboy (Scribe)
on Dec 20, 2010 at 16:34 UTC ( #878049=perlquestion: print w/ replies, xml ) Need Help??
puterboy has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to use $dbh->rollback to roll back changes in a MySQL database but it is not working.... No matter what I do, it always commits whether or not I issue a $dbh->commit() or a $dbh->rollback().

NOTE: I have $dbh->{AutoCommit}=0 (and I KNOW it is set in the right place because if I change it to '1' then I get the expected error that rollback is ineffective.

Here is sample code that fails:
$dbh->{AutoCommit} = 0; $sth = $dbh->prepare("UPDATE $table SET title = 'new title' WHERE key1 + = ? AND key2= ?"); $sth->execute($key1, $key2); $dbh->rollback(); }

The code above always "COMMITS" and doesn't rollback. I.e., the title field is always changed for the selected record. What could be blocking the rollback from actually taking effect?

Comment on DBI $dbh->rollback() NOT rolling back???
Download Code
Re: DBI $dbh->rollback() NOT rolling back???
by runrig (Abbot) on Dec 20, 2010 at 16:42 UTC
    See the CREATE TABLE docs for MySQL. You need to tell MySQL that you want to use the InnoDB engine when you create the table. begin_work/commit/rollback silently do nothing otherwise. To convert the table, you can: ALTER TABLE t1 ENGINE=InnoDB;. Yeah, I was bitten by this also.

      WOW - thanks!

      Why doesn't this signal an error in the same way that trying to rollback when you have AutoCommit set signals that the rollback is ineffective.

      i.e., why does the rollback command essentially fail silently? Theoretically, even if I create the database right, someone could change it and I would never know that my rollbacks stopped working? Also, I skimmed through lots of official manpages, docs, and examples of rollback but this 'fatal' error was not mentioned.

        You can have a mixture of MyISAM and InnoDB tables in your database, so transactions will only apply to the InnoDB tables. Don't call it "failure", call it a MySQL "feature" :-) (BTW, DBI has nothing to do with it).

        Update: I sense a misunderstanding of the relationship between DBI and the underlying database. DBI "knows" when you have the AutoCommit attribute set or not, so it can warn you when you do commits and rollbacks with AutoCommit unset. DBI doesn't "know" which of your MySQL tables are using the MyISAM or InnoDB engines on every SQL statement, so it can't warn you about that (the DBD::mysql library, with a bunch of extra work, might be made to do that, but it's really too much bother).

      Saved me from my hair pulling, thanks!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://878049]
Approved by Corion
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (3)
As of 2014-08-01 04:26 GMT
Find Nodes?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:

    Results (256 votes), past polls