Need help with an unexpected behaviour

[Andreas Mock]

Hi all,

today I stumpled on a behaviour that I didn't expect
this way. And currently I haven't found an explanation.
That's the reason why I want to ask you whether you can
give me the right hints.

Environment:
SL Linux 6.7 64bit
MySQL Community Server 5.5.40
perl 5.10.1
DBI 1.633
DBD::mysql 4.038

The smallest programm I can replicate the problem is
that:
======================8<===============================
1 #!/usr/bin/perl
2 use 5.010;
3 use strict;
4 use warnings;
5
6 use utf8;
7 use DBI;
8
9 my %attr = (
10 'AutoCommit' => 1,
11 'RaiseError' => 1,
12 'FetchHashKeyName' => 'NAME_lc',
13 'mysql_enable_utf8' => 1,
14 );
15
16 my $dsn = 'dbi:mysql:database=XXXXXXX';
17 my $user = 'USERUSER';
18 my $password = 'PASSWORD';
19 my $dbh = DBI->connect($dsn, $user, $password, \%attr);
20
21
22 # Prepare
23 $dbh->do("drop table if exists mca_rb_test");
24 $dbh->do("create table mca_rb_test (
25 name varchar(20) not null primary key
26 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"
27 );
28
29 doit($dbh);
30
31 $dbh->disconnect;
32 say "Ende";
33
34
35 sub doit {
36 my $dbh = shift;
37
38 local $dbh->{'AutoCommit'} = 1;
39
40 $dbh->begin_work;
41 $dbh->do("insert into mca_rb_test values ('short')");
42 $dbh->do("insert into mca_rb_test values
('looooooooooooooooooooooooooooooooooooooooooooooooooong')");
43 $dbh->commit;
44 say "Commit done";
45 return;
46 }

======================8<===============================

Whithout line 38 I get what I expect. The transaction is opened
with begin_work, the first insert statement is done, the second
statement raises an exception which is not catched, the DESTROY
function issues as rollback against the database. After that
I don't see any record in the table.

BUT: As soon as I have line 38 in there, which shouldn't change
the initially set 'AutoCommit', the first insert is commited
to the database even the exeption is raised in the opened
transaction.

Can someone explain what is happening behind the scenes or
give a pointer to some helpful documentation which I have
overlooked?


Best regards
Andreas

[Tim Bunce]

On Fri, Jan 26, 2018 at 04:47:35PM +0100, Andreas Mock wrote:
> Hi all,

>
> 9 my %attr = (
> 10 'AutoCommit' => 1,
> 11 'RaiseError' => 1,

> 35 sub doit {
> 36 my $dbh = shift;
> 37
> 38 local $dbh->{'AutoCommit'} = 1;
> 39
> 40 $dbh->begin_work;
> 41 $dbh->do("insert into mca_rb_test values ('short')");
> 42 $dbh->do("insert into mca_rb_test values
> ('looooooooooooooooooooooooooooooooooooooooooooooooooong')");
> 43 $dbh->commit;

> Whithout line 38 I get what I expect. [...]

> BUT: As soon as I have line 38 in there, which shouldn't change
> the initially set 'AutoCommit', the first insert is commited
> to the database even the exeption is raised in the opened
> transaction.

That seems like a driver bug at first sight.

While "local $dbh->{'AutoCommit'} = 1;" looks like a simple
hash assignment there's a lot going on behind the scenes.
($dbh is a ref to a tied hash so a STORE method gets called
to handle the assignment.)

While it doesn't "change" the AutoCommit setting, since it's already
enabled, the "local" does cause Perl to arrange to execute
$dbh->{'AutoCommit'} = 1 when the scope exits.

Both the initial assignment and the re-setting assignment may have side
effects.

> Can someone explain what is happening behind the scenes or
> give a pointer to some helpful documentation which I have overlooked?

The DBI (and most drivers) have extensive tracing built in.
The trace output is often very helpful to see what's really happening.
See https://metacpan.org/pod/DBI#TRACING

It'll show you the effect of the local AutoCommit assignment and
re-setting assignment at scope-exit.

Tim.

[Andreas Mock]

Hi Tim,

thank you for your answer. Besides of the explanation given,
do you think someone should expect this behaviour?

Is it something that should be explained and warned about
explicitly?

Best regards
Andreas


-----Ursprüngliche Nachricht-----
Von: Tim Bunce [mailto:Tim....@pobox.com]
Gesendet: Sonntag, 28. Januar 2018 14:44
An: Andreas Mock <andrea...@web.de>
Cc: dbi-...@perl.org
Betreff: Re: Need help with an unexpected behaviour