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 and the 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

#!/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