Drop table migration_versions after doctrine:schema:update --complete

[4n70w4]

Bug Report

Summary

Current behavior

How to reproduce

I use doctrine/migrations, doctrine/orm and etc. But command

php bin/console doctrine:schema:update --dump-sql --complete

generate and execute code:

DROP TABLE migration_versions;

Expected behavior

I expect that the table migration_versions should not be deleted!

composer info | grep doctrine
doctrine/annotations                  v1.6.0             Docblock Annotations Parser
doctrine/cache                        v1.8.0             Caching library offering an object-orient...
doctrine/collections                  v1.5.0             Collections Abstraction library
doctrine/common                       v2.10.0            PHP Doctrine Common project is a library ...
doctrine/dbal                         v2.8.0             Database Abstraction Layer
doctrine/doctrine-bundle              1.9.1              Symfony DoctrineBundle
doctrine/doctrine-cache-bundle        1.3.5              Symfony Bundle for Doctrine Cache
doctrine/doctrine-migrations-bundle   v1.3.1             Symfony DoctrineMigrationsBundle
doctrine/event-manager                v1.0.0             Doctrine Event Manager component
doctrine/inflector                    v1.3.0             Common String Manipulations with regard t...
doctrine/instantiator                 1.1.0              A small, lightweight utility to instantia...
doctrine/lexer                        v1.0.1             Base library for a lexer that can be used...
doctrine/migrations                   v1.8.1             Database Schema migrations using Doctrine...
doctrine/orm                          v2.6.3             Object-Relational-Mapper for PHP
doctrine/persistence                  v1.1.0             The Doctrine Persistence project is a set...
doctrine/reflection                   v1.0.0             Doctrine Reflection component

[Tomsgu]

And what is the reason for using migrations and schema:update in the same time? If this would magically work (try to think what would need to be done), you would have a broken database version anyway and couldn't use migrations after your schema:update (even if you have migration_versions table).

[4n70w4]

@Tomsgu this way (combine migration and scheme:update) was suggested by your open-source colleague @Ocramius in a neighboring issue #7485

[Tomsgu]

