Hacker News Reader

Comment by rick888 | Hacker News Reader

I've done this a few times in my career. I'm smart enough now to always backup before I make any potential mistakes and test everything on a staging server.

The worst was executing "DELETE FROM <table>" instead of "DELETE FROM <table> where userid='X' on a production server.

View on HN

A buddy of mine showed me a cool trick to avoid this type of problem. What you do is first use SELECT to see the records in question:

   SELECT * from some_table where idx >= 5

Then, once you are staring at the records that came back (and are sure they are the ones you want to delete), change SELECT * to DELETE (and change nothing else) and rerun.

   DELETE from some_table where idx >= 5

Pretty hard to get it wrong with this approach ;-)

Or even better, SELECT(*) FROM some_table - that way the developer won't be tempted to skip the step because of too many records scrolling through their screen... not that I learned that from personal experience or anything ;-)

I once updated every user's password to "prettyflowers" or something dumb like that while trying to reset one customer's password.

I realized what I did the instant I hit enter, and all the blood drained from my face.

Thank god for backups.

If I were to re-imagine SQL I'd invert the sequence of the clause and statement for this very reason (FROM TABLE WHERE USERID="X" DELETE).

As it, when I'm writing sensitive SQL to fix an issue I end up writing the where clause first as a SELECT, then issue it.. check the results, history up, ctl-a and replace with a DELETE. Harrowing stuff.

I put in a deliberate syntax error first, and then edit in the actual delete or update query. This protects me from hitting enter too early.

In situations where I can't avoid it (I've come into more projects with no development database and no concept of the idea than I'd like to say), I've taught myself that starting any data modification on a live server always begins with START TRANSACTION.

Reply on news.ycombinator.com