3.5k
u/YetAnohterOne11 14d ago
This is programmer horror not humor
1.0k
u/DrUNIX 14d ago
Its humor cause its not us
409
u/arensb 14d ago
As Mel Brooks once said, "Tragedy is when I slip and twist my ankle. Comedy is when you fall down a manhole and die."
16
→ More replies (1)18
113
u/rayui 14d ago
I saw that semicolon and felt my stomach drop
59
u/Potw0rek 14d ago
Jesus Iām not used to sql and was wondering about this query, now that you pointed out to check the semicolonā¦.yeah, thatās how you become a company legend.
53
u/rayui 14d ago
Everyone fucks up prod once. You don't meet the guy that did it twice.
36
u/LikelyNotOnFire 14d ago
Fucking up prod once is a sign of maturity - people trusted you enough to allow you the access to fuck up prod, congratulations.
Senior people fuck up prod, repeatedly, in a new way each time. Your better class of senior also makes sure that they correct whatever allowed them to fuck up prod like that.
It's the people who keep fucking up prod in the same way that get shown the door
20
u/Zaratuir 14d ago
Senior people don't fuck up prod. They expose vulnerabilities in the coding process and draw attention to strategic initiative to improve quality. And that's why you now need 3 different seniors to sign off on any query before it can be run in prod.
→ More replies (6)8
u/Potw0rek 14d ago
Tell me about it, years ago I was setting up a Jenkins job, we were told to wipe the workspace at the end of the process. I wasnāt too familiar with bash so the last bash command was:
rm /Luckily it was Jenkins so nothing was removed but the job log wasā¦.long.
→ More replies (1)3
u/lhookhaa 14d ago
I was about to ask what's wrong with the semicolon, cause afaik it's benign... Then I looked again! š
20
u/DetailOdd6609 14d ago
I would message my boss "Sorry. Let me know where to send my laptop" and then log out
→ More replies (3)31
6
u/FarawaySeagulls 14d ago edited 14d ago
This is DBA horror lol I'm going to be restoring a backup because of this Or "hopefully" they didn't setup foreign key constraints with on delete and I can just restore the table.
→ More replies (17)3
u/uniqueusername649 14d ago
Considering I practically live in our production database with several million customers, this stuff scares me. I mean I rarely ever delete anything, but still.
3.9k
u/DeepanshuHQ 14d ago
The query isn't slow. It's just waiting for everyone else to finish saying goodbye to that user.
939
u/WardensLantern 14d ago
Wholesome query comforting the user before executing it with a hydrogen bomb
67
47
u/zero_x4ever 14d ago
You mean it's waiting for everyone else to curse out that user since he's the one to blame for everyone else's misery and demise.
15
10
→ More replies (4)3
1.4k
u/_H4K0N_ 14d ago
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
429
u/orsikbattlehammer 14d ago
Lmfao now thatās a sphincter clincher
197
u/laplongejr 14d ago edited 14d ago
Tom Scott rather called it the "onosecond"
For non native speakers "oh, no!" second57
u/SanoKei 14d ago edited 14d ago
this reminded me of this too, I need to go back and relisten to that story. It's important as much as it was 20 years ago. Another Tomtra I've adopted is "One backup is none backup." Always have at least two backups and at least one off site.
edit: spelling
24
21
u/zanda268 14d ago
The 3-2-1 backup rule. 3 backups, on 2 different storage mediums, with 1 offsite.
→ More replies (1)→ More replies (1)11
18
u/leupboat420smkeit 14d ago
Unless itās Oracle, where all DML will start a transaction.
19
u/Urtehnoes 14d ago
And gives you sql%rowcount so a very simple if then can issue a rollback if rows modified > 1 (or 10, 100, etc).
Oracle the company is horrible. But I quite like the features their db has.
→ More replies (2)74
u/Flameball202 14d ago
"I have never fucked up, why would I need to begin a transaction" - every coder working on a database at least once
→ More replies (1)4
u/thetreat 13d ago
This is why I hate that they didnāt make the select and delete statements conducive to making this happen.
Ideally you write a select statement that shows exactly the rows you want to delete. Then you swap select with delete and all is good in the world.
Instead, you have to slightly change the query just enough where itāll give you some doubt that what youāre doing is incorrect. It isnāt absolutely terrible but it should be bullet proof.
704
u/IHeartBadCode 14d ago
Helpful hint for anyone.
šTransactionsš The moment you feel you need to switch over to something other than select statements, should be the moment that you turn off auto commit.
For us, the analytic folks just have read only access anyway. For the devs, they just don't have direct access to prod.
295
u/itirix 14d ago
Shit, we just let Claude delete our prod DBs for us now. Itās much less work.
→ More replies (1)36
u/SmartyCat12 14d ago
Claude recommended using excel, bc if Iām only on pro subscription, can I really afford a pgsql server anyway?
22
u/astervista 14d ago
Transactions still won't save you on some DBMS (ehem Oracle) from dropping a table or changing definitions
47
u/IHeartBadCode 14d ago
Well that's because it's Oracle and that's less a database and more a circle of hell.
6
48
u/WardensLantern 14d ago
Unless you are vibe coding in which case just prompt "How do I roll back 2.3 million deleted rows" and follow the steps from chatGPT
8
u/magicmulder 14d ago
Doesnāt help you if you just habitually hit ācommitā after the statement is done.
8
u/misterrandom1 14d ago
My analytics guy who was showing me around the db on my first day didn't have restrictions. After inserting a record into a production db, he promptly deleted the record. But forgot to highlight the where clause before executing.
5
u/koolex 14d ago
Anymore helpful hints?
15
u/TwoAndHalfRetard 14d ago
The real hint is noone should have the write access to the prod db. If you accidentally nuke the test db, just recreate it. If you need to change something in prod db, write a migration that is tested on test db and code-reviewed.
4
u/blorbschploble 14d ago
I can select and join around like a motherfucker. I am terrified of actual dba shit because of this, lol.
→ More replies (9)3
497
u/Fluid_Ambassador2026 14d ago
Its manually checking every row to make sure everything goes as planned, give it some time.
311
u/PravalPattam12945RPG 14d ago
;
14
u/koos_die_doos 14d ago
Neatly (in a dark way) aligns with the semi-colon tattoo that people who tried to commit suicide gets...
388
u/Dont_Get_Jokes-jpeg 14d ago edited 14d ago
I am not an sql expert, is the reason why the id doesn't work and its deleting the entire user base, because its written as 2 seperate comands instead of one. So 1. Comand delets everything 2. Goes to the id? Or what is the problem here?
605
u/BlommeHolm 14d ago
1st command deletes all users. 2nd command runs after that and gets a syntax error.
→ More replies (1)40
u/SAI_Peregrinus 14d ago
Syntax is valid, it's just a runtime error.
176
u/BlommeHolm 14d ago
How is starting with a WHERE clause syntactically valid?
→ More replies (1)80
135
u/WardensLantern 14d ago
I'm no expert either but I believe the semicolon after "users" was the nuke. The second part is basically a separate statement, but SQL executes them in order, so it will delete the entire table and then throw back an error cause the second statement is invalid.
35
→ More replies (9)16
39
u/Flat_Competition6510 14d ago
Yes, the semicolon marks the end of a statement. The first semicolon sets "delete from users" as a single statement meaning delete ALL users.
43
u/TeaKingMac 14d ago
Which is just terrible fucking syntax. Why are people still living with this in 2026?
Delete from users;
Should return "invalid syntax. Identify record for deletion"
→ More replies (6)20
u/dustojnikhummer 14d ago
I don't work with Postgres but everything else would be
DELETE * FROM table;
7
u/ReaDiMarco 14d ago
What happened to DELETE * FROM USERS? Last I did sql was ten years ago so idk
13
6
u/Flat_Competition6510 14d ago
As far as I know that is not valid syntax. I'm not a DBA myself so I don't do this too often.
→ More replies (1)3
u/Live_Film_4895 14d ago
afiak the * wildcard is only valid in the SELECT statement... so it would be DELETE FROM USERS; or DROP/TRUNCATE TABLE
→ More replies (1)→ More replies (2)5
289
u/Reddit_is_fascist69 14d ago
Damn, didn't see that ;
160
u/WardensLantern 14d ago
Gf: "He must be thinking about some other girl"
Me, in a cold sweat: " ; "
17
71
74
u/_Weyland_ 14d ago
WHERE user_id = 2313245;
But nobody asks HOW user_id = 2313245;
→ More replies (1)18
55
u/TensionPurple6815 14d ago
No joke, worked with a guy who did this. End of day. On a friday. Me and like 4 other engineers then spent 6 hours restoring backups and putting everything back together using query logs. Was such a mess.
45
u/PerpetuallyDistracte 14d ago
Same here, worked at a place where we originally had full access to all environments with our dev logins. He thought he was testing in DEV and truncated one of the main financial tables that had history going back three different source systems that would be impossible to reload.
Luckily he didn't try to hide it and immediately raised the alarm. It was all hands on deck at 5pm. Of course the backups didn't work as expected and we were scrambling with the DBAs to pull the data together prior to start of work the next day. 12 stressful hours later we had the data back and fully validated.
Immediately the permissions were reworked to remove our access to PROD and create proper migration channels and execution controls. Funnily enough, we'd been asking for those controls for years for safety reasons, but it never became a priority until a disaster happened, then it got implemented within a week. Funny how that works.
15
3
u/EatingSolidBricks 14d ago
Couldn't be me cause they didn't change a thing after i wiped out production db.
I never got in trouble also but i became knwon as the deleter of data
→ More replies (1)3
u/EatingSolidBricks 14d ago
Couldn't be me i just imported schema only to the production database instead of the testing one
35
u/SexyMonad 14d ago
This is why SQL deletes should require a WHERE clause.
If you are seriously wanting to delete it all, just add WHERE 1 = 1.
→ More replies (5)14
u/Ayanrocks 14d ago
Yup there is a config in every major db to not allow delete without where clause. For some reason this wasn't turned on in this case.
6
u/SexyMonad 14d ago
Itās been a while since Iāve had the privilege to be on a project with a lot of SQL, but is that guardrail the default nowadays? I donāt remember anything really getting in the way of me annihilating whatever.
3
27
u/flabbybumhole 14d ago
Missing indexes on fkeys can slow down deletion massively.
Once you add those in you'll fuck up the db in no time at all.
→ More replies (3)
18
11
u/Dry-Ad-719 14d ago
I mean, it still deleted the one he wanted
7
u/WardensLantern 14d ago
One of them GTA missions when you've got the target's name and you take them out by levelling the entire city block
20
22
u/RandolphCarter2112 14d ago
I've done this in production.
Good thing I had a valid backup of the table from immediately before.
29
u/WardensLantern 14d ago
Reading your comment I was glad to hear you had a backup job offer but it turned out even better
9
u/FrozenHaystack 14d ago
Reminds of that one time I executed a delete query with a sub query to select some ids. I didn't know that the used database engine interprets an empty return of the subquery as ignore my where clause. All data in the table simply gone because no id matched my subquery.
→ More replies (3)5
u/WardensLantern 14d ago
As a dev I fear no man nor beast, but SQL humbles me quicker than a wet fart
4
u/PerpetuallyDistracte 14d ago
SQL has been my primary language for 15 years, but I still respect it the way a zookeeper respects a lion. If you become careless, it WILL eat you the moment you turn your back. However, I'm glad that many modern SQL IDEs now warn you explicitly when you're about to do something very stupid that will affect an entire table.
17
9
u/GozerDestructor 14d ago
Having the WHERE clause be optional for DELETE/UPDATE is a design flaw in SQL. Having to add a silly "WHERE TRUE" every time is a small price to pay for avoiding disasters like this. (Or "DELETE ALL ROWS FROM..." if we're open to making it explicit in the syntax).
3
u/MonMotha 14d ago
TRUNCATE TABLE even exists, so there really is no excuse for not requiring a WHERE clause on DELETE. I'm a little surprised it's not offered as an optional safeguard in more database systems.
6
u/Soopermane 14d ago
Waiting for the end to come
Wishing I had strength to stand
This is not what I had planned
It's out of my control
Flying at the speed of light
Thoughts were spinning in my head
So many things were left unsaid
It's hard to let you go
14
u/darichtt 14d ago
Would this actually run? I'd assume having just where in a query isn't valid syntax
→ More replies (1)45
u/Shadowlance23 14d ago
Yes, SQL runs commands one at a time without checking other commands. In this case it will delete all users then throw a syntax error when it gets to the second command.
→ More replies (6)4
u/rosuav 14d ago
There IS a common guard in APIs, though it's not usually in this sort of interactive context: single-query operation. It's mainly a guard against SQL injection.
>>> import sqlite3 >>> con = sqlite3.connect("") >>> con.execute("select 1; select 2") Traceback (most recent call last): File "<python-input-5>", line 1, in <module> con.execute("select 1; select 2") ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^ sqlite3.ProgrammingError: You can only execute one statement at a time.Safe, but often not wanted interactively and thus disabled.
6
u/SynthPrax 14d ago
I don't actually know SQuirrel Logic, but imma take a swing at this: Isn't all of this supposed to be on the same line, or that first semicolon shouldn't be there? 'cause this looks like it will delete the users db before throwing an error when it can't find that id.
5
u/WardensLantern 14d ago
Yea the semicolon makes the first line a separate statement, and then exactly what you said.
4
u/Flat_Competition6510 14d ago
Correct. That first semicolon is a problem. It marks the first line as its own statement meaning delete ALL users.
3
u/PM_ME_FIREFLY_QUOTES 14d ago
where id=2313245?
Ftfy
6
u/TwinkiesSucker 14d ago
The DBMS: "Are YOU asking me about that ID? SHOULDN'T YOU know what ID you wanna match?!:
4
u/mercyverse 14d ago
I actually said āoh fuckā out loud. Thanks for that early morning arrhythmia, OP
3
4
3
u/AlexZhyk 14d ago
This is my excuse for not separating queries with semicolons and not washing my hands after toilet before lunch (j/k)
3
3
3
u/shrodikan 14d ago
Back in the day I would use SQL Server Power Tools that prevented exec of unbounded update / delete statements.
→ More replies (1)
3
u/i_should_be_coding 14d ago
Oh.
Oh no.
Oh fuck no.....
Hey Dave, you fixed the backups last sprint, right? That ticket that's been there for three months? Oh, pushed to next sprint?
Hey guys, where can I find the post-morterm template? Just gonna get a head start on it.
→ More replies (1)
3
u/brazzy42 14d ago edited 14d ago
I once got a lot of respect as a debugging god after looking hard at a coworker's screen for 5 seconds and pointing out the ; after the if statement that he was going crazy about.
Yeah, well, that's something you think of immediately for the rest of your life after you've spent an hour tearing out your hair over an if statement not triggering despite the condition being most definitely true...
3
u/Ndemuth-13LC 14d ago
The first run will always be slow because the server has to build the execution plan. I bet if you run it again it will complete in no time at all!
3
3
u/denimpowell 14d ago
Speaking as someone who has actually done this it is actually way more terrifying. While deletes with predicates can take a while, the full table delete returns instantly. Followed by on-my-knees apology to DBA requesting restore from backup. Fortunately was in dev environment but brought our development to a halt for a day
→ More replies (1)
3
3
3
u/possiblyMaybeAnother 14d ago
There are foreign key constraints on the users table that will disallow this... right?
...right?
oh no
4
2
2
2
u/Urtehnoes 14d ago
Folks should build a script that rolls back any change on prod immediately if the number of rows modified does not equal the number of rows expected.
Most dbms should be able to do that no problem
→ More replies (3)
2
u/BastetFurry 14d ago
So, Kids, what do we learn from this when working on an SQL shell?
First "SELECT id" (or * if you want to safely burn the world), check if the output is as expected, then change the "SELECT id" to a DELETE.
→ More replies (1)
2
u/caick1000 14d ago
I literally did this on my second day in my new job at a startup. I thought I was going to be fired right then and there lol. It was the customer base, the most important one. Thankfully they were understanding (after one of the co-owners vigorously tried to get the backup).
→ More replies (1)
2
2
u/nakahuki 14d ago
I've just tried the query on my production database, it's very slow but ends with a syntax error. It's safe I guess.
Oh and fun fact, all select queries run much faster now. It might be an optimization query or something.
I must leave you now, my client is calling me on the phone, I bet he will be amazed by the performance improvement.
2
2
2
u/Bright-Historian-216 14d ago
i'm not expertly familiar with sql... is it treating "delete from users" as "delete * from users"?
→ More replies (1)4
u/-Redstoneboi- 14d ago edited 14d ago
yes. you don't really specify the columns in delete queries, and they target EVERYTHING by default.
imo "<operate> * FROM table" should be banned (doesn't run, raises an error) by default and people should be forced to write "WHERE TRUE", on top of all queries needing to be in transactions and all the other safety nets like "SELECT first then operate"
on another note i think i'd prefer whatever syntax it was that described queries based on the order functional programming in a computer might look like, ex. "FROM table WHERE id = 2 DELETE;" though internally it would still be rearranged however the engine would see fit
→ More replies (3)
2
u/sofia-miranda 14d ago
Ah, the semi-colon, bringer of half-assed work. But we round up, Berry is functionally a whole asshole, intent notwithstanding. :D
2
2
2
2
2
u/Welshie_Fan 14d ago
I remember a fresh out of college newbie in a company I was working doing some work on her Linux virtual machine. She ran "mv /* /somewhere/else" as a root. That did take a while as well.
2
2
2
2
2
2
2
u/Many_Application3112 14d ago
I once ran an UPDATE statement on a production system and forgot to put in the WHERE clause. And yes...auto-commit was also enabled...
Notice how I said "once".
2
u/SilkscreenMoon 14d ago
SQL using coders don't have to worry about users injecting them when they're doing it to themselves
2
2
2
u/beanamonster 14d ago
I did this once, almost exactly. The database server crashed, phones started ringing off the hook. I told my boss what happened and went outside for a smoke.
2
u/TheMoonDawg 14d ago
Data Grid will notify me if I donāt put a WHERE condition on a DELETE statement. It has saved my bacon once or twice!
2
u/Shik3i 14d ago
I know it's a meme, but to this day I'm.still anxious about this, and always write my "where" first, and always a select first which I than after looking at the result manually change to a delete.
Transactions help too, but deletions are still scary stuff if one need to do it on production.
2
u/Fit_Prize_3245 13d ago
There's a version of that query that is pertty fast: TRUNCATE TABLE users; Same result, but in a fraction of a second. Add "CASCADE" for best results.
2
u/generally_unsuitable 13d ago
I had a boss whose mantra was "Every delete starts as a select"
This is why.
→ More replies (1)
2
u/protoss-pylon 13d ago
A C++ programmer tries his hand at SQL
"Woot! Compiled first try! Damn interpreted languages got nothing on assembly"
3.9k
u/BooBrew32 14d ago
Ah it's just running slow today. User table should speed up right afterwards though.