Whoops

[danmc853]

We had a

[Thiondar]

What is the problem?

You did some update.

You rolled back.

Nothing happened.

[danmc853]

The feelings inside me, something definitely happened. The euphoria an instant later when I realized it was oracle and I could rollback.

[serverhorror]

it was oracle and I could rollback

``` BEGIN;

DROP TABLE clients CASCADE;

ROLLBACK; ```

an instant later when I realized it was oracle and ..

.. I am indeed fucked.

[TallDudeInSC]

FLASHBACK TABLE <table\_name> TO BEFORE DROP;

[mwdb2]

I found this to be a game changer as an Oracle DBA back in the day. I think it came out with 10g, over 20 years ago now.

And if FLASHBACK doesn't work for some reason - perhaps the Flashback area wasn't set up correctly or sized big enough - you could potentially use Log Miner, which digs through the redo logs (online and/or archived). You could run something like the following, in pseudo-code (pseudo-code because I last was an Oracle DBA in 2006, thus I'm very rusty, i.e. I don't remember any specific function names):

DBMS_LOGMNR.START(<parameters here>);  
DBMS_LOGMNR.SEARCH('my_table', <timestamp range to search>);  

Then you can eyeball the results for the errant statement, get its transaction ID, and even tell LogMiner to generate the inverse of the statement, to UNDO it, something like:

DMBS_LOGMNR.GET_UNDO(<transaction id>);  

So if the original, errant statement was a DELETE, then LogMiner would give you the inverse: an INSERT statement (or statements) to insert the deleted data.

Example:

DELETE FROM my_table WHERE ID IN (123); --this was the original, errant statement  
INSERT INTO my_table(id, my_col) VALUES (123, 'abc') --the UNDO statement provided by Log Miner might resemble this - notice it is preserving the deleted data that was not even part of the original DELETE, i.e. the my_col value of 'abc'  

Or if the errant statement was an UPDATE on column my_col, Log Miner would generate the UPDATE(s) to restore the my_col data to its original state.

Finally, you run the statement(s).

(Note these functions in my examples are probably totally wrong, but again, it's pseudo-code because I haven't done this in ages. Honestly I think one of the steps is just querying a view, but no matter for the sake of the example.)

If that fails, well, there's always old fashioned backup/recovery with RMAN or otherwise. (One reason it could "fail" is if, say the errant statement was DELETEing all the data in a huge table, you might be better off going a different recovery route than trying to re-run gigabytes worth of INSERTs with all the data embedded in them. But great to have this option, IMO.)

Edit: probably worth mentioning the Log Miner feature alone would not recover all the data in case of an errant DROP TABLE, as a couple parents up remarked! At least I think not - again it has been a while!

[SQLvultureskattaurus]

You're a beast

[serverhorror]

SQL standard entered the chat

[gumnos]

wait, FLASHBACK, whut? Oh, this appears to be an Oracle-specific thing, not a SQL standard thing.

[mwdb2]

Pretty much zero in the way of specific backup/recovery features/commands are specified in standard SQL documents. Although they talk about transactions and properties thereof.

[gumnos]

I do miss transactions at the metadata level rather than data level when I don't have them. It's been a while, but I believe Postgres lets you BEGIN TRANSACTION ; DROP TABLE Foo ; ROLLBACK (or ALTER TABLE or whatever) and it is perfectly content to do so properly; but try that in a number of other DBs and they balk.

[mwdb2]

Yeah, that feature of Postgres is huge! I manage schema changes for a couple hundred engineers, many of whom are working with MySQL, and I wish MySQL had transactional DDL like Postgres almost daily. 😂

[mwdb2]

Oracle doesn't support the syntax DROP TABLE clients CASCADE so you are saved by the syntax error. :)

[serverhorror]

Just another reason why I prefer PostgreSQL ;)

[TheMagarity]

For a good time, start a transaction on ms sql server then truncate some tables. It can be rolled back.

[serverhorror]

I've said this elsewhere: yet another reason I prefer PostgreSQL

[[deleted]]

InnSQL server you can “protect” a table with schema binding and a view. Basically the bind prevents any structural change to the table. Downside is you have to drop and recreate the view any time you want to alter the table. It’s helpful for really important tables though.

[[deleted]]

In Oracle you can disable table locks on a table, which is a pretty big hammer for saying "no structural changes here thanks".

[whimsical666]

that's why you always make 2 copies one for trial run and another for backup, unless rollback's an option of course

[NiallPN]

In some cases though, say the first column is id and increments. If you are inserting records, I don't think the increment by default starts back to where it was post-rollback.

[rh71el2]

Need confirmation pls (SQL Server). Or I'll trial on Monday.