His suggestion was to use only migrations, because schema:update is not that flexible (you can't write additional DB changes by hand) and in advance is not meant for production usage.

[4n70w4]

@Tomsgu in case use only migrations there are also problems with the loss of charset. https://github.com/doctrine/migrations/issues/766

[Tomsgu]

@4n70w4 The advantage of using migrations is that you can modify each migration by hand.

According to "how to change collation and charset faq question" you can't set these values inside the annotations, yml or xml mapping files.

What you can do is to write manual migration that change each table's/database's collation.

[Ocramius]

Closing here: the schema tools are not a production DB management system, and ORM doesn't (nor should) know anything about migrations either.

A possible solution is to put the migration tracking table in a different schema, but really, ORM schema tooling is loud and clear enough about not being supposed to be used in production.

[jwage]

Maybe try this:

$config->setFilterSchemaAssetsExpression('/^(?!migration_versions).*$/');

This will cause the Doctrine DBAL to not return migration_versions when listing the available tables to compare to the tables defined by your entities.

[darius-v]

In symfony 5.3 I had this problem when generating migration - it used to add drop table migration_versisons.

Helped adding

storage:
        table_storage:
            table_name: migration_versions

under doctrine_migrations config

Running php bin/console config:dump-reference doctrine_migrations was showing null for that table. So it probably needed to know the table name which to skip.

[netandreus]

Confirmed at symfony 6.2.9 and: php composer.phar info | grep doctrine

beberlei/doctrineextensions           v1.3.0    A set of extensions to Doctrine 2 that add support for additional query functions available in MySQL, Oracle, PostgreSQL and SQLite.
doctrine/annotations                  2.0.1     Docblock Annotations Parser
doctrine/cache                        2.2.0     PHP Doctrine Cache library is a popular cache implementation that supports many different drivers such as redis, memcache, apc, mongodb and others.
doctrine/collections                  2.1.2     PHP Doctrine Collections library that adds additional functionality on top of PHP arrays.
doctrine/common                       3.4.3     PHP Doctrine Common project is a library that provides additional functionality that other Doctrine projects depend on such as better reflection suppor...
doctrine/data-fixtures                1.6.6     Data Fixtures for all Doctrine Object Managers
doctrine/dbal                         3.6.2     Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management.
doctrine/deprecations                 v1.0.0    A small layer on top of trigger_error(E_USER_DEPRECATED) or PSR-3 logging with options to disable all deprecations or selectively for packages.
doctrine/doctrine-bundle              2.9.1     Symfony DoctrineBundle
doctrine/doctrine-fixtures-bundle     3.4.3     Symfony DoctrineFixturesBundle
doctrine/doctrine-migrations-bundle   3.2.2     Symfony DoctrineMigrationsBundle
doctrine/event-manager                2.0.0     The Doctrine Event Manager is a simple PHP event system that was built to be used with the various Doctrine projects.
doctrine/inflector                    2.0.6     PHP Doctrine Inflector is a small library that can perform string manipulations with regard to upper/lowercase and singular/plural forms of words.
doctrine/instantiator                 1.5.0     A small, lightweight utility to instantiate objects in PHP without invoking their constructors
doctrine/lexer                        2.1.0     PHP Doctrine Lexer parser library that can be used in Top-Down, Recursive Descent Parsers.
doctrine/migrations                   3.6.0     PHP Doctrine Migrations project offer additional functionality on top of the database abstraction layer (DBAL) for versioning your database schema and ...
doctrine/orm                          2.14.3    Object-Relational-Mapper for PHP
doctrine/persistence                  3.1.4     The Doctrine Persistence project is a set of shared interfaces and functionality that the different Doctrine object mappers share.
doctrine/sql-formatter                1.1.3     a PHP SQL highlighting library
knplabs/doctrine-behaviors            2.6.2     Doctrine Behavior Traits
scienta/doctrine-json-functions       5.2.0     A set of extensions to Doctrine 2 that add support for json query functions.
symfony/doctrine-bridge               v6.2.9    Provides integration for Doctrine with various Symfony components

If we do this:

$config->setFilterSchemaAssetsExpression('/^(?!migration_versions).*$/');

Then doctrine:schema:update does not working:

bin/console doctrine:migrations:migrate -n

In ExceptionConverter.php line 45:

  An exception occurred while executing a query: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'migration_versions' already exists

In Exception.php line 28:

  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'migration_versions' already exists

In Connection.php line 32:

  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'migration_versions' already exists

doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration CONFIGURATION] [--em EM] [--conn CONN] [--] [<version>]

[Arkemlar]

@netandreus suppose you have a typo and meant "Then doctrine:migrations:migrate does not working:"

[Arkemlar]

@Ocramius I not agree with clothing this issue. Its totally true that schema:update is not valid thing for production BUT during development it is quite handy and I tend to use it.

And now we (developers) are in stupid situation:

• we use migrations while pushing features to prod but for development we also using schema:update
• but schema:update tries to delete migration table locally, that's not good
• so we add schema_filter to dbal config (aka $config->setFilterSchemaAssetsExpression()) to exclude doctrine_migration_versions table
• now schema:update stops trying to delete migration table BUT migrations:migrate stops working with error: Duplicate table: 7 ERROR: relation "doctrine_migration_versions" already exists

The problem is that we cant use both instruments at same time without pain.

Why doctrine makes discomfort on empty place and make people waste hours to investigate how to overcome this problem?

[Crovitche-1623]

For those who have the same problem (@Arkemlar):

Doctrine seems to create the migration table automatically if it does not exist (if I understand correctly?).

If the table name is changed:

doctrine_migrations:
    storage:
        table_storage:
            table_name: 't___doctrine_migration_versions'

and the following filter (as explained in the Symfony documentation is configured):

