Page 1 of 1

MySqli Rollback under xampp does not work.

Posted: Tue Nov 04, 2014 9:51 am
by bowlesj
Hi, I guess I am stuck on this one.

I am still on local host and trying to get a test of a rollback to work and it keeps doing the update (won't roll back). I am not going to say much except that everything I have tried is in this code (exactly as is). I have commented out some of my earlier attempts. I think I have tried every combination of turning off auto commit and leaving it alone. As you can see, I even tried removing the test update code from the functions to make it all main line. I tested two of the commands for errors but mysqli is not issuing any errors at all - everything I have tried it likes. The only thing I can figure is maybe xampp needs a parameter to make this work.

Can anyone see anything?

Thanks,
John

Code: Select all

<?php
Include 'Mod_session_start.php';
Include 'Mod_Connect_DB.php';
$ResultMessage = "";
$AnyErrors = "N";

//============================================== funcUpdateRecord($RecKey)
function funcUpdateRecord($RecKey){
   global $con;
   global $AnyErrors;
   global $ResultMessage;
   $sql = "
      UPDATE
         tblMemberMaster
      SET
         fldMM_MiddleName='XXX'
      WHERE
         fldMM_Key='$RecKey';
   ";
   if (!@mysqli_query($con, $sql)) {
      $AnyErrors = "Y";
      $ResultMessage = "Error updating member file." . mysqli_error($con);
   }
} //funcUpdateRecord


//============================================== Main Line Starts

//START: attempts at turning off auto commit
//mysqli_query($con, "SET AUTOCOMMIT=0");
//if (!mysqli_autocommit($con, FALSE)) {
//   echo "failed";
//   die;
//}


//START: attempts at beginning the transaction
mysqli_query($con, "START TRANSACTION");
//mysqli_query($con, "BEGIN");
//if (!mysqli_begin_transaction ($con)) {
//   echo "failed mysqli_begin_transaction";
//   die;
//}


//START: two test updates to roll back
//funcUpdateRecord(1); //change John's middle name
//funcUpdateRecord(2); //change Campbell' smiddle name

   $sql = "
      UPDATE
         tblMemberMaster
      SET
         fldMM_MiddleName='XXX'
      WHERE
         fldMM_Key='1';
   ";
   if (!@mysqli_query($con, $sql)) {
      $AnyErrors = "Y";
      $ResultMessage = "Error updating member file." . mysqli_error($con);
   }
   $sql = "
      UPDATE
         tblMemberMaster
      SET
         fldMM_MiddleName='XXX'
      WHERE
         fldMM_Key='2';
   ";
   if (!@mysqli_query($con, $sql)) {
      $AnyErrors = "Y";
      $ResultMessage = "Error updating member file." . mysqli_error($con);
   }
//END: two test updates to roll back


$AnyErrors = "Y"; //Force a rollback

if ($AnyErrors == "N") {
   //mysqli_query($con, "COMMIT");
   mysqli_commit($con);
   $ResultMessage = "Commit";
} else {        
   //mysqli_query($con, "ROLLBACK");
   if (!mysqli_rollback($con)) {
      echo "failed mysqli_rollback";
      die;
   }
   $ResultMessage = "Rollback";
}

//NOTE: There is a text box on the page showing the ResultMessage and it displays "Rollback" but the file is still getting changed.

?>

Re: MySqli Rollback under xampp does not work.

Posted: Tue Nov 04, 2014 10:10 am
by mikosiko
Which storage engine are you using for the table?... Innodb or Myisam?

MyIsam is nontransactional.

https://dev.mysql.com/doc/refman/5.5/en ... tions.html

Re: MySqli Rollback under xampp does not work.

Posted: Tue Nov 04, 2014 10:48 am
by bowlesj
Thanks mikosiko, you answered it. Myisam is being used. Is there a way to convert MyIsam?
John

Re: MySqli Rollback under xampp does not work.

Posted: Tue Nov 04, 2014 11:00 am
by mikosiko
yes... assuming that InnoDB Storage Engine is enabled in your MYSQL installation this will do it

Code: Select all

ALTER TABLE <your table name goes here> ENGINE = innodb
be aware that transactions on InnoDB engine has some caveats too.... read carefully the documentation posted in the previous link.

Re: MySqli Rollback under xampp does not work.

Posted: Tue Nov 04, 2014 11:09 am
by bowlesj
Thanks again mikosiko. I missed your link. Reading it now. I found this link indicating that phpMyAdmin can be used to convert the tables too.
http://www.serverschool.com/database-ma ... hpmyadmin/