Solved: PHP PDO with MySQL set transaction

[andynic]

HI,

Please keep in mind that I'm new to PDO.

I've developed the following method to execute a series of SQL commands in a single transaction. If any one of the commands fails, the transaction should rollback. However, the results that I am getting show that if the last command in the transaction fails, the other three are committed rather than rolled back.

The method:

  public function execXaction ($frmFct, $dbObj, $sqlCmdArr) {
    $fctNm = "execXaction";

    $dbHndl = $dbObj->dbConnect($fctNm);
 //   $dbHndl->exec('SET AUTOCOMMIT=0'); // tried with and without this.  Makes no difference
    $dbHndl->beginTransaction(); 
 
    for ($i=0; $i<sizeOf($sqlCmdArr); $i++) {
      if ( $i % 2 == 0 ) { 
        $sqlCmd  = $sqlCmdArr["sqlCmd" . $i/2];
        $bindArr = $sqlCmdArr["bindArr" . $i/2];

        try {
          $sqlCmdHndl = $dbHndl->prepare($sqlCmd);
          $sqlCmdHndl->execute($bindArr);
        }
        catch(PDOException $excptHndl) {
           $dbHndl->rollback();
           $msgObj = new msgClass();
           $msgObj->rptExcptDtls ($fctNm, $frmFct, "ERR: Failed to execute sqlCmd = $sqlCmd<br />with bindArr = <pre>" . print_r($bindArr) . "</pre><br />"  , $excptHndl);
          unset($msgObj);
        } 
    } // End if
    } // End for

    $dbHndl->commit();
  } // End execXaction

This is the calling test code:

    $sqlCmdArr = array("sqlCmd0"=>"insert into x1 (c1, c2) values (:v0, :v1)",  
                       'bindArr0'=>array(':v0' => 10,
                                           ':v1' => 'c1 is 10'),

                       "sqlCmd1"=>"update x1 set c2 = 'x-action test' where c1 = :v0", 
                       'bindArr1'=>array(':v0' => 1),
                        
                       "sqlCmd2"=>"update x1 set c2 = 'x-action test2' where c1 = :v0", 
                       'bindArr2'=>array(':v0' => 0),

                       "sqlCmd3"=>"update x1xxxx set c2 = 'make it all fail' where c1 = :v0", 
                       'bindArr3'=>array(':v0' => 0)
                      );
    $dbObj = new dbClass();
    $dbObj->execXaction($fctNm, $dbObj, $sqlCmdArr);

This is the starting state of table X1, before the code executes:
mysql> select * from x1;
+------+---------+
| c1 | c2 |
+------+---------+
| 0 | c1 is 0 |
| 1 | c1 is 1 |
+------+---------+
2 rows in set (0.00 sec)

This is the state after the code has failed and returned the error message just below:
(If the code were working correctly, this state should be the same as the original. So it looks as if autocommit is still enabled.)
mysql> select * from x1;
+------+----------------+
| c1 | c2 |
+------+----------------+
| 0 | x-action test2 |
| 1 | x-action test |
| 10 | c1 is 10 |
+------+----------------+
3 rows in set (0.00 sec)

The error message returned by the catch clause:
PHP EXCEPTION:
Script..........: testDbCmds.php
Functie.........: execXaction
Geroepen door...: main
Meelding........: ERR: Failed to execute sqlCmd = update x1xxxx set c2 = 'make it all fail' where c1 = :v0
with bindArr = 1
Exception.......: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'tobivroegh_tobi_live.x1xxxx' doesn't exist

Can anyone see what I might be doing wrong?
Thanks for your help.
Andynic

 

[Ent]

I've only touched on PDO, so I don't know whether you're doing it right.
The one thing that does strike me is you have rollback where the docs have rollBack (capital B). That could certainly cause issues.

 

[andynic]

Hi Ent,
Thanks for the reply.

I tried your suggestion, but whether it's rollback or rollBack does not seem to matter.

I've re-read the PHP manual on the subject and also some tutorials and have made a very simple example without arrays (please see below). I think it is 100% according to Hoyle. But either the 100% is wishful thinking, or there is a parameter somewhere that needs to be set differently. In that vain I've checked php.ini and searched around for mysql configuration parameters but can find nothing that seems relevant.

<?php
 session_start(); 
 $GLOBALS['PROGRAM_NAME'] = "testDbCmds.php";
 $fctNm = "main";
        
   try {
         $dbHndl = new PDO("mysql:host=localhost; dbname=tobidb; charset=utf8", "tobi", "pw");
         $dbHndl->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         $dbHndl->beginTransaction();

         $sqlCmd  = "insert into x1 (c1, c2) values (:v0, :v1)";
         $cmdHndl = $dbHndl->prepare($sqlCmd);
         $cmdHndl->bindValue(":v0", 10);
         $cmdHndl->bindValue(":v1", 'c1 is 10');
         $cmdHndl->execute();
         
         $sqlCmd  = "update x1 set c2 = 'x-action test' where c1 = :v0";
         $cmdHndl = $dbHndl->prepare($sqlCmd);
         $cmdHndl->bindValue(":v0", 0);
         $cmdHndl->execute();
 
         $sqlCmd  = "update x1 set c2 = 'x-action test2' where c1 = :v0";
         $cmdHndl = $dbHndl->prepare($sqlCmd);
         $cmdHndl->bindValue(":v0", 1);
         $cmdHndl->execute();
                       
         $sqlCmd  = "update x1xxxx set c2 = 'make it all fail' where c1 = :v0";
         $cmdHndl = $dbHndl->prepare($sqlCmd);
         $cmdHndl->bindValue(":v0", 0);
         $cmdHndl->execute();
          
         $dbHndl->commit();
   }
   catch(PDOException $excptHndl) {
          $dbHndl->rollBack();  // ik  heb ook rollback() geprobeerd
          echo "ERR: Failed to execute sqlCmd = $sqlCmd<br />";
          echo "Exc msg:  " . $excptHndl->getMessage() . "<br />";
          echo "In file:  " . $excptHndl->getFile() . "<br />";
          echo "At liine: " . $excptHndl->getLine() . "<br />";          
   } 
?>

DB commands used from the mysql command line interface:
create table x1 (c1 integer, c2 varchar(30));
insert into x1 (c1, c2) values (0, 'c1 is 0');
insert into x1 (c1, c2) values (1, 'c1 is 1');

Thanks for your help.
Andynic

 

[Ent]

Since rolling back is an ability of the database engine and not of PHP, I can't see how variations in the precise PHP should make a difference. I'm afraid I don't have time to look at it in the detail it needs right now.
(That said, there are some things that would make your original code easier to understand and possibly less error prone. The whole index by halfs thing, and indeed concatenating to make a string index, is kind of messy. Passing two arrays, or an array of 2-tuples/arrays, would be cleaner and less abuse of PHPy.)

I assume you've checked, as per the many comments in the doc, that it's not a MyISAM table.

 

[andynic]

Hi Ent,

Thanks again for the reply. You hit the nail on the head. I did not realize that the default engine is MyISAM.
Explicitly specifying the engine in the create table command solved it:
create table x1 (c1 integer, c2 varchar(30)) ENGINE=InnoDB;

Andynic