CODEWITHSUNDEEP
phpmysqlpdotransactions
piernik
piernik

Reputation: 3657

Transaction committed despite of error

I have problem with PDO and commiting multiqueries. Here is my php code:

public function executeSql($multiQuery, $conn)
{
    $arrQuery = mb_split(';\s*?(-- )?.*?\n', $multiQuery);

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $conn->beginTransaction();
    try {
        foreach ($arrQuery as $query) {
            if (strlen(trim($query)) > 0) {
                echo "<BR><BR>" . $query;
                $conn->exec($query);
            }
        }
    } catch (Exception $e) {
        echo "<hr>RollBack";
        $conn->rollback();
        throw $e;
    }
    echo "<hr>Commit";
    $conn->commit();
    return true;
}

And my SQL:

INSERT INTO config_new SET nazwa='test', wartosc=123;

ALTER TABLE `koszty_rodzaje` DROP FOREIGN KEY `koszty_rodzaje_ibfk_2`,
ADD FOREIGN KEY (`temat_id`) REFERENCES `tematy` (`temat_id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

Second query generates error, but first is always inserted :/

Here is my response:

INSERT INTO config_new
SET nazwa = 'dummy', wartosc = 1;

ALTER TABLE `koszty_rodzaje` DROP FOREIGN KEY `koszty_rodzaje_ibfk_2`,
ADD FOREIGN KEY (`temat_id`) REFERENCES `tematy` (
`temat_id`
)
ON DELETE CASCADE
ON UPDATE CASCADE;


INSERT INTO config_new SET nazwa='test', wartosc=123

ALTER TABLE `koszty_rodzaje` DROP FOREIGN KEY `koszty_rodzaje_ibfk_2`, ADD FOREIGN KEY (`temat_id`) REFERENCES `tematy` (`temat_id`) ON DELETE CASCADE ON UPDATE CASCADE;
RollBack
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: 
General error: 1025 Error on rename of 
'./smaczny39/koszty_rodzaje' to './smaczny39/#sql2-3212-dafd68' 
(errno: 152)' in /glowna/funkcje/klient.class.php:502 

Stack trace: #0 /glowna/funkcje/klient.class.php(502): 
  PDO->exec('???ALTER TABLE ...') #1 /glowna/funkcje/klient.class.php(550): 
  cl_klient->executeSql('INSERT INTO con...', Object(PDO)) 
  #2 /glowna/admin.php(41): 
   cl_klient->cron_aktualizuj_baze_klienta('1111', '2.0.0-pre.1') 
  #3 {main} thrown in /glowna/funkcje/klient.class.php on line 502

As You see RollBack is executed and no Commit so why first query is inserted in DB?

If I give query like this:

INSERT INTO config_new SET nazwa='test', wartosc=123;

INSERT INTO config_new SET nazwa=testa, wartosc=123;

Also got error and no new values are inserted so works as it should.

Upvotes: 1

Views: 102

Answers (1)

Grzegorz
Grzegorz

Reputation: 348

MySQL always commits transaction before start of ALTER TABLE. So if INSERT query is valid it will go regardless ALTER TABLE is valid or not.

more info here: https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

Upvotes: 4

Related Questions