How I Destroyed My Company's DB
ฝัง
- เผยแพร่เมื่อ 13 มิ.ย. 2024
- Recorded live on twitch, GET IN
/ theprimeagen
Become a backend engineer. Its my favorite site
boot.dev/?promo=PRIMEYT
This is also the best way to support me is to support yourself becoming a better backend engineer.
Reviewed article: zaidesanton.substack.com/p/ho...
By: Anton Zaides
MY MAIN YT CHANNEL: Has well edited engineering videos
/ theprimeagen
Discord
/ discord
Have something for me to read or react to?: / theprimeagenreact
Kinesis Advantage 360: bit.ly/Prime-Kinesis
Hey I am sponsored by Turso, an edge database. I think they are pretty neet. Give them a try for free and if you want you can get a decent amount off (the free tier is the best (better than planetscale or any other))
turso.tech/deeznuts - วิทยาศาสตร์และเทคโนโลยี
![CAMPปลิ้น | EP.76[1/2] โจโฉเปิดป่าโหดรับโอ๊ต-ป๊อบ รอบนี้พี่เอาจริง!](http://i.ytimg.com/vi/pNg5O7Pf-a0/mqdefault.jpg)
"Always wait till Monday" is something I fully agree on. Because I'm often the guy that gets called on Mondays at 5 a.m. because someone made a breaking change during the weekend.
In general, the real lesson learned is that unless its something of strategic importance, there is NO gun barrel touching your head. Work will be always there. It never stops. Your life on the other hand, does end, eventually. So, screw it, weekend is weekend.
@@PhilipAlexanderHassialis Yeah, weekend is important to have time for yourself and family. A lot less stressful to fix problem if you have your team mates present and everyone is aware that some changes are happening as well.
@@PhilipAlexanderHassialisyeah, that's one of the most hard things I've learned owning a company is that work NEVER stops. YOU need to stop.
@@PhilipAlexanderHassialisheh definitely the case in most software. I'm just a hobbyist programmer but in my medical career although there's never a gun to my head on the weekend, there may well be one for a patient! Physicians getting the time and resources to live their lives while still covering patient needs is entirely systemic. It may be the same in some safety or security critical software work.
Yuuuuuuuuuuuuuup... Never, ever, _ever_ do a Major Thing on Friday afternoon (bonus points if that's 5 PM and you leave work at 6... uber points if that's 5:55 PM xD). Just NO. I had to learn to say "Dude BossMan, NO - this will explode somehow, and Friday at 5:30 PM is NOT the time to do it. Let's look at it with fresh eyes on Monday."
DBeaver allows you to create a Production connection and save it as such, which automatically creates transactions for every DML query you do. That would have saved him.
Unless he's got autocommit set to true. Then it treats all queries as individual transactions. So technically no ROLLBACK.
@@conceptraton production connections this disabled by default
Really? I had configured mine just for the red boarder and confirmation text, had no idea it also automatically wrapped everything in a transaction! (Of course, prod connections are exclusively readonly in my company....but still)
@@DeadmanstrollnSure, connection types are configurabled - in Window/Preferences/Connections/Connection types - it has option "auto commit by default", i disabled it for my production connections so i have manual transactions
I know it's 1 month late, but you beat me to it.
When the Euro was first introduced, I set the exchange rate for a top tier bank trading system. I requested three other people, including management, to stand over my shoulder as I submitted the rate.
😂 that's the right call.
The best part- DBeaver actually lets you flag different connection strings as prod/dev. If you run anything against a non-dev environment, all commands are automatically wrapped into a transaction and you have to actually manually confirm changes by hitting a "commit transaction" button. It also lets you set the colors of the various environments so there's no "oopsie I ran this in the wrong place" scenario. This was actually the one feature of DBeaver I thought was a major improvement over SSMS and was surprised this sort of thing wasn't standard. This guy didn't learn the tools he was using, and looking at his resolution in the article, he still hasn't.
This is such a pro feature and saved me before, and changing the whole UI theme based on what DB the command/window is running on is also very powerful (red - production, green - qa, normal grey - dev)
@@MatthewSwabey he means pro as in "good" dbeaver is completely free and its quite awesome :)
DBeaver doesn't do that if you have a semicolon at the end of your overall statement. He forgot the semicolon and so it ran the block he was focused in, adding the semi-colon in automatically at the first double newline. Don't forget semicolons.
Literally my first thought. I never understood "oh, it's easier, you don't have to type semicolons anymore", no, NO, keep the syntax a bit tedious, put a little speedbump in the train of thought, thank me later.
I don't get why it would ever think that a blank line means the end of the query. That seems like a really bad design. Even e.g. mssql, which does not need semicolons, does not do this.
For postgres, just use psql. If you leave a blank line, nothing happens. If you forget the semicolon, nothing happens. You add the semicolon and then it runs.
@@georgehelyar agreed. My company uses beekeeper and it separates using semicolon. If two queries are separated by a newline, it throws a malformed query error but never does shit like "executing partial query because I think new line means end of the query"
what's the point of the semicolon if the parser can't be bothered to fail when the friggin semicolon is missing all right?
tldr: if the semicolon is optional, the semicolon is useless (in this particular scenario)
UPDATE weekend
SET relaxation = 0, sleep = 'minimal', stress_level = 'high'
WHERE plans = 'fix_deleted_database';
Ironic lack of transaction hopefully? 😂
@@Kane0123 can't rollback the gone weekend :C
you forgot to add to the where OR 1=1 :)
If you tmux, connect to production databases through a script or something you can set up the script to run `tmux set -p window-style bg=,fg=` to have some different colors in those terminal splits. I use bg=grey,fg=red to really make it pop.
You can probably make it work with the right escape sequences outside of tmux as well.
I remember back in the 90s or early 00s, a colleague said "oops" and veeery carefully typed rollback instead of commit, after realizing he'd forgotten the where statement. I've never really worked with databases/tooling where anything is committed without explicitly doing so. Even so, mistakes can always happen so keep those archive logs on hand (or whatever your database has as a way of restoring to any point in time).
I did this exact same thing once. I realized what I had done while it was taking too long. In panic, I quickly yanked the network cable out of my laptop while the query was running. No time to try to rely on the GUI to cancel the query. The forced network error successfully rolled back the query.
Defensive response from the author:
Yeah I fucked up with DBeaver, many settings could have saved me.
Regarding Chernobyl caveat - I agree. I added it because I was afraid exactly of those dick comments.
Thanks for putting it together, it was laughing hard through most of it :) Definitely an improvement to the written form
One of the first things I do when wanting to manually update or delete records is to simply run a select query. It doesn't take long to simply verify that the records you are selecting are the ones you actually want to meet. Obviously any edge cases ect might not get caught especially if there are a ton of records and you matched to a small number you didn't mean intend to. You should also still have a recovery plan, ect as well.
Still, sometimes things might go unnoticed, and right after the query is ran it goes to brain like a lightning that you did x,y,z wrong. It's almost like brain knows you are going to f up, and waits until it happens, then tells you.
As far as building "emotional resilience" goes, it really depends. If you're dealing with a situation that is just legitimately tough and kicks your ass, that is something worth getting through. Spending time on a problem to learn and grow is excellent. The more you learn, the more you naturally develop that resilience.
But, if you just throw a person who can't swim in the water with no guidance, there is a good chance they're going to drown. They may never want to go in the water again. Perhaps they had legitimate potential that was sabotaged by a crappy environment. That legitimately sucks.
It's a tough one for sure. In general I feel like the feelings of the person being given feedback to are not a good gauge for feedback quality, at least in the very moment. Sometimes the feedback has to make you feel bad in order for you to grow as a person, but as you said some people are so unprepared for handling that kind of feedback that they learn to avoid the risk altogether (your drowning analogy) instead of learning from the experience.
At the very least total conflict avoidance can easily be much worse than going a bit overboard with the criticism. Someone has to be able to talk about the elephant in the room, and hopefully your manager is a person who can do that when it's necessary.
This is partially a culture-fit issue too and it should not be taboo to acknowledge that not everyone is suited for every type of job or situation. Ideally the environment would support growing a healthy mindset for both how to receive feedback and how to give it. I do agree with prime here that nowadays the scales seem to tip way too much in the territory of conflict avoidance which lets issues fester rather than being addressed. Addressing mindset issues can be very uncomfortable and the average person probably does not enjoy making others uncomfortable, so it's easy to see why this'd be the case.
There are so many stories online of bosses who pretend that everything is fine & give "positive" feedback to an employee, only for the employee to be fired "out of nowhere". This is exactly the kind of conflict avoidance that's bad for everyone involved.
Personally I try to focus on being truthful, fair, and compassionate but also to not avoid conflict or giving feedback which would make others uncomfortable if there's a real issue. Conflict is much better than no communication at all.
BEGIN TRY
BEGIN TRAN
--your shit here
ROLLBACK TRAN
--COMMIT TRAN
END TRY
BEGIN CATCH
--your ass saving error message
ROLLBACK TRAN
END CATCH
Every DML Sql script I write is in that block, no matter what. You only ever drop a prod dB once before you learn how to code defensively.
A try catch would have done nothing. It was a singular statement that didn't fail.
@@toabthis has a begin transaction with the commit transaction commented out and a rollback in place. The try catch is probably there in case a udf or stored procedure called blows. This execute once but roll it back so effected row counts can be checked, then enable the commit is a very common pattern (when live running Sql in prod)
@@goraxe01 don't run things that "blow". That is some junior level space brain right there.
More importantly, the amount of production code many places use that read dirty isn't zero. Do you understand why intentionally pulling a "haha just kidding" with data has the potential to cause issues? Yeah in a perfect world everything only ever reads committed data. We never inherit perfect worlds, and the last guy was always the world's biggest dummy.
Fortunately, in the case from the video, Postgres doesn't allow dirty reads, but yes this is true you need to know and set your isolation levels.
To add to the "should have turned on Dbeavers production environment option", when you do that it flashes a big warning about the operation not having a WHERE clause, which would also have saved them.
When i was a junior, a lead came and asked me to modify data in prod such that decimal precision of order total field is reduced by one digit. They were having typical floating point issues, something not rounding the way they want. I explained 4-5 times if he understand that this is a prod db and this will technically loose data (and money cos those totals are the amoutn others owe us). He said yes. I tested, everything was done as asked. Later he got hammered by his superior for this stupidity and was mad at me... i was pissed but couldnt say anything.
I quit after few months, took a new role and literally doubled my salary.
I love when the standard query language is implemented in a non-standard way
I think the guys designing SQL choosing to have the 'where' part after the delete/update part is one of the most expensive design mistakes ever made.
Agreed.
SQL desperately needs replacing with better RDBMS language that implements the 40 years of experience we have with DBs...
You can still have the same issue unless the where clause is required.
@@TehKarmalizer Which I think it should be (and I realize we have the benefits of decades of hindsight). `UPDATE orders SET is_deleted = true` with no WHERE clause should either be an error or match *nothing*, not *everything*. I think that makes way more sense, both in terms of safety and semantics. To me, an UPDATE with no WHERE is an incomplete statement.
Imagine if the engine was a human assistant: if given the query above, a good one would ask for clarification: "Which records?" Not just assume all. If you actually *do* want to update everything, at the very least require that explicit declaration in SQL-speak: `WHERE true`.
@@TehKarmalizer imagine the where clause being required, but the AI auto-completing with `where 1=1` 😂
Foreign keys save your ass sometimes tho gotta love em :)
Always SELECT manually before you UPDATE manually. Also, always use transactions on any prod env (any env, really)
In DBeaver you can execute a select statement and then edit your desired values in the grid editor and then press the save button.
This way you can't accidentally destroy your database because you do not run any manually created update statements.
Has Primeagen ever gone through the classic story of a jr dev nuking the prod server on his first ever day?
Love that story
Who would give a jr dev prod access day 1?
@@xeridea The same guy who would use actual prod credentials as example for setting up a test db
Classic move! Works fine on Microsoft SQL management studio too. Been there done that, the feeling when a simple update/delete query takes far too much time.. that is when the sweating starts :D The great takeaway is USE TRANSACTIONS even if You have to update only a single record.. it is far less damaging if someone screams at you that you have locked some tables (if you forget to commit) than restoring production backups
I have several handy tricks to prevent the situation:
1. make a quick backup table. this obviously isn't practical if the table is huge, but if it's smallish: `create table lifesaver as select * from next_victim;`
2. instead of deleting first, change it into a select count(*), and run that to test how many rows you're about to crater. if the count it returns isn't the number of rows you're wanting to update, then you know to fix the query. once it's good, just change the `select count(*)` to `delete` and then run it for real
3. disable auto-commit. I highly recommend this for production. it gives you a chance to "undo" before you finalize the change permanently. you run the delete. it says "5,338,902 rows updated". you say oh shit, and type ` rollback; ` and everything is back to normal. just don't forget to commit afterwards so you don't leave an open row lock that causes everything to implode after you log off for the weekend
DBeaver has a "production" flag you can put on the connection so that everything happens inside a transaction and it forces you to manually commit or rollback.
Something i learned to do when update/delete stuff from a database that matters is to always do it in a transaction furst which you then rollback. It will still show you how many lines have changed and if everything looks good you just change rollback to commit and you're good. It's basically no extra error, it's litterally the same query rather than changing a select a delete.
Could you please share a sample query for both ways of doing it, to better illustrate what you're talking about?
Also, using EXPLAIN would probably have helped to see the error instantly. But yeah, I also thought it was lacking a transaction, and I don’t know shit about SQL.
@@nchomey You start with "BEGIN;" and then execute the query you want. If you realize something went wrong (based on number of rows affected for example) you simply type "ROLLBACK;'". If you are satisfied with the results then you type "COMMIT;".
this is exactly the right way to do it. but, i even avoid writing these scripts if i can, by using the database editor to edit records manually
@@nchomey
i hope this helps. it's an example for Microsoft SQL Server (using Management Studio):
BEGIN TRANSACTION;
DELETE
FROM Employee
WHERE DepartmentID = 13;
ROLLBACK; -- cancel changes
then it automatically shows a message with the number of rows affected. if the number of rows in the message looks OK, then you can now replace ROLLBACK with COMMIT and run it again to make it permanent:
BEGIN TRANSACTION;
DELETE
FROM Employee
WHERE DepartmentID = 13;
COMMIT; -- save changes
or if like me, you want to actually see the actual resulting data first instead of just the number of rows affected, then you can add a SELECT after the DELETE statement in the transaction and do the same:
BEGIN TRANSACTION;
DELETE
FROM Employee
WHERE DepartmentID = 13;
SELECT * FROM Employee; -- to see what the data would look like after the DELETE
ROLLBACK; -- cancel changes
At my first job in my career I ran 1000's of queries and updates on live production systems, not a great practice but since we even didn't have QA or dev it was the only option. I never fucked up, but one of my colleagues did, luckily we had backups every thirty minutes and were able to revert it in less then 20 minutes.
I learned from this quite a bit and I am never scared to touch a production system because of it, which a lot of people I work with are, they are scared to the point of paralysis
Seriously, nobody can blame your colleague. The entire process is fucked up.
There was no process, and many companies still operate like this today which is the insane part of it
Scared to the point of paralysis isn't necessarily a bad thing. If there are safeguards that could be there, but aren't, it's usually pretty wise to just put the safeguards in place.
Little bit like handling a gun with the safety off. I know I'm safe enough with firearms that it's very unlikely to actually go off, but I'm still gonna insist that the gun has a safety latch because a lack of safety just isn't necessary.
I am working in one of the biggest banks in Europe as a business analyst, and this is actually how we are doing all of our delete statements - directly in the teradata studio or sql server management studio, there is no api or anything like that, we are directly querying the prod db. We don't even have a working test environment set up since a few months, so I am testing everything manually in temporary tables in the local env xD
This is probably one of the scariest IT things I've ever read..
@@trashmail8I know a bank that does this, but they take a backup/snapshot/diff/delta every 15 minutes and a milestone every 3 hours. Meanwhile some guy in a dungeon is deleting and updating rows from the main database. Insanity.
On DBeaver that he used, if you connect to a server externally to your computer and not in "localhost" string, by default it has auto-commit off, so he forgot to add that he put off the security mechanisim to having to transact the query in external/production servers.
Just for the posibility of this situation I was never separating WHERE keyword from rest of the query via newline. Because I saw a friend melting in front of me doing that mistake, but with an actual DELETE query and without a backup
i work in a company that is quickly growing, so we have some weird things going on like, already over 100 people employed, complicated db cluster etc. but we still often work directly on the production database. so far we never had huge issues and i've done wild things like directly manipulating the schema while production is not even in maintenance mode and such. Stories like this are a real nightmare and it's often why it takes me a long time to release even small things. I have to test everything manually locally and on staging before i feel ready to do it in production.
Killed me with the "Hey! how was your Saturday?" in the the team chat.....got me dying..
Batch API calls to do the delete.
On a side note to Chernobyl, I purchased a copy of Introduction to System Safety Engineering from MIT Press which goes through some details of Chernobyl, Three Mile Island, and Fukushima 👍
If you set the connectiontype to "production" in DBeaver the default is that it opens a transaction automatically whenever you do something.
I recently learned a trick. You always start a transaction. If things go wrong you rollback :P
Another simple trick is to use timestamps instead of flags.
That "Postgresql" was so smooth 😂
You can mark a connection as production in DBeaver and it automatically wraps everything in a transaction. Has saved me before.
this is why never set soft delete column as boolean, instead use timestamp, you can easily recover such incidents
One thing I've learned about modifying code substantially is always this, "Make your biggest changes at the beginning of day/week." Especially if the problem is very complex, diminishing returns are real and devastating.
I re-learned at a live event, always use transactions.. my manager wanted me to delete some "orphaned" rows from the attendees table for a report.. I started typing "Beg.. " for begin transaction and he halted me and said what are you doing, just delete the rows.. so I type "delete from attendees", accidentally touched the touch pad, hit enter on "execute", boom all event attendees gone.. we had point in time restore and got them all back in quickly.. but needless to say, I always use transactions when working with data
Bruh, I think your manager was defective.
Exactly the same thing happened to me, the bug in the GUI DB editor saw the EOL where there was no EOL. Lesson learned: USE transaction for update/delete . My mistake happened on Friday.
I had a colleague that ran unit tests in production where there is no test environment so it picked production configuration. Luckily we had a backup and could replay sql queries after the last backup. The colleague kept his job
I need a full, 8 hour DBeaver tutorial soon.
"I'm a senior DBA, I make senior mistakes" a senior DBA told me 😂
LMAO, the buddy system is something I always did at my previous job where we dealt with customer data in production environments. Always verify what I was gonna do was gonna work and not nuke everything after pressing enter.
No Blame debriefs are also good for going through fixing up incidents. You get to talk through the ways to stop something happening again, without overly piling on
For every Production activity, even critical incident resolutions, you prepare an implementation plan and have it be reviewed by a senior and approved by a product owner/stakeholder. Then you just don't step off this implementation plan and ideally you have a coworker watching your every move as you are executing the steps from this plan. This is how enterprises operate.
My man went in there without bulletproof
The weird thing about the whole thing is that Dbeaver actually isuues a warning popup when you execute UPDATE/DELETE without WHERE clause to confirm :D
An old carpenter told me when I was kid, that if I was not making mistakes, then I wasn't getting anything done.
At least he had a backup plan. That in itself is good enough to keep him around. And he learned from it. "He done learned good."
just the title made me hit that like button XD
My immediate thought once I realized he was making direct database changes, "did he start a transaction first?"
The craziest thing happened. You said "press subscribe" and the subscription button highlighted with a colored ring. How could I refuse that?
LOL!
A long time ago, I accidentally ran Dev code on production, and did a drop all.
This was 10:00pm on Friday after a 90 hour work week.
After I realized what I had done, and and recovered from that "Oh I'm effed!" eternity of zen....
I popped up a web message that the the system was undergoing maintenance.
Found that my scripts had done a full DB backup a couple of hours earlier.
I rebuilt the DB again, reloaded the data.
All the while, expecting phones to start ringing, and people to show up barging into the office screaming with anger.
Midnight, I took the servers out of maintenance mode and they all came up and gave me a green status!
Making systems that were crashing a lot, run reliably, was my job as a maintenance developer. So yay!
I ran some sanity checks. Saw some clients log in and complete their transactions, and went home.
For a solid week I expected to get called into an office, and raked over the coals.
No one noticed....
Thinking back on this. If management had noticed, then they would've sent me home, and called in other developers to fix the database.
I suspect that would've turned into an all nighter, as everyone would contribute their own ideas and processes to fix it. And all the while being micro-managed.
I certainly would've been fired.
A brilliant recovery by the dev for only ~45minutes of downtime.
However, I think the main thing that seems to be brushed over was around the impact of the issue for a ‘single customer’.
This person sounds like a senior dev, and this is something I would have very strongly pushed back on in the beginning unless there was any widespread impact.
An issue affecting a single customer should 100% wait until monday, and the fact that a dev is running mutation queries against a prod db, on their own, out of hours just blows my mind!
If it wasn’t for point in time recovery already being set up this could have gone horribly wrong.
Also, depending on the write throughput during now and the last snapshot there is the potential for lost data, all to satisfy a single customer on a saturday morning!
"do not swap dedication for quality" that's a Important quote
I think Prime has my office bugged because his videos are always related to the thing I was talking about yesterday.
that's why i always put where in same line as update or delete. then conditions can be in different line.
RAW dogging story about RAWing Squeel!
"I'd rather have people stab me in the front than in the back" - The Primeagen, 2024
I did a similar thing once as well. In my case I updated all products in an online shop to be t-shirts.
😅😅😅
As soon as I joined my team I was taught: always, always, always set autocommit to false before any DB queries. That way you do the changes, inspect them and commit when you're sure they're right. If in doubts, just roll back.
Why at 13:22 the "productive balance" logo showcasing a total idiotic and dangerous movement while operating that saw? :D
One my colleague said, that Event Sourcing would saved the DB from destroying, and this was not only advantage of Event Sourcing. If you listened to him, you would became the fan of the approach and since then created all your applications over ES... 😂
I did actually LOL at the coffee cup comment 😂😂
we always set a dumb rule on tables which doesn’t allow deleting more than 50% of rows or more than X GB. (Basically cancels the whole transaction if it would hit that).
The amount of times this saved our team an idiot mistake like this is both amazing and bewildering to me.
If I ever have to run anything on prod db, I will backup the table, transaction wrap my select statement, change that to update or delete, double check my backup is there, write sql to copy backup into new table and have that ready to go, then finally run the update/delete. Sometimes I will restore proddb locally and run through everything on that first. But this is in time critical extreme circumstances. Otherwise, I will attach sql statement to job, get it peer reviewed and then screenshare. Thankfully, I learned my lesson of deleting things with no return without having to nuke prod
"Always start with a SELECT" - hell, I always start with a SELECT COUNT!
What kind of organisation allows unfettered access to production anyway?
Definitely agree with you on the stupid need to disclaimer the obvious: yes, nuclear irradiation of thousands of people is nothing like losing an hour of weekend work on a startup. That’s why it’s a comparison not a harm measuring contest.
10:49 I think there's value in validating someones negative perception of their own actions
I think the line "A small human mistake is never the true problem." is just somehting we agree on so that we have a scapegoat when it happens to us.
Well i think his mindset was 1 small error is never enough. Make it 5 small human errors and there goes the production db.
Nah, small human errors are to be expected. People aren't perfect. That's why you need redundancy if you really want the least amount of accidents. The airline industry is so safe because they expect both human errors and technical issues to occur and have systems and procedures in place to mitigate that. Instead of just saying "skill issue" when an airplane crashes, the national authority creates a detailed report with recommendations to prevent further incidents like it.
@@Kenionatus ya, i watch the crash investigation videos too and my god, i wonder how much they actually invest in safety.
Never make production changes on Friday through Sunday unless it's planned in advance.
Engineer doesn't know how to use chosen tools and shoots self in foot. A tale as old as time.
9:42 I thought you said the Designers of "React" were responsible for Chernobyl xD
This shit is too relatable lmao and with people I always follow the rule of not being rude for no reason. If someones fucks up big time there needs to be some sort of punishment, the issue being who was the one that actually fucked up.
❌Reading Article/Posts ✅Watching ThePrimeTime
Wait, when I run a query on production DB it doesn't do anything unless I hit commit at the end. If a query takes such a long time, I would know that something was wrong and I would not have clicked commit. So Why did he set his environment to auto-commit? That is crazy. Commit and Rollback are there for a reason!
thats transactions
As you said ThePrimeagen, if cowboy culture, as well as always using SELECT, also always include LIMIT.
I did the same thing once. But I am obsessive about making backups. So, I just restored everything, and, no one else was the wiser! But it wasn't my fault. They had a database that was called test_db and they talked about it as if its purpose was for testing. They implied that it only held test data. I decided not to trust them and made three backups of it.
I’ve done this a number of times with whole database backups or sometimes just table backups. It has been a lifesaver more than once.
Some clients, I can theoretically get access to the prod db, like maybe I already do if I knew the steps to see it, but I just don't. I never try and don't look at it at all.
Can't mess up prod if I never view it.
Rule no. 1 of SQL club is don’t delete records, archive them
Rule no. 2 of SQL club is don’t delete records, archive them
Rule no. 3 of SQL club is back them up first if you do delete them so you can restore any records just in case…
Oh yeah and as Prime mentioned. Double and triple check your work. Perform a SELECT statement to check your work first.
Write the sql as a transaction with rollback on exception. Test the thing first, on Monday not the weekend and not at 3am.
Doesn't everybody highlight before running a SQL command?
Do the time Pixar deleted Toy Story 2.
I remembered back in my apprentanceship, they let me just thumble arround in PROD... Well... and that was a 4K+ coworkers mediacompany here in germany....
Edit: It was a network management system. But happily I didn't messed up.
Yeah, my Production terminals are in RED to make sure I don't F that up
2:31 but wait, I’ve used DBeaver before, it looks for statement terminators like semicolons, not newlines 🤔
DataGrip will warn and prevent you from doing bulk deletes and updates without a Where clause unless you supress it.
@4:40 Are you arguning pro pair administering here? 😱
NEVER Update before selecting. Insert the select Id results in a temp table.
How about using a datetime deleted_at instead of a boolean is_deleted?
If I wrote a blog post comparing my work performance to the Chernobyl melt down, I probably wouldn’t include it on my resume
Always do a select before you do a delete
I say: people that are always nice are most dangerous ones ...
Always use red theme for prod terminal
Test query locally
DBeaver in the default production flagged connection has auto transaction and manual commit enabled afaik ...
lesson learned : don't do anything on the weekend! unless an asteroid is about to hit the earth, nothing good will ever come from it.
Do it local, then do it on staging. When it looks good, then boom
Write SQL UPDATEs and DELETEs on production at least with these rules in mind (if not using SELECT first). 1) Always write them in one line. 2) Write WHERE filters first, than add table name and other code. 3) Extra for MS-SQL: use BEGIN TRAN and ROLLBACK and look at the number of processed rows. If it's what's expected, replace ROLLBACK with COMMIT.
I don't get why on one line
So that this doesn't happen as in the video. Or if one selects the update/delete code and runs it (as it is frequently done in SQL Server Management Studio), one could forget to select the line with WHERE filter. It depends on the editor, of course. But I always write non-complex update/delete in one line.
"Inner Asmongold" had me laughing so much
before you update you create a select