4 ms·

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. T

by rick888 15y ago

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.

pavel_lishin 15y ago
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.
YogSothoth 15y ago
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 ;-)
- mootothemax 15y ago
  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 ;-)
famousactress 15y ago
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.
- ars 15y ago
  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.
pseudonym 15y ago
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.

by rick888 15y ago

pavel_lishin 15y ago

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.

YogSothoth 15y ago

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 ;-)

mootothemax 15y ago
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 ;-)

famousactress 15y ago

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.

ars 15y ago
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.

pseudonym 15y ago

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.