KraneBird KraneBird - 1 month ago 25
PHP Question

MySQL InnoDB transaction rollback is not working

I had a problem with a transaction sent from Yii framework (using PDO) to InnoDB MySql database, the problem was: rollback was not working, update statement was committing right away.


  • I tried setting autoCommit explicitly, with no luck.

  • Checked Mysql general log, only one Connection is being open, with one Start transaction sent, and later only one rollback.

  • In the general log, "Start transaction", "rollback" and the update statement were all from the same client thread.

  • No commit was found in the whole log.

  • Tried a rollback example from a mysql client (MySQLWorkbench) and rollback worked!



The problem was just that MySql was committing right away when its sent from the Yii app and I didn't know why.

For no particular reason, I just tried enabling log_bin in my.cnf and the rollback worked!!

Can someone please explain what just happened?

I'm using MySql 5.6.25, with PHP 5.6.10 and Yii 1.1.14.

Update:

Turns out that I have made a mistake, I thought the rollback worked after enabling log_bin, but it didn't.

So now I'm back to the original problem, the rollback is not working, and here is the source code:

$transaction = Yii::app()->db->beginTransaction();
try {

$data = array();
// fill some data here..
$model = Model::createOrUpdate($data);

$errors = $model->getErrors();

} catch (Exception $e) {
$errors []= $e->getMessage();
}

if (empty($errors)) {
$msg = 'Success message!';
$transaction->commit();
echo CJSON::encode(array('success', $msg));
} else {
if ($transaction->active) {
$transaction->rollback();
}
echo CJSON::encode(array('error', implode(', ', $errors)));
}

Yii::app()->end();

Answer

After some debugging, I found the reason behind that. Basically the method:

Model::createOrUpdate($data);

is calling a stored procedure within, so if anyone is having this issue, please check if that's covered.