5 ms·
I was on my way out the door at my first job, and an SEO person stopped me and asked me if I could fix something. I just needed to delete a single row from a da
by donatj 9y ago
I was on my way out the door at my first job, and an SEO person stopped me and asked me if I could fix something. I just needed to delete a single row from a database. Easy enough. I get as far as “DELETE FROM table_name” and for some unknown reason run the damn thing without a WHERE. All the rows, gone!
We did have nightly backups but the guy with access to them was notoriously hard to get the attention of and lived in a different state. I was on my way out the door, and now I ended up being stuck there for another few hours trying to get ahold of devops.
At the company I am at now, we had a devops person whipe our multi TB CDN on their first day trying to make an improvement to our asset sync script. It took probably 2 days to get the whole thing reuploaded.
- Spoom 9y agoAlways write DELETEs as SELECTs, and once you run the SELECT and it returns the rows you want, then swap out the SELECT * with a DELETE.
- mfoy_ 9y agoIt sounds silly, but that makes a lot of sense. Lots of tips for me to take away from this thread. :)
- wonderwonder 9y agoThat's a really great idea.
- amyjess 9y agoThat's good advice in general, not just for SQL. For example, I often write my 'rm' commands as 'ls' at first and then swap out the command once I know it gets the right thing.
- fapjacks 9y agoYes, I learned this from a sysadmin in the early 90s and did this when learning to use Unix and having used the technique in various other contexts (e.g. database DELETEs), I am confident this one weird trick has saved me from catastrophic mistakes countless times.
- reddit_clone 9y agoYep. I do that too. Along the same lines I always do an 'echo' before I 'find -exec' anything.
- hinkley 9y agoI count to five before hitting enter. I have stopped on 3 or 4 a number of times. 5 seconds is frequently enough time for your brain to catch up with your fingers. And nobody is going to notice it took you 15 seconds to do something instead of 10.
- jsight 9y agoThat is really good advice. I have also found that it is good to do this with file delete commands (ls before rm).
- colanderman 9y agoAlso, open a transaction first.
- koolba 9y agoThat works halfway for UPDATE statements as well. At the very least you can see which rows you’ll be updating. Another tip is to write the WHERE clause and SET clause prior to filling in which table is being updated. That way you can’t accidentally run something prematurely.
- pc86 9y agoUnless, like me, you then highlight the statement without the WHERE clause and run it, realizing your mistake a split second before you hear the key click...
- Tenhundfeld 9y agoI have a couple of SQL habits along these lines that I haven't seen others do. If I'm typing an ad-hoc DELETE or UPDATE statement, I'll slap the WHERE keyword on the end of the main command line. For example: UPDATE items SET active = 0, deleted_at = now() WHERE category = 'Foo' DELETE FROM items WHERE category = 'Foo' AND customer = 123 Yeah, it looks ugly, and I wouldn't use that formatting for "real code," with tests and whatnot. But for messing around in the query editor, I just find that I'm much more likely to accidentally leave off whole lines. I never accidentally select parts of lines. More generally, the idea is to make your partial query invalid syntax, if you leave off the last line. I also tend to wrap destructive DML in comment blocks. So in my query editor, I'd actually have: /* DELETE FROM items WHERE category = 'Foo' */ The idea here is just that I want to be required to select this text and run it. If I have some other queries in the window and somehow accidentally run all statements in the window (didn't select something I meant to or maybe a query is hiding beneath the scroll line), I don't want destructive queries to run. EDIT: I've actually never made a catastrophic mistake of this nature, but there have been some very close calls. And I've seen it happen several times, from excellent programmers who just made a mistake.
- hinkley 9y agoIt's like gun safety. All the rigor feels stupid until you hear stories about people not following the rules.
- LanceH 9y agoI did this on the users table in production but I was fortunate enough to have started a transaction. I was just about shaking when I typed 'rollback'. The fact that I was entering a delete -- instead of setting to inactive and doing it in production from the command line should give you an idea of how likely the backups would have been effective. This was in '97, though when everything was seat of the pants during the full internet craze.
- MaxBarraclough 9y agoThis 'default' behaviour strikes me as one of SQL's worst faults. If you want to delete all rows, you should be made to explicitly type out that intention.
- donatj 9y agoI'd say require a WHERE on deletes. If you want "WHERE true" that's fine, but require a WHERE.
- JoeMalt 9y agoIf you start the MySQL Monitor with --i-am-a-dummy, this is enforced automatically
- MaxBarraclough 9y agoInteresting, thanks. As far as I can tell, there's nothing like that in Postgres.
- swalsh 9y agoI worked at a public company where write access to the production databases was required to go through an intranet site which logged the query, required a code review id, and limited the number of rows that could be deleted. It was more or less for sox compliance reasons, but it provided a nice bit of safety net around dangerous queries too. It slowed things down, but the forced code reviewer process saved me once in a big way.
- rocky1138 9y ago> We did have nightly backups but the guy with access to them was notoriously hard to get the attention of and lived in a different state. Read up on "bus factor": https://en.wikipedia.org/wiki/Bus_factor https://en.wikipedia.org/wiki/Bus_factor