Had something like that happen to a local dev database (thankfully). A dev next me blurts out “how to I rollback an update in SQL server”? He was used to Oracle and how easy it is to rollback something. Had to explain that commit just happens in SQL server regardless of whether or not you put that commit line in.
For everyone’s sanity, please restrict access to the prod DB to like two people. No company wants that to happen to them, and no developer wants to do that.
I don’t use readonly with dbeaver, but I do have the prod servers set to automatically make transactions and have to hit a button to commit. Almost certain it asks confirmation that I want to make the changes to prod which is nice too (I rarely have to touch our sql server prod)
I would say you can expand that on the following criteria: 1) a lot of people can have read access, but only a few should have write access, and read access should be restricted to specific tables without PII. 2) The people with write access should go through a Change Approval process: they submit the SQL they’re going to run and someone else approves or denies it before it can be done. 3) Every piece of SQL that modifies a table should be annotated with a comment and the ticket number in it in which that change was approved. 4) You should be able to rollback any committed change within an hour of it happening.
Just a funny story. All of our devs and even BAs used to have prod access. We all knew this was a bad idea and put in a process of hiring a DBA.
I think in the first two weeks the DBA screwed up prod twice. I can’t remember the first mess up but the second he had a lock on the database and then went to lunch.
We eventually hired two awesome DBAs to replace that one but oh boy.
Imagine being hired to help prevent people from fucking something up, only to fuck that thing up in your first week—not once, but twice. You’d think after the first time it wouldn’t happen again…
Double/triple check the messages/console for results. Look good?
Commit;
Worried?
Rollback;
Just be sure to mind your transaction logs for long running queries and by all things holy be sure you’re not doing this to a live db with a ton of transactions since you’re basically pausing any updates until the commit or rollback on the affected tables
For MS-SQL. If it is production, it has a full transaction log, right? I mean I know for development use I turn that off, but for live data you want that on. You should be able to roll back to any point since the last time it was truncated. Or right before hitting return to whatever level of accuracy you’re comfortable with.
You are not logged in. However you can subscribe from another Fediverse account, for example Lemmy or Mastodon. To do this, paste the following into the search field of your instance: !programmerhumor@lemmy.ml
Post funny things about programming here! (Or just rant about your favourite programming language.)
Rules:
Posts must be relevant to programming, programmers, or computer science.
No NSFW content.
Jokes must be in good taste. No hate speech, bigotry, etc.
BEGIN
UPDATE
SELECT
COMMIT
Why update before select? Shouldn’t it be the other way around? (I’m clueless. )
The select after the update is to check if the update went through properly. You can have more selects before the update if you wanted to.
Ah. It makes sense now. Thanks.
Be Safe! Be a BUSCer!
The four horsemen of the datapocalypse
And you can save a bunch of time by inlining all this into one query
I’m dying lol
You can also do this by forgetting a WHERE clause. I know this because I ruined a production database in my early years.
Always write your where before your insert, kids.
Always start every command with EXPLAIN and don’t remove it until you’ve run it
I learned the same lesson the same way 😞
This is what we in the industry refer to as a “big oof.”
I thing the technical term for this is an RGE.
(Resume Generating Event)
Must be a technical term.
But it’s only, like, a handful of rows 🙃
Had something like that happen to a local dev database (thankfully). A dev next me blurts out “how to I rollback an update in SQL server”? He was used to Oracle and how easy it is to rollback something. Had to explain that commit just happens in SQL server regardless of whether or not you put that commit line in.
oopsie daisy moment
Legit have nightmares about this.
For everyone’s sanity, please restrict access to the prod DB to like two people. No company wants that to happen to them, and no developer wants to do that.
Me applying for any database access ever: “read only. I do not want write. READ ONLY.”
Datagrip has an option, and likely other database IDEs do as well - “Connect as READONLY”. Makes me feel a little safer
DBeaver does too
I don’t use readonly with dbeaver, but I do have the prod servers set to automatically make transactions and have to hit a button to commit. Almost certain it asks confirmation that I want to make the changes to prod which is nice too (I rarely have to touch our sql server prod)
I would say you can expand that on the following criteria: 1) a lot of people can have read access, but only a few should have write access, and read access should be restricted to specific tables without PII. 2) The people with write access should go through a Change Approval process: they submit the SQL they’re going to run and someone else approves or denies it before it can be done. 3) Every piece of SQL that modifies a table should be annotated with a comment and the ticket number in it in which that change was approved. 4) You should be able to rollback any committed change within an hour of it happening.
Just a funny story. All of our devs and even BAs used to have prod access. We all knew this was a bad idea and put in a process of hiring a DBA.
I think in the first two weeks the DBA screwed up prod twice. I can’t remember the first mess up but the second he had a lock on the database and then went to lunch.
We eventually hired two awesome DBAs to replace that one but oh boy.
Imagine being hired to help prevent people from fucking something up, only to fuck that thing up in your first week—not once, but twice. You’d think after the first time it wouldn’t happen again…
It’s a good way to wake yourself up in the morning
Doctors HATE this one simple trick! Lose up to 100% of MyChart data - and KEEP it off!
Can help reduce blood pressure, high cholesterol, weight, height, gender, name and more to NULL! Wake up feeling NULL and NULL!
Transactions are your friend here
Then
Double/triple check the messages/console for results. Look good?
Worried?
Just be sure to mind your transaction logs for long running queries and by all things holy be sure you’re not doing this to a live db with a ton of transactions since you’re basically pausing any updates until the commit or rollback on the affected tables
Me: “Ok. What’s the big deal.”
Also me: “Less than a million affected. That’s nothing.”
Still me: “Rule 1: Never let pesky details get in the way of a funny meme.”
Ultimately me: 😱😂 “That guy is in for a rough Monday!”
This makes it safer but like… don’t run queries on production outside emergencies ever.
That transaction frame, depending on your specific DB, may cause severe performance side effects.
Look, the safe approach is to write it into something, PR it, get it reviewed, and then run it as part of a structured deployment process.
Absolutely. It should have been tested and verified.
This is giving me PTSD
Looks like little bobby tables is at it again. (edit: for reference: https://xkcd.com/327/)
Edit #2: For lemmy app users: https://xkcd.com/327
And thanks to @Gestrid@lemmy.ca for the correction.
My Lemmy app doesn’t like that parenthese. Fixed link in case anyone else is affected: https://xkcd.com/327/
Sorry about that, I will update future links to not use parenthesis.
No problem. And all you need to do, really, is put a space between the link and the parenthese.
This is missing NSFW tag!
For MS-SQL. If it is production, it has a full transaction log, right? I mean I know for development use I turn that off, but for live data you want that on. You should be able to roll back to any point since the last time it was truncated. Or right before hitting return to whatever level of accuracy you’re comfortable with.
This is now the correct database.