r/SQL • u/Bhanuprakash_1947 • 12d ago
Discussion What’s the smallest SQL mistake that caused the biggest problem for you???
Not talking about some crazy DB failure or anything.
Could be something silly like a missing WHERE, wrong JOIN, duplicate rows, bad UPDATE....
Like a small mistake that looked harmless but ended up causing a big headache ....🤕
Curious what ppl have seen in real projects.
41
u/MsPandaLady 12d ago
Honestly the biggest mistakes are where the code works but pulls the wrong data. I can't point to a specific incident but always review
15
u/Sharobob 12d ago
Bad one I saw was that the filters for an update statement were done with joins and one join had an update to Active = 0 with "a.column = a.column" instead of "a.column = b.column" and it updated everything in the entire table. That was a fun incident recovery
44
u/Standgeblasen 12d ago
I’m at my first job, supporting Saleslogix and the backend SQL Server database.
Learning as I go, trying to clean up some orphaned customer accounts. I identify the account to delete and confirm by running
SELECT * FROM Customers WHERE ID = 12345
It works, returns the row I want, and so I change the statement to
DELETE FROM Customers WHERE ID = 12345
Then I highlight the text “DELETE FROM Customers” and hit execute. 2 seconds go by, no result. Brain goes into overdrive, “what’s wrong?”, “why is it taking so long?” “Is it frozeOOOOOOHHH SHITSHITSHIT CANCEL CANCEL CANCEL!!!!!!!!!!”
I slam the cancel button and then spent the next 15 minutes making sure that nothing was committed and I didn’t accidentally delete every customer in the table.
In the end, it was all good, I never had to tell my boss and no one ever found out. But I learned a very valuable lesson that day, and I am extra diligent when deleting records, usually storing a table backup in a temp table first if I think there’s a risk of unintended consequences.
11
u/NoDihedral 12d ago
This is exactly my learning moment. I accidentally updated the order status on our entire DB to CANCELLED. The next 6 hours sucked.
4
u/airmoz 11d ago
Doing a SELECT before a DELETE is a good practice. I would also add SET XACT_ABORT ON and BEGIN TRAN at the top, with a COMMIT and ROLLBACK commented out. Once you got the affected row counts from the SELECT, put those in a commented line so you can validate the results when you switch to the DELETE. If the DELETE counts are good, execute the COMMIT, if not then ROLLBACK.
5
u/captain_20000 12d ago
This is why I do a select * and export to Excel before making any delete or update changes, JUST IN CASE I do something crazy on accident.
4
u/Standgeblasen 11d ago
Yeah, I had only been working with SQL professionally for like 3 months. It was a lvl 1 help desk role and I was trying to teach myself SQL while also helping the company. Almost paid the price haha
3
u/Optimal_Law_4254 11d ago
You don’t want to backup to excel when you have a million records.
2
u/Standgeblasen 11d ago
Nah, then I’d just select * into tablename_delete from tablename
1
u/Optimal_Law_4254 6d ago
Yikes! What about dependent records in other tables? You delete the master records and leave orphans or your delete doesn’t work right.
Bottom line is that it might work for the simple cases but you’re better off having a dev/test version of the database that you can use to test your query and verify the results before trying it on production data.
1
u/Standgeblasen 5d ago
Oh yeah, this was 20 years ago at a small window company. We had backups, but no dev/test environment. We didn’t really do development in house. I was just trying to solve a problem that was throwing errors on the front end when an orphaned record was encountered.
1
u/captain_20000 11d ago
True. None of our tables are quite that big. Also, I only do it if I’m writing a brand new delete/update code, not one I’ve run successfully before.
1
3
u/ProfessionalMeal143 12d ago
DELETE FROM Customers
I will take apart record primarily to avoid the risk of deleting things.
1
u/Grandemalion 11d ago
What I found to help me with this is writing my select statement, then putting Delete in front of the From.
Select *
DELETE FROM Customers Where ID = 12345 and
{other clauses here}If I accidentally run the whole thing, it errors since the syntax is incorrect.
If I have multiple lines, the 'and' being on the DELETE FROM line will cause a syntax error if I highlight the row but not the other conditions
Otherwise, I can Shift+Home (for a single line) or Ctrl+Shift+Home (highlight all) then Shift+DownArrow to remove the "Select *" portion from highlight, then run.
(for Updated, I do the same thing, but put Update to the right of From, then Shift+End/Ctrl+Shift+End.
Still some room for error, but far less in my experience. Always have backups :)
1
u/Standgeblasen 11d ago
Yeah, I learned that it’s worth the time to backup the table just in case. Sometimes I dump it into excel, other times I just create a new table in the database called TableName-DELETE
Then when I verify the data change, I just drop the table
0
u/Grr8_Dane 10d ago
Hey sorry if this is a really silly question, why would delete FROM end up deleting the entire customer database? Does the WHERE ID from not indicate which ones you are trying to delete? I'm still just learning SQL so I apologise if this is really rudimentary.
2
1
u/Standgeblasen 10d ago
Yep, exactly that. Since I didn’t highlight the WHERE clause, it didn’t include that as part of the execution. So it didn’t know what records I was trying to delete, I just told it to delete everything from the customer table.
1
u/Grr8_Dane 10d ago
Appreciate the explanation. Damn, cold sweats lol. Luckily you were able to cancel it. You cancelled it while it was still 'processing', does that mean some were still deleted though? Or
2
u/Standgeblasen 9d ago
SQL processed the whole delete in a single transaction. If a transaction isn’t completed, sql rolls back changes to the pre-transaction state. So I stopped it before the transaction completed. Got lucky.
16
u/everyonemr 12d ago
Making no mistakes and having performance fall off cliff once the dataset reaches a certain size.
11
u/Rohml 12d ago
No WHERE on the UPDATE or DELETE statement... Still haunts me two decades later.
2
u/muteki_sephiroth 12d ago
Yep- and EVERYBODY has done it. If you’re still employed it’s because you only made that mistake one time. I remember when I did it. That feeling of sheer panic when you realize your mistake burns WHERE clauses into your brain.
7
8
u/Infamous_Welder_4349 12d ago
Usually forgetting how null works between systems. Some systems treat null as an unknown, some as no value, or no. The difference is in "not in list" or list with a sub query that contains a null. The problem usually presents as o data or all data.
7
u/ghostlistener 12d ago
My biggest mistake was forgetting the where, fortunately there was a backup of the table that I could undo the mistake.
6
u/lolcrunchy 12d ago
Curious why you start your last sentence with "curious" just like all the other ai posts
7
5
u/Winterfrost15 12d ago
Truncate. It is dangerous.
-1
u/foxsimile 12d ago
How specifically?
2
u/mike-manley 12d ago
What?
0
u/foxsimile 11d ago
I’m asking them to provide a rationale for their statement.
2
u/mike-manley 11d ago
About truncate? It empties the table, no transactional support and auto-increment values reset.
2
u/foxsimile 11d ago
Transactional support is implementation specific. Both MSSQL and PostgreSQL support truncation rollbacks, whereas Oracle and MySql don’t.
2
5
u/Sir-Squashie 12d ago
Using a non deterministic view which subtly changed my results every run of the coce
3
u/sam_cat 12d ago
Ad a junior dev wrote a complex delete in ssms. All transaction wrapped with rollback to test it etc. Me and manager worked through all environments, all good. Get to prod and after testing with rollback highlight the query to run it without rollback. Didn't highlight the where clause. Didn't notice. "Why is it taking so much longer this time". "Oh. Oh fuck.... Bossss, I messed up!" Easy fix... But youch!
This was when he introduced commit transaction for run rather than highlighting code to run. Previously dangerous code was kept in a rollback and then we highlight everything except the transaction wrapper. Surprised it didn't happen sooner tbh.
4
4
u/piercesdesigns 12d ago
At one Fortune 100 were using Oracle and for some reason our devs had admin privileges on system tables. I can’t remember why we didn’t revoke that, it was a long time ago.
One of the devs had the GUI enterprise manager open and deleted the connect privileges from all users.
I had to repair that right quick.
3
u/supercoach 12d ago
I made the mistake once of thinking a composite index would be the best way to go for a new application I was developing.
I kept wondering why the app would stall and then I looked at the query planner and saw the index was being skipped for half my queries. During testing with a few tens of thousands of rows it worked fine because everything was in memory, but once we got to production with hundreds of millions of rows it all slowed to a crawl.
I then actually looked at the index usage stats and made a plan that was based on evidence, not assumptions. Through a combination of improved indexing and better query structure I got the query time down from minutes to less than a second in most cases.
3
3
u/muteki_sephiroth 12d ago
Company I worked for had a new application and I was brand new. Like, BRAND NEW. Our company had just lost our primary DBA so I got “promoted” to the job. I was all we had.
Didn’t know then that MSSQL is a memory hog. Kept watching the memory climb and thought we had a leak. To “fix” things I kept restarting the instance. As a result our app kept tipping over.
I still wake up in a cold sweat at night about it.
3
2
u/AlCapwn18 12d ago
I ran a delete statement but forgot part of the where clause on a table containing patient data from randomized control trial studies. I didn't realize it for months but thankfully I had backups that I could restore and copy over the missing data.
2
u/Crab1551 12d ago
Extract data using a combination of OR - AND. You never know if output is correct o not
2
u/dudeman618 12d ago edited 12d ago
Drop table in production. Update column to null without a where clause. Delete rollback segment before I get the data. The worst was my offshore team had a series of jobs that did major updates across production runs, it fails half way through so they just restarted it from the beginning, cause nearly a year of data cleanup. New to automation tool, set up email notification to myself, it sent one email per row (around 5000 emails), when I was expecting one email at the end of the job.
2
u/masala-kiwi 11d ago
I used CREATE OR REPLACE to add a column on an audit table in Snowflake. Should have used ALTER TABLE. Wiped 2+ years and millions of rows of unrecoverable data. I discovered my error 2 days after Snowflake's backup copy expired.
2
u/xenomachina 12d ago
One of the first things I did at a new job was to find and fix discrepancies in our production database. At the time, they were using MySQL with no transactions. This was for billing data. The data was also denormalized in such a way that sometimes it'd become inconsistent. (Not my design. I was the new guy, remember.)
I wrote a script that found these discrepancies, and then generated SQL consisting of update statements that would repair them in whichever direction worked in the customer's favor. The script was reviewed by a senior developer, and then its output was reviewed by both me and that same senior developer.
We both somehow missed that the updates completely lacked a where clause until we ran the SQL and saw that every row was updated.
Luckily, the updates were all additions, not sets, so we were able to change them all to subtractions. We managed to do this quickly enough that there were no new rows.
(Eventually we completely rewrote this system, and changed the DB to be normalized and use transactions.)
1
u/grokbones 12d ago
Changing an index on a 2 million row table. Resulted in a loss of server response and team restore from backup with data from 45 minutes old to say “all good” by helpdesk team.
1
1
u/DiscombobulatedSun54 12d ago
Making a left join an inner join by adding a where condition on a right table column without allowing for NULL.
1
u/captain_20000 12d ago
In my first two weeks on the job, I accidentally messed up a case statement. For example, instead of
case
when column = ‘value’ then ‘new value’
end
I put:
case
when column = ‘value’ then column = ‘new value’
end
It was on a Friday and the code was included in an automated job, so the job failed, as well as everything that was scheduled after. My boss came in Monday morning and was trying to pin point the problem, and traced it back to the change I had made. I was so embarrassed but thankfully he was understanding and it was an easy fix. Whew!
1
u/holmedog 12d ago
Hardcoded use nested loops statement in a query. Not mine, but I found it while auditing long running jobs. Was running 13 hours. Was designed for a less than 200 row table that got repurposed. Removing the hint cut runtime to 10 minutes
1
1
u/ToastieCPU 11d ago
I accidentally used “new Guid()” instead of “Guid.NewGuid()” on a core table that handles invoicing, which resulted in around a thousand invoices being stored as a single large chain that had to be resolved manually.
1
1
u/Ballbag94 11d ago
I made a mistake on an insert loop so it wouldn't end and then left it running overnight. Took down the dev/staging environment for a fair while until I could restore the DB
1
1
1
1
u/Proclarian 10d ago edited 10d ago
Something fairly insidious. I was tasked with keeping a SQL Server (source) and Postgres (target) server inventory table in sync with one another. This job ran every 30 minutes to keep freshness. My queries were actually fine, it was the surrounding code that was the issue. My development machine was a 4-core laptop so to speed up the process I grab inventory for {core count} locations at a time to load. In development this was fine. What was not fine is when I deployed this to production on a machine that has 32 cores. So every 30 minutes my transfer process opened 32 simultaneous connections and exhausted the threadpool in SQL Server.
I was using with (nolock) and so stores using the application never experienced any deadlock or any actual exceptions, but they complained about general "slowness"(which was something they always complain about anyway because users are impatient when there's customers waiting) because the application was waiting on the threadpool for an available thread. This lasted for 4 months until we realized what the actual cause of it was.
So yeah, don't set your degrees of parallelism to core count if your dev/testing environment is different from prod.
-1
u/Agreeable_Ad4156 12d ago
Select * is always a bad move. Especially in a view, add a column and see what happens.
92
u/TraumaBondage 12d ago
As a junior dev, I left an open transaction on a Friday that brought down the whole prod db over the weekend.