6 ms·

Supposedly they’re having to basically restore everyone from backups because a system designed to delete old data was a bit more efficient than it should have b

Supposedly they’re having to basically restore everyone from backups because a system designed to delete old data was a bit more efficient than it should have been: https://reddit.com/r/sysadmin/comments/u14qqq/_/i4a0mk8/?context=1 https://reddit.com/r/sysadmin/comments/u14qqq/_/i4a0mk8/?con...

xenadu02 5y ago
Reminder: never delete data for real as your first step. Always mark it deleted along with a time stamp saying when. Then you can hide deleted itemsfrom everything. When a maintenance script goes haywire you can fix the problem quickly. Have a daily job that really deletes records marked deleted after 30 days. If that is too complicated to retrofit then have any mass cleanup script move the records to a CSV file or temporary table. Never ever ever be in a situation where a rogue script or bad SQL WHERE clause means restoring from backups.
- bpp 5y ago
  Works until you get a bug in the deletion job. I've seen exactly this happen.
  - jdbernard 5y ago
    Yeah, the idea is that by expecting the deletion logic you can make it simpler and more rigorously tested than regularly changing business logic or application code. If you organizationally cannot prioritize quality then nothing can help you.
  - hyperman1 5y ago
    You don't even need a bug. Just a wrong system clock. We had a few windows laptops where something caused them to time travel to 8000 years in the future. Then, they'd slowly spend a few hours deleting every local profile, as nobody had logged in to them for 8000 years. Then, they'd do something to their time zone database and travel back 8000 years. When they started the process, it was unstoppable. Trying to modify the system clock to something sane just caused them to depart to the future again, even if disconnected from the network. None of our users was very amused by this behavior, even if everything important was backed up.
    - tetsusaiga 5y ago
      that is almost cartoonishly nightmarish
      - hyperman1 5y ago
        It happened specifically to 1 type of laptop and we only had about 30 of them. So we pulled all of them out of roulation. Then covid struck, so I reformatted most of them with Debian and we gave them away for home schooling. I wonder if I managed to linuxify some kid in the process.
        ghostly_s 5y ago
        Considering the sorry state of videoconferencing on Linux they probably all immediately had Windows reinstalled.
        bityard 5y ago
        Not sure what this means, I've been using video conferencing on my Linux laptop for work on a daily basis for the last 5 years.
        thatwasunusual 5y ago
        Thanks to you, plenty of kids now think they live 8,000 years in the future! :)
        tetsusaiga 5y ago
        Oh man, so this was relatively recent no less. Hopefully you did!
- schrismartin 5y ago
  How does that work around GDPR and other "Right to be forgotten" legislation? Aren't we required to hard-delete this kind of data?
  - lazide 5y ago
    You are within a certain period of time, not ‘instantly’ (depending on the exact situation you are referring to). The script could take that into account (using a shorter period of time or the like)
  - vbezhenar 5y ago
    What is hard deletion? You can restore rows from database files before vacuum runs. You can often restore data from disk sectors. Some people say SSD can remap sectors under your chair and you won't even know that your deleted data is there.
    - Gigachad 5y ago
      The law isn't a technical specification. You have to follow the spirit of the law. A soft deleted_at timestamp wouldn't be following the law in good faith. Having some data stuck in an unmapped section of an ssd would be within the spirit.
      - nwallin 5y ago
        IANAL, but IMHO a soft 'deleted_at' timestamp along with a daily cron job that hard deletes everything with a deleted_at older than 24 hours would fall within the spirit of the law. I agree that just having a deleted_at timestamp and old entries are never pruned would not be a good faith interpretation of the law.
        Gigachad 5y ago
        From what I have seen, there is no requirement for instant deletes. Even emailing a support address and having them manually delete the data is acceptable. Most places using deleted_at never clean up the data from what I have seen though.
- danuker 5y ago
  > or bad SQL WHERE clause Tip: Begin an SQL session with BEGIN TRANSACTION; at the end you can either COMMIT or ROLLBACK.
  - bmurphy1976 5y ago
    Bonus tip: don't even type COMMIT until after you've run a few select queries to verify the data has in fact changed how you expected it to change.
    - beart 5y ago
      super duper bonus tip: Don't execute SQL in a production environment that you just typed out on the fly.
      - mdoms 5y ago
        It's incredibly depressing how common this is in the real world, but I can tell you from experience that this NEVER happens in Atlassian production systems.
      - sergiotapia 5y ago
        I fucked up once and lost 2 hours of customer data. I was so lucky we were a small startup and had daily backups AND the backup was only 2 hours old. I would have been royally fucked. Never making that mistake again. Always use a copy of prod on a staging server and run your queries there for testing.
  - davio 5y ago
    I always liked LIMIT when doing dangerous stuff in prod
    - progman32 5y ago
      For anyone doing this, just keep in mind that chucking on a LIMIT 1 can give you a false sense of security. For example, say you want to drop a single row but forget the WHERE. A LIMIT 1 will return "yep, deleted one row" but it's not the one you wanted (instead, it's whatever row came up first). Better to do the operation in a transaction that you can rollback - that way, you can better validate the results of your operation before committing.
