in reply to Re^2: turning off Autocommit (MySql)
in thread turning off Autocommit (MySql)

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

Replies are listed 'Best First'.
Re^4: turning off Autocommit (MySql)
by jeanluca (Deacon) on Nov 11, 2005 at 15:21 UTC
    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]

      I currently don't have a MySQL database handy to test this with, but if I try this code out with Postgres it warns me that "Create database" cannot run within a transaction block. Try checking/setting Autocommit again after you're created and "used" the database. You could also try calling begin_work explicitly before you want your transaction to start.

      Also, you may want to check that xxx gets created as table type InnoDB (with "SHOW TABLE STATUS"), that depends on your MySQL configuration.


      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]
        Its a MyISAM table. I've never dealt with MyISAM or InnoDB, so is this wrong ? If yes, how do I create InnoDB tables ?

        Luca
[reply]

In Section Seekers of Perl Wisdom