Loading Fixtures with PHP8 throws a PDO exception "There is no active transaction"

[mbates]

When running the command doctrine:fixtures:load all my fixtures are load, then a critical PDO exception is thrown

/srv # bin/console doctrine:fixtures:load

 Careful, database "casechek_api2" will be purged. Do you want to continue? (yes/no) [no]:
 > yes

   > purging database
   > loading App\Fixtures\Core\TaskFixtures
...
   > loading App\Fixtures\Cases\VendorRequestTrayScanFixtures
[2022-01-26 15:26:20] console.CRITICAL: Error thrown while running command "{command}". Message: "{message}" {"exception":"[object] (PDOException(code: 0): There is no active transaction at /srv/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1853)","command":"doctrine:fixtures:load","message":"There is no active transaction"} []
15:26:20 CRITICAL  [console] Error thrown while running command "doctrine:fixtures:load". Message: "There is no active transaction" ["exception" => PDOException { …},"command" => "doctrine:fixtures:load","message" => "There is no active transaction"]

In Connection.php line 1853:

  There is no active transaction

doctrine:fixtures:load [--append] [--group GROUP] [--em EM] [--purger PURGER] [--purge-exclusions PURGE-EXCLUSIONS] [--shard SHARD] [--purge-with-truncate] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

I've seen this issue opened for migrations, but doctrine:migrations:migrate works fine for me. Is there an argument or configuration option we can use to disable auto-commit?

[greg0ire]

It's not autocommit, it's implicit commit, as in, MySQL does it because it cannot do anything else. Note that you're not alone in reporting this but also that none of the people reporting this provided the SQL queries that lead to this. It would really help. I'm closing for now, but if you post them then I will reopen the issue.

[tlsvda]

I'm running into the same problem, Unfortunately I can't provide the SQL queries that lead to this problem.

Does anyone know when we can expect a fix?

[Eng3l]

@greg0ire I'm having the same issue. How can I provide the SQL queries? There aren't parameters that provide the SQL query.

[greg0ire]

Maybe check your logs?

[fd6130]

Here is my log when i execute with php bin/console doctrine:fixtures:load --purge-with-truncate

[2022-06-21T20:41:09.880048+08:00] doctrine.DEBUG: Executing query: SELECT DATABASE() {"sql":"SELECT DATABASE()"} []
[2022-06-21T20:41:13.689351+08:00] doctrine.DEBUG: Beginning transaction [] []
[2022-06-21T20:41:13.741822+08:00] doctrine.DEBUG: Executing statement: TRUNCATE refund_request_order_item {"sql":"TRUNCATE refund_request_order_item"} []
[2022-06-21T20:41:13.753377+08:00] doctrine.DEBUG: Executing statement: TRUNCATE shopping_cart {"sql":"TRUNCATE shopping_cart"} []
[2022-06-21T20:41:13.763198+08:00] doctrine.DEBUG: Executing statement: TRUNCATE refund_request {"sql":"TRUNCATE refund_request"} []
[2022-06-21T20:41:13.765559+08:00] doctrine.DEBUG: Rolling back transaction [] []
[2022-06-21T20:41:13.766449+08:00] console.CRITICAL: Error thrown while running command "doctrine:fixtures:load --purge-with-truncate". Message: "There is no active transaction" {"exception":"[object] (PDOException(code: 0): There is no active transaction at /home/vui/Documents/Projects/fd-ecommerce/api/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:123)","command":"doctrine:fixtures:load --purge-with-truncate","message":"There is no active transaction"} []
[2022-06-21T20:41:13.766733+08:00] console.DEBUG: Command "doctrine:fixtures:load --purge-with-truncate" exited with code "1" {"command":"doctrine:fixtures:load --purge-with-truncate","code":1} []
[2022-06-21T20:41:13.770380+08:00] doctrine.INFO: Disconnecting [] []

[greg0ire]

