> Rollbacks need to be possible
I always feel like people who write these never faced SQL schema changes or dataset updates. I wonder what rollback plans are in place for complete MySQL replication chains, for example.
I always feel like people who write these never faced SQL schema changes or dataset updates. I wonder what rollback plans are in place for complete MySQL replication chains, for example.
First make sure your code handles both old and new schemas
Then introduce the schema change
Then introduce the code which depends on the schema
Each of these steps is performed separately and monitored for problems, with rollback possible at each stage. The most painful thing to rollback is the database, but it is possible, though rarely necessary if done in isolation and tested against the old code first.
There may be a few more steps after if you want to tidy up the schema by removing old data etc. It's trading off complexity in development/deploy for reliability.
Writing code that can handle both old and new schema is admittedly annoying. But it's safe and forces a thoughtful rollout.
Even data mutations can be rolled back. Dual storage writes, snapshots, etc.
The goal isn't to eliminate risk, it's to reduce the risk and make it calculated and bounded. Heck, having a rollback plan that says "we'll restore a snapshot and lose X minutes worth of data mutations" is fine in some cases. It's tradeoffs.
(I've seen a case where literally adding a column caused an outage, because the presence of the column triggered a new code path. Rollback was to delete the column.)
The typical scenario I’ve had to help with: MySQL database that’s been running in prod for a while. Because of the lax validation checks early in the project, weird data ends up in a column for just a couple rows. Later on you go to make a change to that column in a migration, or use the data from that column as part of a migration. Migration blows up in prod (it worked fine in dev and staging due to that particular odd data not being present), and due to the lack of transactional DDL, your migration is half applied and has to be either manually rolled back or rolled forward.
The former case is infrequent in real life. Users with smaller tables don't encounter it, because DDL is fast with small tables. Meanwhile users with larger tables tend to use online schema change tools (e.g. Percona's pt-osc, GitHub's gh-ost, Facebook's fb-osc). These tools can be interrupted without harming the original table.
Additionally, DDL in MySQL 8.0 is now atomic. No risk of partial application if the server is shut down in the middle of a schema change.
The latter case (multiple schema changes bundled together) tends to be problematic only if you're using foreign keys, or if your application is immediately using new columns without a separate code push occurring. It's avoidable with operational practice. I do see your point regarding it being painful on smaller projects though.
ALTER TABLE user ADD COLUMN country; -- oops
CREATE TABLE user_email (blah blah);
-- process here to move data from user.email column to user_email table. This blows up because someone has a 256-byte long email address and the user_email table has a smaller varchar()
ALTER TABLE user ADD state_province;
With e.g. Postgres, you wrap the whole thing in a transaction and be done with it. It gets committed if it succeeds, or gets rolled back if it fails.
Otherwise, if the table is quite large, the DML step to copy row data would result in a huge long-running transaction. This tends to be painful in all MVCC databases (MySQL/InnoDB and iiuc Postgres even more so) if there are other concurrent UPDATE operations... old row versions will accumulate and then even simple reads slow down considerably.
A common solution is to have the application double-write to the old and new locations, while a backfill process copies row data in chunks, one transaction per chunk. But this inherently means the DDL and DML are separate, and cannot be atomically rolled back anyway.
It's admittedly been a long time since I've used MySQL for anything significant, but I feel like teams in the past have run into issues where DDL operations on their own have succeeded in dev/staging and failed in prod, even though they're running on the same schema. Simply due to there being "weird" data in the rows. I don't know that for sure though. If I'm remembering right, one of those had something to do with the default "latin1_swedish_ci" vs utf8 thing...
They planed changes to the system years in advance in lock step with changes to the physical plant and incrementally introduced changes.
eg release 55 and 56 would introduce stuff that would be used 6 months later in release 58 "Caller ID works on every phone in the country"
Apart from that you can do stupid SRE tricks like copying tables around in-memory before schema changes.