yii2: Postgresql schema ignored

What steps will reproduce the problem?

Install Yii2 advanced app using composer and run ./init.
Create a Postgresql database.
Create a schema in the database (different from the user default schema so it is not in the search path).
Edit the database config in common/config/main-local.php as follows:

return [
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'pgsql:host=myserver;dbname=mydb',
            'username' => 'user',
            'password' => 'password',
            'charset' => 'utf8',
            'schemaMap' => [
                'pgsql'=> [
                    'class'=>'yii\db\pgsql\Schema',
                    'defaultSchema' => 'myschema',
                ],
            ],
        ],
        ...
];

Run migration: ./yii migrate.

What is the expected result?

Migration and user tables should exist under myschema in the database.

What do you get instead?

Tables are created under a different schema (the one that I assume is the default for the user - I don’t have admin rights to the database).

Additional info

Using a controllerMap directive I can get the migration table created under the correct schema (haven’t found out how to do that with the user table yet):

    'controllerMap' => [
        'migrate' => [
            'class' => 'yii\console\controllers\MigrateController',
            'migrationTable' => "myschema.migration",
        ],
    ],

But without specifying a controllerMap for user then that table is still created under the wrong schema.

Q	A
Yii version	2.0.9
PHP version	5.5.38 / 7.0.10
Operating system	MacOs X (El Capitan)

About this issue

Original URL
State: open
Created 8 years ago
Reactions: 1
Comments: 45 (37 by maintainers)

Most upvoted comments

@thomaslindgaard for temporary solution you may configure db component like this:

'db' => [
    'class' => 'yii\db\Connection',
    'dsn' => 'pgsql:dbname=xxx;host=127.0.0.1;',
    'username' => 'xxx',
    'password' => 'xxx',
    'charset' => 'utf8',
    'schemaMap' => [
        'pgsql' => [
            'class' => 'yii\db\pgsql\Schema',
            'defaultSchema' => 'myschema',
        ],
    ],
    'on afterOpen' => function ($event) {
        $event->sender->createCommand("SET search_path TO myschema;")->execute();
    },
],

MaksimKiselev on Oct 18, 2016

Are you sure this is fixed? I stumbled on this again today, with yii2-2.0.27. I applied the migration for the queue db table with ./yii migrate and the queue and migration table were created in the public schema, instead of the one in my user. My db.php contains:

    <?php
    return [
        'class' => 'yii\db\Connection',
        'dsn' => 'pgsql:host=localhost;dbname=db',
        'username' => 'sensor',
        'password' => 'xxxx',
        'charset' => 'utf8',
        'emulatePrepare' => false,
        'enableSchemaCache' => !YII_DEBUG,
        'schemaMap' => [
            'pgsql' => [
                'class' => 'yii\db\pgsql\Schema',
                'defaultSchema' => 'sensor',
            ]
        ],
    ];

So, the tables were created in the public schema instead of sensor.

lourdas on Oct 1, 2019

Any thoughts on what the right approach is to fix this?

Considering defaultSchema is defined in yii\db\Schema (base class, not yii\db\pgsql\Schema), it seems to me that if those are defined, yii\db\QueryBuilder should be respecting their values, rather than leaving it up to yii\db\pgsql\QueryBuilder to override a bunch of methods.

brandonkelly on Jul 24, 2017