@fd6130 As shown in your logs, you are using TRUNCATE. TRUNCATE is listed here: https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

Please read this article I wrote: https://www.doctrine-project.org/projects/doctrine-migrations/en/stable/explanation/implicit-commits.html#implicit-commits

[fd6130]

Hi, thanks for your reply. I can assume that we can't do anything about it since this is not a migration but a command from the bundle, right?

I guess the current solution for this issue is just recreate the database and run doctrine:fixtures:load again.

@fd6130 As shown in your logs, you are using TRUNCATE. TRUNCATE is listed here: https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

Please read this article I wrote: https://www.doctrine-project.org/projects/doctrine-migrations/en/stable/explanation/implicit-commits.html#implicit-commits

[greg0ire]

Have you read this other article I also wrote? https://www.doctrine-project.org/projects/doctrine-data-fixtures/en/latest/explanation/transactions-and-purging.html#transactions-and-purging

[fd6130]

Have you read this other article I also wrote? https://www.doctrine-project.org/projects/doctrine-data-fixtures/en/latest/explanation/transactions-and-purging.html#transactions-and-purging

Nice article, thanks for sharing.

[lifo101]

I'm still having this error using:

• PHP8
• doctrine/doctrine-fixtures-bundle v3.4.2
• doctrine/data-fixtures v1.5.3

Update (2/19/2024): This is still a problem over a year later. Also, the fix below does not work if use_savepoints is enabled in the doctrine config, I get the error SAVEPOINT DOCTRINE_2 does not exist.

A work-around is to start my own transaction in the load function, do my work, commit and then start a new transaction before returning from the method:

    public function load(ObjectManager $manager)
    {
        /** @var Connection db */
        $db = $manager->getConnection();

        // start new transaction
        $db->beginTransaction();

        // do work ...

        // commit and re-start new transaction
        $db->commit();
        // work-around bug "There is no active transaction" in data-fixtures in php8
        $db->beginTransaction();
    }

[fishbone1]

I noticed that I also get this error, even if other errors occur. Unfortunately you don't see the original exception (which is re-thrown by the EntityManager's exception handler), even if you use the -vvv option. So I modified Doctrine\ORM\EntityManager::wrapInTransaction to dump the original exception:

// vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php
// ...
    public function wrapInTransaction(callable $func)
    {
        $this->conn->beginTransaction();

        try {
            $return = $func($this);

            $this->flush();
            $this->conn->commit();

            return $return;
        } catch (Throwable $e) {
            // Modification here:
            dump($e);
            $this->close();
            $this->conn->rollBack();

            throw $e;
        }
    }

The actual cause are violated foreign key constraints because tables are truncated in a random order. I remembered the issue from an older application. (IIRC it wasn't hidden by another exception back then. Such lost stack traces from outer exceptions are a problem with Symfony for me in general that I know different from Java - but that's a different story.)

Anyway, after I fixed that, I can confirm the issue described here: I still get the "inactive transaction" error.

Why is the issue closed? Are you saying that MySQL with truncate is simply not supported anymore? There is a MultipleTransactionORMExecutor which sounds promising. I patched the load-fixtures-command but it didn't solve the problem. Also the command doesn't allow to exchange the executor. So I wondered why this class exists.

[fishbone1]

Here is a workaround that works for me. I created a custom purger that wraps an orginal ORMPurger. After calling the original purger it re-opens the transaction, if necessary.

It also contains a fix for the foreign key problem that we already had in older application (see comment above). The purger factory service provides a parameter to set whether to disable foreign key checks during purging. (If set, it will also enable foreign key checks after purging.) So you can set the behavior in services.yaml.

// src/Purger/MysqlOrmPurgerFactory.php
namespace App\Purger;

use Doctrine\Bundle\FixturesBundle\Purger\PurgerFactory;
use Doctrine\Common\DataFixtures\Purger\ORMPurger;
use Doctrine\Common\DataFixtures\Purger\PurgerInterface;
use Doctrine\ORM\EntityManagerInterface;

