I Accidentally Deleted All Our Data • Hacker News Board

Re: I Accidentally Deleted All Our Data

#51

Post by soult » Fri, Nov 18, 2011, 10:05 PM UTC

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.

Re: I Accidentally Deleted All Our Data

#52

Post by soult » Fri, Nov 18, 2011, 10:07 PM UTC

sneak wrote: post #50

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

Why is this not the default, with an option to DISable?

Because it's against the SQL standard (not that MySQL cares very much for that anyway). Just like SELECT * FROM table returns all the rows, DELETE FROM table deletes all the rows.

Re: I Accidentally Deleted All Our Data

#53

Post by dools » Fri, Nov 18, 2011, 10:07 PM UTC

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

Years ago I developed the habit of writing WHERE clauses first and then hitting the home key to write the query.

Re: I Accidentally Deleted All Our Data

#54

Post by dagheti » Fri, Nov 18, 2011, 10:30 PM UTC

Duplicate values that should be unique is such a common problem if you don't use constraints.

This is a good example of why ACID and declarative constraints are a very good idea for data management.You can do a bad delete from query if you don't have the proper safeguards (though querying ability of SQL lets you more easily preview your changes), but the initial corruption of the data is an easily avoidable problem.

Re: I Accidentally Deleted All Our Data

#55

Post by kfool » Fri, Nov 18, 2011, 11:00 PM UTC

Version data, like source code.

  $ chd version mydb
  $ chd revert -d  mydb

Neither replication nor backups protect from accidentally deleting data. And restoring from binary logs requires downtime.

http://chronicdb.com/blogs/undelete_from_whoops

Re: I Accidentally Deleted All Our Data

#56

Post by harryh » Fri, Nov 18, 2011, 11:01 PM UTC

You should consider having a backup replica with slaveDelay set to an hour or so. If you ever fat finger something in the future you won't necessarily have to go all the way to your backups.

Re: I Accidentally Deleted All Our Data

#57

Post by matc » Fri, Nov 18, 2011, 11:20 PM UTC

jrockway wrote: post #46

joevandyk wrote: post #37

Earlier quoted context omitted.

It depends, if you're working with a smaller database with a smaller team with a smaller client base, there's not as much harm. There could possibly be more harm in having more ceremony for doing simple things.

I disagree with this. Making a script and testing it against a copy of the database is a good idea in 100% of cases. The only reason you don't do that is because you're lazy and stupid. (I've done this because I'm lazy and stupid.) When you defeat a safety interlock system, expect to be injured.

There is no efficient safety interlock system when modifying data.

Even if confident your test passed, what happens when you later realize it hadn't, and at what cost?

Re: I Accidentally Deleted All Our Data

#58

Post by matc » Fri, Nov 18, 2011, 11:23 PM UTC

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.

Why not?

Re: I Accidentally Deleted All Our Data

#59

Post by rhizome » Fri, Nov 18, 2011, 11:41 PM UTC

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.

A lot of our data isn't mission critical, so it wouldn't make sense to do daily backups for it

disk is cheap enough for daily backups and it's simpler to backup everything than make a big project out of slicing and dicing your backup script.

Re: I Accidentally Deleted All Our Data

#60

Post by orenmazor » Fri, Nov 18, 2011, 11:58 PM UTC

I think almost everybody has a story like this. when I was in grade 12, I got a job as the sysadmin of three linux labs at my highschool. I didn't have a lot of sysadmin experience, but part of grade 12 comp sci was to give students a chance to learn something.

One of my first perl scripts (c'mon. it was 12 years ago) was to adjust something about user accounts. I dont remember what it was, just that the result was deleting the home folder of every single teacher at the school.

a very honest mistake.