CODEWITHSUNDEEP
phpmysqltransactions
Eddystone
Eddystone

Reputation: 7

MySQL transaction doesn't stop at error

I am learning to use transactions with MySQL and it doesn't seem to be working as it should.

I created some test code:

$db->query('START TRANSACTION;');
$db->query('DELETE FROM test1 WHERE id=4'); 
$db->query('DELETE FROM ttest2 WHERE id=5');  
$res = $db->commit();

where "ttest2" was a deliberate error (the table is misspelled).

With normal queries, $res would be false and the query would fail. But when I run the transaction, the first delete occurs, it ignores the second, and $res is still true. I thought transactions would not allow any queries to occur unless they all occurred correctly. Why did it return true and how do I catch an error in PHP?

Upvotes: 1

Views: 432

Answers (1)

hatef
hatef

Reputation: 6219

You are missing the rollback:

These statements provide control over use of transactions:

START TRANSACTION or BEGIN start a new transaction.

COMMIT commits the current transaction, making its changes permanent.

ROLLBACK rolls back the current transaction, canceling its changes.

SET autocommit disables or enables the default autocommit mode for the current session.

Upvotes: 1

Related Questions