I'm now crossposting to msql-mysql-modules since that might be more
appropriate.
At 9:13 Uhr -0600 22.12.2001, Philip Molter wrote:
>On Sat, Dec 22, 2001 at 03:16:45PM +0100, Christian Jaeger wrote:
>: Hello
>:
>: It seems there is a feature in DBD::mysql (when used with InnoDB)
>: that get's in the way in some circumstances:
>:
>: We are doing some code like the following (in a mod_perl environment)
>: with $dbh being opened with RaiseError and Autocommit=0:
>
>We run MySQL-InnoDB in the same type of environment. We've found
>that a better way is to only turn AutoCommit off when you need it
>turned off (using $dbh->{'AutoCommit'} = 0|1). That way, you don't
>have to issue the initial commit().
Well I've now actually tried out ->begin_work, but this gives another
problem (I consider it a bug):
#!/usr/bin/perl -w
use strict;
use DBI;
sub HALT {
print "<enter> please..\n"; <STDIN>;
}
my $DB= DBI->connect("DBI:mysql:test", "", "", { RaiseError => 1});
#$DB->do("create table concurrencytest (a int not null primary key, b
int) type=innodb");
my $i=$DB->prepare("insert into concurrencytest values(?,?)");
$DB->begin_work;
$i->execute(111,1);
HALT; # pls restart mysql here!
$i->execute(112,1);
$DB->rollback;
__END__
Now 'select * from concurrencytest' and you will get:
+-----+------+
| a | b |
+-----+------+
| 112 | 1 |
+-----+------+
This means that DBD/DBI reconnect silently without realizing that
there is a transaction going on. That's bad! It should throw an
exception instead so we can redo what we have begun.
(When using AutoCommit=0, commit will give an exception after a
reconnect so everything is fine except for the problem which lead me
to my first mail - I'm putting it here again for those on the
msql-mysql-list:
>We are doing some code like the following (in a mod_perl
>environment) with $dbh being opened with RaiseError and Autocommit=0:
>
>$dbh->commit; # get to the latest snapshot of the data
>$sth->execute some select statement
>
>If the database connection has been closed because of a timeout, the
>commit will give an exception because of "mysql has gone away".
>
>I'm used to the behaviour (with non-transaction tables in mysql)
>that dbi/DBD::mysql reopens database connections transparently. This
>is not the case anymore, since now in some cases there will be an
>exception in others not. And I really didn't expect an exception in
>the above.
>
>I guess the above behaviour is because of the possibility of a case
>where you issue some statements without committing them, then the
>connection goes away, then you issue commit (maybe after another few
>statements), this would give problems.
>
>But dbi/dbd could handle my situation differently: if (and only if)
>the connection did go away *between two commits*, there is no reason
>to give an error. (To rephrase: If I issue some data modifying
>statements, do a commit (which succeeds), then the connection goes
>away, and I do another commit (for the sole purpose to get a recent
>snapshot), don't give an error since I won't expect it and won't
>know what to do with it.)
>
>Unless I'm missing something I would put this as a feature request. :)
)
Thanks,
Christian.
BTW I've played with the idea of subclassing DBI/DBI::db/DBI::st and
implementing the above thins myself by overriding all do/execute/et
al calls. But I guess it will be very slow since the object is a tied
hash and so putting my own flags in there will add even more
overhead. Out of curiosity: is there any reason a tied hash approach
for storing attributes has been choosen and not just accessor methods?
--
Christian Jaeger Programmer & System Engineer +41 1 430 45 26
ETHLife CMS Project - warehouse.ch/ethlife/www - www.ethlife.ethz.ch
