in reply to turning off Autocommit (MySql)

From perldoc DBD::mysql:

o   Switching AutoCommit mode from on to off or vice versa may fail.  
+You should always check for errors, when changing AutoCommit mode.  T
+he suggested way of doing so is using the DBI flag RaiseError.  If yo
+u don't like RaiseError, you have to use code like the following:

$dbh->{'AutoCommit'} = 0;
if ($dbh->{'AutoCommit'}) {
# An error occurred!
}

[download]

I'd suggest you set RaiseError and see whether there's an error message which gives you a clue on what's happening.


Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan

Replies are listed 'Best First'.
Re^2: turning off Autocommit (MySql)
by jeanluca (Deacon) on Nov 11, 2005 at 13:05 UTC
    Code snippet:
    $dbh = DBI->connect($dbc, $root, $passwd, {AutoCommit => 0, RaiseError
+ => 1}) ;
$dbh->{'AutoCommit'}
$dbh->{'AutoCommit'} = 0;
if ($dbh->{'AutoCommit'}) {
     print "An error occurred!\n" ;
}
print "status=".$dbh->{'AutoCommit'}."\n" ;

    [download]
    Output is:status=
    And it didn't rollback the creation of a database....
    How can I check if this options is enabled by MySql (version is 4.1.14) ?

    Luca
[reply]
[d/l]

      Your output looks fine, AutoCommit is turned off (otherwise you'd get "An error occurred" output as well as a 1). I don't think database creations can be rolled back though. If alone for the fact that transaction support in MySQL depends on the table type of the database (InnoDB supports transactions, MyISAM doesn't). Try issuing insert/select statements and rolling them back explicitly (or just disconnect without commit) and see what happens.


      Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan
[reply]
        Here is a test prog I wrote:
        #! /usr/bin/perl

use DBI ;

$root = "root" ;
$dbc = "dbi:mysql::localhost" ;
$dbh = DBI->connect($dbc, $root, $passwd, {AutoCommit => 0, RaiseError
+ => 1}) ;
$dbh->{'AutoCommit'} = 0 ;


$dbh->do("CREATE DATABASE xxx") ;
$dbh->do("USE xxx") ;
$dbh->do("CREATE TABLE test (str VARCHAR(50))") ;

$str = "INSERT INTO test SET str='hello'" ;
$sth = $dbh->prepare($str) ;
$r   = $sth->execute() ;

if (! $r) {
    print "could not insert\n" ;
}
else {
    print "ok\n" ;
    $dbh->rollback() ;
}
$dbh->disconnect() ;

        [download]
        But somehow it didn't work, the row is inserted anyway
        Any suggestions ?

        Luca
[reply]
[d/l]

In Section Seekers of Perl Wisdom