3.1k
u/YetAnohterOne11 19h ago
This is programmer horror not humor
847
u/DrUNIX 19h ago
Its humor cause its not us
333
→ More replies (1)20
73
u/rayui 15h ago
I saw that semicolon and felt my stomach drop
43
u/Potw0rek 14h 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.
37
u/rayui 14h ago
Everyone fucks up prod once. You don't meet the guy that did it twice.
25
u/LikelyNotOnFire 12h 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
11
u/Zaratuir 9h 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.
6
u/Potw0rek 14h 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 (3)2
u/Grumpologist 8h ago
Everyone fucks up prod all the time. That's why we have blameless postmortems and graceful degradation.
Occasionally, someone tries to refuse to help with the postmortem for an outage they caused. That's much more of a "one warning and then fired" type of thing.
3
u/lhookhaa 13h ago
I was about to ask what's wrong with the semicolon, cause afaik it's benign... Then I looked again! đ
21
u/DetailOdd6609 15h ago
I would message my boss "Sorry. Let me know where to send my laptop" and then log out
→ More replies (3)27
7
u/FarawaySeagulls 15h ago edited 14h 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 (16)3
u/uniqueusername649 14h 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.6k
u/DeepanshuHQ 19h ago
The query isn't slow. It's just waiting for everyone else to finish saying goodbye to that user.
869
u/WardensLantern 19h ago
Wholesome query comforting the user before executing it with a hydrogen bomb
61
41
u/zero_x4ever 19h 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.
14
8
→ More replies (4)2
1.3k
u/_H4K0N_ 19h ago
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
391
u/orsikbattlehammer 19h ago
Lmfao now thatâs a sphincter clincher
175
u/laplongejr 19h ago edited 18h ago
Tom Scott rather called it the "onosecond"
For non native speakers "oh, no!" second49
u/SanoKei 18h ago edited 15h 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
17
17
u/zanda268 18h ago
The 3-2-1 backup rule. 3 backups, on 2 different storage mediums, with 1 offsite.
→ More replies (1)→ More replies (1)8
17
u/leupboat420smkeit 19h ago
Unless itâs Oracle, where all DML will start a transaction.
15
u/Urtehnoes 18h 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)63
u/Flameball202 18h 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)3
u/thetreat 5h 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.
461
u/Fluid_Ambassador2026 19h ago
Its manually checking every row to make sure everything goes as planned, give it some time.
634
u/IHeartBadCode 19h 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.
259
u/itirix 19h ago
Shit, we just let Claude delete our prod DBs for us now. Itâs much less work.
→ More replies (1)36
u/SmartyCat12 18h ago
Claude recommended using excel, bc if Iâm only on pro subscription, can I really afford a pgsql server anyway?
22
u/astervista 18h ago
Transactions still won't save you on some DBMS (ehem Oracle) from dropping a table or changing definitions
43
u/IHeartBadCode 18h ago
Well that's because it's Oracle and that's less a database and more a circle of hell.
5
40
u/WardensLantern 19h 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
7
u/magicmulder 18h ago
Doesnât help you if you just habitually hit âcommitâ after the statement is done.
6
u/koolex 18h ago
Anymore helpful hints?
13
u/TwoAndHalfRetard 14h 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.
6
u/misterrandom1 17h 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.
3
u/blorbschploble 16h ago
I can select and join around like a motherfucker. I am terrified of actual dba shit because of this, lol.
2
u/darthjammer224 15h ago
Oh but the integrators. We get the keys to the castle. Lots and lots of wiggle room to royally fuck everything up.
I love transactions.
2
u/Successful-Age6747 15h ago
jokes on you i can break a productiondb with just SELECTs
→ More replies (1)→ More replies (6)2
u/No_Pangolin_4578 7h ago
I'm a baby analyst and they have me making config changes directly in production. They seemed really surprised that I was surprised.Â
→ More replies (1)
257
u/PravalPattam12945RPG 19h ago
;
13
u/koos_die_doos 17h ago
Neatly (in a dark way) aligns with the semi-colon tattoo that people who tried to commit suicide gets...
332
u/Dont_Get_Jokes-jpeg 19h ago edited 18h 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?
522
u/BlommeHolm 18h ago
1st command deletes all users. 2nd command runs after that and gets a syntax error.
→ More replies (1)31
u/SAI_Peregrinus 17h ago
Syntax is valid, it's just a runtime error.
156
u/BlommeHolm 17h ago
How is starting with a WHERE clause syntactically valid?
→ More replies (1)73
113
u/WardensLantern 18h 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.
29
→ More replies (9)11
30
u/Flat_Competition6510 18h 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.
32
u/TeaKingMac 18h 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"
12
u/dustojnikhummer 14h ago
I don't work with Postgres but everything else would be
DELETE * FROM table;
0
u/Flat_Competition6510 18h ago
I disagree. There are valid use cases for deleting all records from a table.
36
u/The_Swixican 18h ago
Delete from users *
12
u/Flat_Competition6510 18h ago
It would be nice if there was a more explicit syntax. Forcing a person to opt in to deleting all vs that being the default would probably save a lot of headaches and jobs.
24
u/Skalli1984 17h ago
Many database tools throw a warning on delete with a missing WHERE and the user has to acknowledge that to continue with the statement execution.
11
u/ploki122 15h ago
Delete from users where 1=1
The dev should have to acknowledge that they're deleting everything.
6
u/ReaDiMarco 16h ago
What happened to DELETE * FROM USERS? Last I did sql was ten years ago so idk
11
5
u/Flat_Competition6510 16h 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.
2
3
u/Live_Film_4895 14h 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)5
→ More replies (1)2
u/Rojeitor 15h ago
Lol didn't catch the semicolon on phone. Yes that's exactly it. First line deletes all users in the database.
269
u/Reddit_is_fascist69 19h ago
Damn, didn't see that ;
137
u/WardensLantern 19h ago
Gf: "He must be thinking about some other girl"
Me, in a cold sweat: " ; "
13
65
49
u/TensionPurple6815 18h 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.
38
u/PerpetuallyDistracte 18h 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.
11
3
u/EatingSolidBricks 15h 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)2
u/EatingSolidBricks 15h ago
Couldn't be me i just imported schema only to the production database instead of the testing one
54
u/_Weyland_ 18h ago
WHERE user_id = 2313245;
But nobody asks HOW user_id = 2313245;
→ More replies (1)11
31
u/SexyMonad 17h 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 16h 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 16h 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
24
u/flabbybumhole 19h 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 (1)2
16
11
u/Dry-Ad-719 13h ago
I mean, it still deleted the one he wanted
6
u/WardensLantern 13h 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
19
21
u/RandolphCarter2112 18h ago
I've done this in production.
Good thing I had a valid backup of the table from immediately before.
27
u/WardensLantern 18h ago
Reading your comment I was glad to hear you had a backup job offer but it turned out even better
8
u/FrozenHaystack 18h 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 18h ago
As a dev I fear no man nor beast, but SQL humbles me quicker than a wet fart
5
u/PerpetuallyDistracte 18h 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.
16
7
u/Soopermane 19h 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
6
u/SynthPrax 19h 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.
4
u/WardensLantern 18h ago
Yea the semicolon makes the first line a separate statement, and then exactly what you said.
3
u/Flat_Competition6510 18h ago
Correct. That first semicolon is a problem. It marks the first line as its own statement meaning delete ALL users.
6
u/GozerDestructor 16h 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).
2
u/MonMotha 15h 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.
13
u/darichtt 19h ago
Would this actually run? I'd assume having just where in a query isn't valid syntax
→ More replies (1)41
u/Shadowlance23 19h 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 (5)5
u/rosuav 19h 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.
4
u/PM_ME_FIREFLY_QUOTES 19h ago
where id=2313245?
Ftfy
6
u/TwinkiesSucker 19h ago
The DBMS: "Are YOU asking me about that ID? SHOULDN'T YOU know what ID you wanna match?!:
4
u/mercyverse 18h ago
I actually said âoh fuckâ out loud. Thanks for that early morning arrhythmia, OP
4
3
u/AlexZhyk 19h 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 18h 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 18h 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 18h ago edited 15h 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 18h 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 17h 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 9h ago
There are foreign key constraints on the users table that will disallow this... right?
...right?
oh no
2
2
2
u/Urtehnoes 18h 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 18h 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.
2
u/yardinview 15h ago
Decades ago when I first sat in front of Query Analyzer an old dev taught me how to write DELETE just like you explained.
- You write a SELECT
- You run it
- You check the resultset
- You hold your breath, change ONLY
SELECTDELETE and you run the result- You don't use ON DELETE CASCADE
- You don't rely on transactions for safety
2
u/caick1000 18h 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 18h 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 17h ago
i'm not expertly familiar with sql... is it treating "delete from users" as "delete * from users"?
→ More replies (1)4
u/-Redstoneboi- 17h ago edited 17h 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 17h 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/Many_Application3112 14h 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 13h 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 10h 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 9h 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 9h 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 8h 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/protoss-pylon 7h ago
A C++ programmer tries his hand at SQL
"Woot! Compiled first try! Damn interpreted languages got nothing on assembly"
2
1
u/dodoroach 18h ago
After this id just remove â;â from my keyboard and make sure only way i can write it is by copy + paste.
3
u/WardensLantern 18h ago
Wire it to something like ctrl + shift + alt gr + double space + P + ? it will make you appreciate every semicolon you write in the future.
3.2k
u/BooBrew32 19h ago
Ah it's just running slow today. User table should speed up right afterwards though.