On Tue, Dec 25, 2001 at 03:26:49AM +0100, Christian Jaeger wrote:
> 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.
I agree. It's a serious bug.
> (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. :)
I have something similar, with the same intent, in my to-do list.
> 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?
There will be hundreds of attributes before too long (ala ODBC GetInfo()).
Tim.
