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.
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…
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.
I may have noticed this on a certain other aggregator site once upon a time, but I'm still none the wiser as to why.
199 rows kind of makes sense for whatever a legitimate query might have been, but if you're going to make up a number, why 2^23? Why subtract? Am I metaphorically barking up the wrong tree?
Is this merely a mistyping of 8388608 and it was supposed to be ±1 row? Still the wrong (B-)tree?
In a place for programmer humour, you've got to expect there's at least one person who knows their powers of two. (Though I am missing a few these days).
As for considering me to be Ramanujan reborn, if there's any of Srinivasa in here, he's not been given a full deck to work with this time around and that's not very karmic of whichever deity or deities sent him back.
For me it's: 2^1 to 2^16 (I remember the 8-bit era), a hazy gap and then 2^24 (the marketing for 24 bit colour in the 90s had 16777216 plastered all over it). Then it's being uncomfortably lost up to 2^31 and 2^32, which I usually recognise when I see them (hello INT_MAX and UINT_MAX), but I don't know their digits well enough to repeat. 2^64 is similar. All others are incredibly vague or unknown.
2^23 as half of 2^24 and having a lot of 8s in it seems to have put it into the "recognisable" category for me, even if it's in that hazy gap.
Ah reminds me of the time (back in the LAMP days) when I tried to apply this complicated equation that sales had come up with to our inventory database. This was one of those "just have the junior run it at midnight" type of shops. Anyway, I made a mistake and ended up exactly halving all inventory prices on production. See OP's picture for my face.
Ive had one of those moments. Where you fuck up so bad that your emotions wrap all the way around from panic, through fear, confusion, rage, dread and back to neutral, and you go 'Hmm..."
(If the query returned the expected amount of affected rows)
COMMIT TRANSACTION
(If the query did not return the expected amount of affected rows)
ROLLBACK TRANSACTION
Note: I’ve been told before that this will lock the affected table(s) until the changes made are committed or rolled back, but after looking it up it looks like it depends on a lot of minor details. Just be careful if you use it in production.
If for example a client application is (accidentally) firing doubled requests to your API, you might get deadlocks in this case. Which is not bad per se, as you don't want to conform to that behaviour. But it might also happen if you have two client applications with updates to the same resource (patching different fields for example), in that case you're blocking one party so a retry mechanism in the client or server side might be a solution.
Just something we noticed a while ago when using transactions.
You should take it upon yourself to make regular backups in case you fuck up really bad.
I had an intern that deleted everything on its fifth day, luckily l was automatically making backups two times a day, so it was fine.
Company was a shitshow, new features or changes were expected immediately, so we got used to work directly on prod. I told him to test anything on a dummy DB and show me before we submit it, but he got around it when I wasn't looking. The security tools were garbage, I wasn't allowed to change permissions.
I actually screwed up twice on dev environment. Luckily the second case was salvageable without using data from an old backup(I wasn't given one that time) and I managed to sweep it up fast.
I started testing my queries super carefully after the first incident, but I was too tired once that I forgot to restrict the update scope for testing and screwed up 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
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 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.
Well at least we got a backup, right?
Right???
It last ran a week ago and we technically haven't tested it. Just our hot replicas which also just deleted all that data.
And of course by now every downstream system replicated AND CACHED that data.
Holy shit the truth with replication deleting the data you needed too true lmao
ah the cold sweat and clenching of the anus
Back up? No, we only go forward in this company
"That's why the windshield is bigger than the rear view mirror, we should be vigilant in remaining forward looking."
Said by an exec in my chain of command when he caused a huge cascading fuck up in the organization and there was no postmortem allowed.
Backup? What is this backup you speak of?
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)
But it’s only, like, a handful of rows 🙃
Must be a technical term.
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!
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
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…
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.
8388409 = 2^23 - 199
I may have noticed this on a certain other aggregator site once upon a time, but I'm still none the wiser as to why.
199 rows kind of makes sense for whatever a legitimate query might have been, but if you're going to make up a number, why 2^23? Why subtract? Am I metaphorically barking up the wrong tree?
Is this merely a mistyping of 8388608 and it was supposed to be ±1 row? Still the wrong (B-)tree?
WHY DO I CARE
Are you Ramanujam reborn or a nerd who put every number they found on wolfram alpha?
In a place for programmer humour, you've got to expect there's at least one person who knows their powers of two. (Though I am missing a few these days).
As for considering me to be Ramanujan reborn, if there's any of Srinivasa in here, he's not been given a full deck to work with this time around and that's not very karmic of whichever deity or deities sent him back.
I know up to like 2^16 or maybe 2^17 while sufficiently caffeinated. Memorizing up to, or beyond, 2^23 is nerd award worthy.
I know that 2^20 is something more that a million because is the maximum number of rows excel can handle.
For me it's: 2^1 to 2^16 (I remember the 8-bit era), a hazy gap and then 2^24 (the marketing for 24 bit colour in the 90s had 16777216 plastered all over it). Then it's being uncomfortably lost up to 2^31 and 2^32, which I usually recognise when I see them (hello
INT_MAXandUINT_MAX), but I don't know their digits well enough to repeat. 2^64 is similar. All others are incredibly vague or unknown.2^23 as half of 2^24 and having a lot of 8s in it seems to have put it into the "recognisable" category for me, even if it's in that hazy gap.
So I grabbed a calculator to confirm.
Ramanujan reborn - the main protagonist from the Wheel of Maths books.
And you can save a bunch of time by inlining all this into one query
The four horsemen of the datapocalypse
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.
Ah reminds me of the time (back in the LAMP days) when I tried to apply this complicated equation that sales had come up with to our inventory database. This was one of those "just have the junior run it at midnight" type of shops. Anyway, I made a mistake and ended up exactly halving all inventory prices on production. See OP's picture for my face.
In retrospect, I'm thankful for that memory.
Ive had one of those moments. Where you fuck up so bad that your emotions wrap all the way around from panic, through fear, confusion, rage, dread and back to neutral, and you go 'Hmm..."
Yeah that's a good way to put it. It's like so close to the thing you were dreading, that it's a sort of sick relief when it actually happens.
It's like...
In T-SQL:
BEGIN TRANSACTION
{query to update/delete records}
(If the query returned the expected amount of affected rows)
COMMIT TRANSACTION
(If the query did not return the expected amount of affected rows)
ROLLBACK TRANSACTION
Note: I’ve been told before that this will lock the affected table(s) until the changes made are committed or rolled back, but after looking it up it looks like it depends on a lot of minor details. Just be careful if you use it in production.
Lol why did I have to scroll so far to see ROLLBACK
Because this is c/programmerhumor and the OP hasn't covered ROLLBACK yet in his sophomore DB class.
If for example a client application is (accidentally) firing doubled requests to your API, you might get deadlocks in this case. Which is not bad per se, as you don't want to conform to that behaviour. But it might also happen if you have two client applications with updates to the same resource (patching different fields for example), in that case you're blocking one party so a retry mechanism in the client or server side might be a solution.
Just something we noticed a while ago when using transactions.
This is now the correct database.
Rollback.
I don't understand environments that don't wrap things in transactions by default.
Especially since an update or delete without a where clause is considered valid.
SQL Server technically does behind the scenes, but automatically commits, which kind of defeats the purpose.
No transaction to rollbackLegit have nightmares about this.
Just hit Ctrl+Z to Undo
That's what backups are for.
Checking the backups... Ah yes, the backup done in August 2017.
Hello boss, I broke the company. I'll see myself out
If you don't have apt backups, that is a failure of the process, not yours.
Plot twist, they were also the one responsible for developing the backup process.
You should take it upon yourself to make regular backups in case you fuck up really bad. I had an intern that deleted everything on its fifth day, luckily l was automatically making backups two times a day, so it was fine.
Why would an intern be allowed anywhere near prod DB? Do you not have lower environments?
Company was a shitshow, new features or changes were expected immediately, so we got used to work directly on prod. I told him to test anything on a dummy DB and show me before we submit it, but he got around it when I wasn't looking. The security tools were garbage, I wasn't allowed to change permissions.
Sounds fun! As long as you have no stake in the company lol
I left to pursue my studies, the intern took my place and was put in charge of everything, I don't know how he's doing now and I don't really care.
Yep I do that on a local project basis before I make any updates. Saved me a couple times from my own mistakes 😅
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 missing NSFW tag!
I actually screwed up twice on dev environment. Luckily the second case was salvageable without using data from an old backup(I wasn't given one that time) and I managed to sweep it up fast.
I started testing my queries super carefully after the first incident, but I was too tired once that I forgot to restrict the update scope for testing and screwed up again.
'content':)Whenever SQL databases come up, I always think of this video from one of the greatest 'content' creators in history.
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
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.
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!"
oopsie daisy moment
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 @[email protected] 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 giving me PTSD
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.
I'm dying lol
wasnt halloween last week. stop scarin me
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.
BEGIN TRANSACTION is your friend
why do i know this is sql sever default font