symfony2: multiple DB connections / entity managers with doctrine migrations & schema_filter

Question

I currently have a single database / entity manager symfony2 (2.1) app working just fine, including doctrine migrations. I am in the process of adding a second database connection + entity manager and am having trouble getting doctrine migrations to do what I want.

Essentially, I want most of my entities to live in my first, 'default' database, and have several new entities / tables in my second database - and then have doctrine migrations manage any changes to the schemas. The documentation seems to imply you can set a 'schema_filter' on the connection to achieve this.

(The use case is this: there are several installs of our app for white-labelled resellers, each with their own primary database. However, we are implementing interactive help, which will be written by us in a CMS / blog type interface and available, via this second database, to all installs.)

So far my doctrine configuration looks like this (from app/config/config.yml):

    dbal:
        default:
            driver:   %database_driver%
            host:     %database_host%
            port:     %database_port%
            dbname:   %database_name%
            user:     %database_user%
            password: %database_password%
            charset:  UTF8
            schema_filter: ~^(?!help_)~
        cross_site:
            driver:   %crossite_database_driver%
            host:     %crossite_database_host%
            port:     %crossite_database_port%
            dbname:   %crossite_database_name%
            user:     %crossite_database_user%
            password: %crossite_database_password%
            charset:  UTF8
            schema_filter: ~^help_~

orm:
    auto_generate_proxy_classes: %kernel.debug%
    default_entity_manager: default
    entity_managers:
        default:
            connection: default
            mappings:
                <most of the bundles>
        help:
            connection: cross_site
            mappings:
                HelpBundle: ~

The intention is to have most of the entities / tables in the 'default' database, but exclude any table beginning with 'help_'. Conversely, the second database should contain only those tables beginning with 'help_'.

However, whenever I run a doctrine migration using either entity manager, it will simply include all tables regardless of their name.

php app/console doctrine:migrations:migrate # includes every table including help_*
php app/console doctrine:migrations:migrate --em="help" # includes every table

Am I mis-using schema_filter? Is there another way to achieve my goal?

Update: It's actually worse... after the first migration using the 'help' entity manager, I am no longer able to run additional migrations using --em="help"; I receive this error:

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

Migrations on the original EM continue to work fine. Help?

Answer 1

So, I learned several things which helped me resolve this... posting here in case anyone else gets in to a similar tangle.

1. doctrine:migrations:migrate more-or-less ignores your schema_filter. This is probably what it should be doing... its job is just to run the SQL in your migration files.
2. doctrine:migrations:diff is where you should worry about your schema_filter. It will view the current state of your database as seen through the lens of your schema_filter. Remember to pass the correct entity manager to the diff command as well as the migrate command, via --em=[name].
3. Don't exclude the migration_versions table with your schema_filter. If you do, the doctrine migrations bundle will think it doesn't exist, and always try to recreate it (which will fail).

So, I added two things to more-or-less achieve my goal. In the configuration for my second database, I adjusted the schema_filter to also include the migration_versions table:

            cross_site: 
                schema_filter: ~^(help_|migration_versions)~

I also added the following code to any migration Version files (in app/DoctrineMigrations) that dealt with this second database / entity manager:

$this->skipIf( $this->connection->getDatabase() != '[second DB name]', 'Skipping help database.' );

This ensures that none of the tables that should only be in the second database pollute the original database(s). Note that this should go in both the up() and down() methods.