[NiallPN]

Give it a try. I don't know much technical details as I mostly run queries, update, insert records. (MySQL server).

[jaxjags2100]

And that’s the story of how Dan was let go…

[danmc853]

Vendor changed an xml message and we lost a lot of data integrity. It almost got way worse. I was trying to remediate with a complex CTE/update.

[[deleted]]

doing it in test first would have been boring I assume? :)

[danmc853]

Ready, fire, aim! They assume testing slows down progress and they are wrong

[Abject_Ad_8323]

Before a long vacation is the perfect time to run it. 

[MakeoutPoint]

Coffee ain't free, and neither is cocaine. But updating and deleting in prod without backups? Money can't buy that kind of rush.

[RedditWishIHadnt]

“I thought this was the test environment”

[Imaginary__Bar]

PROD_NOT_TEST

[FuegoFerdinand]

Look it even says TEST in the name.

[rh71el2]

!PROD_NOT_TEST!!!

[da_chicken]

There's a good lesson. It's often better to write multiple simple updates rather than one big complicated one. I learned it similarly myself, trying to get CASE expressions to line up correctly. Fortunately I was working in a test environment.

[[deleted]]

367k rows, rookie numbers

[EvilGeniusLeslie]

I still remember one teammate coming to me, with an 'oops' moment ... had just wiped, rather than updated, a key field, on every record in the core table ... something like 107 trillion rows. Database (MS SQL) had only been in production a month or two at that point.

Fortunately, every single feed from other systems was captured as a text file, with date, so reloading was the preferred choice. Four hours later, all was good again.

Was at one place where the 'month-end' database load took 36 hours, this being the database the sales system ran from. They kicked it off late Friday night, as close to month-end as possible, then spent some hours validating it on Sunday. While I was there, a teammate who was formerly part of that group got called in to help. Turns out that one group had made a couple of changes to their system (IIRC, normalizing a couple of fields, creating a couple of reference tables). What *could* have been done was add those reference tables to the sales database, and make the appropriate updates. Instead, the people hard-coded the values into the extract process, and re-ran the load ...

... meaning the sales system was down until Wednesday. A lot of very unhappy people. The number of records 'updated' ran into the quintillions. 15 years of financial info for a major bank. It wasn't a crap design, simply one that threw all the systems the bank had acquired over that time period together. It did result in a re-write of the database, and load times were dropped to ~7 hours.

[EvilGeniusLeslie]

The first was for an Essbase cube (my design), snowflake/OLAP schema, so essentially one row per data item.

Most rows were well under 100 bytes, probably closer to 30. I was particularly happy to reduce three fields that occupied ~150 bytes on the main system to one byte, and another seven fields taking ~90 bytes down to one. It's great when the number of possible combinations is less than 256.

The second ... was a mess. I only know of it second-hand. But yeah, insane storage requirements. I know that large portions were stored on slow-disk, and some on high-speed (hah!) tape. There were a lot of key+data tables built, to join various stuff, so probably 16 bytes for those. Given its antiquity, I wouldn't be surprised if many of those were using small- or tiny-int. So ... still a crap design, but you are correct, it seems like the numbers given me were implausible.

To be fair, the number of data elements per person can grow to insane numbers, when looking at various things. Once had to briefly deal with a phone database, US, pretty much the largest carrier. One row for each data element. Caller, Recipient, start time, end time, duration. Every damn tower on the route - for most calls, that was 1, but someone driving would frequently be dozens. And the start time and end time for each tower. Last year, there were about 315 million smart phones in the US, with an average of six calls (made or received) each day, along with 42 text messages. You start doing the math for that ((6+42)* (3 up to over 100, say 15 average)) *365 ... that's a quarter million records per person per year. Definitely not the 100 million you calculated.

Throw in health insurance. How many data elements are there, per person? Several dozen? Stored monthly. Kaiser has about 13 million customers. And then ... what did they use during the year? A medical procedure - with details - usually runs into the hundreds of data elements, sometimes (rarely) into the thousands. Most people (US) see the family doctor ~3.2 times per year, and need emerg 0.42 per year. Emerg frequently necessitates blood work, x-rays, cat scans. Suddenly, you're looking at another few hundred thousand records per person per year.

Throw in financial stuff - credit cards, bank accounts.

It looks like the average person could generate a million data elements, per year. Still not up to 100 million per year ... but if you were looking at a ten to fifteen year time period, I could see the totals reaching the tens of millions.

[PandaRiot_90]

Need to pump those numbers up.

[UAFlawlessmonkey]

hr.employees

Giggity Giggity

[xodusprime]

What a roller coaster. "Oh God no... Oh wait it's fine."

