PDO always autocommits even when beginTransaction() ??? wtf!

[alexweber15]

learning about transactions, prepared queries and whatnot but i cant get transactions to work...

 

$test = new PDO(...);
$test->beginTransaction();

$query[] = 'INSERT x';
$query[] = 'INSERT y';
$query[] = 'INSERT z';

foreach($query as $statement){
    $test->exec($statement);
}

$test->commit();

 

thats what i wanna do... but it just executed the statements directly without waiting for commit() to be called...

 

what am i doing wrong??

 

also it doesnt return any errors or nothing, just stops silently whenever an exec statement fails... is there any way to find which statement failed?

 

i tried:

$test->exec($statement) or die(var_dump($test->errorInfo()));

 

but it just printed an empty error array after the first insert and if i removed the "or die" it then worked normally...

 

im kinda confused!! (using mysql btw)

 

thanks

 

Alex

[aschk]

I believe MySQL has auto commit on by default.

Also, your exec() queries will fail here (because it isn't valid SQL).

 

So performing die() if it fails means that your whole PHP script will terminate.

[aschk]

Might be an idea to use exceptions:

<?php
try {
  $query[] = 'INSERT x';
  $query[] = 'INSERT y';
  $query[] = 'INSERT z';

  $dbh = new PDO(...);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $dbh->beginTransaction();

  foreach($query as $statement){
    $dbh->exec($statement);
  }

  $dbh->commit();

} catch(PDOException $e){
  $dbh->rollback();
  echo $sql . '<br />' . $e->getMessage();
}
?>

 

Just info i just came across:

A PDO transaction begins with the with PDO::beginTransaction() method. This method turns off auto-commit and any database statements or queries are not committed to the database until the transaction is committed with PDO::commit. When PDO::commit is called, all statements/queries are enacted and the database connection is returned to auto-commit mode.

[alexweber15]

thanks im gonna try the exceptions for error-checking...

 

but what about the fact that it autocommits anyway?  yeah mysql has it on by default but according to the docs if you explicitly call PDO::beginTransaction() it will turn off autocommitting until PDO::commit() is called.

 

in this case even without the commit() at the end it still commits them on-the-fly as in if i kill the script right after the foreach (or even in the middle of it) it will have created the tables without me having to call commit()....

 

??? ???

[aschk]

It's an interesting issue. I would look closer at the MySQL setup you have. Technically calling BeginTransaction() turns autocommit off.

One thing has occured to me however, what table type are you using? InnoDB, MyISAM?

[alexweber15]

It's an interesting issue. I would look closer at the MySQL setup you have. Technically calling BeginTransaction() turns autocommit off.

One thing has occured to me however, what table type are you using? InnoDB, MyISAM?

 

InnoDB cause of the FKs...hmm never occurred to me but afaik its actually MyISAM that can't disable autocommitting...