- vbezhenar 5y ago
  What's wrong with restoring from backups? This is one reason they exist after all. I don't think that making a mistake in delete statement is something you would do every week.
  - lonelyasacloud 5y ago
    Could be that they are restoring from backups - just that that restoring from those backups is very very slow. Atlassian would not be the first where resourcing and testing a speedy disaster recovery strategy wasn't given the highest engineering priority.
  - btgeekboy 5y ago
    This whole debacle is Exhibit A. They’re lucky they have a sound backup strategy in place, and that the amount of data lost is appearing to be minimal.
    - wrycoder 5y ago
      Yea, and this whole thread is very informative for a non-db specialist like me!
  - Gigachad 5y ago
    Because you lose all the work done since the issue happened. It's very rarely acceptable to just give up and do a full backup restore unless literally everything is gone. If there was just some bug that cause partial issues, you have to find some other way to fix it.
- justin_oaks 5y ago
  I agree that data must never be "deleted and forever gone" unless you've already been very sure about it a few time. But I would like to warn people about certain implementations of database "soft deletes" that I'm not a fan of. To be clear, I'm talking about the idea of having a "deleted" and/or a "date_deleted" column and using those columns in the WHERE clause to filter out rows that shouldn't be visible. That pattern complicates the table structure, queries, and indexes. It increases table and index size, thus more data has to be sifted through (either table data or index data) to ensure only non-deleted entries are returned. More data to go through means slower queries. It's also really easy for people to write SQL that accidentally leaves the "deleted" column out of the WHERE clause. Then old, irrelevant data is being returned. Accidentally deleting data that needs to be undeleted is usually rare so I don't think people should optimize for it. We should optimize for things that happen frequently. I have dealt with the rare "Oops! I deleted important data!" by restoring from backups and it has worked fine. I think it may be too strong to say you should never be in a position to restore data from a backup. In fact, I think it's important to streamline the restore process. For cases where we know ahead of time that we want to query deleted data I'll move deleted data to another database table that exists solely for maintaining history. For example, an ORDER table will have a DELETED_ORDER table, or an ORDER_HISTORY table. The HISTORY tables can also record data overwritten from updates. These tables take up disk space, but never affect the structure or size of the original table and its indexes. Queries to the original table don't need to be modified to account for soft deletes. To guarantee that things go to the delete/history tables, I'll usually put a trigger on the original table to move data over to the history tables. This way no application-specific code is needed.
  - bartread 5y ago
    > Accidentally deleting data that needs to be undeleted is usually rare so I don't think people should optimize for it. That's very use case dependent. We've made it easy for people to undelete data they've accidentally deleted simply because they used to do it so often and the only people who could get it back were our tech team. We're a devops org so part of our job is of course to support the systems we build, but our time is better spent on building solutions to business problems than to repeatedly providing support for issues that come up all the time. Part of building those systems is of course engineering in solutions that make it hard to screw up, and easy to unscrew when things inevitably do go wrong. No mean feat given our platform dates back over 15 years and still includes a lot of legacy from the time when tech was just a couple of people. I suppose the object lesson here is that edge cases in one system or company can be part of core business in another so it's best not to make too many assumptions.
- m0llusk 5y ago
  This is also true for people cleaning house or going minimal. Put it in a well labeled box and tuck it away somewhere. If it matters then you'll fetch it, otherwise just chuck it when the box gets in the way.
- duxup 5y ago
  Amen. I always default to “soft” deleting whenever possible.
- ignoramous 5y ago
  > Never ever ever be in a situation where a rogue script or bad SQL WHERE clause means restoring from backups. As a second step, restore from the backup at a set frequency. This would force orgs to automate and optimize not just the backup flow but also the restore flow. Tear-down and restore entire systems from backups. Of course, doing so enormously adds to the cost, but when there's an outage, it will pay itself over.
