7 ms·
It’s fine, Rewind: Revert a migration without losing data
- joshstrange 5y agoThis is pretty awesome. I've been super happy with my move to PlanetScale so far and they keep improving the product and adding things I didn't even think were possible (or at least easy/viable).
- robertlagrant 5y agoDoes it only work if no data has been written to the new structure? E.g. drop a column, and new record comes in without that column? What then?
- shlomi-noach 5y agoEngineer at PlanetScale here: it _does_ work if data has been written to the new structure! In your scenario, you drop a column, populate some new rows in the new structure. Then, you regret the migration and rewind. You get the column back with all the pre-dropped values, AND you get to keep all the new rows which you've inserted. The values for the now-restored column for the newly inserted rows is the DEFAULT value per column definition.
- robertlagrant 5y agoAnd now I have a follow-up! So what if you upgrade your data structure, add a new required column and a record is inserted. You then roll back, removing the column, in order to fix a bug. When you roll forward again, is that removed/readded column's data for the inserted record still there?
- shlomi-noach 5y agoSo what you are describing here is Rewind-the-Rewind. We have built the support for this in OSS Vitess, and the answer would be "yes", you would roll forward and regain the new column with data again. But this is not (yet?) supported in PlanetScale.
- asleepawake 5y agothat’s awesome but when would it be useful? wouldn’t that lead to data loss?
- rorymalcolm 5y agoIf you drop a 'title' column from a users table, for example, you can revert and have the title column reappear with the dropped data, new users added during this time (while the column was dropped) will not have a title.
- codingdave 5y agoJust to clarify my understanding - If we extend that scenario a bit to dropping a title column and at the same time adding a foo column. Then add rows with data in foo. Then revert. Do you lose the foo data? Alternatively, can we separate those actions out? Drop columns in one migration. Add columns in another. Add rows and data. Then revert only the migration where columns were dropped, keeping the more recent adds?
- shlomi-noach 5y agoRight. So if you're both adding one column and removing another, then the revert will lose your new column and will regain your old column. Normally, you deploy DB and app in steps. E.g. if your migration adds a new column, your app is not yet aware of the column (or else your app would break). The moment the migration completes, your app is still on the not-knowing state. It takes an app deployment to actually start utilizing the new column. If you do that, and then want to revert -- you will lose any new data you've added to the new column. In my experience, when a schema migration goes wrong, it goes wrong with a bang. It takes seconds to maybe one minute until pagers are alarming. So I'd say in a common scenario you will not get to deploy your app with the new column awareness, because you'll have realized the migration was bad right away. > Alternatively, can we separate those actions out? Drop columns in one migration. If you choose to do that, then you're on safer grounds; it costs you some wall clock time, because migrations do take a while to complete on medium to large tables. Do note that Rewind only lets you rewind your most recent deployment (PlanetScale's app will not let you run the next migration before you've committed to, or have rewinded, the previous one).
- robertlagrant 5y agoMakes sense! Thanks. And congratulations.
- mnapoli 5y agoThis is pretty incredible TBH
- ZeroCool2u 5y agoI’m not really the target audience for PlanetScale, but this is still pretty damn cool. This is the link where they go into detail about the mechanism that enables this feature. https://vitess.io/docs/13.0/reference/vreplication/vreplication/ https://vitess.io/docs/13.0/reference/vreplication/vreplicat... Maybe GitHub should look into migrating to PlanetScale for their mysql1 cluster that keeps going down this week? Unless PlanetScale uses GitHub and that would introduce a circular dependency. Eh, it’s turtles the whole way down either way I suppose.
- gtowey 5y agoThey already do. Not for that cluster, but large tranches of data are being logically separated fr there then physically moved to separate Vitess-based clusters. It takes a lot of engineering time to safely separate the data.
- ksec 5y agoIs this similar to Temporal Tables in MSSQL?
- shlomi-noach 5y agoEngineer at PlanetScale -- It is not similar; so temporal tables are about getting a table's dataset at a given point in time in the past, sort of a time machine for your table. Rewind is about undoing a (bad) structural schema change, and _without_ having to go back in time - you continue your current timeline, with your current data, but flipped into the previous schema. It's also not something you need to activate ahead of time, like you do in MSSQL temporal tables; it is activated on your behalf for any schema change you deploy.
- epberry 5y agoP.S. you can do the forward version of this, online schema changes, directly in the Arctype SQL GUI https://arctype.com/blog/changelog/planetscale-exports/ https://arctype.com/blog/changelog/planetscale-exports/. I imagine we'll have support for Rewind too in the future. Game changing feature.
- losvedir 5y agoThis is pretty amazing. What are the restrictions on it working? For example, suppose we had a non-NULL column A which we drop in the migration, and new records come in without A data. That works on the new table, but if you revert, presumably you would lose those records since they can't be added to the old schema. Does it prevent you from rolling back, or let you roll back to a modified previous schema that allows NULLs, or does it roll back and drop those new records?
- shlomi-noach 5y agoEngineer at PlanetScale; If you drop columns that are `NOT NULL DEFAULT <something>`, and then you insert some new rows to your newly-versioned table, then you're in a good spot: when you revert, those columns will get the DEFAULT value on those rows. If those columns were `NOT NULL` and with no DEFAULT, then you are unable to rewind. The rewind process will make an attempt -- after all, maybe you didn't add new rows; maybe you just deleted or updated -- but if you did INSERT new rows, then the Rewind process will fail (and you will be notified that rewind is impossible). There's a couple more interesting scenarios, see this doc page for more: https://docs.planetscale.com/concepts/deploy-requests https://docs.planetscale.com/concepts/deploy-requests
- omegalulw 5y agoThis is why you should put minimal consistency checks in your DB directly. Put them in your storage later instead.
- cesaref 5y agoSo that you can cope with someone messing up a deployment and wanting to roll it back? Let's say you avoid column restrictions in your db design, and you deploy some dodgy code that doesn't fill in a column correctly, now you've got heaps of dubious data cluttering your database, and apps failing because some precondition isn't being met. That doesn't sound like a good compromise to avoid headaches when updating a schema, does it? So as ever, it's about making the right decisions on a case by case basis, and there is no 'one size fits all' for stuff like this.
- danielovichdk 5y agoDo you backup the present schema and data before the new migration is done ? And then if you rewind, you "simply" point to that backup scehma and data instead of the newly migration? I know it's very simplified, but is this the gist of it?
- shlomi-noach 5y agoIt's not like that -- that BTW is super simple to achieve with either of the existing online schema change tools (pt-online-schema-change, gh-ost, facebook's OSC) -- they all end up with your old table renamed away, and which you can instantly reinstate back in place. Very cool and important feature! But then, you lose data; all the data you've accumulated since the migration completed; or some data you've deleted will suddenly reappear. Rewind does not move you back to an old snapshot, but rather keeps you on your current timeline, with the current data, but with the old schema. Technically, there are two tables involved, yes! And a synching mechanism that compensates for the structural differences between them. But perhaps I should just point to this technical explanation of how this works internally: https://planetscale.com/blog/behind-the-scenes-how-we-built-rewind https://planetscale.com/blog/behind-the-scenes-how-we-built-...
- throwusawayus 5y ago> But then, you lose data; all the data you've accumulated since the migration completed; or some data you've deleted will suddenly reappear. this is not correct, for example pt-online-schema-change has long had a --reverse-triggers option which reverses the direction of the triggers to keep the old table up to date
- throwusawayus 5y ago"We are giving away limited-edition, retro Rewind t-shirts to the first 100 people that successfully revert a schema change." they are encouraging people to record videos of making schema changes and reverting them, in order to win a t-shirt does this seem unnecessarily risky or in really poor taste to anyone else?
- ebiester 5y agoHopefully someone is doing this in testing or staging.
- misterbwong 5y agoI think that's the point. Schema migration is traditionally a _very_ risky thing but they're trying to make it come off as "safe", if you're a PlanetScale customer.
- throwusawayus 5y agoyeah but half the risk is on the application side, completely independent of planetscale! not all applications can handle some schema change occurring and then suddenly reversing. someone may get fired just trying to win a shirt also their exact wording, "successfully", so if it fails your db is broken AND you do not get a shirt?
- rad_gruchalski 5y agoThey don't say it needs to be a production system.
- KwisaksHaderach 5y agoDoes planetscale doesn't have PITR? couldn't find anything in the docs.
- deleted 5y ago[deleted]
- ahachete 5y agoDoes it support migrations that affect more than one table?