support for "ALGORITHM=INPLACE, LOCK=NONE;" in migrations #44757
Replies: 4 comments 2 replies
-
I learned about this the hard way by running the migration to add indexes to a table in the middle of the day 😂 |
Beta Was this translation helpful? Give feedback.
-
This was brought up previously in #31530 but was closed. Really hope these options are added. It’s essential for performing safe migrations via CI/CD in enterprise environments. Some more background in this Planetscale article: https://planetscale.com/blog/zero-downtime-laravel-migrations. Their current recommendation Planetscale basically advises not to use php artisan migrate in live production because of this:
|
Beta Was this translation helpful? Give feedback.
-
To be fair, this than only helps for indexes. If you want to do other migrations it still gets locked. Using pt-online-schema-change or gh-ost you can omit downtime. Also, I think recent versions of MySQL/MariaDB migrate indexes without doing a full table lock. |
Beta Was this translation helpful? Give feedback.
-
@rikvdh Are you sure about that? My understanding is that the algorithm and lock flags can apply to any db alteration, not just adding indexes. And if the specified algorithm can’t be applied to the requested operation, MySQL won’t let it run at all. MySQL 8 online DDL operations: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html So by setting certain flags we can silently fail (i.e prevent) any migrations that would unknowingly lock the db. This would be ideal for CI/CD because it’s impossible for non-DBAs to know exactly what alterations will cause a lock. Any failed (locking) migrations could then be scheduled for a more appropriate time. Using something like gh-ost, which creates a new version of tables and copies over data in the background, is overkill for most people. And creates a big load on the db. It would be much simpler to use the native functionality MySQL provides to specify whether you want to allow or disallow locks during a migration. That’s what it’s there for. |
Beta Was this translation helpful? Give feedback.
-
While working on big db's with lots of traffic specifying algorithm and lock is usually helps to prevent table locks which could cause outage in prod.
Would it be possible to add those options in migrations ? Or is it suggested to use plain queries for such migrations since it's InnoDB specific?
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
Beta Was this translation helpful? Give feedback.
All reactions