migrations
migrations copied to clipboard
doctrine
doctrine:schema:validate returns DROP TABLE doctrine_migration_versions;
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
- composer require doctrine/doctrine-migrations-bundle
- composer require doctrine/orm-pack
- bin/console doctrine:migration:diff
- bin/console doctrine:migration:migrate
- bin/console doctrine:schema:validate -v
+1:
// 1 schema diff(s) detected:
DROP TABLE doctrine_migration_versions;
Somebody posted a workaround for Symfony applications relying on the Doctrine bundle:
doctrine:
dbal:
schema_filter: ~^(?!doctrine_)~
Can people here please test?
@greg0ire
[OK] The database schema is in sync with the mapping files.
doctrine:migrations:diff also work fine.
Possible next steps:
- determine how this works with DBAL 3
- determine why it no longer works with DBAL 4
The bug exists on ORM 3.1 with DBAL 3.8 as well, it is not per se a DBAL 4.0 issue.
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.
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.
/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
--complete is a flag on commands of the doctrine namespace, which are the commands this issue is about, right?
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>]
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
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).
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.
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.
@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.
Here is my attempt: https://github.com/doctrine/DoctrineMigrationsBundle/pull/526
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
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.
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 ?
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.
Gave it a try, and I'm starting to see some cons:
- Is it really
orm:schema-tool:update's responsibility to create that migrations table? If you are usingorm: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 ofdoctrine:schema:validateis, 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. - 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.
I gave it a try as well and ended up opening three PRs where each PR requires the previous one.
- doctrine/orm#11374
- doctrine/migrations#1418
- 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.
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.
ORM's SchemaTool has two methods generating a schema both providing a way to hook into the generated schema before it is returned.
-
getSchemaFromMetadata(link) throws events after generation allowing you to hook into the event and do anything you want toSchema $schema. -
createSchemaForComparison(link) only has theSchemaAssetFilterto 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.
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.
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
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...)?
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;
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.