You are reading a single comment by @Velocio and its replies. Click here to read the full conversation.
  • ...Have no single message anymore...

    Are they coming back? / Will they be restored?

    I really, really, really hope that the messages will be back! - Have no issue if they Sat or Sun is missing, but deleting all is not exactly what I would look for - I just accepted the fact that once in a while I get a spam massage from a spam user...

  • Will they be restored?

    Yes!

    So the messages are still in the database, but I screwed up a query when I nuked the spam.

    For reference a "huddle" is kinda like a direct message conversation.

    The data structure is "huddle exists and contains comments, huddle profiles maps huddles to those who can view them and contribute further messages"... the profiles exist, the comments exist, but when I nuked the spam I figured I had left orphaned huddles so best delete them. I wrote a complex piece of SQL to "find huddles which have only deleted comments, remove the profiles and delete the huddle"... but the bit that is "have only deleted comments" was wrong, and the query went too far and has deleted the huddles for all on this site.

    So... how to repair? Well... just get the valid huddles and huddle profiles bits back from a backup.

    Where are my backups? Tarsnap.

    I've been pulling a backup all day, but I back up the whole database and so far it's at 14GB downloaded and decrypted, and then I need to pull it somewhere and restore the two tables, find the rows to be copied back and then put them back.

    And lesson learned? Don't delete spam manually even when I don't have access to test environments... ban first (safe) and delete later with a proven and tested script.

  • Deleting / updating too much data in prod. Been there, done that :)
    After talking some more experienced devs, they did it too. But now I always wrap the whole thing in a transaction that rolls back immediately. Something like this:

    begin tran
     // select stuff before
     // do delete / update
     // run the same select to check and check number of rows affected
    rollback tran
    

    And re-run with commit if it's ok.

About

Avatar for Velocio @Velocio started