3 ms·

In general I agree, but in MySQL/MariaDB this is quite tricky because you can't do a lot of stuff in transactions; definitely seen problems where someone does;

by arp242 2y ago

In general I agree, but in MySQL/MariaDB this is quite tricky because you can't do a lot of stuff in transactions; definitely seen problems where someone does; -- migrate-2024-08-12-foo.sql create table [..] alter table [..] insert into migrations values ('migrate-2024-08-12-foo'); And the create works, but the alter fails. And then you try to run it again and now the create fails because you have half a migration. Herp derp. And you often can't split those two statements either: that would be "half a migration" that won't work with the code. As far as I know there isn't really a good solution for this. It can be quite a pain. In PostgreSQL and SQLite this is not an issue as you can run all the above in a transaction. You indeed rarely need "if not exists" there.

kgeist 2y ago
For that exact reason, we have a rule that every DDL migration must be a separate migration in the framework. So if migration #2 out of 3 fails, only 1 migration will be marked as completed. If we see migration failed in CI/CD logs, it's immediately clear what exactly failed, and we can quickly revert what has managed to run (by applying the corresponding down migration), to return the DB to the previous valid state (if we decide to abort the release and fix the migrations first). Or we can rerun individual DDL migrations again, if it's a timeout issue. We design migrations in such a way so that new schemas/data never conflict with already running (old) code. And new code is activated only after all migrations completed successfully. So in practice half completed migrations is rarely an issue for us. But yeah, it requires more boilerplate. However, there's fewer surprises, compared to IF NOT EXISTS.
- arp242 2y ago
  There's lots of cases where that doesn't really work, and can't logically work. An obvious example is: drop index old; create index new [..]; There's tons of examples like this where things can kind-of work with enough effort, but also not really – if the query takes 5 minutes without that index it's just going to timeout, so not that different from having broken code, and not much you can do about that too. You really want those two combined to be atomic. And in cases of "create table new; drop table old" using the old table is often 1) a major hassle, and 2) often doesn't actually work all that well as it's not a tested path. Either way, it seems MariaDB does support transactions for all of this if you disable "autocommit mode", judging from their docs? I haven't used MySQL/MariaDB in quite a few years, but I've never seen migrations for them done well without tons of hassle and it's a major reason I've typically recommended PostgreSQL. It's just eliminates an entire class of common headaches and errors.
  - kgeist 2y ago
    For your index example, why not just do: create index new [..] drop index old; At every point, there's always an index to use. We usually have to split releases into several "subreleases": Release #1 (automatic migration step) - alter the table - add a new index which uses new columns (etc.) (code release step) - new code goes live and starts using the new index Release #2 (we call it "post release") - remove the old unused index Automation, CI/CD help here. Yeah, it's somewhat error prone, but you usually get used to it. Not saying it's perfect, but manageable. In any case, you can never make both SQL migration and code release atomic (if you use rolling updates with zero downtime) so you already have to split into such subreleases anyway.
    - arp242 2y ago
      > Yeah, it's somewhat error prone, but you usually get used to it. Not saying it's perfect, but manageable. Of course; I'm just saying this is why people want idempotency and use "if not exists", especially in MariaDB/MySQL land.
  - evanelias 2y ago
    > it seems MariaDB does support transactions for all of this No, DDL cannot be used in a transaction in any version of MySQL or MariaDB. > I've typically recommended PostgreSQL. It's just eliminates an entire class of common headaches and errors That's fair, but it does create new headaches, for example the notion of an invalid index (as described in the post) simply does not exist in MySQL or MariaDB. Index creation does not block concurrent writes, and won't fail mid-stream due to a lock conflict with old row purging (equivalent to PG's vacuum). And if a DDL statement somehow does fail, e.g. due to an ill-timed host crash, it is fully cleaned up -- DDL is atomic, just not transactional. One way to avoid the "half a migration" problem you mentioned up-thread is to use declarative schema management, which doesn't require tracking completion state of "migrations" at all: https://www.skeema.io/blog/2019/01/18/declarative/ https://www.skeema.io/blog/2019/01/18/declarative/
marcosdumay 2y ago
There are tools that will break each statement into a different migration, and apply a larger grouping by the files you create. I have actually never seen people adopt those. And the "enterprise" tools people tend to circle around seem to create more failure modes than it helps solving. But I imagine somebody uses them somewhere. Without those tools, you can break your migration around the failure point and mark the first half as complete. You should have some way to mark them, because you always need some channel for "free" management of a database. Anyway, transactional DDL is great.
stephenr 2y ago
This issue is detectable, at least, to prevent cascading issues (it probably requires manual intervention to resolve though): You have a started and finished columns and/or a state column on your migrations table; Insert into migrations; Run migration foo; Update migrations set finished = now, state = finished where migration = foo; Then your deploy tool refuses to run (and reports an error) when there are any non-finished migrations found. Honestly if your migrations need `if not exist` regularly for basic stuff like creating tables or whatnot something is wrong - it implies you can't rely on other migrations having been performed at which point you have no reasonable way to expect that the end result will be correct.

by arp242 2y ago

kgeist 2y ago

For that exact reason, we have a rule that every DDL migration must be a separate migration in the framework. So if migration #2 out of 3 fails, only 1 migration will be marked as completed. If we see migration failed in CI/CD logs, it's immediately clear what exactly failed, and we can quickly revert what has managed to run (by applying the corresponding down migration), to return the DB to the previous valid state (if we decide to abort the release and fix the migrations first). Or we can rerun individual DDL migrations again, if it's a timeout issue. We design migrations in such a way so that new schemas/data never conflict with already running (old) code. And new code is activated only after all migrations completed successfully. So in practice half completed migrations is rarely an issue for us. But yeah, it requires more boilerplate. However, there's fewer surprises, compared to IF NOT EXISTS.

arp242 2y ago
There's lots of cases where that doesn't really work, and can't logically work. An obvious example is: drop index old; create index new [..]; There's tons of examples like this where things can kind-of work with enough effort, but also not really – if the query takes 5 minutes without that index it's just going to timeout, so not that different from having broken code, and not much you can do about that too. You really want those two combined to be atomic. And in cases of "create table new; drop table old" using the old table is often 1) a major hassle, and 2) often doesn't actually work all that well as it's not a tested path. Either way, it seems MariaDB does support transactions for all of this if you disable "autocommit mode", judging from their docs? I haven't used MySQL/MariaDB in quite a few years, but I've never seen migrations for them done well without tons of hassle and it's a major reason I've typically recommended PostgreSQL. It's just eliminates an entire class of common headaches and errors.
- kgeist 2y ago
  For your index example, why not just do: create index new [..] drop index old; At every point, there's always an index to use. We usually have to split releases into several "subreleases": Release #1 (automatic migration step) - alter the table - add a new index which uses new columns (etc.) (code release step) - new code goes live and starts using the new index Release #2 (we call it "post release") - remove the old unused index Automation, CI/CD help here. Yeah, it's somewhat error prone, but you usually get used to it. Not saying it's perfect, but manageable. In any case, you can never make both SQL migration and code release atomic (if you use rolling updates with zero downtime) so you already have to split into such subreleases anyway.
  - arp242 2y ago
    > Yeah, it's somewhat error prone, but you usually get used to it. Not saying it's perfect, but manageable. Of course; I'm just saying this is why people want idempotency and use "if not exists", especially in MariaDB/MySQL land.
- evanelias 2y ago
  > it seems MariaDB does support transactions for all of this No, DDL cannot be used in a transaction in any version of MySQL or MariaDB. > I've typically recommended PostgreSQL. It's just eliminates an entire class of common headaches and errors That's fair, but it does create new headaches, for example the notion of an invalid index (as described in the post) simply does not exist in MySQL or MariaDB. Index creation does not block concurrent writes, and won't fail mid-stream due to a lock conflict with old row purging (equivalent to PG's vacuum). And if a DDL statement somehow does fail, e.g. due to an ill-timed host crash, it is fully cleaned up -- DDL is atomic, just not transactional. One way to avoid the "half a migration" problem you mentioned up-thread is to use declarative schema management, which doesn't require tracking completion state of "migrations" at all: https://www.skeema.io/blog/2019/01/18/declarative/ https://www.skeema.io/blog/2019/01/18/declarative/

marcosdumay 2y ago

There are tools that will break each statement into a different migration, and apply a larger grouping by the files you create. I have actually never seen people adopt those. And the "enterprise" tools people tend to circle around seem to create more failure modes than it helps solving. But I imagine somebody uses them somewhere. Without those tools, you can break your migration around the failure point and mark the first half as complete. You should have some way to mark them, because you always need some channel for "free" management of a database. Anyway, transactional DDL is great.

stephenr 2y ago

This issue is detectable, at least, to prevent cascading issues (it probably requires manual intervention to resolve though): You have a started and finished columns and/or a state column on your migrations table; Insert into migrations; Run migration foo; Update migrations set finished = now, state = finished where migration = foo; Then your deploy tool refuses to run (and reports an error) when there are any non-finished migrations found. Honestly if your migrations need `if not exist` regularly for basic stuff like creating tables or whatnot something is wrong - it implies you can't rely on other migrations having been performed at which point you have no reasonable way to expect that the end result will be correct.