phpBB sql transaction function not working

Question

I'm using phpBB and this code is not working as expected. It's simply using the phpBB sql_transaction function to start an sql transaction, then commit it. But the exception should be thrown half way through and issue a rollback using the same function.

However, the rollback never happens. Queries 1 and 2 take effect, and I can't get them to rollback.

The documentation states that if there is an sql error, it will automatically issue a rollback, but I'm trying to roll it back if there is a php error, such as a timeout or something.

I'm using MySQL 5.7, phpBB 3.0.11, and php 5.6.

Can someone point out the issue?

$db->sql_transaction('begin');

try {
    $sql = 'UPDATE aaa_temp SET method = "a" WHERE id = 1';
    $db->sql_query($sql);

    $sql = 'UPDATE aaa_temp SET method = "b" WHERE id = 2';
    $db->sql_query($sql);

    throw new Exception('OMG TOTAL ERROR');

    $sql = 'UPDATE aaa_temp SET method = "c" WHERE id = 3';
    $db->sql_query($sql);

} catch (Exception $ex) {
    $db->sql_transaction('rollback');
    trigger_error($ex->getMessage(), E_USER_ERROR);
}

$db->sql_transaction('commit');

Thanks in advance! I know it's got to be something really simple and stupid, but let me know if more detail is needed.

EDIT: Just so it's clear, I'm talking about SQL transactions in MySQL.

are you asking why the two UPDATE queries before the `throw new Exception` are being executed? — Webeng, May 02 '16 at 03:39
I know they're being executed, but they should be part of a transaction. Why are they succeeding when there's an exception thrown, and a rollback issued? Shouldn't those two updates be rolled back? — Captain Hypertext, May 02 '16 at 03:51

score 0 · Accepted Answer · answered May 03 '16 at 04:09

It turns out that the table I was trying to rollback updates to was using the MyISAM storage engine, which doesn't support transactions.

Ironically, that is specifically how phpBB builds them. sql_transaction('rollback') returned true because the rollback did indeed work, it just didn't pick up on the SQL warning about data not being rolled back.

Figured this out by enabling the general_query_log in MySQL to see exactly what this function was doing in the background:

SET AUTOCOMMIT=0;

UPDATE aaa_temp SET method = "a" WHERE id = 1;

UPDATE aaa_temp SET method = "b" WHERE id = 2;

ROLLBACK;

SET AUTOCOMMIT=1;

So since I had a mixture of MyISAM and InnoDB tables, changes to the latter ones were being rolled back while changes to the formers were not.

score -1 · Answer 2 · answered May 02 '16 at 03:56

You have a misconception of how Exceptions work. If you have a try{...}catch(...){...} block, and an exception is thrown somewhere inside the try{...} portion, then that is a signal that your codes flow will jump to the beginning of the catch(...){...} block.

All code that happened before the exception was thrown does happen. Only code inside the try block that is after the exception does not happen. There is no rollback that takes place that eliminates all processes that were done previously because that would be extremely (extraordinarily) difficult for the php developers to make. Imagine if your php code did something like send an email. How do you expect a rollback to take place then when the email was already sent? Hence, rollbacks do not happen.

Sorry, I am talking about SQL transactions. phpBB has an `sql_transaction` function that allows you to interact with transactions. Despite the fact that the first two update statements ran, mysql should roll them back when I call `$db->sql_transaction('rollback');` — Captain Hypertext, May 02 '16 at 04:01

phpBB sql transaction function not working

2 Answers2