CODEWITHSUNDEEP
symfonydoctrinemigration
Tom
Tom

Reputation: 11

"There is no active transaction" in Doctrine migrations

I try to apply migrations, first three of them to create table, the last one - insert data. When i run php bin/console doctrine:migrations:migrate it gives me an error "There is no active transaction" after each migration and had stoped migration. So i had to run migrations:migrate 4 times. Whats could be the problem?

Upvotes: 1

Views: 9488

Answers (3)

Ricardo Martins
Ricardo Martins

Reputation: 6003

This issue existed before, but now is visible after PHP 8 PDO.

I will quote a great explanation located at your vendor/doctrine/migrations/docs/en/explanation/implicit-commits.rst:

Implicit commits

Since PHP8, if you are using some platforms with some drivers such as MySQL with PDO, you may get an error that you did not get before when using this library: There is no active transaction. It comes from the fact that some platforms like MySQL or Oracle do not support DDL statements (CREATE TABLE, ALTER TABLE, etc.) in transactions.

The issue existed before PHP 8 but is now made visible by e.g. PDO, which now produces the above error message when this library attempts to commit a transaction that has already been commited before.

Consider the following migration.

    public function up(Schema $schema): void
    {
        $users = [
            ['name' => 'mike', 'id' => 1],
            ['name' => 'jwage', 'id' => 2],
            ['name' => 'ocramius', 'id' => 3],
        ];

        foreach ($users as $user) {
            $this->addSql('UPDATE user SET happy = true WHERE name = :name AND id = :id', $user);
        }

        $this->addSql('CREATE TABLE example_table (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id))');
    }

When you run that migration, what actually happens with some platforms is you get the updates inside an implicitly commited transaction, then the CREATE TABLE happens outside that transaction, and then there is an attempt to commit an non-existent transaction.

In that sort of situation, if you still wish to get the DML statements inside a transaction, we recommend you split the migration in 2 migrations, as follows.



    final class Version20210401193057 extends AbstractMigration
    {
        public function up(Schema $schema): void
        {
            $users = [
                ['name' => 'mike', 'id' => 1],
                ['name' => 'jwage', 'id' => 2],
                ['name' => 'ocramius', 'id' => 3],
            ];

            foreach ($users as $user) {
                $this->addSql('UPDATE user SET happy = true WHERE name = :name AND id = :id', $user);
            }
        }
    }

    final class Version20210401193058 extends AbstractMigration
    {
        public function up(Schema $schema): void
        {
            $this->addSql('CREATE TABLE example_table (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id))');
        }

        public function isTransactional(): bool
        {
            return false;
        }
    }

Please refer to the manual of your database platform to know if you need to do this or not.

At the moment, this library checks if there is an active transaction before commiting it, which means you should not encouter the error described above. It will not be the case in the next major version though, and you should prepare for that.

To help you deal with this issue, the library features a configuration key called transactional. Setting it to false will cause new migrations to be generated with the override method above, making new migrations non-transactional by default.

Solution 1

Disable the parameter transactional in your migrations config file as explained here.

Solution 2

Disable transactional migration only this time by adding --all-or-nothing=0 in your php bin/console doctrine:migrations:migrate command.

Upvotes: 3

Prabhat
Prabhat

Reputation: 37

For me following code worked

public function isTransactional(): bool
{
    return false;
}

Upvotes: 0

Limetics
Limetics

Reputation: 141

If you use PHP 8.0, implements "isTransactional" in migration class and return false (See https://github.com/doctrine/DoctrineMigrationsBundle/issues/393)

Upvotes: 1

Related Questions