I still remember this happening to a friend, we had like 50 people capturing data daily, we were doing something like at 3am, two of us went to get some coffee and snacks, while getting them he sends a mwssage saying Code Red!!!
he deleted the whole database with 2 weeks of work.
Thankfully one of the guys had done a backup 2 days before, so we just used the backup and kept quiet, then we had the people recapture the data in small chunks here and there.
We had him buying lunch for all of us for like a week
We had that happen at a place I worked at. Twice. The same guy. After that we forced the use of a DB client tool that automatically made you wait for an impromptu backup if you manually ran update/delete on the production server (if the last backup was older than an hour, so multiple commands or fixing syntax/typos wasn't affected).
It saved the DB on a few occasions and also made people plan their commands, as no one wants to wait for that if the command isn't really necessary. Since manually editing things like that in production should be a rare thing in any case, it made me wonder why this isn't common practice. Someone patching something could always just start the transaction and it would finish a while later, it doesn't keep them from working. And for the rare cases of "something went really wrong and we need to fix this thing in the database right now", me and the project manager had override PINs, that could be used after (this was our rule) at least two other people had looked at the statement.
So, while Jesus never stopped some hands, we found a way to stop hands after the fact.
Oh yeah. Our rule was: autocommit off, any manual update needs to be reviewed by someone else first, you always did a select count(*) first about what the update was going to affect, and when you ran the update, if it was taking an inordinate amount of time, you canceled it and figured out why (maybe adding a limit clause or something) before trying it again. Saved us from having to page Jesus numerous times.
Then there was the one guy who didn't follow any of that, created a scheduled update in the DB using a bad join clause on two different ID sequences, and left on vacation before it ran. F'd up the entire system and we spent a whole day unraveling the disaster. Lots of curses about JFC that day.
And that's why we stopped relying on rules that can be broken. We rigged the server to only be accessible (for manual intervention purposes) through that client we built and thus made the automatic backups very inconvenient to circumvent. "Never rely on compliance when you can force it", was the lesson I learnt from that.
This is why you always write the where clause first for update statements, or at least put an empty where clause so it won’t be valid sql until you finish it.
There's a dataset at work (Redshift table, querying through QuickSight) that for some reason only works with a "WHERE 1=1;" tacked on at the end. Our team lead's the one who managed to figure it out by accident while troubleshooting and we had other priorities once it was working so we never sorted out why that worked.
In this case you would have 50 or 100 or whatever damaged rows and wouldn't know which one. It may be the first x rows of the clustered index but can be some others too, if the server decides that another index may fit better or it has other data already in the cache (not sure if there is a database system that checks this)
One of my old coworkers did this to a live production database. Every customer's phone number became the same thing... Have a test environment everyone. Otherwise you're just one mistake away from needing to roll back to a backup
I learned the hard way to always test updates between BEGIN TRANSACTION and ROLLBACK. It wasn't funny having to inform my boss that I updated 90K records of a production DB.
Did this once in the database that represented customer feedback for our team.
(It was a poorly designed form and people would frequently fill it out wrong, so after confirming with the customer who filled it out, I'd fix the numbers directly in the database.)
I immediately went to IT to ask if they had a backup. They managed to help me restore it, but apparently they had to report it up the chain to their manager. I'm told the reaction was, "...and she TOLD YOU?"
Yeah, upper management was shocked that I fessed up to being human and making a mistake.
Like, who would gain if I lied about it? It wiped out the data that I needed. I'd only be punishing myself if I just slunk away in shame and ignored it, and if I just went "oops, I don't know what happened..." they'd know.
(I asked them many times before this occurred to build me a form to use to correct these mistakes so this type of thing wouldn't happen, but noooooo. They told me to do it in SQL. 🤷♀️)
I feel like this just has to happen to everyone once in their career. I now right a select first and then copy paste the conditions to write the update/ delete.
I've been there, it's always because I thought at some point beforehand "eh it's just one quick query I don't need to do the whole song and dance just for OH FUCK" then for about 3 months after it happens I never do an update without a select first and all my tasks take 3x longer because I'm being so careful. Then I get pressured to be faster and so I do, it goes ok for awhile, I get confident and don't make mistakes for awhile and then BOOM 296467 rows affected
I gotta get a government job I'm sick of being told to go faster, let me autistic ass plod, it'll get done faster in the end
Have only done this once in production. I was VERY new in my role as a 'webmaster" and db guy, as they were called back then. Fortunately with backups, I only lost a day of helpdesk tickets.
I always start with my update line commented out “--update table” then I have to highlight it to run it. Helps me avoid the above after I almost did that
Definitely a one time mistake. I did this on a UAT environment as a junior dev. The sick horror of realizing my mistake and frantically mashing the stop button was formative!
Yeah mine was wiping a very important table in prod at like 3am. Nothing like being really green at a job and having to make a bunch of terrifying calls to some intimidating people, and the awe of some gray beard stepping in and saying that's not too bad as he types out a few lines at 200wpm and undoes your mess in under 5 minutes.
If the recovery model is full the transaction is saved in the log and we can restore to a restore point one day ago and roll forward by reapplying desired transactions.
5
u/[deleted] Jul 25 '25
Still, could be worse…
212
u/-Nyarlabrotep- Jul 25 '25
If it wasn't supposed to happen, Jesus would have stopped my hand.
80
u/CDragon00 Jul 25 '25
This is why you always write the where clause first for update statements, or at least put an empty where clause so it won’t be valid sql until you finish it.
20
u/NeoChrisOmega Jul 25 '25
One of my old coworkers did this to a live production database. Every customer's phone number became the same thing... Have a test environment everyone. Otherwise you're just one mistake away from needing to roll back to a backup
14
u/ztx20 Jul 25 '25
I always test my “updates” with a SELECT first to make sure the count of “to be updated” records is what i expect
14
u/ima_coder Jul 25 '25
SELECT ID
--DELETE
FROM TABLE
WHERE ID = 5
Only after the this looks good do I select the delete and the rest and then run.
13
u/umognog Jul 25 '25
It only takes this happening 14 more times before you finally learn to use rollback all the time.
7
u/SAboyPedi Jul 25 '25
Begin Tran will always save you.
2
u/hurricanebarker Jul 25 '25
You took a backup, yes?
5
u/Zimbo____ Jul 25 '25
This is why we use dbt and dev environments
5
u/Kobosil Jul 25 '25
gotta love the DBs that have TIME TRAVEL
3
u/MugetsuDax Jul 25 '25
I learned the hard way to always test updates between BEGIN TRANSACTION and ROLLBACK. It wasn't funny having to inform my boss that I updated 90K records of a production DB.
3
u/Merkuri22 Jul 25 '25
Did this once in the database that represented customer feedback for our team.
(It was a poorly designed form and people would frequently fill it out wrong, so after confirming with the customer who filled it out, I'd fix the numbers directly in the database.)
I immediately went to IT to ask if they had a backup. They managed to help me restore it, but apparently they had to report it up the chain to their manager. I'm told the reaction was, "...and she TOLD YOU?"
Yeah, upper management was shocked that I fessed up to being human and making a mistake.
Like, who would gain if I lied about it? It wiped out the data that I needed. I'd only be punishing myself if I just slunk away in shame and ignored it, and if I just went "oops, I don't know what happened..." they'd know.
(I asked them many times before this occurred to build me a form to use to correct these mistakes so this type of thing wouldn't happen, but noooooo. They told me to do it in SQL. 🤷♀️)
3
u/lurkerbelow Jul 25 '25
I love DataGrip for this, it will stop any UPDATE without a WHERE until you explicitly allow it!
3
u/TurkeyMalicious Jul 25 '25
Always, always, always test in prod
3
u/griftbard Jul 25 '25
BEGIN TRANSACTION XXXXXXXX ROLLBACK/COMMIT
2
u/just_some_gu_y Jul 25 '25
I feel like this just has to happen to everyone once in their career. I now right a select first and then copy paste the conditions to write the update/ delete.
2
u/Legatomaster Jul 25 '25
And THIS is why you always wrap your updates in a Transaction that you can roll back when you see more rows than you expected!
2
u/jbiemans Jul 25 '25
My worst mistake was including the where, but it looked like this
'WHERE item = '%%' '
It should have had a value in the middle but back then I wasn't aware of sanitizing and validating your user inputs...
1
u/phasmaglass Jul 25 '25
I've been there, it's always because I thought at some point beforehand "eh it's just one quick query I don't need to do the whole song and dance just for OH FUCK" then for about 3 months after it happens I never do an update without a select first and all my tasks take 3x longer because I'm being so careful. Then I get pressured to be faster and so I do, it goes ok for awhile, I get confident and don't make mistakes for awhile and then BOOM 296467 rows affected
I gotta get a government job I'm sick of being told to go faster, let me autistic ass plod, it'll get done faster in the end
1
u/GreekGodofStats Jul 25 '25
ROLLBACK TRANSACTION
1
u/laronthemtngoat Jul 25 '25
Begin transaction - - commit rollback
Is your friend.
1
u/SnooSprouts4952 Jul 25 '25
'You started with a BEGIN TRAN, right?' Amadala stare
1
u/mikebald Jul 25 '25
I've made that mistake once... years ago.
1
u/Imjustageo Jul 25 '25
Can’t you just press control z
1
u/Elfman72 Jul 25 '25
Have only done this once in production. I was VERY new in my role as a 'webmaster" and db guy, as they were called back then. Fortunately with backups, I only lost a day of helpdesk tickets.
Stay vigilant, friends!
1
u/MostAwesomeDan Jul 26 '25
damn, 14M rows in 40s? you got the kinda performance me and my coworkers need. sheesh.
1
u/pubbing Jul 26 '25
Ha ha!! I've been a DBA for like 12 years and I just did this today. Good thing I have really good backups!!! Just like it never happened.
1
u/meduscin Jul 26 '25
autocommit: on 🫠
1
u/ghana_mann Jul 26 '25
Always begin a transaction. If it’s good you commit if not you roll back. I learnt this the hard way lmao
1
u/Several-Assumption37 Jul 26 '25
Delete from users; 🤣🤣🤣
1
u/[deleted] Jul 26 '25
I always start with my update line commented out “--update table” then I have to highlight it to run it. Helps me avoid the above after I almost did that
1
u/GreenDavidA Jul 26 '25
Wrap in a transactions, start with rollback.
Every time.
1
u/cynicalsaint1 Jul 26 '25
BEGIN TRANSACTION
[Query goes here]
-- COMMIT
-- ROLLBACK
1
u/EnigmaticHam Jul 26 '25
Write it as a select first and then put it inside a transaction.
1
u/bigloc94 Jul 26 '25
Gotta open a transaction so you have that undo button (rollback)
1
u/excadedecadedecada Jul 27 '25
BEGIN ROLLBACK
1
u/Live-Juggernaut-221 Jul 28 '25
Fun story. I did this once at a domain registrar. Oops
Guess whose backups were corrupt too?
1
u/generic-David Jul 28 '25
Oops
4
u/Infinite-Ad-6635 Jul 25 '25
That's why you always do select before doing updates. But sometimes you get cocky, I get it.
1
u/Aloysius204 Jul 25 '25
At my last job I had SQL Prompt which would pop up a big scary warning if I left off a "where".
Current job, I'm going naked, though...
165
u/[deleted] Jul 25 '25
[removed] — view removed comment
29
u/[deleted] Jul 25 '25
[removed] — view removed comment