migrations
migrations copied to clipboard
doctrine
Version Table in Schema Named After the Connecting User Always Tries to be Re-Created
Bug Report
| Q | A |
|---|---|
| BC Break | no |
| Version | 2.1.1 |
Summary
I've got a postgres database with two schemas: public and {userName} (where {userName} is the name of my connecting user). My migration versions table is in {userName}.migration_versions.
When migrations attempts to figure out whether the table exists, it always determines that it does not exist and attempts to recreate. Which causes an error.
Current behavior
An initial run of a migraitons:* command will create the version table perfectly in the correct schema and such. But subsequent runs will always try to create the table again.
How to reproduce
Create a postgres database with two schemas and put the migration versions table in the non public schema.
I also suspect it's important that the other schema comes before the public in the query that generates the search paths (see below).
Expected behavior
Migrations shouldn't try to recreate the table each time.
DBAL has a bug open for this: https://github.com/doctrine/dbal/issues/1599 and https://github.com/doctrine/dbal/issues/2491, but it seems like this might be expected behavior in DBAL (the table does exist and the search path would make that the case were you to run queries on it).
Maybe a work around would be to use listTableDetails or something like that to determine if the table exists.
I guess you can workaround this by specifying the table name as public.migration_versions. In this way if will not fallback on the postgres search_pathvariable
Old issue, but I got the same one recently, so here is my analysis.
When you connect with username to a postgres db the search path is "$user",public.
The version table is created again because the method used to check if the table exists remove the first schema of the search path. When you're connected with username, the PostgreSqlSchemaManager::_getPortableTableDefinition() remove the username part of the table name because it is the first schema in the search path. Then when it try to check if username.migration_versions is in the list it fail.
You can try to solve defining in doctrine migration config migration_versions instead of username.migration_versions. As migration_versions is the first schema of the path, the table will be created in the correct schema, not in public.
But if you have a second migration table, with the same name, but in the schema public, a new error will appear because retrieving the list of tables, doctrine will call PostgreSqlPlatform::getListTableForeignKeysSQL() and the query will fail because the subquery will returns more than one row.
With the current process to determine if a given table exists, we do not have a correct solution. In my case I'll use something like username.username_migration_versions to get the table in the correct schema but with a unique name accross all schemas.
other possibilities are to update the postgres config to always have public un first on the search path instead of $user, or do not use a schema and a user with the same value.
I was hit by the same bug today (Doctrine Migrations 3.6.0) and can confirm @geelweb's analysis. In case it can be useful, the behavior can be narrowed down to these four cases:
| User / schema names | table_name setting | Finds the table? |
|---|---|---|
| Same | Without schema | Succeeds |
| Same | With schema | Fails |
| Different | Without schema | Fails |
| Different | With schema | Succeeds |
