Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1337

Rollback doesn't work on transactional multi table update with mysql

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.1
    • Fix Version/s: 2.1.2
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      Mysql 5.1.54-1ubuntu4

      Description

      When doing transactional multi table update using DQL query the changes are not rolled back after exception is raised after the update.

      $em->transactional(function($entityManager) {
          $dql = "UPDATE ..."; // some multi table update DQL
          $query = $entityManager->createQuery($dql);
          $query->execute();
      
          throw new Exception();
      });

      This is because Doctrine executes "DROP TABLE" for temporary table created for the update but MySQL is doing commit right after DROP and CREATE statements automatically.

      From PHP documentation:

      "Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary."

        Activity

        Hide
        Benjamin Eberlei added a comment -

        Verified that transactions do not get committed when you CREATE or DROP temporary tables. That is also with MySQL 5.1.54 / Ubuntu

        You seem to have some other kind of error or something?

        Show
        Benjamin Eberlei added a comment - Verified that transactions do not get committed when you CREATE or DROP temporary tables. That is also with MySQL 5.1.54 / Ubuntu You seem to have some other kind of error or something?
        Hide
        Aigars Gedroics added a comment -

        I'm almost sure that updated data was committed in my situation, but will check and provide the test case if possible.

        Show
        Aigars Gedroics added a comment - I'm almost sure that updated data was committed in my situation, but will check and provide the test case if possible.
        Hide
        Aigars Gedroics added a comment -

        Found the problem!
        It's because the Doctrine calls "DROP TABLE" instead of "DROP TEMPORARY TABLE" when getting rid of the temporary tables.

        To prove this strange MySQL behaviour, such PHP script can be executed:

        $pdo = new PDO('mysql:dbname=test', 
                'root', 
                'root', 
                array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
        
        $pdo->exec('DROP TABLE IF EXISTS a');
        $pdo->exec('CREATE TABLE a (a INT) ENGINE = innodb');
        
        $pdo->beginTransaction();
        
        $pdo->exec('INSERT INTO a VALUES (1)');
        $pdo->exec('CREATE TEMPORARY TABLE c (b int) ENGINE = innodb');
        $pdo->exec('DROP TABLE c');
        $pdo->exec('INSERT INTO a VALUES (2)');
        
        $pdo->rollBack();
        
        $st = $pdo->prepare('SELECT * FROM a');
        $st->execute();
        $row = $st->fetch(PDO::FETCH_ASSOC);
        
        var_dump($row);
        

        The dump should output "false", still the first inserted record is output.

        This is actual at least on Mysql 5.1.54-1ubuntu4 and PHP 5.3.6.

        The solution is trivial - use "DROP TEMPORARY TABLE" syntax instead.

        Show
        Aigars Gedroics added a comment - Found the problem! It's because the Doctrine calls "DROP TABLE" instead of "DROP TEMPORARY TABLE" when getting rid of the temporary tables. To prove this strange MySQL behaviour, such PHP script can be executed: $pdo = new PDO('mysql:dbname=test', 'root', 'root', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); $pdo->exec('DROP TABLE IF EXISTS a'); $pdo->exec('CREATE TABLE a (a INT) ENGINE = innodb'); $pdo->beginTransaction(); $pdo->exec('INSERT INTO a VALUES (1)'); $pdo->exec('CREATE TEMPORARY TABLE c (b int ) ENGINE = innodb'); $pdo->exec('DROP TABLE c'); $pdo->exec('INSERT INTO a VALUES (2)'); $pdo->rollBack(); $st = $pdo->prepare('SELECT * FROM a'); $st->execute(); $row = $st->fetch(PDO::FETCH_ASSOC); var_dump($row); The dump should output "false", still the first inserted record is output. This is actual at least on Mysql 5.1.54-1ubuntu4 and PHP 5.3.6. The solution is trivial - use "DROP TEMPORARY TABLE" syntax instead.
        Hide
        Benjamin Eberlei added a comment -

        Verified, even on mysql console client.

        Thanks for investigating, big thumbs up.

        Show
        Benjamin Eberlei added a comment - Verified, even on mysql console client. Thanks for investigating, big thumbs up.
        Hide
        Benjamin Eberlei added a comment -

        Fixed.

        Show
        Benjamin Eberlei added a comment - Fixed.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Aigars Gedroics
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: