doctrine:schema:validate returns DROP TABLE doctrine_migration_versions;

[ValentinDk]

BC Break Report

Q	A
BC Break	yes
Version	3.0.0

Summary

When I updated doctrine/orm to version 3.0.0, my CI started to fail due to the doctrine:schema check:validate, which returned DROP TABLE doctrine_migration_versions;

I found that adding schema_filter can fix this problem: "~^(?!doctrine_migration_versions$)~" in the dbal configuration, indeed, then the error no longer appears, but after that my migrations stopped tracking the current state and think that at the moment no migration has been performed.

In version 3.0, the saveMode argument was removed in \Doctrine\ORM\Tools\SchemaTool::getUpdateSchemaSql and because of this, doctrine:schema:validate tracks deleted tables and returns sql for this action.

Previous behavior

Current behavior

How to reproduce

1. composer require doctrine/doctrine-migrations-bundle
2. composer require doctrine/orm-pack
3. bin/console doctrine:migration:diff
4. bin/console doctrine:migration:migrate
5. bin/console doctrine:schema:validate -v

[dmamchyts]

+1:

 // 1 schema diff(s) detected:                                                                                          

     DROP TABLE doctrine_migration_versions;

[greg0ire]

Somebody posted a workaround for Symfony applications relying on the Doctrine bundle:

doctrine:
    dbal:
        schema_filter: ~^(?!doctrine_)~

Can people here please test?

[dmamchyts]

@greg0ire

[OK] The database schema is in sync with the mapping files.                    

doctrine:migrations:diff also work fine.

[greg0ire]

Possible next steps:

• determine how this works with DBAL 3
• determine why it no longer works with DBAL 4

[wmouwen]

The bug exists on ORM 3.1 with DBAL 3.8 as well, it is not per se a DBAL 4.0 issue.

[greg0ire]

OK so possible next steps:

• determine how this works with ORM 2
• determine why it no longer works with ORM 3

If somebody could dump their DBAL schema filter at runtime using the DBAL configuration object on ORM 2 then on ORM 3, it might help.

[greg0ire]

Can somebody here please confirm that they get the issue with ORM 2 when using the --complete flag? I just remembered that this is likely the cause for this bug.

[dmamchyts]

/var/www/html $ composer show | grep doctrine
doctrine/cache                        2.2.0
doctrine/collections                  2.2.1
doctrine/common                       3.4.3
doctrine/data-fixtures                1.7.0
doctrine/dbal                         3.8.3
doctrine/deprecations                 1.1.3
doctrine/doctrine-bundle              2.11.3
doctrine/doctrine-fixtures-bundle     3.5.1
doctrine/doctrine-migrations-bundle   3.3.0
doctrine/event-manager                2.0.0
doctrine/inflector                    2.0.10
doctrine/instantiator                 2.0.0
doctrine/lexer                        3.0.1
doctrine/migrations                   3.7.4
doctrine/orm                          2.19.0
doctrine/persistence                  3.3.2
doctrine/sql-formatter                1.2.0
phpstan/phpstan-doctrine              1.3.62
symfony/doctrine-bridge               v7.0.5

/var/www/html $ bin/console doctrine:migration:diff --help

Description:
  Generate a migration by comparing your current database to your mapping information.

Usage:
  doctrine:migrations:diff [options]

Options:
      --configuration=CONFIGURATION                        The path to a migrations configuration file. [default: any of migrations.{php,xml,json,yml,yaml}]
      --em=EM                                              The name of the entity manager to use.
      --conn=CONN                                          The name of the connection to use.
      --namespace=NAMESPACE                                The namespace to use for the migration (must be in the list of configured namespaces)
      --filter-expression=FILTER-EXPRESSION                Tables which are filtered by Regular Expression.
      --formatted                                          Format the generated SQL.
      --line-length=LINE-LENGTH                            Max line length of unformatted lines. [default: "120"]
      --check-database-platform[=CHECK-DATABASE-PLATFORM]  Check Database Platform to the generated code. [default: false]
      --allow-empty-diff                                   Do not throw an exception when no changes are detected.
      --from-empty-schema                                  Generate a full migration as if the current database was empty.
  -h, --help                                               Display help for the given command. When no command is given display help for the list command
  -q, --quiet                                              Do not output any message
  -V, --version                                            Display this application version
      --ansi|--no-ansi                                     Force (or disable --no-ansi) ANSI output
  -n, --no-interaction                                     Do not ask any interactive question
  -e, --env=ENV                                            The Environment name. [default: "dev"]
      --no-debug                                           Switch off debug mode.
      --profile                                            Enables profiling (requires debug).
  -v|vv|vvv, --verbose                                     Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug

/var/www/html $ bin/console doctrine:schema:validate --help

Description:
  Validate the mapping files

Usage:
  doctrine:schema:validate [options]

Options:
      --em=EM                Name of the entity manager to operate on
      --skip-mapping         Skip the mapping validation check
      --skip-sync            Skip checking if the mapping is in sync with the database
      --skip-property-types  Skip checking if property types match the Doctrine types
  -h, --help                 Display help for the given command. When no command is given display help for the list command
  -q, --quiet                Do not output any message
  -V, --version              Display this application version
      --ansi|--no-ansi       Force (or disable --no-ansi) ANSI output
  -n, --no-interaction       Do not ask any interactive question
  -e, --env=ENV              The Environment name. [default: "dev"]
      --no-debug             Switch off debug mode.
      --profile              Enables profiling (requires debug).
  -v|vv|vvv, --verbose       Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug

No --complete flags

[greg0ire]

--complete is a flag on commands of the doctrine namespace, which are the commands this issue is about, right?

[yapro]

This is a real problem for our project, especially for production. And this trick doesn't work:

schema_filter: ~^(?!doctrine_)~

because it causes an even bigger problem:

bin/console doctrine:migrations:migrate --no-interaction

In ExceptionConverter.php line 45:

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

In Exception.php line 28:

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

In Connection.php line 33:

  SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'doctrine_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>]

[wmouwen]

Here is a shell script to trigger both a succeeding workflow with ORM 2.19 and a failing one with ORM 3.0.

Script

```shell #!/usr/bin/env sh # Set up new Symfony 7 project composer create-project symfony/skeleton:"7.0.*" my_test_project cd my_test_project # Install Doctrine bundles composer require --no-interaction "doctrine/doctrine-bundle:~2.11" "doctrine/doctrine-migrations-bundle:~3.3" "doctrine/orm:~2.19" "doctrine/dbal:~3.8" # Use sqlite for local testing echo "DATABASE_URL=\"sqlite:///%kernel.project_dir%/var/data.db\"" > .env.local # Install empty migration. This creates the sqlite database with the doctrine_migration_versions table bin/console doctrine:migrations:generate bin/console doctrine:migrations:migrate # Validate schema: succeeds bin/console doctrine:schema:validate -v # Upgrade to ORM 3.* composer require "doctrine/orm:~3.0" # Validate schema: fails bin/console doctrine:schema:validate -v ```

The difference causing the change is that within the callstack of the command, the variable $saveMode in \Doctrine\DBAL\Schema\SchemaDiff::_toSql (link) is true when using ORM 2.19 and false when using ORM 3.0, causing dropped tables to be included in the changeset.

Backtrace ORM 2.19

``` [generate trace] at vendor/doctrine/dbal/src/Schema/SchemaDiff.php:252 Doctrine\DBAL\Schema\SchemaDiff->_toSql() at vendor/doctrine/dbal/src/Schema/SchemaDiff.php:225 Doctrine\DBAL\Schema\SchemaDiff->toSaveSql() at vendor/doctrine/orm/src/Tools/SchemaTool.php:992 Doctrine\ORM\Tools\SchemaTool->getUpdateSchemaSql() at vendor/doctrine/orm/src/Tools/SchemaValidator.php:358 Doctrine\ORM\Tools\SchemaValidator->getUpdateSchemaList() at vendor/doctrine/orm/src/Tools/SchemaValidator.php:344 Doctrine\ORM\Tools\SchemaValidator->schemaInSyncWithMetadata() at vendor/doctrine/orm/src/Tools/Console/Command/ValidateSchemaCommand.php:75 Doctrine\ORM\Tools\Console\Command\ValidateSchemaCommand->doExecute() at vendor/doctrine/orm/src/Tools/Console/CommandCompatibility.php:18 Doctrine\ORM\Tools\Console\Command\ValidateSchemaCommand->execute() at vendor/symfony/console/Command/Command.php:279 Symfony\Component\Console\Command\Command->run() at vendor/symfony/console/Application.php:1049 Symfony\Component\Console\Application->doRunCommand() at vendor/symfony/framework-bundle/Console/Application.php:125 Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand() at vendor/symfony/console/Application.php:318 Symfony\Component\Console\Application->doRun() at vendor/symfony/framework-bundle/Console/Application.php:79 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at vendor/symfony/console/Application.php:169 Symfony\Component\Console\Application->run() at vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:49 Symfony\Component\Runtime\Runner\Symfony\ConsoleApplicationRunner->run() at vendor/autoload_runtime.php:29 require_once() at bin/console:11 ```

Backtrace ORM 3.0

``` [generate trace] at vendor/doctrine/dbal/src/Schema/SchemaDiff.php:252 Doctrine\DBAL\Schema\SchemaDiff->_toSql() at vendor/doctrine/dbal/src/Schema/SchemaDiff.php:242 Doctrine\DBAL\Schema\SchemaDiff->toSql() at vendor/doctrine/dbal/src/Platforms/AbstractPlatform.php:2391 Doctrine\DBAL\Platforms\AbstractPlatform->getAlterSchemaSQL() at vendor/doctrine/orm/src/Tools/SchemaTool.php:900 Doctrine\ORM\Tools\SchemaTool->getUpdateSchemaSql() at vendor/doctrine/orm/src/Tools/SchemaValidator.php:322 Doctrine\ORM\Tools\SchemaValidator->getUpdateSchemaList() at vendor/doctrine/orm/src/Tools/SchemaValidator.php:308 Doctrine\ORM\Tools\SchemaValidator->schemaInSyncWithMetadata() at vendor/doctrine/orm/src/Tools/Console/Command/ValidateSchemaCommand.php:71 Doctrine\ORM\Tools\Console\Command\ValidateSchemaCommand->execute() at vendor/symfony/console/Command/Command.php:279 Symfony\Component\Console\Command\Command->run() at vendor/symfony/console/Application.php:1049 Symfony\Component\Console\Application->doRunCommand() at vendor/symfony/framework-bundle/Console/Application.php:125 Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand() at vendor/symfony/console/Application.php:318 Symfony\Component\Console\Application->doRun() at vendor/symfony/framework-bundle/Console/Application.php:79 Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at vendor/symfony/console/Application.php:169 Symfony\Component\Console\Application->run() at vendor/symfony/runtime/Runner/Symfony/ConsoleApplicationRunner.php:49 Symfony\Component\Runtime\Runner\Symfony\ConsoleApplicationRunner->run() at vendor/autoload_runtime.php:29 require_once() at bin/console:11 ```

[greg0ire]

Related PRs:

• https://github.com/doctrine/orm/pull/10153
• https://github.com/doctrine/dbal/pull/5766

