Missing foreign keys from dump-schema command

[clement-michelet]

Bug Report

Q	A
BC Break	no
Version	2.3.3

Summary

Original issue : #820

While a solution was proposed to generate a diff from empty schema, in my use case, I cannot generate a schema from an empty diff.

Use case :

• Production database had some changes on foreign key names (done by external tools as a side effect of huge DB migrations)
• No migrations were generated to backport those changes to the basecode (because there is about 300+ FK/constraints changed)
• New migrations generate invalid changes when we want to do changes on constraints (like dropping FK)

I need to :

• Generate a schema from the existing database with dump-schema which contains all constraints including FK
• Execute a rollup to have clean database state on every application environments (non-prod environments are reset with a prod-like anonymized dump)

Current behavior

Foreign keys are missing from the schema dumped and DB diff generate foreign keys

How to reproduce

Expected behavior

Foreign keys are dumped into the migration file

Workaround

Dump structure from MySQL and create the migration manually

[goetas]

were those FK defined in your ORM mappings?

dump-schema does not read your database schema but your ORM metadata and generates the needed SQL to get what is defined there.

[clement-michelet]

were those FK defined in your ORM mappings?

They are defined on entities (annotations) but with the autogenerated name.

dump-schema does not read your database schema but your ORM metadata and generates the needed SQL to get what is defined there.

That's odd because when I look on the dump-schema command / SchemaDumper service, it depends on Schema Manager. I thought It was using the database state with information schema and so on (not digged enough to check that).

If it's not supposed to rely on database schema, maybe it should be added to the command description and service descriptions because it is a little misleading as of now :

MigrationsDumpSchemaDoctrineCommand.php

Command for dumping your current database schema to a migration.

SchemaDumper.php

The SchemaDumper class is responsible for dumping the current state of your database schema to a migration. This is to be used in conjunction with the Rollup class.

When I saw "current database schema" and "current state of your database schema", I thought it was refering to the database, not the database mapping.

[stof]

SchemaDumper is indeed not dumping the foreign keys. It only dumps the table definitions.

Instead of creating the SQL queries itself, it should reuse the DBAL machinery for that (or use DiffGenerator with fromEmptySchema: true which already does it)

[loco8878]

Please add an option to set AbstractPlatform::CREATE_FOREIGNKEYS in lib/Doctrine/Migrations/SchemaDumper.php:91
Foreign keys are part of schema.

[stof]

Well, the proper fix is described in my previous comment. But we need someone to implement it.

[jazithedev]

Probably the best way now is to use migrations:diff --from-empty-schema instead. It does similar thing as migrations:dump-schema but it will contain foreign keys.

[jacekkarczmarczyk]

sorry, what's a migrations:diff?

[gsjurebus]

@jacekkarczmarczyk bin/console doctrine:migrations:diff --from-empty-schema

[jacekkarczmarczyk]

bin/console doctrine:migrations:diff --from-empty-schema

@gsjurebus thanks, but this looks like a Symfony solution, I don't use Symfony, I'd expect a native doctrine solution without using external libs