Migrations fail when slave replication is running significantly behind master

[edhgoose]

Hi,

We have a problem where our build script will fail if run after another longer running migration.

If our build script executes and starts running a long migration it'll randomly (it appears) pick one of our three database instances, where 2 are slaves and one is master. The following can happen:

1. Migration begins on randomly chosen database instance. Let's say the master is picked.
2. Migration is large, and replication is unusually slow so the 2 slaves lag behind.
3. Build completes, but replication still running.
4. A new build (e.g. a hotfix) is kicked off.
5. Migrations runs again, but picks a slave this time - i.e. one of the database instances which is running behind.
6. It checks the migrations, and decides the migration hasn't been run yet because the replication hasn't finished.
7. Build fails because of an integrity constraint - the change it wants to apply has already been (at least partially) applied.

I'd expect, or assume there would be an option, to force the migrations to operate against the master (or a specific) database.

I'm using MySQL in Amazon's RDS if that helps.

Thanks!
Ed

[stof]

Are you using the MasterSlaveConnection of DBAL ?

[edhgoose]

Yes, I believe so. We're using it with Symfony2 and our configuration looks like this:

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver:   "%database_driver%"
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  utf8mb4

and in doctrine_slaves.yml:

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                slaves:
                    readonlyslave2:
                        host:     "%database_host_slave2%"
                        port:     "%database_port%"
                        dbname:   "%database_name%"
                        user:     "%database_user%"
                        password: "%database_password%"
                        charset:  utf8mb4
                    readonlyslave3:
                        host:     "%database_host_slave3%"
                        port:     "%database_port%"
                        dbname:   "%database_name%"
                        user:     "%database_user%"
                        password: "%database_password%"
                        charset:  utf8mb4

[stof]

OK, then I have an idea to fix it. Expect it to be submitted in the next few days

[edhgoose]

Magnificent, thank you very much.