Why are MySQL transactions not working with PDO?

86
May 09, 2018, at 07:30 AM

I am encountering some weird behaviour with php, pdo and mysql when trying to use transactions and I am sure there shouldn't be an issue with it, but there is.

Changes are being written to the database even though a rollback should be triggered!

So I got this part of an API that is making changes to data in the database. For some reason the queries (query1 and query2)return 0, which means, that no lines were edited. This is weird enough, as the data in the db has changed, but that's another issue..

So here's the particular passage. Note that the actual PDO queries are being wrapped by functions for scalability and ease of use, but they work, no worries.

if ($this->db->transaction('begin') == FALSE)
{
    $this->application->setStatus('508002', 'Database transaction failure');
    return FALSE;
}
$count = 0;
$query1 = $this->db->genericUpdate('someData');
$query2 = $this->db->genericDelete('someData');
$count     += $query1 + $query2;
if ($count < 1)
{
    $this->db->RWQuery('ROLLBACK'); // Tried invoking the rollback manually...
    $this->log('Count', $count); // Count is 0 in log file...
    if ($this->db->transaction('rollback') == FALSE)
    {
        $this->application->setStatus('508002', 'Database transaction failure');
        return FALSE;
    }
}

For some strange reason the MySQL server seems to ignore the rollback command which is being sent to the server. I turned on query-logging in my.cnf:

649 Query     START TRANSACTION
649 Query     UPDATE `tbl` SET bla = 'foo' WHERE blub = 'bar'   LIMIT 1
649 Query     DELETE FROM `tbl2` WHERE bla = 'fooo'
649 Query     ROLLBACK
649 Query     rollback
649 Quit

The database engines are MyISAM, I checked twice or more. Also no exceptions are being thrown, neither in the Apache/PHP error logs nor in any other syslog facility.

Also I am not issueing any DDL-Statements that could trigger a commit.

I haven't found anything like my issue anywhere on the net and I would love to hear if someone experienced similar problems.

READ ALSO
events, category and timeslot

events, category and timeslot

i have 3 tables, events, timeslot and event_timethis is is db schema:

55
mysql Error 2013 in django 2 and ubuntu &gt;=16.04

mysql Error 2013 in django 2 and ubuntu >=16.04

Ok soI'm making a django project that needs the database to be shared with other project

81
AngularJS API for local database, Where to start?

AngularJS API for local database, Where to start?

Im new to relatively new web development on a professional sideI got about 30 tabs open and looking for a good starting point

78
How to change a bad url generated

How to change a bad url generated

I will know how if possible to change this elementthe problem appear when I want to change the language on the website

58