The setting schema_filter causes migrations to fail

[J4VMC]

Bug Report

Q	A
BC Break	no
Version	2.0.0

Summary

The setting schema_filter causes migrations to fail

Current behavior

When enabling the schema_filter: setting in config/packages/doctrine.yaml and running the bin/console doctrine:migrations:migrate command, it throws the error SQLSTATE[42P07]: Duplicate table: 7 ERROR: relation "migration_versions" already exists

How to reproduce

In a Symfony project with the ORM pack, add schema_filter: ~(_|^)(?!aggregate)(_|$)~ setting to config/packages/doctrine.yaml. Create a table that has aggregate in the name, make a change to an existing entity and then run bin/console doctrine:migrations:migrate.

Not 100% sure if it'd make any difference, but we're using Postgres 10.6 and we can't downgrade it to an earlier version.

Expected behavior

I'd expect Doctrine Migrations to ignore any tables matching the regular expression within the schema_filter setting

[jwage]

Are you running any other commands before migrate? Do you have unexecuted migrations when you get the error?

[J4VMC]

Are you running any other commands before migrate? Do you have unexecuted migrations when you get the error?

No, the commands I'm running are make:entity, make:migration and doctrine:migrations:migrate. No unexecuted migrations either. It also fails when generating the migration.

[J4VMC]

@jwage @alcaeus I've been doing some more research on this. It fails with slightly more complex regex expressions like ~(_|^)(?!aggregate)(_|$)~, but it works fine with very simple ones like ~^(?!aggregate_)~.

I feel this issue is quite important, because in many cases people don't have the option to change the naming convention of the custom tables. Sometimes you'll have a prefix but other times it would be a keyword that you'd like to match or similar, and this case it wouldn't work.

[rubenrubiob]

Hi!

Same problem here, even when using simpler expressions. I am using ~_translation$~ and it fails with the error:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'migration_versions' already exists

[jwage]

If someone can provide a failing test that would be helpful.

[jrmgx]

I had the same problem and it seems to be the $ char that is problematic, removing it solve the bug

[tmsnvd]

Sadly it does not. Env: MySql 8 / PHP 7.3 / symfony 4.3

doctrine.yaml: doctrine: dbal: schema_filter: ~^acc_~

Running php bin/console doctrine:migrations:diff it fails with SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'migration_versions' already exists

Of course, the table exists and without schema_filter migration works fine.

[edaubert]

Hi,

I ran on the same issue. After some digging, I found that adding migration_versions on the schema_filter solved it.

Is it possible that you also need to add the table migration_versions on the schema_filter ?

Hope it helps.

[icetee]

The situation is that two are on different abstraction layers. Call it first Doctrine\DBAL\Schema\AbstractSchemaManager -> filterAssetNames function. Remove any element that is not true from regExp.

So there is no chance to solve only this in migrations lib.

[stof]

@jwage @alcaeus maybe doctrine/migrations should temporarily unset the asset filter configuration while checking whether the migration_versions table exists. Otherwise, things break when the migration_versions table is excluded by the filter.

[alcaeus]

@stof yes, that's probably a sane choice. If anyone of the people involved feels comfortable creating a PR (it's Hacktoberfest after all), I'd be happy as it'll take me a while to get to it.

[serrvius]

if someone needs to allow and exclude tables in one row you can use the regexp like this: ~^(?!(api_messenger_messages|api_crm_user))(api_)~ here we allow only tables with prefix api_, but exclude some specific tables with the same prefix in the first part you exclude the tables with the same prefix then you allow all others: '~^(?!(<listOfExcludesSeparatedByPipe>))(prefixOfAllowTables)~'

maybe it will help someone :)