Transaction fails when SQL execute #6553

New Issue

admin · 2026-01-22T15:34:55+01:00

admin commented

2026-01-22 15:34:55 +01:00

Originally created by @CsabaNa on GitHub (Oct 15, 2020).

Hi,

There is a bug (or i did not find any description about it).
in transaction:

steps:
- create user,
- create database,

~ on success commit
~ on failure rollback.

the sample:

...
        /** @var Connection $conn */
        $conn = $this->getDoctrine()->getConnection();
        $conn->beginTransaction();
        $userResponse = $this->userRegister($request, $userValidator, $hashGenerator, $data);
        if ($userResponse instanceof Response) {
            $conn->rollBack();
            return $userResponse;
        }

        $otherResponse = $this->otherRegister($request, $otherValidator, $data);
        if ($otherResponse instanceof Response) {
            $conn->rollBack();
            return $otherResponse;
        }

        $data['userId'] = $userResponse;
        try {
            $someRepository = $this->getDoctrine()->getRepository(Some::class);
            $someRepository->createDatabase($someDatabaseData, $somePassword);
        } catch (\Exception $e) {
            $conn->rollBack();
        }

        
        $conn->commit();
...

in the createDatabase

...
        $conn = $this->getEntityManager()->getConnection();
        $sql = 'CREATE DATABASE `' . $database->getDatabaseName() .
            '` DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci`;';
        $stmt = $conn->prepare($sql);
        $stmt->execute();
...

if otherRegister fails the rollback is just working fine it does not contain $stmt->execute.

if something goes wrong in createDatabase the rollback will not work all user data get place in to user table i guess the $stmt->execute commits all the transactions as well? how can it be solved?

Originally created by @CsabaNa on GitHub (Oct 15, 2020). Hi, There is a bug (or i did not find any description about it). in transaction: - steps: - create user, - create database, ~ on success commit ~ on failure rollback. the sample: ``` ... /** @var Connection $conn */ $conn = $this->getDoctrine()->getConnection(); $conn->beginTransaction(); $userResponse = $this->userRegister($request, $userValidator, $hashGenerator, $data); if ($userResponse instanceof Response) { $conn->rollBack(); return $userResponse; } $otherResponse = $this->otherRegister($request, $otherValidator, $data); if ($otherResponse instanceof Response) { $conn->rollBack(); return $otherResponse; } $data['userId'] = $userResponse; try { $someRepository = $this->getDoctrine()->getRepository(Some::class); $someRepository->createDatabase($someDatabaseData, $somePassword); } catch (\Exception $e) { $conn->rollBack(); } $conn->commit(); ... ``` in the `createDatabase` ``` ... $conn = $this->getEntityManager()->getConnection(); $sql = 'CREATE DATABASE `' . $database->getDatabaseName() . '` DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci`;'; $stmt = $conn->prepare($sql); $stmt->execute(); ... ``` if otherRegister fails the rollback is just working fine it does not contain `$stmt->execute`. if something goes wrong in `createDatabase` the rollback will not work all user data get place in to user table i guess the `$stmt->execute` commits all the transactions as well? how can it be solved?

admin commented

2026-01-22 15:34:56 +01:00

@CsabaNa commented on GitHub (Oct 15, 2020):

Actually do not need so complex sample:

        /** @var Connection $conn */
        $conn = $this->getDoctrine()->getConnection();
        $conn->beginTransaction();
        try {
            $manager->persist($entity01);
            $manager->flush();
            $manager->persist($entity02);
            $manager->flush();
            $manager->persist($entity03);
            $manager->flush();

            $sql = 'SELECT * FROM not_exist;';
            $stmt = $conn->prepare($sql);
            $stmt->execute();
            $conn->commit();
        } catch (\Exception $e) {
            $conn->rollBack();
        }
the transaction fails if the `$stmt->execute();` also fails. if something failed prior  this The rollBack would work fine.

@CsabaNa commented on GitHub (Oct 15, 2020): Actually do not need so complex sample: ``` /** @var Connection $conn */ $conn = $this->getDoctrine()->getConnection(); $conn->beginTransaction(); try { $manager->persist($entity01); $manager->flush(); $manager->persist($entity02); $manager->flush(); $manager->persist($entity03); $manager->flush(); $sql = 'SELECT * FROM not_exist;'; $stmt = $conn->prepare($sql); $stmt->execute(); $conn->commit(); } catch (\Exception $e) { $conn->rollBack(); } the transaction fails if the `$stmt->execute();` also fails. if something failed prior this The rollBack would work fine.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6553