Multiple application instances

[calebboyd]

We're getting ready to deploy a schema using umzug for migrations. I was wondering (couldn't find in the docs) if the logs table acted as a mutex in the event the applications were scaled horizontally in front of a single database. eg. ensuring that my-migration.js was only started and completed once even if multiple servers run it against the same database.

[sdepold]

Hi @calebboyd,

if you are running the migrations on two different machines in parallel (or even on one machine twice), you will probably have awkward times, since the migration is only marked as executed, when it finished. So you should make sure to only run the migration once

[jukkah]

@sdepold I'm wondering if migrations should be executed and (un)logged in a single transaction unit or two if storage != DB?

[sdepold]

I think I tried that at some point and noticed that for example structural changes cannot be wrapped (at least not for every db) in a transaction. Can you elaborate on that idea @jukkah ?

[jukkah]

@sdepold You are right, it will not work. At least, in MySQL, DDL statements cannot be rolled back. @calebboyd's problem can be still resolved by locking the storage (file or table) as he originally suggested.

[gvko]

@calebboyd we were facing the same issue and a workaround we found was:

1. have some flag that the application is/is not a MIGRATION_HOST
2. upon deploy run in the following steps:
   • upgrade the app as a MIGRATION_HOST=true (which runs the migrations and updates the DB)
   • kill the app after the migrations executions have finished
   • upgrade the rest of the instances of the as MIGRATION_HOST=false

The whole process is automated. :)

[soulchild]

Maybe we can make this work by locking the SequelizeMeta table FOR UPDATE?

The first process running the migrations will issue a SELECT * FROM "SequelizeMeta" FOR UPDATE query which basically makes all other processes wait until the table has been updated by the first process, seeing the updated data afterwards.

I don't know about other databases, but I think there are equivalent solutions for most of them. At least, I believe that's how Flyway tackles this issue. There are several implementations for all supported databases of the doLock() method which seems to be responsible for locking Flyway's meta table.

[ababich]

Maybe we can make this work by locking the SequelizeMeta table FOR UPDATE?

The first process running the migrations will issue a SELECT * FROM "SequelizeMeta" FOR UPDATE query which basically makes all other processes wait until the table has been updated by the first process, seeing the updated data afterwards.

I don't know about other databases, but I think there are equivalent solutions for most of them. At least, I believe that's how Flyway tackles this issue. There are several implementations for all supported databases of the doLock() method which seems to be responsible for locking Flyway's meta table.

migrations table lock is used successfully in rails migrations for years and I'm truly shocked it is not even an option in Sequelize

[SamSpiri]

I just got this problem as a devops. it's really the wtf moment here. where are the locks? why nodejs comunity is like that most of the time... Gone back into my cave to build another shit tech workaround with some sticks and sticky tape, as I don't know how to code that dumb nodejs...

[gvko]

Alright, guys, finally it's here: db-migrator-distributed. For the lack of a better name, that's what I chose... :D

Enjoy, give feedback and contribute! 🙌