Keeping your head as an entire database goes pear-shaped
- Reference: 1653897852
- News link: https://www.theregister.co.uk/2022/05/30/who_me/
- Source link:
"Stephen" is the author of today's confession and was faced with what should have been a simple case of applying an update to an Estimating and Invoicing system.
The system ran on a PostgreSQL-database and was, in his words, "Software that I don't touch save when there's an issue, needs rebooting, etc."
[2]
The best sort of software, in the opinion of this writer. However, an update was required and Stephen was the man assigned to the task. He had prepared the installer and was ready to wait for the 30 or so minutes while it ran when he noticed that the backups had not been running.
[3]
[4]
In fact, the backups had not been run in the last nine months.
Wisely, he decided that running a backup before an update was A Good Idea™ and so, after making sure everybody was logged out, hit the manual backup button.
[5]
It failed.
[Expletive deleted] Stephen tried again.
It failed again.
[6]
"Surely I'm smarter than this issue," Stephen thought to himself, and decided that he could make it work. In fact, he could remember some of the steps the vendor had given him and would simply run through those. After all, he was outside of support hours and didn't want the users to come in next morning to find the system both inaccessible and not updated.
He fired up a console for the database and began to run through the steps he remembered. Drop the database, then run a manual backup. That sort of thing.
Being a careful chap, he also took a copy of the directory of the application from the Programs Folder. Just to make sure he had a copy of the data.
At this point, pretty much every DBA is likely yelling at their screens, but Stephen was confident. He had this. He was, after all, smarter than the software.
Once he'd gone through those steps, he installed the update, which worked like a charm.
He fired up the system…
"And was immediately queried for the information needed for a new install."
Uh oh.
No problem. He was smarter. He could sort it. He had the original folder. He manually copied it back and fired things up once more.
Again, there was the new install message.
It must be the silly update. Stephen re-opened the service ticket on the vendor's website, complaining that something was horribly, horribly amiss with the update. He'd run it. It seemed to work. But the database was blank.
Fortunately for him, the service desk was open and he was soon called back by a tech who listened to his tale of woe and each of the steps he'd taken to back things up. There was a silence on the phone before the tech emitted a quiet "Oh no…"
"The next level of consulting," said Stephen, "included me asking why the software doesn't have an email switch for notifications about backup? If I'd known this was happening, I would've reached out much sooner."
The retort was to ask why Stephen hadn't noticed the failed backups earlier?
"I offered that I'm not a daily driver on the database," said Stephen, "I just make sure it's up [and] running and even if I connected, the account I'd connect with was not a privileged user."
And so it went on. However, despite the best efforts of tech support, the database was gone. Because of course it had - there was the DROP command after all. And, let's face it, databases are not normally stored in the same place as the executables…
[7]Beware the fury of a database developer torn from tables and SQL
[8]Confirmation dialog Groundhog Day: I click OK and it keeps coming back
[9]Microsoft Security Experts: Humans and automation to fight off cyber threats
[10]Phishing operation hits NHS email accounts to harvest Microsoft credentials
This writer well remembers an occasion several decades ago where a DBA was so convinced that a running database meant database files would be locked that a swift DEL *.* could be used to remove redundant files. There were no backups then either.
"We decided in the moment to shut everything down to not make it worse than it already was," said Stephen.
His next step was to make The Call Of Shame™ to the owners of the company.
"Hey folks… have a problem running the update… lost data…"
"How much?"
"All of it."
In the end, the nine-month-old backup was restored and recovery experts enlisted in the hope that the missing records could be recovered. No joy.
"So the company was forced to recover using what printed invoices and tickets we had and to forge ahead from there," recalled Stephen.
"It seems that my candor was what saved my rear end from the genuine (and deserved) ire that should have rained down."
"I have since started making multi-layer backups, without dropping the db, mind you, and learning the lesson that if rebooting the server doesn't fix it, it's time to stop and reach out to the vendor."
Stephen sent us a longer list of lessons learned, but for some reason most also involve backing things up. That, and being thankful for understanding managers.
Backups are that thing that always seems to get forgotten about until they are needed to save one's career. Ever thought "sure, I am smart, I can fix this" only to be proven catastrophically wrong? Tell your story with an email to [11]Who, Me? ®
Get our [12]Tech Resources
[1] https://www.theregister.com/Tag/Who,%20Me?/
[2] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=2&c=2YpSVw5aCWgntQNpbTWEzKAAAAI8&t=ct%3Dns%26unitnum%3D2%26raptor%3Dcondor%26pos%3Dtop%26test%3D0
[3] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44YpSVw5aCWgntQNpbTWEzKAAAAI8&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0
[4] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=3&c=33YpSVw5aCWgntQNpbTWEzKAAAAI8&t=ct%3Dns%26unitnum%3D3%26raptor%3Deagle%26pos%3Dmid%26test%3D0
[5] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44YpSVw5aCWgntQNpbTWEzKAAAAI8&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0
[6] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/databases&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=3&c=33YpSVw5aCWgntQNpbTWEzKAAAAI8&t=ct%3Dns%26unitnum%3D3%26raptor%3Deagle%26pos%3Dmid%26test%3D0
[7] https://www.theregister.com/2022/05/23/who_me/
[8] https://www.theregister.com/2022/05/13/something_for_the_weekend/
[9] https://www.theregister.com/2022/05/09/microsoft_security_experts/
[10] https://www.theregister.com/2022/05/05/phishing_campaign_nhs/
[11] mailto:whome@theregister.com
[12] https://whitepapers.theregister.com/
I'd say this is an object lesson for companies to ensure that they hire staff specifically for the purpose of controlling, maintaining and managing their hardware, software and data.
An Administrator of Systems if you will.
(Yes, I've been caught in the "you're technical, you can handle this" catch22 before now. Never again, thanks).
DROP...
Deltree *.*
Kill "C:\" (a VBA classic)
rm -rf
:(){:|:&};: (please dont use this on something that you don't regard as expendable / rebootable).
Amongst another commands that exist for legitimate reasons but very, very easy to misuse. Deltree was particularly vicious, seen as it doesn't operate from the currently selected directory, but rather from the drive selection.
What's the deal with ":(){:|:&};:" ? Is something missing, or this a really strange parser thing in a command line?
It looks strange (it certainly confused the hell out of me the first time I saw it mentioned), but it is basically an obfuscated [1]fork bomb (the explanation on Wikipedia of what it actually does makes it much clearer).
[1] https://en.wikipedia.org/wiki/Fork_bomb
I'm glad El Reg is sanitizing their inputs, otherwise the :(){:|:&};: fork bomb would have hosed the entire site...
They do sanitize user inputs, do they?
Backups
I'm not a DB person, but as an on-site engineer, I once turned up to a customer site where a DB had gone titsup. My first question was "Do you have a backup?" which was answered by someone proudly holding up a box of tapes. The next question was "How do we restore from those?" This was answered with embarrassed silence and looks of complete confusion.
Luckily the DB man was on his way and turned up to fix everything without needing the tapes.
Re: Backups
As the old mantra goes - a backup isn't a backup until it's tested and proven to restore...
Re: Backups
A friend of my worked as an IT consultant and told of a company he occasionally visited, where the head of IT would pick a random day to turn off a random machine and tell his team to recover, just to prove that their processes were correct. He must have had a lot of confidence (and balls of steel).
Re: Backups
Doesn't NetFlix have a Chaos Monkey script that does just this?
Re: Backups
Back in my early days as a VMS operator, the IT director once came down into Ops central, marched onto the machine floor and boldly flipped the Big Red Switch that switched the power off to the entire data floor.
Cue clenched sphincters as we waited (and waited ... and waited) for the backup generators to kick in before the UPS died. Then they marched out and simply said, "We've had a power failure. Call DEC and put the disaster recovery plan into action."
This was, apparently their way of conducting a full resilience test - no, DEC had not been pre-informed of the test either - as far as they were aware, it was a genuine disaster - and the recovery plan involved them trucking in duplicate hardware for all our key machines on what was effectively a mobile data centre. Must have cost [i]someone[/i] a hell of a lot of cash to put that thing into mobilisation.
Re: Backups
"a hell of a lot of cash"
That would be a regular payment to DEC for DR cover, presumably with provision for occasional tests.
Re: Backups
I don't know if they still do, but at one point Amazon had a process that would intentionally crash random servers to make sure all the fail-over stuff was done right.
At least this guy learned from his own and his employer's mistake
In the late 90's I was called to rescue an occupational health company that had all of their information (and when I say everything, I really mean everything: clients, contracts, test results, payroll, every last bit of business information they needed) in a single 700 MB Access file. An hardware failure crashed the .mdb and their most recent backup copy was over a month old. Unfortunately (for them), we were unable to restore it properly, only managing to salvage parts of tables' content, unlinked to anything else.
They ended up losing several contracts over the issue, but do you think they learned the lesson?
Well no, they rebuilt from the backup copy and manually inputted all the missing information from what we'd recovered and their paperwork, keeping everything else as it was....
Dont touch grandmother
In the days of 3340's which you could physically pickup and mount/unmount (and looked a bit like the starship enterprise), spinning disks etc.
One of our testers who was an operator in a previous job, had had problems with the disk containing the master database for the banks customers. He called over the senior operator who said.... we had better try it on a different drive in case the drive is suspect.
It didn't work there either - so it must be the disk. The got out the mother disk. Yesterday's database is copied to a different disk and the batch update run to make today's database (so Mother database begats today's database).
That didnt work either, so the senior op got out the Grandmother disk from the manager's cupboard. Mounted it - and it didnt work either.
So they phoned the manager who said "that's ok - just do not touch the grandmother disk".... "Ahhh too late - came the response".
There had been a head crash on the original disk.
Mounting it on a different disk drive damaged the heads of the second disk drive.
The mother disk was corrupted by the damaged heads.
The grandmother disk was then damaged by the damaged heads.
Fortunately they had a copy of the database which was only a month old, and could reapply the overnight changes which took about a week to do.
And that's when the tested decided to join our company where he could do less damage.
Re: Dont touch grandmother
I remember something very like this happening with PDP-11s in the late 80s - a defective head destroying multiple disks. (I personally did manage to destroy a tape, I have to admit, but didn't lose anything).
Should have used a cloud DB
Then someone else could have dropped the database for him!
Best practice for fsckups
Put your finger up, and admit you royally screwed up!
Not the thumbs up, but usually the index finger, shakily...
I'm not a DBA...
but even I got to the line that started DROP and I was squirming.