class MysqlOrmPurgerFactory implements PurgerFactory
{
    public function __construct(private readonly bool $disableForeignKeyChecks = false) {
    }

    /**
     * {@inheritDoc}
     */
    public function createForEntityManager(
        ?string $emName,
        EntityManagerInterface $em,
        array $excluded = [],
        bool $purgeWithTruncate = false
    ): PurgerInterface
    {
        $purger = new MysqlOrmPurger($em, $excluded);
        $purger->setPurgeMode(
            $purgeWithTruncate ? ORMPurger::PURGE_MODE_TRUNCATE : ORMPurger::PURGE_MODE_DELETE);
        $purger->setDisableForeignKeyChecks($this->disableForeignKeyChecks);
        return $purger;
    }
}

// src/Purger/MysqlOrmPurger.php
namespace App\Purger;

use Doctrine\Common\DataFixtures\Purger\ORMPurger;
use Doctrine\Common\DataFixtures\Purger\ORMPurgerInterface;
use Doctrine\DBAL\Exception;
use Doctrine\ORM\EntityManagerInterface;

class MysqlOrmPurger implements ORMPurgerInterface
{

    private readonly ORMPurgerInterface $purger;
    private bool $disableForeignKeyChecks = false;

    public function __construct(?EntityManagerInterface $em = null,
                                array $excluded = [])
    {
        $this->purger = new ORMPurger($em, $excluded);
    }

    /**
     * Disable foreign key checks before purging. Enable foreign key checks after purging
     */
    public function setDisableForeignKeyChecks(bool $disableForeignKeyChecks): void
    {
        $this->disableForeignKeyChecks = $disableForeignKeyChecks;
    }

    public function getDisableForeignKeyChecks(): bool
    {
        return $this->disableForeignKeyChecks;
    }

    /**
     * Set the purge mode
     *
     * @param int $mode
     *
     * @return void
     */
    public function setPurgeMode($mode): void
    {
        $this->purger->setPurgeMode($mode);
    }

    /**
     * Get the purge mode
     *
     * @return int
     */
    public function getPurgeMode()
    {
        return $this->purger->getPurgeMode();
    }

    public function setEntityManager(EntityManagerInterface $em)
    {
        $this->purger->setEntityManager($em);
    }

    /**
     * Retrieve the EntityManagerInterface instance this purger instance is using.
     *
     * @return EntityManagerInterface
     */
    public function getObjectManager()
    {
        return $this->purger->getObjectManager();
    }

    /**
     * @throws Exception
     */
    public function purge()
    {
        $conn = $this->getObjectManager()->getConnection();
        /** @var \PDO $pdo */
        $pdo = $conn->getNativeConnection();
        if(!($pdo instanceof \PDO)) {
            throw new \Exception('Unsupported native connection');
        }
        $wasInTransaction = $pdo->inTransaction();

        if ($this->disableForeignKeyChecks) {
            $conn->executeStatement('SET FOREIGN_KEY_CHECKS = 0');
        }

        $this->purger->purge();

        if ($this->disableForeignKeyChecks) {
            $conn->executeStatement('SET FOREIGN_KEY_CHECKS = 1');
        }

        if ($wasInTransaction && !$pdo->inTransaction()) {
            $pdo->beginTransaction();
        }
    }
}

# config/services.yaml
services:
    App\Purger\MysqlOrmPurgerFactory:
        arguments:
            $disableForeignKeyChecks: true
        tags:
            - { name: 'doctrine.fixtures.purger_factory', alias: 'mysql_purger' }

Then call the command with:

php bin/console doctrine:fixtures:load --purge-with-truncate --purger=mysql_purger

[marsender]

fishbone1 workaround is ok for DATABASE_URL="mysql:// If you use mysqli with DATABASE_URL="mysqli:// you have no error without the workaround