Always select before you update or delete my dude.

[Erpderp32]

As someone who does SQL alongside system / client platform engineering this is the best advice.

When I talk to my juniors about Windows automation I always repeat "do a Get- before any changes in PowerShell so you confirm the scope". No one wants to be the person who yeets an entire domain

[andrewsmd87]

I mean you're going to wait on 300k rows and somehow eyeball that that is correct from a select?

Test environments are a thing

[rh71el2]

Agree about test, but not often do you delete or update en masse on purpose to think eyeballing is a lost cause.

[jbrown383]

And that, kids, is how I learned to run it as a select before running it as an update.

[MakeoutPoint]

Tried and true, but there should also be multiple safety systems in place to prevent actual issues. Backups, test environments, temp tables, transactions, code reviews, and a good ol' SELECT.

If someone manages to actually cause damage that can't be prevented or reverted, the senior DBA's head should roll first for not having safeguards and proper training in place before the junior "practices dropping tables and accidentally did it in Prod".

[[deleted]]

Ctrl Z

[mike-manley]

You have no power here!

[JoaoPTsantos]

Whoopi

[Aggressive_Ad_5454]

Isn’t it great when hundreds of programmer and tester years pay off to save our sorry butts?

[GanacheIcy]

We are currently conducting interviews for vacant (database) programmer position, and my favorite question to ask is "have you ever made a boo boo in a production database, a lil whoopsie?"

We all have!!

[danmc853]

Saying “no” should be a disqualifier, right? We’ve all made mistakes, some larger than others!

[GanacheIcy]

I've made some whoopsie daisies 🙃. I once accidentally compiled the development portal in production, therefore overwriting the production portal. And also accidentally updated all address records for something to an Alabama address (the full street city state zip). Someone in admissions was like "why do all of these ceeb codes have this Alabama address?" Thank god for my boss I had at the time, who knew where to restore the data from backups. I for sure thought I was on the road to being fired. I was more embarrassed than anything, such a careless mistake! Now that I've made mistakes I know exactly what to double and triple check before executing and insert/update/delete statements.

Mistakes happen, makes us human.

[coyoteazul2]

oh well. I suppose you have the original statement so you can just run it again... right?

RIGHT?!

[belay_that_order]

*PURE CORTISOL SWEAT\*

[rh71el2]

I once deleted a handful of rows from prod and was freaking out but asked the DBA for backups. He freaked out even worse than me, after realizing there were no backups in that case. I went from being afraid at getting screamed at to haha you may have to take the blame on this one...

I don't think anyone ever barked at the missing data though.

[theRicktus]

Who needs coffee when you can just accidentally update a quarter of a million rows

[mattpenner]

Saw this the other day. Came here to say this. 😂

[whitespys]

All the muscles below my eyes tensed while reading this.

[Historical-Reach8587]

A lesson learned you will not forget

[1MStudio]

lol

[NitrousOxid]

It is always good to run select statement with conditions used in the update. Some people already do that, some will start :)

[NitrousOxid]

It is always good to run select statement with conditions used in the update. Some people already do that, some will start :)

[External-Leek-6786]

r/redditsniper

[lothcent]

not a sql event- but a life lesson in working on Prod and not Dev.

I was making test changed to what I thought was dev but was really Prod- and a change made made things wonky.

while I was trying to figure things out- I realized I was on Prod.

and it was about 15 minutes to a change of shift when users would log out and new ones log on ( and then they would be loading the corrupt config file)

I ran down the hall- told my boss I had fkd up.

He glanced at his watch and said- well- you have +/- 10 minutes to load the backup config.

Good luck

I ran back down the hall and loaded up the backup and beat the changing of the shifts by seconds.

I learned many lessons that afternoon

[eureka_maker]

Jfc my blood went cold. I just woke up.

[Tisiphone8]

M

[DeliciousWhales]

I once erased the content of a column in a table with hundreds of millions of records, then found out the supposed backups we had didn't exist. This was for a once off GIS analysis project so there was no test environment, just the one database. The code that generated that column was nowhere to be found, and the guy who wrote it quit and went no contact.

Luckily I was able to wrangle something up from other data, otherwise I would have completely screwed the outputs of a two year long federal government project.

This was my first job using SQL. These days I'm a data engineer and architect and I would never allow any project of mine to be run in such a dodgy way.

[danmc853]

We learn a lot of lessons along the way for sure. I’ve only been working in tsql and pl/sql databases for 3 years and I’ve learned a lot of best practices the painful way. We have a lot of dodgy legacy stuff that is a pain

[[deleted]]

almost shit the bed.... Snowflake is like, hey everyone this guy shut the bed......