Kind of along the same lines in the sense of being my biggest facepalm moment; many years ago in my first professional programming gig we had full SA access to our sql server. We regularly did troubleshooting via direct SQL statements (a practice I've since shied away from). One particular day, we were troubleshooting a problem in our monthly billing process. I went to delete a few rows and foolishly forgot to include a limiting statement on the WHERE clause, which resulted in most of the data being blown away.
Thankfully, we were able to restore that table from a backup and rerun everything to rebuild the table's state to a correct place ... but suffice to say the experience taught me many things about attention to detail, and how debugging in production should happen.
My biggest production whoops was an update gone awry ... instead of updating one row, updated all 300 million. Spotted it pretty quick, cancelled the update, but then the rollback locked the table and wouldn't allow inserts while it was rolling back ...
A good practice there is to do tricky DELETE/UPDATE inside a transaction. In e.g. Postgres, executing DELETE/UDPATE tells you number of affected rows, so if you see 1243 rows were updated rather than 1 as you expected, you can ROLLBACK.
Another good approach is to replace 'delete' with 'select count(1)' or something similar. The where clause can remain the same, you know how many rows will be affected and there's less overhead of remembering to set up a transaction, writing an update statement, rolling back, etc.
Yeah, if I ever did have to troubleshoot in SQL directly, I definitely started doing this ... only changing it to update or delete when I was 100% sure it was affecting only what I wanted to affect.
Thankfully, we were able to restore that table from a backup and rerun everything to rebuild the table's state to a correct place ... but suffice to say the experience taught me many things about attention to detail, and how debugging in production should happen.
Good times!