Consecutive migrations throw "SAVEPOINT DOCTRINE_... does not exist" when second migration "isTransactional" set to false

[Jalliuz]

In doctrine/dbal 3.9.4 I have "use_savepoints" option enabled, (Deprecated option, I know, but the option was there since some time)

doctrine:
    dbal:
        use_savepoints: true

I have one migration where I want to bulk commit per 1000 UPDATE queries in a while loop

final class Version20251015152029 extends AbstractMigration
{
    public function isTransactional(): bool
    {
        return false;
    }

    public function up(Schema $schema): void
    {
        $stmt = $this->connection->executeQuery('SELECT id FROM some_table');

        $i = 0;
        $this->connection->beginTransaction();
        while (($row = $stmt->fetchAssociative()) !== false) {
            $this->connection->executeQuery('UPDATE some_table SET something = 'some_value' WHERE id = :id', ['id' => $row['id']]);
            
            // Bulk commit per 1000 UPDATE queries
            if (++$i % 1000 === 0) {
                $this->connection->commit();
                $this->connection->beginTransaction();
            }
        }

        $this->connection->commit();
    }

This worked perfectly when I ran this migration on its own
bin/console doctrine:migrations:execute --up 'DoctrineMigrations\Version20251015152029'

But as soon as I wanted to do multiple consecutive migrations
bin/console doctrine:migrations:migrate
And for some reason I would have another migration before this one, then this second non transactional migration failed.
[error] Migration DoctrineMigrations\Version20251015152029 failed during Execution. Error: "An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT DOCTRINE_2 does not exist"

So this error only happened when this was not the first migration in the process

After a lot of debugging I found out that removing this option solved the issue:

doctrine:
    dbal:
        ...
        # use_savepoints: true

After all I see that this option is deprecated. But I want to post the bug. Because in DBAL 4 this is standard set to true?
This is a dangerous problem because it might only occur when deploying to production. The problem only arises if this migration has another migration before in the migration process. I have not tested this in dbal 4

Don't know if it matters but the migration before this one was one where isTransactional was default, so true

[stof]

doctrine/migrations itself uses transactions when using either transactional migrations (which you seem to have disabled here based on the code snippet, but your last sentence says the opposite) or when using the all-or-nothing option to execute multiple migrations.
Nested transactions rely on savepoints.

And for database platforms that don't support transactional DDL (hint: MySQL), things can break because a DDL statement will automatically commit the current transaction (and savepoints created in a transactions won't be usable after it has been implicitly closed).

[Jalliuz]

I indeed use MySQL. So the problem is executing a transactional (manually setting isTransactional to false) + a non-transactional (default behaviour) in 1 migration run. We can't mix them when using MySQL if I understand well?

Edit:
I found a fix which solved the issue in another way

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

    public function up(Schema $schema): void
    {
        // Fix for mixing non-transactional migrations with default Doctrine migrations which are transactional
        // This resets savepoints from previous migrations
        $this->connection->close();
        $this->connection->connect();

        // ... rest of my migration (see comment above)

So now I can keep the option set to true

doctrine:
    dbal:
        use_savepoints: true

It does the job, So resetting the connection here seems to get rid of those unknown savepoints. But I have the feeling I am doing something that is not right. :) You see any caveats in this approach? I guess the option all-or-nothing will break by doing this?

I start to think the bug is that savepoints are not properly cleaned up per previous migration.

[Jalliuz]

I think #1426 (Migrations are piling up bogus savepoints on MySQL/MariaDB with PDO_MySQL) might be related to this same issue.
I indeed have a bogus savepoint at the start of my non-transactional migration.

[derrabus]

I indeed use MySQL. So the problem is executing a transactional (manually setting isTransactional to false) + a non-transactional (default behaviour) in 1 migration run. We can't mix them when using MySQL if I understand well?

You probably have another migration that is flagged as transactional, but uses DDL statements. We have some dirty code in place to make that work, but its safer to flag those migrations as non-transactional (because that's what they are on MySQL unfortunately).