migrations:dump-schema loses information

[guilliamxavier]

Bug Report

Q	A
BC Break	no
Version	2.0.2

Refs #689

Summary

The migration generated for the dumped schema is not complete.

Current behavior

The migrations:dump-schema command does not handle existing foreign keys, as opposed to the migrations:diff command.

How to reproduce

PHP 7.2, MySQL 5.7

1. Run composer create-project symfony/website-skeleton myapp && cd myapp
   => relevant packages installed:

   • doctrine/dbal (v2.9.2)
   • doctrine/migrations (v2.0.2)
   • doctrine/orm (v2.6.3)
   • doctrine/doctrine-bundle (1.10.2)
   • doctrine/doctrine-migrations-bundle (v2.0.0)

2. Copy file .env to .env.local and configure the DATABASE_URL environment variable (mysql://db_user:db_password@127.0.0.1:3306/db_name)

3. Run php bin/console doctrine:database:create

4. Create two entity files, with an association:

   • src/Entity/Foo.php:

     <?php
     
     namespace App\Entity;
     
     use Doctrine\ORM\Mapping as ORM;
     
     /** @ORM\Entity */
     class Foo
     {
         /** @ORM\Column(type="integer") @ORM\Id */
         private $id;
     
         /** @ORM\Column(type="string", length=50) */
         private $name;
     }

   • src/Entity/Bar.php:

     <?php
     
     namespace App\Entity;
     
     use Doctrine\ORM\Mapping as ORM;
     
     /** @ORM\Entity */
     class Bar
     {
         /** @ORM\Column(type="integer") @ORM\Id */
         private $id;
     
         /** @ORM\Column(type="string", length=100) */
         private $label;
     
         /** @ORM\ManyToOne(targetEntity=Foo::class) */
         private $foo;
     }

5. Run php bin/console cache:clear && php bin/console doctrine:migrations:diff
   => generated file (src/Migrations/Version20190511090651.php):

   <?php
   
   declare(strict_types=1);
   
   namespace DoctrineMigrations;
   
   use Doctrine\DBAL\Schema\Schema;
   use Doctrine\Migrations\AbstractMigration;
   
   /**
    * Auto-generated Migration: Please modify to your needs!
    */
   final class Version20190511090651 extends AbstractMigration
   {
       public function getDescription() : string
       {
           return '';
       }
   
       public function up(Schema $schema) : void
       {
           // this up() migration is auto-generated, please modify it to your needs
           $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
   
           $this->addSql('CREATE TABLE bar (id INT NOT NULL, foo_id INT DEFAULT NULL, label VARCHAR(100) NOT NULL, INDEX IDX_76FF8CAA8E48560F (foo_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
           $this->addSql('CREATE TABLE foo (id INT NOT NULL, name VARCHAR(50) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');
           $this->addSql('ALTER TABLE bar ADD CONSTRAINT FK_76FF8CAA8E48560F FOREIGN KEY (foo_id) REFERENCES foo (id)');
       }
   
       public function down(Schema $schema) : void
       {
           // this down() migration is auto-generated, please modify it to your needs
           $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
   
           $this->addSql('ALTER TABLE bar DROP FOREIGN KEY FK_76FF8CAA8E48560F');
           $this->addSql('DROP TABLE bar');
           $this->addSql('DROP TABLE foo');
       }
   }

   => correct (notably, the SQL queries in the up() function are the same as output of php bin/console doctrine:schema:update --dump-sql [or php bin/console doctrine:schema:create --dump-sql here with empty DB])

6. Run php bin/console doctrine:migrations:migrate --no-interaction

7. Now that the database schema is in sync with the mapping files (which can be checked with php bin/console doctrine:schema:validate),
   run rm src/Migrations/* && php bin/console doctrine:migrations:dump-schema
   => generated file (src/Migrations/Version20190511091302.php):

   <?php
   
   declare(strict_types=1);
   
   namespace DoctrineMigrations;
   
   use Doctrine\DBAL\Schema\Schema;
   use Doctrine\Migrations\AbstractMigration;
   
   /**
    * Auto-generated Migration: Please modify to your needs!
    */
   final class Version20190511091302 extends AbstractMigration
   {
       public function getDescription() : string
       {
           return '';
       }
   
       public function up(Schema $schema) : void
       {
           // this up() migration is auto-generated, please modify it to your needs
           $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
   
           $this->addSql('CREATE TABLE bar (id INT NOT NULL, foo_id INT DEFAULT NULL, label VARCHAR(100) NOT NULL COLLATE utf8mb4_unicode_ci, INDEX IDX_76FF8CAA8E48560F (foo_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB COMMENT = \'\' ');
           $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
   
           $this->addSql('CREATE TABLE foo (id INT NOT NULL, name VARCHAR(50) NOT NULL COLLATE utf8mb4_unicode_ci, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB COMMENT = \'\' ');
       }
   
       public function down(Schema $schema) : void
       {
           // this down() migration is auto-generated, please modify it to your needs
           $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
   
           $this->addSql('DROP TABLE bar');
           $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');
   
           $this->addSql('DROP TABLE foo');
       }
   }

   => problems:

   • missing foreign key
   • table DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci and colum COLLATE utf8mb4_unicode_ci instead of table DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci (and column implicitly using table options)
   • duplicate database platform check

Expected behavior

Executing migrations:dump-schema with an up-to-date DB should generate the same code as executing migrations:diff with an empty DB.

[pscheit]

I can confirm that.

Workaround is to call: console doctrine:schema:create --dump-sql and put it into a migration by hand

[guilliamxavier]

By the way, I understand that migrations:dump-schema generates its SQL queries from the database rather than from the entity mapping (hence the discrepancy), but since it was originally intended (in combination with migrations:rollup) for "replacing" all the historical migrations with one full (and my migrations were generated with successive migrations:diff over time), I expected it to work rather like schema:create --dump-sql... Now maybe that would need a new command, e.g. "migrations:create-schema"?

[jwage]

This is a bug. I should be able to fix it in the next bug fix release.

[mhitza]

Just noticed this myself, ran a migrations rollup a couple of days ago and I noticed today that there are no foreign keys defined anymore.

If this bug is a bit more complex to resolve I would suggest a warning message to the rollup command and reference this issue in the meantime.

[guilliamxavier]

I agree (my current workaround is to temporarily switch to an empty DB and run migrations:diff). And the more time passes, the more I think it needs a distinct migrations:create-schema command which would generate SQL from the mapping (while letting the migrations:dump-schema command continue to generate SQL from the DB).

[alxvgt]

Same issue here. Any information about a fix release date ?

[guilliamxavier]

See #981 for a simple proposition leveraging the migrations:diff command

[jazithedev]

Hello. Any feedback about this case :)? Missing foreign keys in migration files seems to be quite a large issue in here.

[goetas]

This has been fixed in 2.3.0, use migrations:diff --from-empty-schema to get a diff from the empty schema.

[alzee]

Still have this problem today on doctrine/migrations (v3.1.2)
Fortunately, we have migrations:diff --from-empty-schema now.
But it's sitll confusing, hope it got fixed.

[pacproduct]

Faced this today with doctrine/doctrine-migrations-bundle 3.3.0.

That's rather confusing that doctrine:migrations:dump-schema and doctrine:migrations:diff --from-empty-schema don't behave the same! oO

I do agree with @jazithedev, it seems like a rather big issue to dump a database schema without foreign keys.

Is there a reason to keep the current behavior of doctrine:migrations:dump-schema exporting everything except foreign keys?

(see related issue #1163)