jeffybefffy519 5y ago
Or ransomware got them.
mdoms 5y ago
> This data was from a deprecated service that had been moved into the core datastore of our products. That is very interesting. This implies they are backing off, at least somewhat, from their very aggressive microservice strategy. Perhaps they feel like they have gone too far in decomposing their products.

xenadu02 5y ago

Reminder: never delete data for real as your first step. Always mark it deleted along with a time stamp saying when. Then you can hide deleted itemsfrom everything. When a maintenance script goes haywire you can fix the problem quickly. Have a daily job that really deletes records marked deleted after 30 days. If that is too complicated to retrofit then have any mass cleanup script move the records to a CSV file or temporary table. Never ever ever be in a situation where a rogue script or bad SQL WHERE clause means restoring from backups.

bpp 5y ago
Works until you get a bug in the deletion job. I've seen exactly this happen.
- jdbernard 5y ago
  Yeah, the idea is that by expecting the deletion logic you can make it simpler and more rigorously tested than regularly changing business logic or application code. If you organizationally cannot prioritize quality then nothing can help you.
- hyperman1 5y ago
  You don't even need a bug. Just a wrong system clock. We had a few windows laptops where something caused them to time travel to 8000 years in the future. Then, they'd slowly spend a few hours deleting every local profile, as nobody had logged in to them for 8000 years. Then, they'd do something to their time zone database and travel back 8000 years. When they started the process, it was unstoppable. Trying to modify the system clock to something sane just caused them to depart to the future again, even if disconnected from the network. None of our users was very amused by this behavior, even if everything important was backed up.
  - tetsusaiga 5y ago
    that is almost cartoonishly nightmarish
    - hyperman1 5y ago
      It happened specifically to 1 type of laptop and we only had about 30 of them. So we pulled all of them out of roulation. Then covid struck, so I reformatted most of them with Debian and we gave them away for home schooling. I wonder if I managed to linuxify some kid in the process.
      - ghostly_s 5y ago
        Considering the sorry state of videoconferencing on Linux they probably all immediately had Windows reinstalled.
        bityard 5y ago
        Not sure what this means, I've been using video conferencing on my Linux laptop for work on a daily basis for the last 5 years.
      - thatwasunusual 5y ago
        Thanks to you, plenty of kids now think they live 8,000 years in the future! :)
      - tetsusaiga 5y ago
        Oh man, so this was relatively recent no less. Hopefully you did!
schrismartin 5y ago
How does that work around GDPR and other "Right to be forgotten" legislation? Aren't we required to hard-delete this kind of data?
- lazide 5y ago
  You are within a certain period of time, not ‘instantly’ (depending on the exact situation you are referring to). The script could take that into account (using a shorter period of time or the like)
- vbezhenar 5y ago
  What is hard deletion? You can restore rows from database files before vacuum runs. You can often restore data from disk sectors. Some people say SSD can remap sectors under your chair and you won't even know that your deleted data is there.
  - Gigachad 5y ago
    The law isn't a technical specification. You have to follow the spirit of the law. A soft deleted_at timestamp wouldn't be following the law in good faith. Having some data stuck in an unmapped section of an ssd would be within the spirit.
    - nwallin 5y ago
      IANAL, but IMHO a soft 'deleted_at' timestamp along with a daily cron job that hard deletes everything with a deleted_at older than 24 hours would fall within the spirit of the law. I agree that just having a deleted_at timestamp and old entries are never pruned would not be a good faith interpretation of the law.
      - Gigachad 5y ago
        From what I have seen, there is no requirement for instant deletes. Even emailing a support address and having them manually delete the data is acceptable. Most places using deleted_at never clean up the data from what I have seen though.
danuker 5y ago
> or bad SQL WHERE clause Tip: Begin an SQL session with BEGIN TRANSACTION; at the end you can either COMMIT or ROLLBACK.
- bmurphy1976 5y ago
  Bonus tip: don't even type COMMIT until after you've run a few select queries to verify the data has in fact changed how you expected it to change.
  - beart 5y ago
    super duper bonus tip: Don't execute SQL in a production environment that you just typed out on the fly.
    - mdoms 5y ago
      It's incredibly depressing how common this is in the real world, but I can tell you from experience that this NEVER happens in Atlassian production systems.
    - sergiotapia 5y ago
      I fucked up once and lost 2 hours of customer data. I was so lucky we were a small startup and had daily backups AND the backup was only 2 hours old. I would have been royally fucked. Never making that mistake again. Always use a copy of prod on a staging server and run your queries there for testing.
