Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

I Accidentally Deleted All Our Data

taylor.fausak.me

Post reply on HN
Print view View original

61–70 of 78 posts

Re: I Accidentally Deleted All Our Data

#61

Post by biot »

taylorfausak wrote: post #22
latchkey wrote: post #17

"We had a backup from earlier in the week" week? That seems kind of off. I'd suggest setting up backups more often than that, especially for production data. Sorry to hear about that. I destroyed a drive once a long time ago (~1994) as well. Happens to the best of us.

Because of this, we're going to back up parts of our database more frequently. A lot of our data isn't mission critical, so it wouldn't make sense to do daily backups for it. Other things, like family accounts, obviously are. We're going to back those up daily from here on out.

At work, our production databases are incrementally backed up every 2 hours with a full backup daily. The performance impact to the server is negligible and if little has changed the backup is small.

Re: I Accidentally Deleted All Our Data

#62

Post by redler »

anujkk wrote: post #6

Although direct database update isn't recommended in SAP, still for some strange reasons it was needed in one of the projects to modify a wrong entry done by functional consultant. One of my fellow programmer was assigned this task and this is what she did - Update set = instead of Update set = where The code was executed on development server(thankfully) and it created a big mess. Full day work of 5 guys was lost. P…

We always try to wrap documentation, procedure and ceremony around the occasionally necessary direct bulk update. Test in dev. Test in staging. All good. But inevitably there's that one-off special case; it's critical, there are immediate business implications, perhaps the hair of one's biggest customer is on fire. And it's late.

So this happens, and I devise a complex, clever, surgical, just-this-once query. Focused on the correctness of the cleverness, naturally I miss the elided WHERE clause deep within the SQL of a derived table. I run the query, and then spend several reflective hours restoring from backup and writing angry SQL to make things be as they were.

Having learned my lesson, I meticulously document the incident and the recovery steps. I'll certainly never make that mistake again, but I'll be magnanimous and help the next poor fool. A few months later I'm facing the same situation. This time, thank goodness, I have a clear process well documented. There's even big red warning text. A little copying and pasting of that clever SQL, execute the query, and -- what the...?!

Like a virus, the bad query with the missing WHERE clause had worked its way into my documentation of the event -- as the good code. It was convenient to have the restoration queries ready to go, shaving a bit of time off the two hour restoration process. Never assume you're not the next poor fool, I guess.

Re: I Accidentally Deleted All Our Data

#63

Post by ams6110 »

soult wrote: post #51
mootothemax wrote: post #33

Potential lifesaver for anyone using MySQL, you can start the client with --i-am-a-dummy to prevent DELETE or UPDATE statements without a WHERE clause: http://sql-info.de/mysql/notes/I-am-a-dummy.html

I don't know how to do this for the MySQL client, but a very useful trick for your the PostgreSQL client is to turn autocommit off, which basically turns your session into a transaction that you have to manually commit before closing the client. So if you do a "DELETE FROM important_data WHERE condition_that_is_always_true" or something and it says "Removed 9001 rows" you can just ROLLBACK and your data is still save…

Oracle works this way, too. I fact I don't believe there is any such ridiculous feature as "autocommit" in Oracle, that is only something that might happen in a client application. Closest thing I can think of is that sql*plus will perform a commit upon normal exit.

Re: I Accidentally Deleted All Our Data

#64

Post by sibsibsib »

kingkilr wrote: post #31
JonnieCache wrote: post #28

OUCH. Doesn't django let you check if models are valid without saving them? In rails I can do: Family.all.select {|f| !f.valid?}

Django's ORM does (obj.full_clean()), but he's not using Django's ORM, he's using mongoengine, I have no idea if it does.

mongoengine does do validation. It can be disabled on a per-model basis though.

Re: I Accidentally Deleted All Our Data

#65

Post by jeltz »

jrockway wrote: post #43
joevandyk wrote: post #35

Earlier quoted context omitted.

Watch out for database locks if you do this. I once opened a transaction, did an ALTER TABLE, and the site hung until I either committed or rolled back the transaction.

Yeah. Changing the schema is not something I'd do with code running against the database. Also, my database typically refuses to start a transaction if the entire database is locked, and my application handles failing transactions by waiting a while and retrying.

Assuming you have tested the deploy script altering the schema to check that the transaction locking tables does not take too long then there is no reason to take down the application. In my personal experience this is the case for virtually all of our changes of tables.

Re: I Accidentally Deleted All Our Data

#66

Post by cloudwalking »

soult wrote: post #51
mootothemax wrote: post #33

Potential lifesaver for anyone using MySQL, you can start the client with --i-am-a-dummy to prevent DELETE or UPDATE statements without a WHERE clause: http://sql-info.de/mysql/notes/I-am-a-dummy.html

I don't know how to do this for the MySQL client, but a very useful trick for your the PostgreSQL client is to turn autocommit off, which basically turns your session into a transaction that you have to manually commit before closing the client. So if you do a "DELETE FROM important_data WHERE condition_that_is_always_true" or something and it says "Removed 9001 rows" you can just ROLLBACK and your data is still save…

  SET autocommit=0;

Re: I Accidentally Deleted All Our Data

#67

Post by charliesome »

taylorfausak wrote: post #36
JonnieCache wrote: post #28

OUCH. Doesn't django let you check if models are valid without saving them? In rails I can do: Family.all.select {|f| !f.valid?}

Django offers a validate function that does exactly that. Unfortunately, the email uniqueness constraint is handled in our save function, due to some backend complexity. So it wouldn't have helped here. Plus, it was a typo that caused the problem, not the function :)

I'm struggling to think of what kind of backend complexity would make it infeasible to check email uniqueness in validation.

Care to elaborate?

Re: I Accidentally Deleted All Our Data

#69

Post by jakejake »

I try to always keep in mind that you can deal with any error except data loss. Any bug can be fixed but if data is gone there is absolutely nothing you can do. It's pretty easy to forget about keeping good backups because it's not particularly exciting. Testing regularly to make sure you can recover from backups will give you a lot of peace of mind.

With that in mind, I'm going to do a test restore from our backups right now!

Re: I Accidentally Deleted All Our Data

#70

Post by karmajunkie »

codypo wrote: post #5

Ha, this is actually pretty funny in retrospect. There I was, giving a talk on all of this great stuff we do with our data, having no idea that a good portion of that data had just vanished. The universe has many potent mechanisms for keeping us humble.

Something like this happened to me several years back... I accidentally deleted a table full of emails out of a spam appliance when I thought I was in my development window. Thanking my stars it happened at 5pm on a Friday afternoon, took me the entire weekend to reconstruct the database. Ever since then I set my production terminal backgrounds to bright red so I can't miss what window I'm in :)
Post reply on HN