MySqli Rollback under xampp does not work.

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
bowlesj
Forum Contributor
Posts: 179
Joined: Fri Jul 18, 2014 1:54 pm

MySqli Rollback under xampp does not work.

Post 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.

?>
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySqli Rollback under xampp does not work.

Post 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
bowlesj
Forum Contributor
Posts: 179
Joined: Fri Jul 18, 2014 1:54 pm

Re: MySqli Rollback under xampp does not work.

Post by bowlesj »

Thanks mikosiko, you answered it. Myisam is being used. Is there a way to convert MyIsam?
John
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySqli Rollback under xampp does not work.

Post 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.
bowlesj
Forum Contributor
Posts: 179
Joined: Fri Jul 18, 2014 1:54 pm

Re: MySqli Rollback under xampp does not work.

Post 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/
Post Reply