3 ms·
> or bad SQL WHERE clause Tip: Begin an SQL session with BEGIN TRANSACTION; at the end you can either COMMIT or ROLLBACK.
by danuker 5y ago
> or bad SQL WHERE clause
Tip: Begin an SQL session with BEGIN TRANSACTION; at the end you can either COMMIT or ROLLBACK.
- bmurphy1976 5y agoBonus tip: don't even type COMMIT until after you've run a few select queries to verify the data has in fact changed how you expected it to change.
- beart 5y agosuper duper bonus tip: Don't execute SQL in a production environment that you just typed out on the fly.
- mdoms 5y agoIt's incredibly depressing how common this is in the real world, but I can tell you from experience that this NEVER happens in Atlassian production systems.
- sergiotapia 5y agoI fucked up once and lost 2 hours of customer data. I was so lucky we were a small startup and had daily backups AND the backup was only 2 hours old. I would have been royally fucked. Never making that mistake again. Always use a copy of prod on a staging server and run your queries there for testing.
- davio 5y agoI always liked LIMIT when doing dangerous stuff in prod
- progman32 5y agoFor anyone doing this, just keep in mind that chucking on a LIMIT 1 can give you a false sense of security. For example, say you want to drop a single row but forget the WHERE. A LIMIT 1 will return "yep, deleted one row" but it's not the one you wanted (instead, it's whatever row came up first). Better to do the operation in a transaction that you can rollback - that way, you can better validate the results of your operation before committing.
- Trasmatta 5y agoAnother tip: never enter queries directly in a production database connection with write access in the first place. (Ideally very few people even have that level of access.) Write it in your codebase, write tests for it, get it code reviewed, and run it in a dry run first and get a list of affected records before running it for real.