P.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/. I imagine we'll have support for Rewind too in the future. Game changing feature.
It’s fine, Rewind: Revert a migration without losing data
11–20 of 39 posts
Re: It’s fine, Rewind: Revert a migration without losing data
#12Does 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?
Engineer 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 valu…
This is pretty incredible TBH
Re: It’s fine, Rewind: Revert a migration without losing data
#13Earlier quoted context omitted.
that’s awesome but when would it be useful? wouldn’t that lead to data loss?
Engineer at PlanetScale; it will let you go back to safety without data loss, and without making your database inconsistent. If you will indulge a realistic story; I've been through this process multiple times in production. You change a large table via ALTER TABLE; you possibly change a data type, or drop a column, or modify an index. The change takes 5 hours to complete - and things go bad. Testing in staging was g…
Sounds a bit iffy if your prod deployment fails after testing… surely the correct way is to have staging pickup any issues prior to prod
Re: It’s fine, Rewind: Revert a migration without losing data
#14This 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?
Re: It’s fine, Rewind: Revert a migration without losing data
#15This 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…
Engineer at PlanetScale; If you drop columns that are `NOT NULL DEFAULT `, 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
Re: It’s fine, Rewind: Revert a migration without losing data
#16Do 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?
Re: It’s fine, Rewind: Revert a migration without losing data
#17Earlier quoted context omitted.
Engineer at PlanetScale; it will let you go back to safety without data loss, and without making your database inconsistent. If you will indulge a realistic story; I've been through this process multiple times in production. You change a large table via ALTER TABLE; you possibly change a data type, or drop a column, or modify an index. The change takes 5 hours to complete - and things go bad. Testing in staging was g…
Sounds a bit iffy if your prod deployment fails after testing… surely the correct way is to have staging pickup any issues prior to prod
lol.
I mean, yes... but also - have you really never seen a bug make it to production?
Re: It’s fine, Rewind: Revert a migration without losing data
#18Do 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?
It'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-...
Re: It’s fine, Rewind: Revert a migration without losing data
#19Do 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?
It'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 wi…
> 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
Re: It’s fine, Rewind: Revert a migration without losing data
#20Earlier quoted context omitted.
that’s awesome but when would it be useful? wouldn’t that lead to data loss?
If 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.
Just 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?