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?