- davio 5y ago
  I always liked LIMIT when doing dangerous stuff in prod
  - progman32 5y ago
    For anyone doing this, just keep in mind that chucking on a LIMIT 1 can give you a false sense of security. For example, say you want to drop a single row but forget the WHERE. A LIMIT 1 will return "yep, deleted one row" but it's not the one you wanted (instead, it's whatever row came up first). Better to do the operation in a transaction that you can rollback - that way, you can better validate the results of your operation before committing.
vbezhenar 5y ago
What's wrong with restoring from backups? This is one reason they exist after all. I don't think that making a mistake in delete statement is something you would do every week.
- lonelyasacloud 5y ago
  Could be that they are restoring from backups - just that that restoring from those backups is very very slow. Atlassian would not be the first where resourcing and testing a speedy disaster recovery strategy wasn't given the highest engineering priority.
- btgeekboy 5y ago
  This whole debacle is Exhibit A. They’re lucky they have a sound backup strategy in place, and that the amount of data lost is appearing to be minimal.
  - wrycoder 5y ago
    Yea, and this whole thread is very informative for a non-db specialist like me!
- Gigachad 5y ago
  Because you lose all the work done since the issue happened. It's very rarely acceptable to just give up and do a full backup restore unless literally everything is gone. If there was just some bug that cause partial issues, you have to find some other way to fix it.
justin_oaks 5y ago
I agree that data must never be "deleted and forever gone" unless you've already been very sure about it a few time. But I would like to warn people about certain implementations of database "soft deletes" that I'm not a fan of. To be clear, I'm talking about the idea of having a "deleted" and/or a "date_deleted" column and using those columns in the WHERE clause to filter out rows that shouldn't be visible. That pattern complicates the table structure, queries, and indexes. It increases table and index size, thus more data has to be sifted through (either table data or index data) to ensure only non-deleted entries are returned. More data to go through means slower queries. It's also really easy for people to write SQL that accidentally leaves the "deleted" column out of the WHERE clause. Then old, irrelevant data is being returned. Accidentally deleting data that needs to be undeleted is usually rare so I don't think people should optimize for it. We should optimize for things that happen frequently. I have dealt with the rare "Oops! I deleted important data!" by restoring from backups and it has worked fine. I think it may be too strong to say you should never be in a position to restore data from a backup. In fact, I think it's important to streamline the restore process. For cases where we know ahead of time that we want to query deleted data I'll move deleted data to another database table that exists solely for maintaining history. For example, an ORDER table will have a DELETED_ORDER table, or an ORDER_HISTORY table. The HISTORY tables can also record data overwritten from updates. These tables take up disk space, but never affect the structure or size of the original table and its indexes. Queries to the original table don't need to be modified to account for soft deletes. To guarantee that things go to the delete/history tables, I'll usually put a trigger on the original table to move data over to the history tables. This way no application-specific code is needed.
- bartread 5y ago
  > Accidentally deleting data that needs to be undeleted is usually rare so I don't think people should optimize for it. That's very use case dependent. We've made it easy for people to undelete data they've accidentally deleted simply because they used to do it so often and the only people who could get it back were our tech team. We're a devops org so part of our job is of course to support the systems we build, but our time is better spent on building solutions to business problems than to repeatedly providing support for issues that come up all the time. Part of building those systems is of course engineering in solutions that make it hard to screw up, and easy to unscrew when things inevitably do go wrong. No mean feat given our platform dates back over 15 years and still includes a lot of legacy from the time when tech was just a couple of people. I suppose the object lesson here is that edge cases in one system or company can be part of core business in another so it's best not to make too many assumptions.
m0llusk 5y ago
This is also true for people cleaning house or going minimal. Put it in a well labeled box and tuck it away somewhere. If it matters then you'll fetch it, otherwise just chuck it when the box gets in the way.
duxup 5y ago
Amen. I always default to “soft” deleting whenever possible.
ignoramous 5y ago
> Never ever ever be in a situation where a rogue script or bad SQL WHERE clause means restoring from backups. As a second step, restore from the backup at a set frequency. This would force orgs to automate and optimize not just the backup flow but also the restore flow. Tear-down and restore entire systems from backups. Of course, doing so enormously adds to the cost, but when there's an outage, it will pay itself over.

jeffybefffy519 5y ago

Or ransomware got them.

mdoms 5y ago

> This data was from a deprecated service that had been moved into the core datastore of our products. That is very interesting. This implies they are backing off, at least somewhat, from their very aggressive microservice strategy. Perhaps they feel like they have gone too far in decomposing their products.