RollBack Commit working example (engine=innodb)
Code:
<?php
/*
Fri.29.Sep.2017 rollback_commit.php
copy this php in any app and run config.inc.php db-settings will be taken
https://www.w3resource.com/mysql/mysql-transaction.php
MySQL and the ACID Model
--------------------------------------
following tables for this example engine=innodb <===strictly
DROP TABLE IF EXISTS `pubhol`;
CREATE TABLE `pubhol` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`holdt` varchar(20) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`holremarks` varchar(30) COLLATE latin1_general_ci DEFAULT NULL,
`last_changed_by` varchar(15) COLLATE latin1_general_ci DEFAULT '',
`last_change_date` varchar(20) COLLATE latin1_general_ci DEFAULT '',
`last_change_ip` varchar(20) COLLATE latin1_general_ci DEFAULT '',
`created_by` varchar(15) COLLATE latin1_general_ci DEFAULT '',
`created_date` varchar(20) COLLATE latin1_general_ci DEFAULT '',
`created_ip` varchar(20) COLLATE latin1_general_ci DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `holdt` (`holdt`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
DROP TABLE IF EXISTS `qry`;
CREATE TABLE `qry` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`material_type` varchar(10) COLLATE latin1_general_ci DEFAULT 'FG',
`date_from` varchar(10) COLLATE latin1_general_ci DEFAULT '',
`date_to` varchar(10) COLLATE latin1_general_ci DEFAULT '',
`remark` varchar(500) COLLATE latin1_general_ci DEFAULT '',
`last_changed_by` varchar(15) COLLATE latin1_general_ci DEFAULT '',
`last_change_date` varchar(20) COLLATE latin1_general_ci DEFAULT '',
`last_change_ip` varchar(20) COLLATE latin1_general_ci DEFAULT '',
`created_by` varchar(15) COLLATE latin1_general_ci DEFAULT '',
`created_date` varchar(20) COLLATE latin1_general_ci DEFAULT '',
`create_ip` varchar(20) COLLATE latin1_general_ci DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
*/
$config_atkroot = "./";
include_once("atk.inc");
$db = &atkGetDb();
$user = getUser(); //$user[userid]
//30.Sep.2017 ref atk/db/classatkdb.inc
$haltonerror_org = $db->m_haltonerror;
$db->m_haltonerror = false; //#IMP#otherwise Uniq Key Constraint error is thrown ut with abort
//$db->m_haltonerror = $haltonerror_org; //#IMP# RESTORE orgval otherwise Uniq Key Constraint error is thrown ut with abort
$qry = "SET AUTOCOMMIT=0;";
$rows = $db->getrows($qry);
$qry = "START TRANSACTION;";
// $rows = $db->getrows($qry); //somehow this is given error
$flag = true;
//-- 1st qry
$qry = "INSERT INTO qry (material_type) VALUES ('rollbacktest')";
$result = $db->getrows($qry);
//if ($db->hasError() && $db->getErrorType() != "user")
if ($db->hasError())
{
//triggerError($record, null, '', $db->getErrorMsg());
$msg1="Qry1False".$db->getErrorMsg();
$flag = false;
}
//-- 2nd qry This qry has uniq constraints
$qry = "INSERT INTO pubhol (holdt) VALUES ('rollbacktest')";
$result = $db->getrows($qry);
//if ($db->hasError() && $db->getErrorType() != "user")
if ($db->hasError())
{
//triggerError($record, null, '', $db->getErrorMsg());
$msg1="Qry2False".$db->getErrorMsg();
$flag = false;
}
if ($flag) {$db->commit(); $msg = "Commit happened" ;}
else {$db->rollback(); $msg = "RollBack happened"; }
$db->m_haltonerror = $haltonerror_org; //#IMP# RESTORE orgval otherwise Uniq Key Constraint error is thrown ut with abort
echo $msg . '<br><br>' . $msg1 . '<br><br>' . $msg2 . '<br><br>';
//to check post rollback commit if this works
//atkmysqlidb.inc->doConnect()-> mysqli_autocommit($this->m_link_id, TRUE);
//$db = &atkGetDb(); //resets autocommit no does NOT work that way
$qry = "SET AUTOCOMMIT=1;"; //resets autocommit otherwise everyttime $db->commit();
$rows = $db->getrows($qry);
//Now autocommit=1 always settings restored. so check once if it is inserting as standalone qry
$qry = "INSERT INTO qry (material_type) VALUES ('rollbacktest')";
$result = $db->getrows($qry);
// $db->commit();
echo "postRollBackCommit qry works" . '<br><br>' ;
return;