AW: Need help with an unexpected behaviour
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.bu...@pobox.com] Gesendet: Sonntag, 28. Januar 2018 14:44 An: Andreas Mock <andreas.m...@web.de> Cc: dbi-users@perl.org Betreff: Re: Need help with an unexpected behaviour 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 > ('looong')"); > 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.
Re: Need help with an unexpected behaviour
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 > ('looong')"); > 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.
Need help with an unexpected behaviour
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=XXX'; 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 ('looong')"); 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