# config/packages/doctrine.yaml
doctrine:
    dbal:
        schema_filter: ~^(?!t___)~

The exception "t___doctrine_migrations_versions" already exists is thrown when the commands doctrine:migrations:* are run, I had to use the following filter (inspired by the provided example in this documentation) to exclude my tables beginning with t___:

# config/packages/doctrine.yaml
doctrine:
    dbal:
        schema_filter: /^t___/

[Arkemlar]

@Crovitche-1623 This has no effect for me. I have following config:

doctrine:
    dbal:
        driver: 'pdo_pgsql'
        server_version: '%env(DATABASE_SERVER_VERSION)%'
        charset: utf8
        host: '%env(DATABASE_HOST)%'
        port: '%env(DATABASE_PORT)%'
        dbname: '%env(DATABASE_DB)%'
        user: '%env(DATABASE_USER)%'
        password: '%env(DATABASE_PASSWORD)%'
        schema_filter: /^doctrine/

doctrine_migrations:
    migrations_paths:
        'DoctrineMigrations': '%kernel.project_dir%/migrations'
    enable_profiler: false
    storage:
        table_storage:
            table_name: doctrine_migration_versions

doctrine-schema-update keeps willing to delete doctrine_migration_versions table.

So whatsoever, problem is not fixed 😒

[alexandre-le-borgne]

Workaround for Symfony

I'm a bit sad to have had to do this and I blame DoctrineMigrationsBundle

Supports calls of commands inside a command

$application->get('doctrine:schema:validate')->run(new ArrayInput([]), $output);

<?php

namespace App\EventSubscriber;

use Doctrine\DBAL\Schema\AbstractAsset;
use Doctrine\Migrations\Metadata\Storage\TableMetadataStorageConfiguration;
use Doctrine\Migrations\Tools\Console\Command\DoctrineCommand;
use Symfony\Component\DependencyInjection\Attribute\AutoconfigureTag;

/**
 * Acts as a schema filter able that hides the migration metadata table except
 * when the execution context is that of command inside the namespace of migrations.
 *
 * @see https://github.com/doctrine/DoctrineMigrationsBundle/pull/526
 * @see https://github.com/doctrine/orm/issues/7487
 */
#[AutoconfigureTag('doctrine.dbal.schema_filter')]
final class DoctrineMigrationsFilterSubscriber
{

    public function __invoke(AbstractAsset|string $asset): bool
    {
        if ($asset instanceof AbstractAsset) {
            $asset = $asset->getName();
        }

        if ($asset === (new TableMetadataStorageConfiguration())->getTableName()) { // 'doctrine_migration_versions'
            foreach (debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS) as $call) {
                $class = $call['class'] ?? null;
                if ($class && is_a($class, DoctrineCommand::class, true)) {
                    return true;
                }
            }
            // If we are not in the context of the command executed by doctrine-migrations bundle
            // but a doctrine command, we want to ignore/filter the doctrine_migration_versions table
            return false;
        }

        return true;
    }

}

[xgc1986]

I add my KISS solution, maybe is not the cleanest but I believe is the simplest and easiest workaround. And there is no need to change any config and the only downside is that you have this unnecessary entity with all the other ones

Instead of applying the configurations written above, I integrated the doctrine_migration_version with my entities. With just that my migrations and doctrine works as usual.

<?php
// src/Entity/DoctrineMigrationVersions.php (just add it where you have your other entities and put the proper namespace)
declare(strict_types=1);

namespace App\Entity;

use DateTime;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class DoctrineMigrationVersions
{
    public function __construct(
        #[ORM\Id]
        #[ORM\Column(type: 'string', length: 191)]
        public readonly string $version,
        #[ORM\Column(type: 'datetime', nullable: true)]
        public readonly ?DateTime $executedAt,
        #[ORM\Column(type: 'integer', nullable: true)]
        public readonly ?int $executionTime,
    ) {
    }
}