I have a PDO Transaction that im trying to run, the first query creates a switch and the second adds information about it to another table. My issue is that for some reason the 1st query doesn't execute correctly but the transaction is committed. (Im using the following PDO Class http://culttt.com/2012/10/01/roll-your-own-pdo-php-class/)
try{
//Insert into required tables
$db->beginTransaction();
$db->Query("INSERT INTO firewall (Name)VALUES(:Name)");
$db->bind(':Name',$Name);
$db->execute();
$db->Query("INSERT INTO firewall_switch (Switch_ID, firewall_id,customer_ID)VALUES(:Switch,LAST_INSERT_ID(),:Customer)");
$db->bind(':Switch',$switch);
$db->bind(':Customer',$customer);
$db->execute();
$db->endTransaction();
}catch(PDOException $e){
$db->cancelTransaction();
}
The following is what gets run in SQL from the logs:
6 Query START TRANSACTION
6 Prepare [6] INSERT INTO firewall (Name)VALUES(?)
6 Prepare [7] INSERT INTO firewall_switch (Switch_ID, firewall_id,customer_ID)VALUES(?,LAST_INSERT_ID(),?)
6 Execute [7] INSERT INTO firewall_switch (Switch_ID, firewall_id,customer_ID)VALUES('2',LAST_INSERT_ID(),'164')
6 Query COMMIT
as you can see the first query never executes but the second does. this particular transaction should have rolled back as there was a duplicate ID which is not allowed.
If there are no duplicates then the transaction seems to complete as expected but im not sure why rollback doesn't work...
EDIT:
DB Class: class Db{
private static $Connection = array();
public $connection;
private $dbh;
private $error;
private $stmt;
public static function GetConnection($connection)
{
if(!array_key_exists($connection,self::$Connection))
{
$className = __CLASS__;
self::$Connection[$connection] = new $className($connection);
}
return self::$Connection[$connection];
}
public function __construct($connection){
global $config;
//Load Settings
$this->id = uniqid();
$this->connection = $connection;
if(array_key_exists($connection,$config['connections']['database'])){
$dbConfig = $config['connections']['database'][$connection];
// Set DSN
$dsn = 'mysql:host=' . $dbConfig['host'] . ';port='.$dbConfig['port'].';dbname=' . $dbConfig['database'];
}
// Set options
$options = array(
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
// Create a new PDO instantiate
try{
$this->dbh = new PDO($dsn, $dbConfig['user'], $dbConfig['password'], $options);
}
// Catch any errors
catch(PDOException $e){
$this->error = $e->getMessage();
error_log($e->getMessage());
}
}
//Create the SQL Query
public function query($query){
$this->stmt = $this->dbh->prepare($query);
}
//Bind SQL Params
public function bind($param, $value, $type = null){
if (is_null($type)) {
switch (true) {
case is_int($value):
$type = PDO::PARAM_INT;
break;
case is_bool($value):
$type = PDO::PARAM_BOOL;
break;
case is_null($value):
$type = PDO::PARAM_NULL;
break;
default:
$type = PDO::PARAM_STR;
}
}
$this->stmt->bindValue($param, $value, $type);
}
//Execute the SQL
public function execute($array = NULL){
if($array == NULL){
return $this->stmt->execute();
}else{
return $this->stmt->execute($array);
}
}
public function resultset(){
$this->execute();
return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
}
//Return Single Record
public function single(){
$this->execute();
return $this->stmt->fetch(PDO::FETCH_ASSOC);
}
//Count rows in table
public function rowCount(){
return $this->stmt->rowCount();
}
//Show last ID Inserted into table
public function lastInsertId(){
return $this->dbh->lastInsertId();
}
//Transactions allows the tracking of multiple record inserts, should one fail all will rollback
public function beginTransaction(){
return $this->dbh->beginTransaction();
}
public function endTransaction(){
return $this->dbh->commit();
}
public function cancelTransaction(){
return $this->dbh->rollBack();
}
//Debug dumps the info that was contained in a perpared statement
public function debugDumpParams(){
return $this->stmt->debugDumpParams();
}
}
?>
DB Structure:
CREATE TABLE `firewall` (
`id` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Name_UNIQUE` (`Name`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1
CREATE TABLE `firewall_switch` (
`id` int(11) NOT NULL auto_increment,
`Switch_ID` int(10) unsigned NOT NULL,
`firewall_id` int(10) unsigned NOT NULL,
`Customer_ID` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `fk_firewall_switch_Switch1_idx` (`Switch_ID`),
KEY `fk_firewall_switch_firewall1_idx` (`firewall_id`),
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1
$dbclass code and also where you construct it.. the link just shows how to write one, it doesn't give a completed class, . What is the nature of the duplicate check? Can you also show the results of SHOW CREATE TABLE on each of the two tables referenced please. - Arth