Autocommit with PDO

Question

The rollback of my transaction doesn't work. How do I set autocommit to false (or 0) in the php script using PDO (I have InnoDB 5.7.18) ?

Here is my code:

     global $bdd;  //defined with PDO

       try {                             
            $bdd->beginTransaction();
            /* my requests */
            $bdd->commit();
        } catch (Exception $e) {
            $bdd->rollBack();
            return $e->getMessage();
        }

        return true;
    }

Bloater

PDO::beginTransaction () method disables auto-commit mode. – Decalcomania

Answer 1

I solved my problem myself: a few of my tables were in MyISAM (whereas the majority are in InnoDB -> I work with an old database system...); so the rollback didn't work for these tables. Once I changed them into InnoDB, it worked. Thanks to everybody for the help !

Bloater

Answer 2

 $db = new PDO('mysql:dbname=employee');
 $db->setAttribute(PDO::ATTR_AUTOCOMMIT,0);
 var_dump($db->query('SELECT @@autocommit')->fetchAll());

Kilburn

thanks for the answer; the var_dump gave me that: "array(1) { [0]=> array(2) { ["@@autocommit"]=> string(1) "0" [0]=> string(1) "0" } }", but the autocommit is still to "ON" when I enter in mysql "SHOW VARIABLES WHERE Variable_name='autocommit';" .... and the rollback still not work... :( – Bloater

Make sure, Database definition language (DDL) statement such as DROP TABLE or CREATE TABLE, in MySQL, cannot be used within transactions. – Kilburn

Have You tried - $dbh->setAttribute( PDO::ATTR_AUTOCOMMIT, 0 ); $dbh->beginTransaction(); – Kilburn

yes I have tried this, and I only use UPDATE requests in my transaction... :( – Bloater

Answer 3

Try setting the following attribute:

$bdd->setAttribute(PDO::ATTR_AUTOCOMMIT,0);

Marita

Were you set this attribute? try to set it when you opening a connection with database – Marita

It doesn't work either... but thank you for your help ! – Bloater