Hi Monks,

I think I'm missing something in the code below which involves operations on a number of tables. I'm trying to make use of rollback to restore all the affected tables to the pre-operation state should any of the operations fail.

I tried to test whether rollback works by tweaking the last_id. The operation to insert $last_id into preference should fail as a result of a foreign key constraint. That works (i.e. when I changed the last_id to an arbitrary higher number, the insert operation on preference failed and so none of the previous operations was effected.)

After that tweaking test, I commented out $last_id (so the code is back to normal) and called the new_account subroutine. Strangely, no change was effeced in any of the tables.

I found out I the changes can only be effected if I shut down the MySQL server and restart it.

Update 1

Thanks to pg! As he advised, I moved commit out of the eval block and now after I removed the last_id tweak, the code works normally :)

Update 2

I realised I can't have AUTO_COMMIT with rollback (the error said: Rollback ineffective while AutoCommit is on). The problem was that with AUTO_COMMIT, when the last_id was tweaked, the table members was erroneously updated. With that error message, the solution now is to disable AUTO_COMMENT as an attribute of members.

Update 3

I moved commit to after eval and so far so good.

#Platforms: Windows 98
#Perl version: 5.8.0
#MySQL version: 4.0.16

sub create_account {
   my ($username, $level) = @_;
   $dbh->{RaiseError} = 1;
   $dbh->begin_work;
 
   eval {
   ## (1) Insert username into members (TYPE=INNODB)
   $sth = $dbh->prepare(qq{
    INSERT INTO members VALUES(?, ?, ?) });
   $sth->execute(undef,$username,$level);

   ## (2) Select from members to get last id
   $sql = qq{ SELECT LAST_INSERT_ID() FROM members };
   $sth = $dbh->prepare($sql);
   my $last_id = $sth->execute();

   # Tweaking $last_id to test rollback
   #$last_id = 5;

   ## (3) Insert last_id into preference  (TYPE=INNODB)
   $sth = $dbh->prepare(qq{
    INSERT INTO preference VALUES(?, ?, ?) });
   $sth->execute($last_id,undef,undef);

   }; # eval
   if ($@) {
    eval { 
          $dbh->rollback();
          # Call bail_out and die
          bail_out("Error at new_accout.");
        };
   }
   $dbh->commit();
   $dbh->{RaiseError} = 0;
}
## Definitions of tables
DROP TABLE IF EXISTS members;
CREATE TABLE members (
    member_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NULL,
    level CHAR(2) NULL,
) TYPE=INNODB;

DROP TABLE IF EXISTS preference;
CREATE TABLE preference (
    member_id MEDIUMINT UNSIGNED NOT NULL,
    pref1 VARCHAR(20) NOT NULL,
    pref2 CHAR(10) NOT NULL,
    PRIMARY KEY (member_id, pref1),
    FOREIGN KEY (member_id) REFERENCES members (member_id) ON DELETE C
+ASCADE,
    UNIQUE (pref1)
) TYPE=INNODB;
[download]

Could someone enlighten me?

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.