SQL query fails but the transaction rollback doesn't work

0
votes

The actual column names are account and passwd so the second query will failed, but the first query still inserted into database.

$sql1 = "INSERT INTO users (account, passwd) VALUES ('account+1', 'password+1')";
$sql2 = "INSERT INTO users (account, password) VALUES ('account+2', 'password+2')";
try {

 $db->beginTransaction();
 $db->query($sql1);
 $db->query($sql2);
 $db->commit();

} catch (Exception $e) {
  $db->rollback();
  die($e->getMessage());
}

The transaction started successfully with no errors, the problem is that it doesn't rollback, always rows are inserted regardless of failing queries.

MySQL version 5.5.25 and table type is InnoDB.

2
phpmysqltransactions
This is PDO? Do you have it configured to throw exceptions? - Michael Berkowski
Any reason you're hard-coding values into your queries? You should be using SQL placeholders for that. - tadman
@tadman I assume this to be just a test scenario. - Michael Berkowski

2 Answers

1
votes

Did you turn on exceptions? By default pdo doesn't throw them and silently ignores errors.

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
0
votes
$sql1 = "INSERT INTO user (account, passwd) VALUES ('account+1', 'password+1')";
$sql2 = "INSERT INTO users (account, password) VALUES ('account+2', 'password+2')";
try {

 $db->beginTransaction();
 $db->query($sql1);
 $db->query($sql2);
 $db->commit();

} catch (Exception $e) {
  $db->rollback();
  die($e->getMessage());
}

$mysql refence is wrong, you have to use, $db->rollback();