4 ms·

> 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 i

by pmlnr 7y ago

> 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.

Etheryte 7y ago
Building datasets up via migration sets, not manual updates, and storing source data will solve both of those issues. If you also need fresh data, store snapshots which you should be doing anyway. Shortly put, with a thought out approach I don't see this as a bigger issue than anything else on the list.
grey-area 7y ago
This is addressed in the article (in passing). You split the changes up - 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.
- B-Con 7y ago
  Pretty much this. 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.)
  - tonyarkles 7y ago
    And to add to this, one of brutal anti-rollback gotchas I’ve seen on smaller projects (not distributed systems failures, but code bugs that put you in a painful spot): not having transactional DDL for your schema changes. 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.
    - marcosdumay 7y ago
      Ideally, if you are not sure you have any environment equal to prod, you clone prod first, apply your schema migration there, install your applications and run their tests.
    - deleted 7y ago
      [deleted]
    - evanelias 7y ago
      In my experience this is rare, but I suppose it depends what you mean by "Migration blows up in prod". Do you mean the ALTER TABLE is interrupted (query killed, or server shutdown unexpectedly)? Or do you mean you're bundling multiple schema changes together, and one of them failed, which is causing issues due to lack of ability to do multiple DDL in a single transaction? 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.
- C1sc0cat 7y ago
  This absolutely! I used to see the high level plans at BT for the at the time biggest IBM system DB2 in this case. 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"
- bbatha 7y ago
  I have a simple mantra for this that covers this and other situations “always release readers before writers”.
evanelias 7y ago
fwiw, in this case the author (Rachel) was a very senior production engineer on Facebook's cross-functional Web Foundation team, which worked closely with the db ops team on many occasions. A huge amount of automation existed around both schema changes and dataset migrations, so rollbacks generally weren't a problem.
BurningFrog 7y ago
My answer to this is that it's why we have backups.
parliament32 7y ago
Databases suck for this, but don't forget "restore from backup" can be a viable rollback option. Apart from that you can do stupid SRE tricks like copying tables around in-memory before schema changes.

by pmlnr 7y ago

Etheryte 7y ago

Building datasets up via migration sets, not manual updates, and storing source data will solve both of those issues. If you also need fresh data, store snapshots which you should be doing anyway. Shortly put, with a thought out approach I don't see this as a bigger issue than anything else on the list.

grey-area 7y ago

This is addressed in the article (in passing). You split the changes up - 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.

B-Con 7y ago
Pretty much this. 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.)
- tonyarkles 7y ago
  And to add to this, one of brutal anti-rollback gotchas I’ve seen on smaller projects (not distributed systems failures, but code bugs that put you in a painful spot): not having transactional DDL for your schema changes. 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.
  - marcosdumay 7y ago
    Ideally, if you are not sure you have any environment equal to prod, you clone prod first, apply your schema migration there, install your applications and run their tests.
  - deleted 7y ago
    [deleted]
  - evanelias 7y ago
    In my experience this is rare, but I suppose it depends what you mean by "Migration blows up in prod". Do you mean the ALTER TABLE is interrupted (query killed, or server shutdown unexpectedly)? Or do you mean you're bundling multiple schema changes together, and one of them failed, which is causing issues due to lack of ability to do multiple DDL in a single transaction? 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.
C1sc0cat 7y ago
This absolutely! I used to see the high level plans at BT for the at the time biggest IBM system DB2 in this case. 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"
bbatha 7y ago
I have a simple mantra for this that covers this and other situations “always release readers before writers”.

evanelias 7y ago

fwiw, in this case the author (Rachel) was a very senior production engineer on Facebook's cross-functional Web Foundation team, which worked closely with the db ops team on many occasions. A huge amount of automation existed around both schema changes and dataset migrations, so rollbacks generally weren't a problem.

BurningFrog 7y ago

My answer to this is that it's why we have backups.

parliament32 7y ago

Databases suck for this, but don't forget "restore from backup" can be a viable rollback option. Apart from that you can do stupid SRE tricks like copying tables around in-memory before schema changes.