"relation "migration" does not exist" during a migration

Yii 2.0 General Discussions
#1

I’m trying to update an existing Yii site, and I need to execute a DB migration.

I’ve added the migration by yii migrate/create command, and execute SQL within it.

When I run yii migrate, the migration content itself executes all right, I can see the new tables in the database.

But the migration command ends with an error

Exception 'yii\db\Exception' with message 'SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "migration" does not exist
LINE 1: INSERT INTO "migration" ("version", "apply_time") VALUES ($1...

One of the last frames of the backtrace is the execution of function MigrateController::addMigrationHistory.

At the beginning of that function I add:

yii\helpers\Console::output(print_r(['all tables' => $this->db->createCommand("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")->queryAll()], true));

When I run the migration, I get all the tables printed out, including migration.
Still, the last statement in MigrateController::addMigrationHistory – ->execute() – throws the said exception.

What could be the problem here?

The whole stack trace:

Stack trace:
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1303): PDOStatement->execute()
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute()
#2 /var/www/html/vendor/yiisoft/yii2/console/controllers/MigrateController.php(295): yii\db\Command->execute()
#3 /var/www/html/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(759): yii\console\controllers\MigrateController->addMigrationHistory()
#4 /var/www/html/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(216): yii\console\controllers\BaseMigrateController->migrateUp()
#5 [internal function]: yii\console\controllers\BaseMigrateController->actionUp()
#6 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#7 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#8 /var/www/html/vendor/yiisoft/yii2/console/Controller.php(182): yii\base\Controller->runAction()
#9 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): yii\console\Controller->runAction()
#10 /var/www/html/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction()
#11 /var/www/html/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction()
#12 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): yii\console\Application->handleRequest()
#13 /var/www/html/yii(23): yii\base\Application->run()
#14 {main}
#2

relation "migration" does not exist means that table cannot be found by postgresql
Reason can be the table was dropped or that it is in the wrong schema and Postgres cannot find it in the search path.

Do you use multiple schema? Can you verify it exist with PGAdmin4?

#3

I can see all the tables in PostgreSQL console.

If I list all the tables in the database with \dt, I can see all the app tables, and the migration table, and the newly created tables (with the new migration), and they are all listed with the same scheme public.

In MigrateController::addMigrationHistory, this lists migration as well:

yii\helpers\Console::output(print_r(['all tables' => $this->db->createCommand("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")->queryAll()], true));

But this throws the same exception:

yii\helpers\Console::output(print_r(['one history migration' => $this->db->createCommand("SELECT * FROM migration LIMIT 1")->queryAll()], true));
#4

Can you post connection configuration removing sensitive data and renaming anything sensitive?

#5

This is interesting. I can see that error is mentioned in multiple places on SO, most common reason is that the table was created with quoted name and then it’s referenced unquoted which for PGSQL are two different tables, but I’m not sure if it works the same the other way around. It would be nice to check if INSERT INTO migration (... works in opposite to INSERT INTO "migration" (... what you are showing right now from the error logs because it looks like table is created but cannot be accessed for the insert.

#6

In PGSQL table is quoted if it is in Capital letters. But a table name in small letters it does not really matter whether it is quoted or not

#7

Oh, didn’t know that, cheers. So my guess is wrong. @jaanise please provide also Yii and Postgres versions you are using.

1 Like
#8

PostgreSQL 13.7, Yii 2.0.45.

The DB config:

return [
    'components' => [
        'db' => [
            'class' => yii\db\Connection::class,
            'dsn' => 'pgsql:host=dev_db_host;dbname=ourdbname',
            'username' => 'ourdbname',
            'password' => 'ourpass',
            'charset' => 'utf8',
        ],

Adding quotes gave the same error when running this within addMigrationHistory:

yii\helpers\Console::output(print_r(['one history migration' => $this->db->createCommand("SELECT * FROM \"migration\" LIMIT 1")->queryAll()], true));

But I found the culprit.

The migration I was doing was a custom SQL file, executed by Yii::$app->db->pdo->exec($sql);, retrieved from pg_dump and stripped of a lot what’s not needed. It contained the following statement:

SELECT pg_catalog.set_config('search_path', '', false);

When I removed that as well, the migration completed successfully.

2 Likes