DBAL-1168: Schema's getMigrateFromSql always adds CREATE SCHEMA

[doctrinebot]

Jira issue originally created by user vbence:

I originally posted this to Migrations; noticing that all the generated down() methods start with a "CREATE SCHEMA public" line.

Inspecting the return from Schema#getMigrateFromSql it indeed contains the create statement.

[doctrinebot]

Comment created by asentner:

I am also having this issue. The down() method always adds: $this->addSql('CREATE SCHEMA public');

Same environment, also using Postgres.

Any chance this is on anyone's radar for a release in the near future?

[doctrinebot]

Comment created by acasademont:

Hit by this too. The problem seems to be that the "public" namespace is not added to the table names by default and hence the diff between what postgres says (a "public" schema is created by default in the DB) and what our schema says.

I tried to solve this with a workaround by prepending "public." to all table names. It works for the first migration but then in the next migration will try to delete all tables without the "public." and create them again. So that's not working!

The solution is assuming that there's always a default 'public' namespace in the Schema.php class.

[cleentfaar]

Any updates on this? Would be nice to get rid of this in our version files

[josensanchez]

As @doctrinebot commented, the problem seems to be here:

        $fromSchema = $conn->getSchemaManager()->createSchema();
        $toSchema = $this->getSchemaProvider()->createSchema();

the first line builds the schema querying the database

SELECT schema_name AS nspname
          FROM   information_schema.schemata
...

and the second one builds it from the ORM metadata in your application

@ORM\Table(name="table_1" ...

there is a "public" schema in $fromSchema but since there is no @ORM\Table(name="table_1", schema="public"...) also there is no "public" schema in $toSchema

trying the @doctrinebot workaround @ORM\Table(name="table_1", schema="public"...) it's also useless as he already commented because table_1 and public.table_1 are two different things to the dbal, witch is logic.
but it will try to perform the next code every time.

create table public.table_1 ...  -- table already exists exception
drop table table_1

the drop command is without the "public." and I assume that it's to add support to tables without namespaces...

I don't see an easy solution for this issue. Either finding a way to define table_1 as an alias for public.table_1 (maybe in postgresql if a database object doesn't have a namespace defined, then the 'public' namespace is attached to it) or removing support in postgresql for no namespace objects, forcing always to define schema="public"

for the moment if anybody is working with postgresql and doctrine I suggest to use only the public schema or not use it at all.

[idchlife]

Yep, waiting issue to be resolved

[garex]

Can't use diff commands as schema always differs by those CREATE SCHEMA public.

[Melkij]

My workaround without modify library and waiting patch.
Add class in any namespace in application:

<?php

namespace EngineBundle\Doctrine;

use Doctrine\Common\EventSubscriber;
use Doctrine\ORM\Tools\Event\GenerateSchemaEventArgs;

class MigrationEventSubscriber implements EventSubscriber
{
    public function getSubscribedEvents()
    {
        return array(
            'postGenerateSchema',
        );
    }

    public function postGenerateSchema(GenerateSchemaEventArgs $Args)
    {
        $Schema = $Args->getSchema();

        if (! $Schema->hasNamespace('public')) {
            $Schema->createNamespace('public');
        }
    }
}

Register event subscriber. For symfony this can be done in config:

# app/config/services.yml
services:
    doctrineMigrationDiffListener:
        class: EngineBundle\Doctrine\MigrationEventSubscriber
        tags:
            - { name: doctrine.event_subscriber, connection: default }

Works for me, no more useless CREATE SCHEMA public in down migration.

[teohhanhui]

@Melkij What about other schemas? For example when using PostGIS.

[garex]

@teohhanhui see my PR #2490

There is $platform->getDefaultSchemaName() -- then it will be unversal. Currently 'public' is hardcoded value in this solution.

[Melkij]

@teohhanhui I test only public schema. This is just workaround for my project, not complete solution.
I think same hasNamespace + createNamespace needed for any schemas, which is not used in any Entity. And for schemas in search_path setting.

[vudaltsov]

Created a bundle addressing this issue

[isaackearl]

If you are using the Laravel doctrine package, here is a workaround: laravel-doctrine/migrations#51

[alemosk]

If it very old and very hard bug, why not replace constant 'CREATE SCHEMA ' to 'CREATE SCHEMA IF NOT EXISTS ' ?

Not beauty, but will no exceptions.

[Ocramius]

Silent failures are the worst failures Marco Pivetta http://twitter.com/Ocramius http://ocramius.github.com/

…

On Thu, Jul 13, 2017 at 4:17 PM, alemosk ***@***.***> wrote: If it very old and very hard bug, why not replace constant 'CREATE SCHEMA ' to 'CREATE SCHEMA IF NOT EXISTS ' ? Not beauty, but will no exceptions. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1110 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJakEloMbcE7GNgVQTAZ-5UK9C8-EKjks5sNibwgaJpZM4HXQHQ> .

[vudaltsov]

I think, I found a cleaner solution. At least for PostgreSQL.
And it looks like it follows @deeky666's advice in #2490 (comment) since it uses AbstractSchemaManager::getSchemaSearchPaths() through PostgreSqlSchemaManager::determineExistingSchemaSearchPaths().

<?php

declare(strict_types=1);

namespace App\EventListener;