Instead of using SchemaDiff::toSaveSql() for discarding schema changes in 3rd-party database objects (if that's the case), it is recommended to use asset filtering.

I think we should leverage asset filtering to do this. If it causes issues with command from doctrine/migrations, then that needs to be investigated (maybe the filter needs to be dynamic).

[greg0ire]

Just remembered this solution that does exactly that (using a dynamic filter).

Something better that might benefit not just Symfony users might be to do it directly from code inside doctrine/migrations, at runtime.

[gjuric]

Proposed schema_filter solution does not work because it hides the doctrine_migrations_versions table from the rest of the code that needs to see it.

If you run for example doctrine:migrations:list you can see that Doctrine thinks that none of the migrations have been executed because it believes doctrine_migrations_versions table does not exist.

[greg0ire]

@gjuric yes, @yapro already established that in https://github.com/doctrine/migrations/issues/1406#issuecomment-1994382323

Can you try the solution in my last command? I'm currently working on integrated the solution from my previous comment in the migrations bundle.

[greg0ire]

Here is my attempt: https://github.com/doctrine/DoctrineMigrationsBundle/pull/526

[Brewal]

For now, I manually add the migration table to the schema with a doctrine event listener on postGenerateSchema event and don't use schema_filter.

Here's a gist if anyone interested : https://gist.github.com/Brewal/d0fe0792a69e7e5fdf3fb06898b20d35

[stof]

Adding a listener for postGenerateSchema to register the migration table in the schema is an interesting approach. Maybe doctrine/migrations should ship with such a listener.

[greg0ire]

If it relies solely on the ORM's event system and not on Symfony or the DoctrineBundle, I can see how it would be superior to the asset filtering solution, in that it could benefit to other frameworks such as Laminas or Laravel as well.

@Brewal @stof are you willing to open a pull request? Otherwise I might work on it soon.

If I understand properly, the ORM command works by diffing 2 schemas. @flyingdr's solution works by making sure the migrations table appears in neither schemas while @Brewal's solution works by making sure it appears in both schemas.

@flyingdr's relies on asset filtering at the DBAL level, while Symfony's event system, which means it works only for Symfony (I think? Can orm:schema-tool:update be used in other frameworks, and if yes, are Symfony events also fired?). It means it also has to be disabled for migrations commands which need to know about the table.

@Brewal's solution relies on the ORM event system, which will kick in only for the ORM. I hope we are not forgetting about other cases relying on schema diffing and not relying on the ORM somehow.

Are there other pros or cons I'm forgetting ?

[wmouwen]

by making sure the migrations table appears in neither schemas

Removing the table from the schemas won't detect changes to version_column_name or the other column-related settings. Having the table appear in both schemas does cover this.

[greg0ire]

Gave it a try, and I'm starting to see some cons:

1. Is it really orm:schema-tool:update's responsibility to create that migrations table? If you are using orm:schema-tool:update, then you are bypassing migrations, and do not need that table so… there is no point in detecting changes in it, is there? Likewise, the role of doctrine:schema:validate is, according to the error message in @ValentinDk 's screenshot above, to validate that the schema is in sync with the mapping files. The migrations table is not represented by mapping files, and is not part of the ORM schema.
2. It leads to a duplication between the listener code and this method: https://github.com/doctrine/migrations/blob/3.7.x/lib/Doctrine/Migrations/Metadata/Storage/TableMetadataStorage.php#L231-L246

I think I'd rather we go with @flyingdr's solution, where only doctrine/migrations knows about the metadata storage table.

[wmouwen]

I gave it a try as well and ended up opening three PRs where each PR requires the previous one.

1. doctrine/orm#11374
2. doctrine/migrations#1418
3. doctrine/DoctrineMigrationsBundle#529

Is it really orm:schema-tool:update's responsibility to create that migrations table? If you are using orm:schema-tool:update, then you are bypassing migrations, and do not need that table so… there is no point in detecting changes in it, is there?

I don't believe so as doctrine/migrations is using doctrine/orm, however doctrine/orm can open up its core through events to allow doctrine/migrations to alter its behavior. It has allowed it to do so with the postGenerateSchema event, so adding postGenerateComparisonSchema felt logical. The SchemaAssetFilter serves the same purpose but with a different, less open approach.

The migrations table is not represented by mapping files, and is not part of the ORM schema.

Hence the choice to remove the table from the comparison schema instead of adding it to the generated (metadata) schema. This doesn't catch changes to version_column_name as I mentioned in a previous comment, but there's no proper way to put that change in a migration anyway.

One thing that is missing is documentation on how to use #1418 without DoctrineMigrationsBundle. I feel like we can and should add that to the docs of this repository.

[greg0ire]

The SchemaAssetFilter serves the same purpose but with a different, less open approach.

Can you please elaborate on this? I think I might know what you mean, but am not sure. The schema asset filter is kind of hard to work with when you have several filters to add, and that is patched by https://github.com/doctrine/DoctrineBundle/blob/2.11.x/Dbal/SchemaAssetsFilterManager.php, which feels like it could have been contributed to DBAL directly.

[wmouwen]

ORM's SchemaTool has two methods generating a schema both providing a way to hook into the generated schema before it is returned.

1. getSchemaFromMetadata (link) throws events after generation allowing you to hook into the event and do anything you want to Schema $schema.
2. createSchemaForComparison (link) only has the SchemaAssetFilter to edit the schema after it has been generated, allowing you to ignore tables, but that's it.

Adding postGenerateComparisonSchema opens up the option to fully edit Schema $schema as well for schemas generated by createSchemaForComparison.

[greg0ire]

Ah I see, it's less open because it only allows to remove assets, got it 👍 I think I'm OK with your proposal, let's try to get your PRs merged.

[devbysb]

Hi I have the same problem, doctrine:schema:validate show database not in sync. If I test the workaround it's ok but I can execute other migrations. I have update my packages but it's always not ok

composer show | grep doctrine

dama/doctrine-test-bundle                      v8.0.2    Symfony bundle to isolate doctrine database tests and improve test performance
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.2.1     PHP Doctrine Collections library that adds additional functionality on top of PHP arrays.
doctrine/data-fixtures                         1.7.0     Data Fixtures for all Doctrine Object Managers
doctrine/dbal                                  4.0.1     Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management.
doctrine/deprecations                          1.1.3     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.12.0    Symfony DoctrineBundle
doctrine/doctrine-fixtures-bundle              3.5.1     Symfony DoctrineFixturesBundle
doctrine/doctrine-migrations-bundle            3.3.0     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.10    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                          2.0.0     A small, lightweight utility to instantiate objects in PHP without invoking their constructors
doctrine/lexer                                 3.0.1     PHP Doctrine Lexer parser library that can be used in Top-Down, Recursive Descent Parsers.
doctrine/migrations                            3.7.4     PHP Doctrine Migrations project offer additional functionality on top of the database abstraction layer (DBAL) for versioning your database schema and easily deploying changes to it. It is a very easy to use and a powerful tool.
doctrine/orm                                   3.1.1     Object-Relational-Mapper for PHP
doctrine/persistence                           3.3.2     The Doctrine Persistence project is a set of shared interfaces and functionality that the different Doctrine object mappers share.
doctrine/sql-formatter                         1.2.0     a PHP SQL highlighting library
jonmldr/grumphp-doctrine-task                  v3.0      This library provides Doctrine's schema validation in Symfony projects as a GrumPHP task.
symfony/doctrine-bridge                        v6.4.6    Provides integration for Doctrine with various Symfony components

[Fahl-Design]

+1 with same versions as @devbysb

[ecourtial]

Not sure where it comes from. I faced the issue with the 3.7 DBAL, but with the 3.8.3 it is no longer here. I wonder if it is not coming from another package (migration, bridge...)?

[ecourtial]

Problem is not here

doctrine/cache                      2.2.0   PHP Doctrine Cache library is a popular cache implementation that supports many different drivers such as re...
doctrine/collections                2.2.1   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...
doctrine/data-fixtures              1.7.0   Data Fixtures for all Doctrine Object Managers
doctrine/dbal                       3.8.3   Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and mana...
doctrine/deprecations               1.1.3   A small layer on top of trigger_error(E_USER_DEPRECATED) or PSR-3 logging with options to disable all deprec...
doctrine/doctrine-bundle            2.12.0  Symfony DoctrineBundle
doctrine/doctrine-fixtures-bundle   3.5.1   Symfony DoctrineFixturesBundle
doctrine/doctrine-migrations-bundle 3.3.0   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 ...
doctrine/inflector                  2.0.10  PHP Doctrine Inflector is a small library that can perform string manipulations with regard to upper/lowerca...
doctrine/instantiator               2.0.0   A small, lightweight utility to instantiate objects in PHP without invoking their constructors
doctrine/lexer                      3.0.1   PHP Doctrine Lexer parser library that can be used in Top-Down, Recursive Descent Parsers.
doctrine/migrations                 3.7.4   PHP Doctrine Migrations project offer additional functionality on top of the database abstraction layer (DBA...
doctrine/orm                        2.19.3  Object-Relational-Mapper for PHP
doctrine/persistence                3.3.2   The Doctrine Persistence project is a set of shared interfaces and functionality that the different Doctrine...
doctrine/sql-formatter              1.2.0   a PHP SQL highlighting library
phpstan/phpstan-doctrine            1.3.64  Doctrine extensions for PHPStan
symfony/doctrine-bridge             v6.4.5  Provides integration for Doctrine with various Symfony components
symfony/doctrine-messenger          v6.4.4  Symfony Doctrine Messenger Bridge

But just switching to the upper version of the ORM, and the issue is back :

ock file operations: 0 installs, 1 update, 1 removal
  - Removing doctrine/common (3.4.3)
  - Upgrading doctrine/orm (2.19.3 => 3.1.3)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 0 installs, 1 update, 1 removal
  - Removing doctrine/common (3.4.3)
  - Upgrading doctrine/orm (2.19.3 => 3.1.3): Extracting archive

bin/console doctrine:schema:update --dump-sql
DROP TABLE doctrine_migration_versions;

[fnowacki]

Any updates? 🤞🏻

[Brewal]

Any updates? 🤞🏻

If you are stuck, maybe you can try my workaround even if that's not the definitive solution. Most likely it will not break anything when this issue is fixed.

[wmouwen]

Another workaround is to check whether the output of the schema difference equals DROP TABLE doctrine_migration_versions;. For example, in GitHub workflows:

jobs:
  validation:
    steps:
      # Preliminary steps...

      - name: Run migrations
        run: ./bin/console doctrine:migrations:migrate --all-or-nothing --no-interaction

      - name: Validating doctrine schema
        run: ./bin/console doctrine:schema:validate --skip-sync

      # Workaround
      - name: Validating database schema sync
        run: [[ "$(./bin/console doctrine:schema:update --dump-sql)" = "DROP TABLE doctrine_migration_versions;" ]] && echo "OK"

[wmouwen]

Related issues:

• symfony/symfony#54039
• doctrine/orm#11409

[LeikoDmitry]

Any updates ?

[greg0ire]

There's doctrine/orm#11409 you can help with if you need this

[arderyp]

the schema filter workaround works for me. I used to use this years ago, but it became unnecessary at some point as we dropped it and our doctrine migrations table was maintained and ignored by default. If it could be ignored by default again, that would be useful.

EDIT: on further testing, the schema filter resolves the schema validate error, but it breaks migrations, as the table is no longer found/considered during migrations (it thinks I haven't run any migrations before)

EDIT 2: php bin/console doctrine:schema:update --dump-sql shows an instruction to DROP the migrations table, but ./bin/console doctrine:migrations:diff does not actually generate it, so, :shrug: