PDO startTransaction rollback MySQL
Hieronder:
De begin staat van de DB.
De verwachte staat van de DB.
Het eind resultaat.
De foutmelding::
De code dat moet mislukt want tabel x1xxxx bestaat niet maar toch is de transactie niet teruggedraait.
De begin staat van de DB:
mysql> select * from x1;
+------+---------+
| c1 | c2 |
+------+---------+
| 0 | c1 is 0 |
| 1 | c1 is 1 |
+------+---------+
2 rows in set (0.00 sec)
De vwrwachte staatt van de DB:
Het zelfde als de begin staat.
Het werkelijke eind resultaat:
mysql> select * from x1;
+------+----------------+
| c1 | c2 |
+------+----------------+
| 0 | x-action test |
| 1 | x-action test2 |
| 10 | c1 is 10 |
+------+----------------+
3 rows in set (0.00 sec)
De foutmelding:
ERR: Failed to execute sqlCmd = update x1xxxx set c2 = 'make it all fail' where c1 = :v0
Exc msg: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'tobivroegh_tobi_live.x1xxxx' doesn't exist
In file: /Users/andynic/Sites/andyTest/public_html/t.php
At liine: 32
De code:
try {
$dbHndl = new PDO("mysql:host=localhost; dbname=tobivroegh_tobi_live; charset=utf8", "tobivroegh", "qweasdzxc");
$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 />";
}
Dat was het. Ik realizeerde mij niet dat de default engine was MyISAM.
Expliciet aangeven van de engine in de create commando heeft het probleem opgelost:
create table x1 (c1 integer, c2 varchar(30)) ENGINE=InnoDB;