use Doctrine\Common\EventSubscriber;
use Doctrine\DBAL\Schema\PostgreSqlSchemaManager;
use Doctrine\ORM\Tools\Event\GenerateSchemaEventArgs;
use Doctrine\ORM\Tools\ToolEvents;

class FixDefaultSchemaListener implements EventSubscriber
{
    /**
     * {@inheritdoc}
     */
    public function getSubscribedEvents(): array
    {
        return [
            ToolEvents::postGenerateSchema,
        ];
    }

    public function postGenerateSchema(GenerateSchemaEventArgs $args): void
    {
        $schemaManager = $args->getEntityManager()
            ->getConnection()
            ->getSchemaManager();

        if (!$schemaManager instanceof PostgreSqlSchemaManager) {
            return;
        }

        foreach ($schemaManager->getExistingSchemaSearchPaths() as $namespace) {
            if (!$args->getSchema()->hasNamespace($namespace)) {
                $args->getSchema()->createNamespace($namespace);
            }
        }
    }
}

[yurtesen]

I have also faced with this problem.

[greg0ire]

@vudaltsov I don't think this bug happens on anything but Postgres, does it? Maybe create a PR?

[antonmedv]

Any updates?

[Ocramius]

@antonmedv can you provide a failing test case for the DBAL test suite?

[garex]

@igorjacauna I suggest you to switch to hibernate ))

[PedroDiSanti]

Is this bug still occurring while using Symfony 4 + Postgres?
Thanks for any help!

[mneute]

@PedroDiSanti : Yes, this is still occuring

[PedroDiSanti]

@PedroDiSanti : Yes, this is still occuring

Thanks, mate.

[melyouz]

This is still occuring. Even when there are no migrations to generate, the following line is added:

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

Full generated migration content could be found here.

[vasilvestre]

Still occurring, got some fresh news?

[Ocramius]

@vasilvestre pick up #2490 if you can work on it.

[ghost]

Anyone know how to implement #1110 (comment) into a symfony4 project? What yaml file does it go into, and where?

Any help would be awesome.

[vudaltsov]

@pc-trent , https://gist.github.com/vudaltsov/ec01012d3fe27c9eed59aa7fd9089cf7

[ghost]

@vudaltsov - legend thanks!

[Autsider666]

@vudaltsov 2 years later and you're still a legend!

[aleksandrphilippov]

@vudaltsov Any updates?

[TheFox]

@vudaltsov 3 years later and you're still a legend.

[greg0ire]

Notes to self:

• CREATE SCHEMA public appears in the down() method. This means that the desired schema is not considered to have it.
• @vudaltsov's fix consists in copying it from one representation to another, by relying on an event defined in the ORM https://www.doctrine-project.org/projects/doctrine-orm/en/2.11/reference/events.html#postgenerateschema

Doesn't this mean this is actually a bug in doctrine/migrations (since it's building the desired schema)? If yes, should @Melkij / @vudaltsov 's fix be implemented in doctrine/migrations + DoctrineMigrationsBundle?

[myselfhimself]

Thanks!
Very good to fix the following error on PostgreSQL + symfony + doctrine:

 Creating database schema...


In ToolsException.php line 19:
                                                                                                                                                                                                        
  Schema-Tool failed with Error 'An exception occurred while executing a query: SQLSTATE[42P06]: Duplicate schema: 7 ERROR:  schema "public" already exists' while executing DDL: CREATE SCHEMA public  
                                                                                                                                                                                                        

In ExceptionConverter.php line 87:
                                                                                                                              
  An exception occurred while executing a query: SQLSTATE[42P06]: Duplicate schema: 7 ERROR:  schema "public" already exists  
                                                                                                                              

In Exception.php line 28:
                                                                               
  SQLSTATE[42P06]: Duplicate schema: 7 ERROR:  schema "public" already exists  
                                                                               

In Connection.php line 69:
                                                                               
  SQLSTATE[42P06]: Duplicate schema: 7 ERROR:  schema "public" already exists  
                                                                               

doctrine:schema:create [--em EM] [--dump-sql]

[myselfhimself]

Hmm actually it is problematic for doctrine:schema:create, here is a variant fix:

<?php

namespace EngineBundle\Doctrine;

use Doctrine\Common\EventSubscriber;
use Doctrine\ORM\Tools\Event\GenerateSchemaEventArgs;

/**
 * @see https://github.com/doctrine/dbal/issues/1110#issuecomment-255765189
 */
class MigrationEventSubscriber implements EventSubscriber
{
    public function getSubscribedEvents()
    {
        return array(
            'postGenerateSchema',
        );
    }

    public function postGenerateSchema(GenerateSchemaEventArgs $Args)
    {
        // This listener helps to prevent generating 'down' migrations trying to remove a 'public' schema for ever
        // however it confuses the doctrine:schema:create command which tries to recreate a 'public' table.
        // The workaround is to run doctrine:schema:create command with the following environment variable
        // set to a non-empty value, then unsetting that variable forever.
        if(empty(getenv('NO_PUBLIC_SCHEMA_CREATE_PLEASE'))) {
            $Schema = $Args->getSchema();

            if (! $Schema->hasNamespace('public')) {
                $Schema->createNamespace('public');
            }
        }
    }
}

And running the doctrine:schema:create command:
NO_PUBLIC_SCHEMA_CREATE_PLEASE=yes php bin/console doctrine:schema:create
But that environment variable is removed for all cases:
php bin/console make:migration

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.