Well at least we got a backup, right?

Right???

This is what we in the industry refer to as a “big oof.”

It's a good way to wake yourself up in the morning

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.

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

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.

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.

This is now the correct database.

Rollback.

Legit have nightmares about this.

Just hit Ctrl+Z to Undo

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.

This is missing NSFW tag!

That's what backups are for.

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

Begin transaction;

Then

Your sql here

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

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.

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.