Migrations diff and postgres schema problem with recreating existing tables

[seticzech]

Bug Report

Q	A
BC Break	no
doctrine/annotations	v1.6.0
doctrine/dbal	v2.9.2
doctrine/migrations	v1.8.1
doctrine/orm	v2.6.3
PostgreSQL	9.6.11
PHP	7.2.15

Summary

Running doctrine:migrations:diff multiple times without any changes and with existing table in database always generate the same code trying to create table with schema and delete the very same table without schema.

How to reproduce

On any project create entity:

/**
 * @ORM\Entity(repositoryClass = "App\Domain\Repositories\UserRepository")
 * @ORM\Table(name = "users", schema = "public")
 */
class User
{

    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy = "IDENTITY")
     * @ORM\Column(type = "integer")
     */
    protected $id;

    /**
     * @ORM\Column(type="string", length = 64, nullable = false, unique = true)
     * @var string
     */
    protected $username;

}

Run doctrine:migrations:diff

Generated migration is ok now:

$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

$this->addSql('CREATE TABLE public.users (id SERIAL NOT NULL, username VARCHAR(64) NOT NULL, PRIMARY KEY(id))');
$this->addSql('CREATE UNIQUE INDEX UNIQ_2552C48DF85E0677 ON public.users (username)');

Run doctrine:migrations:migrate and table is created succesfully in database.

Run doctrine:migrations:diff again and new migration is generated:

$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

$this->addSql('CREATE TABLE public.users (id SERIAL NOT NULL, username VARCHAR(64) NOT NULL, PRIMARY KEY(id))');
$this->addSql('CREATE UNIQUE INDEX UNIQ_2552C48DF85E0677 ON public.users (username)');
$this->addSql('DROP TABLE users');

Of course next running doctrine:migrations:migrate ends with error

relation "users" already exists

Expected behavior

I think expected behavior is clear here - don't create new migrations when nothing was changed.

[Ocramius]

Seems very similar to doctrine/dbal#3482

Can you maybe chat with the original author of that table to see if you can reduce it to either a DBAL or an ORM test case?

[seticzech]

@Ocramius I'm not sure that issue is similar to mine. He's using MySQL and multiple database, I use Postgres and it's schema. I found simlar cases here and here. I also found notice in Doctrine API documentation

A Doctrine Schema has nothing to do with the "SCHEMA" defined as in PostgreSQL, it is more related to the concept of "DATABASE" that exists in MySQL and PostgreSQL.

Is even Doctrine able to work with Postgres schemas?

[flolivaud]

Same problem here !

[seticzech]

@flolivaud For now I'm not using schema annotation in entities. This solves the problem about recreating tables but every time on diff command Doctrine is trying to create public schema in down() method:

    public function down(Schema $schema)
    {
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('CREATE SCHEMA public');
        ...

Fortunately I don't need to use other schemas in psql at least for now. It's pretty old issue. For (some) solution look here and here

[flolivaud]

Already using this solution since the beginning of my app.

But i think it's not the same issue.

[seticzech]

@flolivaud I think it's exactly the same issue, look at DBAL-1168 and comment 352177498 :)

I'd issues with auto generated FK constraints too as it described here. I've to use modified Doctrine sources (or write migrations manually).

[flolivaud]

@seticzech it's not the only issue for me.
I've modified AbstractAsset.php but it still occurs.

<?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 Version20190326205509 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() !== 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('CREATE TABLE "user"."user" (id UUID DEFAULT uuid_generate_v4() NOT NULL, email VARCHAR(180) NOT NULL, roles JSON NOT NULL, password VARCHAR(255) NOT NULL, PRIMARY KEY(id))');
        $this->addSql('CREATE UNIQUE INDEX uniq_33a053ffe7927c74 ON "user"."user" (email)');
        $this->addSql('COMMENT ON COLUMN "user"."user".id IS \'(DC2Type:uuid)\'');
        $this->addSql('DROP TABLE "user"."user"');
    }

    public function down(Schema $schema) : void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('CREATE TABLE "user"."user" (id UUID DEFAULT \'uuid_generate_v4()\' NOT NULL, email VARCHAR(180) NOT NULL, roles JSON NOT NULL, password VARCHAR(255) NOT NULL, PRIMARY KEY(id))');
        $this->addSql('CREATE UNIQUE INDEX uniq_33a053ffe7927c74 ON "user"."user" (email)');
        $this->addSql('COMMENT ON COLUMN "user"."user".id IS \'(DC2Type:uuid)\'');
        $this->addSql('DROP TABLE "user"."user"');
    }
}

Removing quote around uuid_generate_v4() doesn't resolve it.

[seticzech]

@flolivaud Wow :D I'm trying to understand what is going on... but it's completely mess. Is this unmodified auto generated migration?

[flolivaud]

@seticzech yes it is ! :). Honestly i think postgresql should not be "officialy" supported by doctrine/dbal (or doctrine/orm).

[Ocramius]

Well, write tests then :-P

[Cupkek05]

@seticzech Hey, Do you have any news about it please ? :)

[seticzech]

@Cupkek05 I haven't. Is it still occured? I didn't use Doctrine for some time and I noticed version 3 is out.

[matheus-aguilar-linx]

I'm still have the same problem on a similar situation

[seticzech]

I "fixed" this issue with separating the entitites from different schemas. Every schema has its own configuration, entity manager and migration files.

[OndrosI]

any update? i have the same problem..