This gives most SaaS providers a bad name. The error here is not the engineer deleting the db, it's the complete lack of data restore testing.
Complex restoring never works well when the first implementation is under the pressure of the real event. Other SaaS providers will be cursing such a big name tool making such a public mess.
Yep. My regular rant: until it is tested what you have the is just a file, or collection of files or other object(s), that might be a backup. You don't really have a backup until you have tested it.
Good point. And in the cloud, restoring a backup can take ages, which is important to take into consideration. In fact, I'm not sure how to do it efficiently other than swapping the drives.
DR testing is hard, complicated and costs a lot. Yes, it should be done regularly, but it's not an easy task; I believe small(ish) companies simply can't afford it.
Then perhaps small(ish) companies shouldn't hold data that's critical to their customers. Just like real engineering companies don't build nuclear reactors if they can't test their safety systems, cars if they can't afford to do crash testing and so on.
DR is not a luxury. Systems that don't properly do DR aren't unoptimized or something, they're badly engineered.
I wasn't referring to this specific case. Also, "don't worry, our services may suck, but not to the point where they bring down your business" is not exactly the kind of reliability one would want to aspire to.
Testing complicated failover systems is hard. Testing backups is not. Especially if you just do a full nightly backup and not something more complex. The options range from "manually restore a random 10%" to "write a moderately complex script to automatically restore and validate everything".
Those all look like more trouble than they're worth before you start, but they aren't that hard. And you'll recover your whole investment the first time you need to restore something and it's just a simple tweak to your standard test practice.
In the DR services my company provides, failover testing is baked in to the cost, and is _mandatory_ annually. It is hard and complicated and expensive, but not as hard as explaining to the customer that "we have your backups, we just can't use them because we never tested whether the software would run on the DR hardware" or "don't worry, the replica of the file server is secure in the datacenter, you just can't log in now because the Active Directory server is tombstoned and won't process your logon." When someone needs their data, what's the difference? Can I see my files or not?
It's more than a cliché that without restore tests, you don't really have a backup-- if the customer won't commit to testing the DR, we won't provide the service anymore. Anyone who pretends anything else is acceptable is kidding themselves.
Apologies if I've put the wrong slant on this — designer not a tech expert. Could still edit the title of the submission if there's something else it should say?
Well, with Dropbox the default setting is to keep local copies of all files in every computer connected to the system, so it probably wouldn't be catastrophic.
There was a bug where Dropbox wiped a few of someone's files. These changes were sync'd to the customer's computers, which dutifully wiped the files.
I think he was able to recover most or all of his data (I forget if it was photos or what) but it was interesting to realize that even with Dropbox's stellar record and pedigree, errors like that can still happen.
It makes you realize data is just so fragile. Our bits are unlikely to last centuries, which is kind of unfortunate. I've wanted to take on this problem in a systematic way somehow. It seems like bittorrent offers a way to make a digital time capsule -- theoretically, if you have seeders, your data will persist forever. So you could imagine setting up a "time capsule" of sorts. Example: $2,000 on Digital Ocean will get you 33 years of server time. So that's one seeder, "guaranteed" to last 33 years. I wonder how far the costs could be reduced? Could you reach the 1 century mark for less than $3k? Costs will continue to go down, so it should be possible to have 10 seeders for your time capsule.
And then of course you have to think of what to put into your time capsule. There's not any guarantee the computing architecture will be the same a century from now. But somehow I suspect Python 2.7 might still run. :) You could write some kind of rudimentary AI program that responds to basic questions, maybe using the data embedded in the time capsule...
The only part I can't figure out is the most obvious one: Digital Ocean probably won't be around 20 years from now, let alone a century from now. So even if you get a bunch of money together, how do you reliably transform that money into running servers over the course of a century?
"The good news is that we have copies of our database that are replicated daily, up until the exact point of time when the database was deleted. We are working hard to retrieve all of your data."
Better news would be if every user had local copies of their work too. both in local storage, and on a cloud storage provider of their choice.
Preferably in a non proprietary format.
This isn't just about getting me to trust your site if you crash or have a tragic mistake. This is also about getting me to trust your site if you go out of business (as too many startups unfortunately do).
I was once on-call working for one the leading organizations. I got a call in the middle of the night that some critical job had failed and due to the significant data load, it was imperative to restart the processing.
I login to the system with a privileged account. Restart the job with new parameters and since I wanted not to see the ugly logs, I wanted to redirect the output to /dev/null.
I run the following command
./jobname 1>./db-file-name
and there is -THE DISASTER-
For some reason this kept popping in my head - "Bad things happen to Good people"
We recovered the data but there was some data loss still as the mirror backup had not run.
Of course, we have come long way since then. Now, there are constant sync between Prod/DR and multitude of offline backups and recovery is possible for last 7 days, the month or any month during the year and the year before.
I was doing a favor to a friend and on a refferal talked to a guy who didnt have anything but weekly backups and had a corrupt database due to some drive failures.
I was able to determine that the corrupt data was repairable if we had a copy of the old db, and since it was a tiny machine system I asked "Would you mind restoring the backup side by side with production and I can do what I need?"
"Sure thing!"
I wait for a minute, and then my connection to the production database dies.
I refresh the client, and now the one database available is restoring from a backup...
I called him and asked if he meant to overwrite his production copy with his backup instead of do it side by side, and he says petulantly, "I didnt do that!"
I ask him to check again, and he responds with "I will call you right back!"
Five minutes later I get the call, "How do I roll back my restore partially through the restore process?"
I have a story about that based on real facts(TM): I once had to reset my own password on a production database and I decided to hash it by hand and UPDATE my row in the users table.
A few hours later we had got a few calls from angry customers who couldn't log in. I had effectively forgotten the WHERE clause so all users had the same password: mine.
Extra points for not having read the "xxx rows updated" line that the mysql console outputs after each query...
I updated the hash and the salt in the same query. They weren't salted against the user id or anything like that, just a second column for the salt, which is... common practice.
I am a bit less convinced by this practice. The general consensus seems to be: "oh you can SELECT around after you've done your update and before you commit; the update." But while you're doing that, you might be holding row locks on all the data your update touched, and meanwhile the lock acquisitions in your app are failing, along with whatever that entails. And if you're not doing that and immediately commit;ing, are you gaining anything?
When the UPDATE or DELETE finishes it will output the number of rows changed. If that number is much larger than expected, something probably went wrong. So there's a gain even if you don't investigate the results in detail.
I agree with parent that using transactions is good practice. In general, naked SQL on a production DB is a bad idea - it is not different to running untested coded in a production environment. Why risk it?
Transactions are a sane safeguard if you absolutely must run SQL on your production database.
This is a lot of manual steps. Why not have migration scripts in your code base. Run them in dev and qa and staging so you know they work. Then you go to prod and are done.
The less manual steps the less mistakes will happen.
I have a very strict habit after once or twice doing this: always start all DELETE commands with "--" (that is, comment it out) until I have written a where-clause.
My command in the buffer might go through these steps:
1. "Delete from"
2. "-- delete from"
3. "-- delete from table where condition limit n;"
4. (Generally either ask a co-worker or make a Jira with the exact command I have at this point, so there's a sanity check and/or permanent record, but for very low risk/especially mundane/especially time critical updates, do it)
5. Delete the "--" and run it.
6. Think hard about adding some functionality in the app for doing it in app code unsteady of in database code.
You can also start all of your commands with "BEGIN;" (at least in Postgres) to ensure you're in a transaction you can easily rollback.
Another trick it to start all "DELETE" commands as a "SELECT" to ensure you get the "WHERE" part correct, and then swap out the "SELECT *" with a "DELETE".
I use the latter almost religiously. Also can be useful to add a LIMIT on the end, just to guard against an UPDATE running amok. And I tend to write my queries 'backwards' (e.g. starting with the WHERE clause, ending with the "DELETE", to guard against executing the query prematurely.
I've turned to creating a temp table and putting the primary keys from the select statement into there so I can guard myself against the thing that was to delete a handful of rows deleting everything.
Plus with that you can do another join and see if those rows have some sort of value in it that you didn't expect.
I also put my DELETE statement on a line by itself and comment out the line. In SQL Server execution processes only the selected text. So I select starting with DELETE then run.
Yep, I've never done it, but I've definitely been aware of how easy it can happen. When I'm in this kind of situation on a production DB without easily restorable backups I usually try to remember to put all my delete/update queries in a transaction. Then commit after I see the appropriate number of rows have been effected.
I dont use mysql, but it actually has a command line flag to help with this problem that always made me laugh, --i-am-a-dummy
This is an alternate form of the flag --safe-updates, this option prevents MySQL from performing update operations unless a key constraint in the WHERE clause and / or a LIMIT clause are provided.
Several years ago a colleague wrote a handy command line tool that was potentially destructive if misused, so naturally the outsourced operations team misused it. In response he added a flag along the lines of “—-yes-i-really-do-know-what-im-doing” that was undocumented and mandatory for the destructive operations.
My personal preference is to either have a "deleted_at" column so deletes never need to happen, or have a "history" table.
When this isn't possible, I do a select for the PK and delete based upon only the PK. This way I can review the rows to be deleted and back them up manually if needed.
All of this is way overkill considering the backups available these days.
I'm a fan of the "Write as a SELECT first" method for any DELETE or UPDATE queries, but in my busier no-sleep days, I was very fond of mysql's --i-am-a-dummy switch[1] on the CLI client, which will "Permit only those UPDATE and DELETE statements that specify which rows to modify by using key values."
Although, these days, I generally avoid mucking around with production DBs directly. It's all manually scripted migrations, and testing said scripts on a backup or unused slave, which is by far the safest, and also helps to avoid running queries that might adversely affect production's performance.
I had a great mentor who taught me all the safety mechanisms.
Start your .sql file with "use xxxxx" (non-existent database name; will prevent execution if you fat-finger F5.)
Always write your deletes as a SELECT first:
SELECT *
-- delete
FROM blah
WHERE blah = 1
Never uncomment the "delete" line; just select the portion of the query after the comment with your mouse and F5 it.
And of course: always design your database with support for soft-deletes, because sooner or later you'll need to add them in.
I initially thought it was weird they had to run several "processes" in case 1 failed. But running out of space or something correctable is actually something likely to happen. Is this standard? It's quite smart.
Anyway, assuming they get the data back, I think they've done pretty good - 0 data loss and a days downtime isn't bad given this is a true disaster.
It would be nice if they'd let us know how the db got deleted, and what they suggest to mitigate in a blog after.
In my experience it's good to have a few different restore attempts running in parallel, so long as they won't conflict with one another. For example, the restores may be to different hardware, from a different point-in-time, in a different datacenter, or from a unique backup system (image-based, file-based, transaction logs only, etc). One of their "restore" attempts could even be a scan of the original disk for recoverable data, though that seems unlikely.
In a critical outage like the one Gliffy is experiencing, I take the same approach. Outline all your restore options, estimate time for each restore, drawbacks of each approach, etc and take every available angle.
If you want to know how the database was deleted, read through some of the horror stories posted in the comments here and assume it was probably one of those!
The mitigation is relatively simple, don't allow (by policy and by enforcement) anyone to directly use the creds with DDL access in prod. All DDL changes should be checked in, code reviewed, and tested on pre-prod environments before being ran on prod.
I'll join the chorus of people who've done something similar. In my case it was the database of a small e-commerce site, where I'd taken a backup and then formatted the database server to reinstall it.
What I hadn't realised was that the backup script was set to dump to the OS drive, so in the process I'd also just formatted the backup. Thankfully one of our developers had a recent copy of the database locally, but it definitely wasn't my finest hour.
We've all been there. Shit happens. That's what backup is for.
OT: It's probably bad form to publicly blame someone for it, even if it's done by him. It's suffice to say, we screwed up but on our way to recovery. It's better to follow the practice of praising in public and discussing problem in private.
I guess it could be "we accidentally deleted the production database." But at that point they would just be euphemizing - clearly someone pulled the trigger. If they were naming the person, that would be pretty terrible on their part. But they're not. It seems perfectly fine to my eyes.
The person might be under the gun from someone else to fix the prior problem, and made a mistake under pressure. Singling out the admin just absolves management from any blame. And it shows the lack of leadership and the lack of willingness to take on responsibility for the organization.
And the problem is clearly a organizational problem. There's no clear backup and restore procedure. It's probably never tested for restore. There's no failover. There's no disaster recovery. Even if it's there, it has not been fire-drilled periodically. There's no clear access procedure in protecting the production servers. There's no prior spell out of definite steps to address production problems before doing them. There's no rollback procedure. There's no review. There's no approval process.
As I mention above, it's not so much the administrators fault as it is the VP of engineering. There was clearly no disaster recovery plan which is unacceptable.
I worked on a team that had a list of "breakfastable offences" -- violating these rules meant you had to bring in breakfast for the whole team (donuts, bagels, whatever). One of them was "throwing someone under the bus." In conversations with anyone outside the team, you weren't allowed to single out a person as responsible for any particular bug/error/etc.
Granted, this is pretty vague (depending on how many "administrators" the company has), but it's still too specific for me.
I like the idea of "breakfastable offenses." I'm curious if you had remote teammates. If so, was there a workaround?
It reminds me of my SCUBA training. The head instructor had a list of offenses. If you committed an offense (i.e. leaving goggles on forehead after surfacing), he'd say, "6-pack," obligating you to bring a 6-pack of beer. ;-)
We had no remote teammates. Not sure how we would have handled that :-)
Some of the other offenses:
* Breaking the build (unit tests only) and then leaving for the day without fixing it or reverting your change
* Walking away without logging out of your machine (very security-conscious business)
* Not completing an assigned code review within a week of being assigned (within reason -- if the code review was enormous, or you were overly busy with an enormous project of your own, don't worry about it)
That was how we generally accomplished that enforcement -- see an unlocked machine, write an email to the team list saying "I'm bringing breakfast tomorrow!"
At a previous company, we had a culture of sending out prank emails from open machines.
I once sent out an email from a co-founders account which said that he was fed up with the crappy codebase and was hiring a new team to rewrite it from scratch and that the other (non-tech) co-founder was to take over the existing tech team. No one took it seriously (non-tech co-founder helped me draft the email, the other one laughed while reading it after the fact), but there was a board member in the mailing group who thought it was serious and started sending panicky emails to the co-founders.
Sorry that seems cancerous to me. It breeds entire departments who are unaccountable for the work they do because nobody is safe to speak out against it.
If someone fucks up you should be able to point it out - and they should be immediately forgiven. It's only when they show that they repeatedly take no care in their work and cause the same problems over and over that they should be fired - and those people should be fired instead of being the anchors around the necks of everyone else dragging us down to drown at the bottom of oceans of day to day misery.
The team as a whole was still accountable. The tech lead/PM were still accountable. Internally, members were accountable.
Certainly, if there was a specific problem, it could be raised to management. But generally, if we were in a weekly customer-attended meeting, or dealing with a bug discovered after a production release, no individual could be singled out as responsible for a particular blunder.
On a public relations note, though: I think a case could be made that it was important to give some specifics about who is to blame. Consider the alternative:
"We discovered the production database had been deleted but we are now working diligently to restore it"
How are people -- both non-technical and the HN crowd -- not supposed to suspect that this is a result of an external malicious hack?
The organization can take responsibility for the issue. "During a system update, we mistakenly deleted a production database. We are restoring it and shoring up our disaster recovery plan."
That's very different from "During a system update, Dave mistakenly deleted a production database." In an organization with 5 or 10 people, "During a system update, our administrator mistakenly deleted a production database," is still identifying.
Like I said, I'm not sure it's an issue in this particular case. I don't personally know anything about the site in question.
Last week I deleted a large portion of our pre-production ldap. I use jXplorer ldap client and for some reason the control d (delete) confirm dialog defaults to "Ok" instead of cancel. I'm use to hitting control f (search) and then enter to repeat the last search and when I hit d instead of f I deleted a bunch of stuff. The silver lining is I patched the problem in jXplorer and submitted it. It's my first legit contribution to a project.
Destroyed the production payroll database for a customer with a bug in a shell script.
No problem - they had 3 backup tapes.
First tape - read fails.
Second tape - read fails.
Third tape - worked.... (very nervous at this point).
I think most people have an equivalent educational experience at some point in their careers.
Edit: Had a project cancelled for one customer because they lost the database of test results..... 4 months work! Their COO (quite a large company) actually apologised to me in person!
Edit: Also had someone from Oracle break a financial consolidation system for a billion dollar company - his last words were "you need to restore from tape" and then he disappeared. I was not happy as it was his attempts at "improving" things were the cause of the incident! Wouldn't have been angry if he had admitted he had made a mistake and worked with us to fix it - simply saying "restore from tape" and running away was not a good approach.
To put these in perspective, it is not that uncommon for a years worth of work to go to waste, just for different reasons (eg. wrong assumptions, no market for the product, etc). However when you do mistakes, the process of encountering them and fixing them is much more stressful in the moment, and most of us try to avoid them as much as we can. However it might be that trying and making mistakes is more productive in longer run than avoiding risks and taking safe and calculative approach.
"wrong assumptions, no market for the product, etc"
Been there, done that!
However, to stick to the subject of databases - I found later in my career that the really alarming events are more to do with having data you shouldn't have rather than not having data you should.
NB The only one of these that I'm going to admit to involved a demo application being sent out on CDs to hundreds of thousands of end-users by IBM and sample data sourced from a particularly unpleasant alt.* newsgroup - I was CTO and when an engineer approached me and rather sheepishly told me that some of the same data had gone to IBM I had a very bad moment indeed.
However, on investigation it turned out the only thing that did go out on the CD was the single word "sheep".
At a startup earlier in my career, we generated an email address for every customer which they could use to interact with our system. The CEO wrote a script to pick words at random from the Princeton Wordnet database.
One afternoon I thought, wait a minute, what's actually in Wordnet?
Needless to say, many apology emails were sent later that day.
In recent years, I created a homework assignment for a security class where the student's job is to crack a set of password files. First easy, un-salted hashes, then salted hashes, ... etc.
To illustrate the badness of using dictionary words as a password, I randomly generated a unique password file for each user by sampling from /usr/share/dict/words.
> One afternoon I thought, wait a minute, what's actually in Wordnet?
I know exactly how you feel. It took me about 5 minutes to find Shutterstock's list of dirty, naughty, obscene, and otherwise bad words [1], and about 20 more minutes to add a blacklist check to my script.
Fortunately none of the students had actually been given a bad word. Whew!
Reminds me of an incident at a very large UK company where the outsourced operator manged to destroy all of the payroll tapes.
First run fails second run loads backup tape into a malfunctioning tape unit which then trashes the tape - they kept doing this for all of the backups.
This was the second month of the new out sourced payroll system replacing an in house one which had run for decades without incident.
I got fired from a (sysadmin) job because a servo in a tape library failed while i was working 14h days on conference that I was told was "my only priority".
We didn't need the backups at all, prod was fine, and I found out 2 business days after the servo failed when the conference was over. apparently 2 days of not having backups due to a mechanical failure out of my control was beyond unacceptable.
in the early 2000s I was a young guy and board observer on a .com that was growing extremely rapidly and had a 9 day complete outage.
The venture funds, including the one I was part of, came in screaming, "who's getting fired for this", to which the CEO responded: "are you kidding me? We just spent $1.5M educating this team and you want me to hand them over to our competition?"
Every investor and other board member got real quiet, realizing how correct the CEO was. Good CEOS recognize that mistakes happen. The best make sure they retain the people that have learned through those mistakes.
Don't know if that was the same one, but happened to me at Rent.com. The story is that a change in a shell script meant that backups were not actually being sent properly to tape. That was OK, there was another online backup copy. But the restore process deleted that for 1 hour each day before it was recreated.
The production database died during that hour. We had to take the last good backup (several months earlier) and replay WAL logs to bring it up to date.
The sysadmin whose mistake it was offered her resignation, and was turned down by the head of tech because he knew she wouldn't have made it if she had a more reasonable load. The head of tech offered his resignation to the CEO and was turned down because the CEO knew that it was due to incorrect company priorities.
The next tech hire was a DBA whose sole task was make sure that we have multiple levels of verified backups.
In less than a year we were sold to eBay at a nice price. Part of the reason was that they thought that the way that we handled failure said very positive things about the organization.
Now that's terrifying. Glad it worked out for you.
My tifu is when I first started working for an actual client, I thought I was a genius running my own git server on my own computer. Also I had the ssh key for my server on the same computer.
When my hdd failed, I lost everything.
Now the funny thing was, while my server was running I had no way to gain access to the server, the customer had already started using it, and they had inserted around 400-500 records already.
Now there I was, locked out of my own server, no source code I was working on for around 3-4 months.
Lucky for me, someone online had mentioned about a way to gain access to an aws server if you lost the ssh key.
I created a new instance, and mapped the old storage to the new instance. Lucky for me this worked.
Also, 2 weeks(ish) before this blunder I had sent a copy of my code to a friend who was helping me with some issues I had.
So my email saved me.
Now I backup regularly, use github(recent projects gitlab) and I have my ssh keys on 3 separate pen-drives and this http://www.oakalleyit.com/node/4
----------------
> You now have to type your 1,675 character key in by hand! Obviously this is not only tedious, but also very error-prone. And that's where the QR code comes in handy. With a QR code, you would only need to scan the page to bring your digital copy back to life. No errors, no tedium.
The GP is already using AWS, and successfully restored the system through AWS snapshots. In this case, that third-party with administrative control saved the day.
Oracle is extremely good at coming in, completely hosing everything, then disappearing and sending you a $350/hr invoice for the privilege. Their consultants seem to tread the line between totally incompetent and actively malevolent surprisingly well.
Do you have anything to back your opinion or you had just one bad experience with Oracle consultant and now generalize to tens or hundreds of thousands of cases that you have absolutely no knowledge about?
On my part I only have very positive experience with Oracle consultants, but this is only my opinion based on just a few interactions.
I worked for a Fortune 500 healthcare company that used both Oracle and IBM consultants extensively. I had exactly two positive experiences with Oracle consultants where they delivered as promised and maybe four or five where they delivered but took longer/went over budget/etc to the point where I feel we lost value in the long run. And maybe two dozen experiences that were as I described initially.
So in my experience somewhere between 5-8% will be worth it.
At my last job, we used to have an Oracle-acquired storage product in a rather critical part of our stack. It was having some performance problems one night, during a peak traffic period. It may even have been Cyber Monday; I don't remember exactly.
Our then systems team lead called Oracle support, and was advised by the support rep — a third tier rep, mind you, because we had priority support, and had the call escalated when the first guy couldn't do anything — to run an on-board low-level diagnostic. He confirmed with the phone rep three separate times that running such a check would be safe to do with live, mounted filesystems, and would not adversely impact operations. He was answered in the affirmative each time.
He hit enter. The site went down. $.75mm in lost revenue over the next 8-10 hours, unfucking that pretty little mess.
When in doubt, refuse and escalate. It's your systems! Especially with Oracle support, who have a particularly bad reputation.
Honestly, Oracle's reputation is mud. Ever since I saw the Oracle Chief CSO's presentation on security which used biblical parables as principles for securing environments I've been even more wary of accepting any advise on face value from any Oracle employer...
In my first job (at a pretty small company) I needed to debug something. I made a dump of the production database, downloaded it to my dev machine, then deleted the live production database instead of the one on my dev machine by accident (typed the command in the wrong shell).
This happened while the CEO was talking to someone external to the company, right beside my desk. Little adrenalin rush there.
Anyway I had of course just made a backup, and only one order was placed while I was downloading it that I could restore by hand using the email that was also sent for each order, so no damage done. But I'll never make that mistake again :-)
I think the "wrong shell incident" is quite common:
"Just need to load this dump to the testing database and I'm on my way to weekend...ok done...oh wait, if I look closer, this shell isn't the testing system...hell noooo, just nuked 8 hours of work each from 20 product managers."
Literally happend to me when I was a working student at a DAX 30 corporation. Never happend to me since then again.
It's common enough that I made sure our AWS boxes put the name of the current environment both in the bash prompt and rails console prompts (production is in bright red, staging is a nice soothing purple :).
I actually make a terminal-window-size PNG of 'PRODUCTION' and 'TEST' and set it as the background gif at like 10% alpha in the terminal windows, which also have different background colors.
I set the background colour in our team's shared iTerm config. Green for CI, blue for the test server, yellow for staging, and we don't have access to production!
The thing I like about our setup is that it's completely independent of individual developers' setups. Some of us use iterm, some of us are okay with terminal.app (iterm 2 ftw!). All of the backend capable folks (three of us) do have access to production, but we use it very responsibly (helped by the white on a red background production in the console prompt)
Oh boy, is it ever. I once had a Linux gateway without X installed on it, and I ssh'ed into a remote billing server. A friend called to see if I wanted to go out for lunch so I issued the shutdown -h now command.
I was trying to work out why the system wasn't responding right at the moment when I realised I hadn't logged out of the ssh session.
To prevent this I change the PS1 in the bash config. For servers I use a different prompt format.
For my local computer:
hollander@mypc 19:30:45 ~ >
For servers:
[hollander@server01 19:30:45 ~]
This all with coloring for name, servername, time and filepath, different for local computer and server. Root and username have different colors as well.
As always you can tailor them if you prefer different things to be different colors, I'm often lazy and don't change local machines to green but always change staging to orange and production to red.
We were a development team years ago (90s) working late on the production Oracle database writing some SQL (cringe). One junior dev deleted all records from one of the core tables and sat expressionless in total fright for a good 5 minutes. I let her sweat for a bit before I typed 'rollback;' Good thing she didnt commit and there was enough rollback space :)
One of many close calls.
A coworker of mine used to say "It's not the backup, it's the restore." Meaning your backup process isn't meaningful unless you have a tested and effective means for recreating your system from that backup. It has stuck with me.
One of the first things I did at my current place was to add a Nagios check "Is the most recent backup size within x bytes of the previous one". It wasn't comprehensive, but was fast to implement and was later replaced with full restores and checks, but nevertheless a few weeks later it caught a perfectly formed tar.gz of a completely empty database due to a bug that would have continued creating empty archives until it was fixed.
This is a fantastic illustration of testing 101: often it's the dumbest possible checks catch huge errors. Get those in place before overthinking.
E.g. a team adjacent to mine years ago had a dev who made a one-character typo in a commit that went to production. Which caused many $MM to incorrectly flow out the door post-haste. The bad transactions were fortunately reversible with some work, but I was floored that there were no automated tests gating these changes. It wasn't a subtle problem. The most basic, boring integration test of "run a set of X transactions, check the expected sum" would have prevented that failure.
All these threads are complete gold mines of years of hard won experience and system administration tips! Fascinating, especially the war stories I'm reading - both hilarious and horrifying in equal measure.
Resonates. I didn't spend the extra $5 to get a USB 3.0 Flash Drive. Currently the read process for the image backup looks like it will take 4 hours to create write media, 2 to actually restore. Good lesson above about te restore being more inportant than backup.
Had the same training from the head of operations at my first gig. If you don't restore the backup each time it's pulled to verify it's valid as a test then it's a risk.
My first job was doing software engineering consulting. My main client was a defense contractor. We wanted to at least use cvs or svn for source control, but you wouldn't believe the red tape associated with getting approval to use a free source control system... so we used date-named zip files for "source control". The rule was the last person out at the end of the day zipped up the shared drive contents and shut off the lights.
Though, we were required to keep good backups. 3 sets of tapes.. 1 always in the tape drive, 1 always in a fire-proof bomb-resistant bunker, and one sometimes in transit to or from the bunker.
Our manager was paying some obscene sum for this backup service, so I suggested we just hide one of the daily backup zip files and pretend we deleted it. The head of the group humored my request. It turned out that nobody was monitoring the tape or the backup job. The tape had filled up and nobody had swapped tapes and called the bunker courier.
Luckily, they at least used PVCS for configuration management of the releases. No source control for development, but every time we cut a release of the software, the head office sent over no fewer than 3 people to literally watch over our poor release guy's shoulder as he zipped up the source, built the binaries, checked both into PVCS, and burned two CDs of binaries and source.
Defense industry... things you're required to do get done in triplicate. Things you're not required to do, but cost no money and significantly reduce risk, take approvals from 5 different people 9 levels above you. I guess how often the backup tape needed to be checked for available space was insufficiently specified.
On a side note, at that client I also once sat quietly in a meeting for 30 minutes watching two grown men argue over if my use of "will" in a design document needed to be changed to "shall". I didn't care, and right away said I was fine changing the word to "shall", but the second reviewer was adamantly opposed to unnecessary changes.
"Will" to "Shall" -- very important legal distinction. Thank the second reviewer. Your job in "software engineering consulting" was actually managing risk.
Except that, as mentioned, this was a software design document and not a legal document. I described intended behavior of a software component. There's no ambiguity in using "will" or "shall" since software does not (at least did not at that time) have intent and does not make promises.
I tried to avoid argument because it didn't matter, not because I thought my word choice was incorrect. It was an internal document describing intended software behavior, to help the poor soul who had to maintain that code.
"When 'shall' is used to describe a status, to describe future actions, or to seemingly impose an obligation on an inanimate object, it's being used incorrectly."[0]
Formerly critical tool (now, sole repository of necessary historical information) that runs only on an outdated stack and for "copy protection" has critical data in an undocumented, obfuscated DAT file. It also requires a parallel port dongle, which it checks for as part of every read operation. The vendor went bust a decade before I was ever hired.
I've automated a 'backup to zip file' each time the application terminates. It's saved me more than once - it's easy to clobber the data in the thing and the users have a tendency to screw it up when trying to navigate its cryptic keystroke-driven interface.
Trying to export all the legacy data into a new tool met with incredibly frustration the couple of times we tried. It all becomes irrelevant in late 2017 and there's been a new system in place since 2007, so this abomination needs to only live on for a little while longer.
When appropriate, this is why I adore full-disk, bootable system backups. Plug a backup drive in, spot-check the contents (e.g. by data priority), run a filesystem compare tool, boot from it, etc. Backup is easy, as is restore and testing. A booted backup drive can restore to a replaced system drive.
That's obviously not the right solution for many IT-centric backup needs, but when full-disk backup became cheap and easy it set a new standard in how I think about backup and restore process everywhere.
Made that mistake once. I was interning at the IT Helpdesk at a Pharma Startup. Our CEO calls us one day, informing us that he deleted a slide deck, and was wondering if we could do anything about it. We dutifully attempt to restore from tape, to find the tapes blank and that our backup has done literally nothing for the past month. Thankfully, the data loss was nothing more severe, but the lesson stuck.
In my last job I was a member of an IT team of three, one guy was the CIO + he did sysadmin and IT support for an office of about 60. I did sysadmin and support for about half a dozen offices, 200 odd staff, in two countries with a geographical spread of several hundred kilometres. The third guy was support and sysadmin for our US office which had about 12 people (cushy job). I popped out there once for a week or so to help train the guy on our new systems. They had an old backup system that copied diffs to disk which he had to take home every day, but he didn't like doing that so they invested in a very expensive off site system. I was having a dig around when I was over there and it appeared to me that the diffs hadn't been happening. I confronted the guy and he said that he hadn't set it up yet and knew that would bite him in the ass eventually. They hadn't run any backups for six months, but he had been giving positive reports to both his manager and the rest of the IT team about the hard work he was putting in setting it up, and how it had been so reliably backing up, we celebrated when the first full copy of data finally finished, etc. Needless to say he lost his job.
We were using an external vendor for our production database (I've changed this now) and I was poking around their management console one morning when I notice something (I was about two months into this gig). The CTO walks into the office at that point and I ask him: "Hey, I can see that the staging database is being backed up, but the prod database doesn't have any backup files. Where are the backups for prod?". His response: "Press that manual backup button immediately, please." Turns out that production had been running for nearly three years without backup actually running...
This morning I backed up my iPhone with iTunes. Once finished iTunes said backup was successful.
Then formatted my iPhone, and went to restore. It say's the backup was corrupt.
Tried some closed source iTunes Backup fixer, didn't work.
Pretty heated right now, but oh well, what can I do? I guess I'll just have to start over. Thankfully I am a iCloud Photo Library subscriber.
So yes, can confirm, "It's not the backup, it's the restore."
Careful with that. I recently went through something similar, and had a block of 4 years worth of photos deleted. Been working with Apple for over a month now to restore it, and am not very hopeful.
This is the right answer! You want a staging/acceptance/mirror environment that's the same as production, right? So make it with nightly restores of the production backups. You get a crisp, fresh staging environment, and regular validation of your backups too. Just remember to run full production monitoring against your staging environment too.
This is made more serious in practice by the continual recurrence of bugs in Backup Exec over the last several versions, which would often manifest as "backups ran fine, verify ran fine, restores claim to run fine. But all your restored files are 0 bytes long".
I first spelunked through UNIX sometime in 1985/1986; my employer decided to sell Altos UNIX systems along with their CP/M offerings. No documentation, just a running system and a password although one person hinted that man man would help.
We used it for our accounting system along with Peachtree? It turned out we didn't have good backups because the system's disk drive was dirty and misaligned. I fixed it.
The backup scripts couldn't deal with going over one floppy and failed anyway.
I learned a lot of very useful things on that system - like when I created an extra copy of etc with an unprintable character in the name (etc^?) I got to learn all about using stty to set your erase character, ls -lq to find files with unprintable names and inodes along with ls -li and find -inum to fix my errant /etc^?.
Unfortunately for my accountant I didn't learn how to make a backup span multiple volumes until some time after I also learned a very hard lesson about unix device names and wiped the system drive while trying to mkfs on a new volume to expand the system.
He ended up rekeying all our accounting data from printouts and I ended up buying him more than one lunch.
If you think that's bad, ~12 years ago I worked on a project that was an online insurance form. The output from the application form was going to be printed out and then submitted to the existing paper-based workflow for handling applications - which involved scanning the application and then workers typing in the data from the scanned form.
Not more than 2.5 years ago I wrote a script to take the bill of materials from a CAD program and cross-reference it with a flat-file database (read: Excel spreadsheet) that contained information on how 'old' part numbers mapped to 'new' part numbers. Shame the CAD program couldn't just be scripted to do this. Anyway, the final output of this was a bill of materials that said what you'd expect: I need X quantity of Y parts, here are the part numbers for each.
This was emailed to a "warehouse" specialist, who then, I found out, printed out my BOM, then compared it to that day's (printed) inventory of parts, came up with what needed to be issued directly and what needed to be ordered, and then he manually entered that into the actual warehouse logistics system.
And lest you think I was lazy, I asked to be allowed to script all this out, too, only to be told "there is no way for us to submit anything to our logistics system that way, everything has to be entered manually."
This wasn't some no-name company, this was a major aerospace company.
I managed to get employee of the quarter because the company I worked for (a major company that made inkjet printers) had been tasked to find out more about third party ink cartridges from their service system.
This company had a Pick database, and rather than extend it normally someone had the bright idea of storing each ink cartridge record in a .INI file. That's right, each record was a .INI file and the fields were stored as column=value.
To get at this data, they tasked one of their employees to open each file, copy the first field to an Excel spreadsheet, then copy the second field to the Excel spreadsheet, etc. There were something like 10,000 ini files, and it took about 6 weeks for the guy to input the data.
I was rather young and bored on the call centre helpdesk, and I had been fooling about with Linux and Perl. When I heard what he was doing, I rather naively said that this was what Perl was designed for. The guy deliberately said he didn't believe me, and I took that as a challenge so that night I went home and whipped up a Perl program that processed the ini files into a CSV file. I then installed ActiveState's Windows port of Perl and ran it over a copy of the ini files. About 5 seconds later it produced the csv file.
I told the guy to not tell anyone as I wasn't really meant to install ActiveState's program on any corporate computers. He ignored this and the next thing I know I was made employee of the quarter.
I think the highlight of this story is that the company had the culture to reward you instead of burying your achievement under policy-violation blanket.
I'm not a programmer by trade. I just seem to be one of the few engineers in my tiny little specialized domain that doesn't mind writing a little bit of code now and then to solve a problem.
You can imagine how it felt at the time - 18 years old and I had just destroyed the accounting system for a sales-driven organization. Was convinced that was my last night at the company and had to stew in my own juices from that evening until telling my boss about it the following morning.
Yep. I interned at my high school over the summer back in the late 90's and (aside from ghosting pcs or helping with Windows login stuff) also managed the tapes for our main server. The underling in the it shop, one afternoon, was showing me something and to prove a point about RAID, pulled one of the drives from the servers raid array, then plugged it in again. It was magic!
Fast forward a month. I do the same thing when talking to someone. The next day, the sever is offline. I either pulled the wrong drive, the array was degraded, or some actual failure occurred overnight. I'll never know.
I did, however, learn that day about how veritas backups work, and to always know wtf I'm doing before showing off. :-)
At my first professional full-time gig as a software engineer I had to find a way of dumping data from barcode scanners, after some software written in-house was supposed to download all data, flush it to disk, then empty the scanners for re-use. Problem was that the flushing to disk part didn't work (but said it did) so all data was lost. This was for scanning attendees at conferences, so all data on attendees were seemingly lost.
It wasn't me losing the data, and I don't blame the engineer that did, but management sure didn't mind doing that. I took the heat for it by standing up to them and saying "there's a bad news/good news situation here" and refocusing their attention. Bad news was obviously the data looked lost, the good news was it's probably not overwritten so I suggested we write a very small piece of firmware that just does a memory dump – the scanners didn't have separate slots for storage, firmware etc, it was all in one place – and hope the memory is ordered such that the firmware comes first, followed by all the attendee data. It worked – the firmware was indeed written first and dumping it all revealed all attendee data in raw form. Thankfully it was more or less all ascii so it wasn't difficult to process the dump after it'd been downloaded. We had to do this with 20+ scanners since the mistake wasn't discovered right away.
I believe management later tried to resell this as a "data recovery solution" to other firms using these types of scanners – don't think they had much success though.
The engineer who lost the data at first mostly got off the hook with some blame game and a warning. It wasn't a very good working environment, and prior to losing the data the engineer had been pushing serious overtime hours without so much as a thank you because management had promised delivery without involving engineering in the discussions. A few months later that engineer and several others, including myself, quit for other jobs.
My team got hired to document this fairly large database (800+ tables, 2000+ SPs). We decided to use a product that actually writes the documentation to the DB as extended properties. The client specifically did not want us to use dev for this, so they put us in test. Immediately this raised some warning bells in my mind so I let them know that risks regarding what could happen if they move from dev to test without notifying me (if I'm notified, I can script out the extended properties and reapply them to the test after the migration is complete).
We're about 2 months in and 90% complete. One morning, ops decides to migrate to test and doesn't let me know. One of my team members logs into the system and tells me that everything has disappeared. At this point, I'm sweating bullets and I'm pretty sure that I'm going to be in some tough conference calls the next couple of days. It's a fixed fee project with a client that we have a really good relationship with and I don't want to be the one that destroys it. After about 15 minutes of pondering my demise and hoping that they back-up the test environment, I get notified from the ops team that they create back ups weekly and test monthly for all environments. Luckily for me, this was a Monday morning so we lost maybe a few hours of work.
That being said, that was a very stressful 15 minutes and taught me how important it is to:
1) Back-up every environment - storage is cheap and labor often isn't. Having to recreate work would have been way more expensive than having the systems in place to save the potential lost work. Additionally, you never know what data might be lost during testing or work done in test that you really wish you could retrieve.
2) Test your DR processes - their recovery was smooth and well executed and we had reasonable assurance it would work.
Seen this from a different angle: watched a rookie admin delete the only copy of the soon-to-be-production database, which was the result of a year of data-mangling/massaging before it could be put online for production .. saw him do the delete, nothing I could about it in time.
Spent a week recovering the data, a block at a time .. learned enough about the database filesystem to write an 'un-delete' tool, sold it after using it to successfully recover the original project, and have never looked back ..
Yes I think everyone has had one of those "Wanna get away?" moments. Hopefully they're very early in your career and only ever happen once. Mine was small and I was very Jr so nothing more than my pride got hurt.
The worst is when someone else notices it before you. We came in to work on Monday and some reported that they couldn't log into the system. We checked logs and noticed there were timeout errors on some SQL queries. Huh, that's odd, why is there a query "update Users set deletedat=now()" with no "where" clause running in production??? Turns out someone was in a hurry and tried to make a test pass without thinking to much about what the code was doing. Then - push right to prod on a Friday evening!
You do not have backups until you have tested the restore. Typically, I restore the production backups to the test environment. This way test gets up to date data and the backup restores are tested.
I have so many stories of data loss from many years working, that I do nothing without backups. From fat finger deletes to raid card batteries failing to multiple disks in the raid array failing at the same time. Data will be lost at some time with the only recovery method being a backup.
> Also had someone from Oracle break a financial consolidation system for a billion dollar company - his last words were "you need to restore from tape" and then he disappeared.
Tech: "Err, boss, I just totally broke their entire system"
Manager: "That's no problem, you got them to sign that contract, right? We always pad those with more services than they have employees or customers to use, so we can use that to cover any problems."
Tech: "..."
Manager: "Johnson, I'm not hearing the confirmation I expected."
Tech: "Sorry boss, there was an issue they were keen to have me look at and... well..."
Manager: "Johnson, you get your ass back here now. Do not pass go. Do not collect $200. The phone will not accurately convey the ass-chewing you are about to receive, and you don't deserve the paltry protection it would allow." <click>
Same deal here, we'd been backing up the servers to tape and dutifully boxing and taking one set offsite. Finally someone deleted something by accident and that's when I discovered you should verify that you can read the tape once in a while. Fortunately, not an important document, but that was just luck.
You'd like the system administrator at a medical research facility where I once worked. Every Monday around noon, he deleted a file, a randomly file from a randomly chosen production system, then restored that file from backup.
I've always admired that, but never emulated it...
On one contract about a decade ago, the manager used to proactively screw around with server hardware (eg pull a hdd out of a raid set) when bored, to ensure our recovery processes were always battle tested.
Didn't generally cause too many issues... until the one time he pulled a working hdd out of a raid5 set that already had a (real) failed drive, which we were already attempting to recover. It was a production database, but thankfully not a mission critical one :>.
From memory, he stopped testing things like that for a while afterwards. ;)
In my opinion it is way too easy in Unix to accidentally delete stuff (even for experienced users). Having a filesystem with good (per-user) rollback support is, imho, more than just a luxury.
One time the DBA and I were looking at our production database, and one by one the tables started disappearing. Turned out one of the devs had tried out a Microsoft sample script illustrating how to iterate through all the tables in the database, without realizing that the script was written to delete each table.
Small company, dev had access to test and prod databases. Was using SQL Developer or something similar, and had gotten into the habit of using auto-commit when on the test database. Must have switched over to prod without realizing it, and did a `DELETE FROM USERS` to truncate some production table.
These days it should be possible to roll back a few steps (à 15 min / 1 hour) with a copy on write filesystem like zfs? Full scale restore from backup should only be necessary if the storage hardware fails (IMHO).
You still need to apologize for a some data loss, though. So make sure that everything you do has one or two safety nets before it hits the customer.
Last year I was on a flight en-route to an Ed-Tech convention in Philly. There was on board wifi and my phone had the wifi turned on. I go and check my emails before departure and get a debug page from tomcat. My initial reaction was panic, but then I remembered that the wifi runs separately from avionics. After all the mental pictures of the plane going down in flames due to someone leaving a server in DEBUG mode disappeared I simply closed the tab, turned the airplance mode on and went to sleep. Knowing in full that I had put my life in the hands of people who are pressured into writing code that works under impossible deadlines.
That's why I think that just like the romans poisoned themselves over time with lead in their pipes we will kill ourselves with buggy code from shitty projects.
I had a client call me in panic after he'd run a unit test script that started by wiping and recreating the database from scratch, and he'd run it against the wrong server.
Thankfully he'd just run it against a dev environment where the loss wasn't particularly severe (the prod environment is firewalled off, so he couldn't have done the same thing against that), but from the panicked tone of his messages before it was clear what had happened, I'm sure he's come to be extra careful about database credentials going forward....
I would really love to get more detail about how they structured the full backups and transaction log backups for the database. Are the backups dumped to disk before being picked up on tape? Or are the backups streamed directly to the backup system?
I'd also would love to know how large is the database that was deleted. Doing a point in time restore of database that's a couple of hundred gigs should be relatively fast (depending on what hardware you are running on).
Once I asked server support guy to move database from production to dev. He did - without any question of doubt - exactly that: copied database to dev environment and deleted it from the production.
(note: in my language word "move" is less unambiguous than in English - it may mean, depending on the context "move" or "copy").
Your server support person did wrong and language is no excuse for them. If there's any ambiguity, if data may be lost, if something may cause an outage - then they have a duty to tell you and then ask if you're sure.
I do it all the time. "Give this access to this user to this db." 'Okay, but do you know they'll be able to drop your db?' "Oh shit okay wait a sec..."
My mentor told me: "get everything wrong, but get the backups right", as he was busy debugging the backup solution he had in place at my college (ZFS + NetApp + rsync + sh + perl + tape).
On my own, I'd put CoW wherever possible. It's so easy to delete something on UNIX that it should also be easy to restore and CoW is without a doubt a no-brainer for this.
I've done this - ran out of space on /home for the mSQL database (~1996 era), I moved it to /tmp which had plenty free. I suspect most people can now guess which OS this was on and what happened when the machine rebooted some weeks later...
That didn't stop a tester on a team I managed. Server was getting low on space, I found a bunch of crap in a sub-directory named tmp. Deleted said crap. Tester complains shortly after, I explain what happened and why one shouldn't put stuff in directories named "tmp". He retorted that those were production tests. Okay, maybe one can't be expected to remember 30 or more years of Unix naming conventions, but I should challenge you to battle simply for using crappy, non-descriptive directory names. I'm supposed to find production tests in a directory named "tmp"? Clean out your desk.
Of course there was no backup, because the sysadmin doesn't back up things in tmp directories.
338 comments
[ 3.2 ms ] story [ 403 ms ] threadThere's always room for computer error, and more like, human error.
Imagine if something like this happened to Dropbox? Ooooft.
Complex restoring never works well when the first implementation is under the pressure of the real event. Other SaaS providers will be cursing such a big name tool making such a public mess.
DR is not a luxury. Systems that don't properly do DR aren't unoptimized or something, they're badly engineered.
Those all look like more trouble than they're worth before you start, but they aren't that hard. And you'll recover your whole investment the first time you need to restore something and it's just a simple tweak to your standard test practice.
If they had done this exercise even once a year, they would have known better what to expect, or how much it would take.
It's more than a cliché that without restore tests, you don't really have a backup-- if the customer won't commit to testing the DR, we won't provide the service anymore. Anyone who pretends anything else is acceptable is kidding themselves.
I think he was able to recover most or all of his data (I forget if it was photos or what) but it was interesting to realize that even with Dropbox's stellar record and pedigree, errors like that can still happen.
It makes you realize data is just so fragile. Our bits are unlikely to last centuries, which is kind of unfortunate. I've wanted to take on this problem in a systematic way somehow. It seems like bittorrent offers a way to make a digital time capsule -- theoretically, if you have seeders, your data will persist forever. So you could imagine setting up a "time capsule" of sorts. Example: $2,000 on Digital Ocean will get you 33 years of server time. So that's one seeder, "guaranteed" to last 33 years. I wonder how far the costs could be reduced? Could you reach the 1 century mark for less than $3k? Costs will continue to go down, so it should be possible to have 10 seeders for your time capsule.
And then of course you have to think of what to put into your time capsule. There's not any guarantee the computing architecture will be the same a century from now. But somehow I suspect Python 2.7 might still run. :) You could write some kind of rudimentary AI program that responds to basic questions, maybe using the data embedded in the time capsule...
The only part I can't figure out is the most obvious one: Digital Ocean probably won't be around 20 years from now, let alone a century from now. So even if you get a bunch of money together, how do you reliably transform that money into running servers over the course of a century?
Better news would be if every user had local copies of their work too. both in local storage, and on a cloud storage provider of their choice. Preferably in a non proprietary format.
This isn't just about getting me to trust your site if you crash or have a tragic mistake. This is also about getting me to trust your site if you go out of business (as too many startups unfortunately do).
I was once on-call working for one the leading organizations. I got a call in the middle of the night that some critical job had failed and due to the significant data load, it was imperative to restart the processing.
I login to the system with a privileged account. Restart the job with new parameters and since I wanted not to see the ugly logs, I wanted to redirect the output to /dev/null.
I run the following command ./jobname 1>./db-file-name
and there is -THE DISASTER-
For some reason this kept popping in my head - "Bad things happen to Good people"
We recovered the data but there was some data loss still as the mirror backup had not run.
Of course, we have come long way since then. Now, there are constant sync between Prod/DR and multitude of offline backups and recovery is possible for last 7 days, the month or any month during the year and the year before.
I was able to determine that the corrupt data was repairable if we had a copy of the old db, and since it was a tiny machine system I asked "Would you mind restoring the backup side by side with production and I can do what I need?"
"Sure thing!"
I wait for a minute, and then my connection to the production database dies.
I refresh the client, and now the one database available is restoring from a backup...
I called him and asked if he meant to overwrite his production copy with his backup instead of do it side by side, and he says petulantly, "I didnt do that!"
I ask him to check again, and he responds with "I will call you right back!"
Five minutes later I get the call, "How do I roll back my restore partially through the restore process?"
Oops.
A few hours later we had got a few calls from angry customers who couldn't log in. I had effectively forgotten the WHERE clause so all users had the same password: mine.
Extra points for not having read the "xxx rows updated" line that the mysql console outputs after each query...
abort;
begin;
Just to be doubly sure. Then notice the number of deleted rows, and select from the table afterwards.
Transactions are a sane safeguard if you absolutely must run SQL on your production database.
The less manual steps the less mistakes will happen.
My command in the buffer might go through these steps:
1. "Delete from" 2. "-- delete from" 3. "-- delete from table where condition limit n;" 4. (Generally either ask a co-worker or make a Jira with the exact command I have at this point, so there's a sanity check and/or permanent record, but for very low risk/especially mundane/especially time critical updates, do it) 5. Delete the "--" and run it. 6. Think hard about adding some functionality in the app for doing it in app code unsteady of in database code.
Generally do the same for "UPDATE".
Another trick it to start all "DELETE" commands as a "SELECT" to ensure you get the "WHERE" part correct, and then swap out the "SELECT *" with a "DELETE".
This is an alternate form of the flag --safe-updates, this option prevents MySQL from performing update operations unless a key constraint in the WHERE clause and / or a LIMIT clause are provided.
I've lost a lot of data to "oh lets find that line in mysql history... up, up, enter, FUCK"
When this isn't possible, I do a select for the PK and delete based upon only the PK. This way I can review the rows to be deleted and back them up manually if needed.
All of this is way overkill considering the backups available these days.
Although, these days, I generally avoid mucking around with production DBs directly. It's all manually scripted migrations, and testing said scripts on a backup or unused slave, which is by far the safest, and also helps to avoid running queries that might adversely affect production's performance.
1: http://dev.mysql.com/doc/refman/5.7/en/mysql-command-options...
Start your .sql file with "use xxxxx" (non-existent database name; will prevent execution if you fat-finger F5.) Always write your deletes as a SELECT first:
SELECT *
-- delete
FROM blah
WHERE blah = 1
Never uncomment the "delete" line; just select the portion of the query after the comment with your mouse and F5 it.
And of course: always design your database with support for soft-deletes, because sooner or later you'll need to add them in.
I initially thought it was weird they had to run several "processes" in case 1 failed. But running out of space or something correctable is actually something likely to happen. Is this standard? It's quite smart.
Anyway, assuming they get the data back, I think they've done pretty good - 0 data loss and a days downtime isn't bad given this is a true disaster.
It would be nice if they'd let us know how the db got deleted, and what they suggest to mitigate in a blog after.
In a critical outage like the one Gliffy is experiencing, I take the same approach. Outline all your restore options, estimate time for each restore, drawbacks of each approach, etc and take every available angle.
If you want to know how the database was deleted, read through some of the horror stories posted in the comments here and assume it was probably one of those!
Thank god for backups and translog replays.
Dropping an entire database brings problems, having duplicate content and deleted content coming back bring a whole new realm of others good times.
What I hadn't realised was that the backup script was set to dump to the OS drive, so in the process I'd also just formatted the backup. Thankfully one of our developers had a recent copy of the database locally, but it definitely wasn't my finest hour.
OT: It's probably bad form to publicly blame someone for it, even if it's done by him. It's suffice to say, we screwed up but on our way to recovery. It's better to follow the practice of praising in public and discussing problem in private.
I guess it could be "we accidentally deleted the production database." But at that point they would just be euphemizing - clearly someone pulled the trigger. If they were naming the person, that would be pretty terrible on their part. But they're not. It seems perfectly fine to my eyes.
Once the system has been fully restored they can provide more details as to the "database issue"
And the problem is clearly a organizational problem. There's no clear backup and restore procedure. It's probably never tested for restore. There's no failover. There's no disaster recovery. Even if it's there, it has not been fire-drilled periodically. There's no clear access procedure in protecting the production servers. There's no prior spell out of definite steps to address production problems before doing them. There's no rollback procedure. There's no review. There's no approval process.
Granted, this is pretty vague (depending on how many "administrators" the company has), but it's still too specific for me.
It reminds me of my SCUBA training. The head instructor had a list of offenses. If you committed an offense (i.e. leaving goggles on forehead after surfacing), he'd say, "6-pack," obligating you to bring a 6-pack of beer. ;-)
Some of the other offenses:
* Breaking the build (unit tests only) and then leaving for the day without fixing it or reverting your change
* Walking away without logging out of your machine (very security-conscious business)
* Not completing an assigned code review within a week of being assigned (within reason -- if the code review was enormous, or you were overly busy with an enormous project of your own, don't worry about it)
I once sent out an email from a co-founders account which said that he was fed up with the crappy codebase and was hiring a new team to rewrite it from scratch and that the other (non-tech) co-founder was to take over the existing tech team. No one took it seriously (non-tech co-founder helped me draft the email, the other one laughed while reading it after the fact), but there was a board member in the mailing group who thought it was serious and started sending panicky emails to the co-founders.
If someone fucks up you should be able to point it out - and they should be immediately forgiven. It's only when they show that they repeatedly take no care in their work and cause the same problems over and over that they should be fired - and those people should be fired instead of being the anchors around the necks of everyone else dragging us down to drown at the bottom of oceans of day to day misery.
Certainly, if there was a specific problem, it could be raised to management. But generally, if we were in a weekly customer-attended meeting, or dealing with a bug discovered after a production release, no individual could be singled out as responsible for a particular blunder.
"We discovered the production database had been deleted but we are now working diligently to restore it"
How are people -- both non-technical and the HN crowd -- not supposed to suspect that this is a result of an external malicious hack?
That's very different from "During a system update, Dave mistakenly deleted a production database." In an organization with 5 or 10 people, "During a system update, our administrator mistakenly deleted a production database," is still identifying.
Like I said, I'm not sure it's an issue in this particular case. I don't personally know anything about the site in question.
Destroyed the production payroll database for a customer with a bug in a shell script.
No problem - they had 3 backup tapes.
First tape - read fails.
Second tape - read fails.
Third tape - worked.... (very nervous at this point).
I think most people have an equivalent educational experience at some point in their careers.
Edit: Had a project cancelled for one customer because they lost the database of test results..... 4 months work! Their COO (quite a large company) actually apologised to me in person!
Edit: Also had someone from Oracle break a financial consolidation system for a billion dollar company - his last words were "you need to restore from tape" and then he disappeared. I was not happy as it was his attempts at "improving" things were the cause of the incident! Wouldn't have been angry if he had admitted he had made a mistake and worked with us to fix it - simply saying "restore from tape" and running away was not a good approach.
Been there, done that!
However, to stick to the subject of databases - I found later in my career that the really alarming events are more to do with having data you shouldn't have rather than not having data you should.
NB The only one of these that I'm going to admit to involved a demo application being sent out on CDs to hundreds of thousands of end-users by IBM and sample data sourced from a particularly unpleasant alt.* newsgroup - I was CTO and when an engineer approached me and rather sheepishly told me that some of the same data had gone to IBM I had a very bad moment indeed.
However, on investigation it turned out the only thing that did go out on the CD was the single word "sheep".
One afternoon I thought, wait a minute, what's actually in Wordnet?
Needless to say, many apology emails were sent later that day.
To illustrate the badness of using dictionary words as a password, I randomly generated a unique password file for each user by sampling from /usr/share/dict/words.
> One afternoon I thought, wait a minute, what's actually in Wordnet?
I know exactly how you feel. It took me about 5 minutes to find Shutterstock's list of dirty, naughty, obscene, and otherwise bad words [1], and about 20 more minutes to add a blacklist check to my script.
Fortunately none of the students had actually been given a bad word. Whew!
[1] https://github.com/shutterstock/List-of-Dirty-Naughty-Obscen...
the really really alarming events are more to do with your users having data they shouldn't!
First run fails second run loads backup tape into a malfunctioning tape unit which then trashes the tape - they kept doing this for all of the backups.
This was the second month of the new out sourced payroll system replacing an in house one which had run for decades without incident.
We didn't need the backups at all, prod was fine, and I found out 2 business days after the servo failed when the conference was over. apparently 2 days of not having backups due to a mechanical failure out of my control was beyond unacceptable.
The venture funds, including the one I was part of, came in screaming, "who's getting fired for this", to which the CEO responded: "are you kidding me? We just spent $1.5M educating this team and you want me to hand them over to our competition?"
Every investor and other board member got real quiet, realizing how correct the CEO was. Good CEOS recognize that mistakes happen. The best make sure they retain the people that have learned through those mistakes.
Don't know if that was the same one, but happened to me at Rent.com. The story is that a change in a shell script meant that backups were not actually being sent properly to tape. That was OK, there was another online backup copy. But the restore process deleted that for 1 hour each day before it was recreated.
The production database died during that hour. We had to take the last good backup (several months earlier) and replay WAL logs to bring it up to date.
The sysadmin whose mistake it was offered her resignation, and was turned down by the head of tech because he knew she wouldn't have made it if she had a more reasonable load. The head of tech offered his resignation to the CEO and was turned down because the CEO knew that it was due to incorrect company priorities.
The next tech hire was a DBA whose sole task was make sure that we have multiple levels of verified backups.
In less than a year we were sold to eBay at a nice price. Part of the reason was that they thought that the way that we handled failure said very positive things about the organization.
That's rare. Too rare!
The database went down DURING the CEO's 50th birthday party!
My tifu is when I first started working for an actual client, I thought I was a genius running my own git server on my own computer. Also I had the ssh key for my server on the same computer.
When my hdd failed, I lost everything.
Now the funny thing was, while my server was running I had no way to gain access to the server, the customer had already started using it, and they had inserted around 400-500 records already.
Now there I was, locked out of my own server, no source code I was working on for around 3-4 months.
Lucky for me, someone online had mentioned about a way to gain access to an aws server if you lost the ssh key.
I created a new instance, and mapped the old storage to the new instance. Lucky for me this worked.
Also, 2 weeks(ish) before this blunder I had sent a copy of my code to a friend who was helping me with some issues I had.
So my email saved me.
Now I backup regularly, use github(recent projects gitlab) and I have my ssh keys on 3 separate pen-drives and this http://www.oakalleyit.com/node/4 ----------------
https://upload.wikimedia.org/wikipedia/commons/thumb/3/32/Ja...
Failing that, just use an online reader and upload the PNG image of the QR code. Your pass rate should be 100%.
> You now have to type your 1,675 character key in by hand! Obviously this is not only tedious, but also very error-prone. And that's where the QR code comes in handy. With a QR code, you would only need to scan the page to bring your digital copy back to life. No errors, no tedium.
Be sure to use MFA, of course ;)
On my part I only have very positive experience with Oracle consultants, but this is only my opinion based on just a few interactions.
So in my experience somewhere between 5-8% will be worth it.
Our then systems team lead called Oracle support, and was advised by the support rep — a third tier rep, mind you, because we had priority support, and had the call escalated when the first guy couldn't do anything — to run an on-board low-level diagnostic. He confirmed with the phone rep three separate times that running such a check would be safe to do with live, mounted filesystems, and would not adversely impact operations. He was answered in the affirmative each time.
He hit enter. The site went down. $.75mm in lost revenue over the next 8-10 hours, unfucking that pretty little mess.
Honestly, Oracle's reputation is mud. Ever since I saw the Oracle Chief CSO's presentation on security which used biblical parables as principles for securing environments I've been even more wary of accepting any advise on face value from any Oracle employer...
This happened while the CEO was talking to someone external to the company, right beside my desk. Little adrenalin rush there.
Anyway I had of course just made a backup, and only one order was placed while I was downloading it that I could restore by hand using the email that was also sent for each order, so no damage done. But I'll never make that mistake again :-)
Fortunately we also had the DBAs in the same room - so HR database restored without anyone noticing. Poor chap - I think he got quite a scare.
Fun and games... :-)
"Just need to load this dump to the testing database and I'm on my way to weekend...ok done...oh wait, if I look closer, this shell isn't the testing system...hell noooo, just nuked 8 hours of work each from 20 product managers."
Literally happend to me when I was a working student at a DAX 30 corporation. Never happend to me since then again.
I was trying to work out why the system wasn't responding right at the moment when I realised I hadn't logged out of the ssh session.
For my local computer:
hollander@mypc 19:30:45 ~ >
For servers:
[hollander@server01 19:30:45 ~]
This all with coloring for name, servername, time and filepath, different for local computer and server. Root and username have different colors as well.
Saved me a whole bunch of times when tired.
export PS1="\[\e[31m\]\u\[\e[m\]\[\e[31m\]@\[\e[m\]\[\e[31m\]\h\[\e[m\]\[\e[31m\]:\[\e[m\]\[\e[31m\]\W\[\e[m\] "
Orange
export PS1="\[\e[33m\]\u\[\e[m\]\[\e[33m\]@\[\e[m\]\[\e[33m\]\h\[\e[m\]\[\e[33m\]:\[\e[m\]\[\e[33m\]\W\[\e[m\] "
Green
export PS1="\[\e[32m\]\u\[\e[m\]\[\e[32m\]@\[\e[m\]\[\e[32m\]\h\[\e[m\]\[\e[32m\]:\[\e[m\]\[\e[32m\]\W\[\e[m\] "
As always you can tailor them if you prefer different things to be different colors, I'm often lazy and don't change local machines to green but always change staging to orange and production to red.
Finding out that your backup consists of a perfectly backed up empty database isn't much fun!
E.g. a team adjacent to mine years ago had a dev who made a one-character typo in a commit that went to production. Which caused many $MM to incorrectly flow out the door post-haste. The bad transactions were fortunately reversible with some work, but I was floored that there were no automated tests gating these changes. It wasn't a subtle problem. The most basic, boring integration test of "run a set of X transactions, check the expected sum" would have prevented that failure.
Wish I learned that.
Though, we were required to keep good backups. 3 sets of tapes.. 1 always in the tape drive, 1 always in a fire-proof bomb-resistant bunker, and one sometimes in transit to or from the bunker.
Our manager was paying some obscene sum for this backup service, so I suggested we just hide one of the daily backup zip files and pretend we deleted it. The head of the group humored my request. It turned out that nobody was monitoring the tape or the backup job. The tape had filled up and nobody had swapped tapes and called the bunker courier.
Luckily, they at least used PVCS for configuration management of the releases. No source control for development, but every time we cut a release of the software, the head office sent over no fewer than 3 people to literally watch over our poor release guy's shoulder as he zipped up the source, built the binaries, checked both into PVCS, and burned two CDs of binaries and source.
Defense industry... things you're required to do get done in triplicate. Things you're not required to do, but cost no money and significantly reduce risk, take approvals from 5 different people 9 levels above you. I guess how often the backup tape needed to be checked for available space was insufficiently specified.
On a side note, at that client I also once sat quietly in a meeting for 30 minutes watching two grown men argue over if my use of "will" in a design document needed to be changed to "shall". I didn't care, and right away said I was fine changing the word to "shall", but the second reviewer was adamantly opposed to unnecessary changes.
I tried to avoid argument because it didn't matter, not because I thought my word choice was incorrect. It was an internal document describing intended software behavior, to help the poor soul who had to maintain that code.
"When 'shall' is used to describe a status, to describe future actions, or to seemingly impose an obligation on an inanimate object, it's being used incorrectly."[0]
[0]https://law.utexas.edu/faculty/wschiess/legalwriting/2005/05...
Formerly critical tool (now, sole repository of necessary historical information) that runs only on an outdated stack and for "copy protection" has critical data in an undocumented, obfuscated DAT file. It also requires a parallel port dongle, which it checks for as part of every read operation. The vendor went bust a decade before I was ever hired.
I've automated a 'backup to zip file' each time the application terminates. It's saved me more than once - it's easy to clobber the data in the thing and the users have a tendency to screw it up when trying to navigate its cryptic keystroke-driven interface.
Trying to export all the legacy data into a new tool met with incredibly frustration the couple of times we tried. It all becomes irrelevant in late 2017 and there's been a new system in place since 2007, so this abomination needs to only live on for a little while longer.
That's obviously not the right solution for many IT-centric backup needs, but when full-disk backup became cheap and easy it set a new standard in how I think about backup and restore process everywhere.
We were very lucky that I caught it.
Tried some closed source iTunes Backup fixer, didn't work.
Pretty heated right now, but oh well, what can I do? I guess I'll just have to start over. Thankfully I am a iCloud Photo Library subscriber.
So yes, can confirm, "It's not the backup, it's the restore."
We used it for our accounting system along with Peachtree? It turned out we didn't have good backups because the system's disk drive was dirty and misaligned. I fixed it.
The backup scripts couldn't deal with going over one floppy and failed anyway.
I learned a lot of very useful things on that system - like when I created an extra copy of etc with an unprintable character in the name (etc^?) I got to learn all about using stty to set your erase character, ls -lq to find files with unprintable names and inodes along with ls -li and find -inum to fix my errant /etc^?.
Unfortunately for my accountant I didn't learn how to make a backup span multiple volumes until some time after I also learned a very hard lesson about unix device names and wiped the system drive while trying to mkfs on a new volume to expand the system.
He ended up rekeying all our accounting data from printouts and I ended up buying him more than one lunch.
I am cringing.
This was emailed to a "warehouse" specialist, who then, I found out, printed out my BOM, then compared it to that day's (printed) inventory of parts, came up with what needed to be issued directly and what needed to be ordered, and then he manually entered that into the actual warehouse logistics system.
And lest you think I was lazy, I asked to be allowed to script all this out, too, only to be told "there is no way for us to submit anything to our logistics system that way, everything has to be entered manually."
This wasn't some no-name company, this was a major aerospace company.
This company had a Pick database, and rather than extend it normally someone had the bright idea of storing each ink cartridge record in a .INI file. That's right, each record was a .INI file and the fields were stored as column=value.
To get at this data, they tasked one of their employees to open each file, copy the first field to an Excel spreadsheet, then copy the second field to the Excel spreadsheet, etc. There were something like 10,000 ini files, and it took about 6 weeks for the guy to input the data.
I was rather young and bored on the call centre helpdesk, and I had been fooling about with Linux and Perl. When I heard what he was doing, I rather naively said that this was what Perl was designed for. The guy deliberately said he didn't believe me, and I took that as a challenge so that night I went home and whipped up a Perl program that processed the ini files into a CSV file. I then installed ActiveState's Windows port of Perl and ran it over a copy of the ini files. About 5 seconds later it produced the csv file.
I told the guy to not tell anyone as I wasn't really meant to install ActiveState's program on any corporate computers. He ignored this and the next thing I know I was made employee of the quarter.
I'm not a programmer by trade. I just seem to be one of the few engineers in my tiny little specialized domain that doesn't mind writing a little bit of code now and then to solve a problem.
Fast forward a month. I do the same thing when talking to someone. The next day, the sever is offline. I either pulled the wrong drive, the array was degraded, or some actual failure occurred overnight. I'll never know.
I did, however, learn that day about how veritas backups work, and to always know wtf I'm doing before showing off. :-)
It wasn't me losing the data, and I don't blame the engineer that did, but management sure didn't mind doing that. I took the heat for it by standing up to them and saying "there's a bad news/good news situation here" and refocusing their attention. Bad news was obviously the data looked lost, the good news was it's probably not overwritten so I suggested we write a very small piece of firmware that just does a memory dump – the scanners didn't have separate slots for storage, firmware etc, it was all in one place – and hope the memory is ordered such that the firmware comes first, followed by all the attendee data. It worked – the firmware was indeed written first and dumping it all revealed all attendee data in raw form. Thankfully it was more or less all ascii so it wasn't difficult to process the dump after it'd been downloaded. We had to do this with 20+ scanners since the mistake wasn't discovered right away.
I believe management later tried to resell this as a "data recovery solution" to other firms using these types of scanners – don't think they had much success though.
The engineer who lost the data at first mostly got off the hook with some blame game and a warning. It wasn't a very good working environment, and prior to losing the data the engineer had been pushing serious overtime hours without so much as a thank you because management had promised delivery without involving engineering in the discussions. A few months later that engineer and several others, including myself, quit for other jobs.
My team got hired to document this fairly large database (800+ tables, 2000+ SPs). We decided to use a product that actually writes the documentation to the DB as extended properties. The client specifically did not want us to use dev for this, so they put us in test. Immediately this raised some warning bells in my mind so I let them know that risks regarding what could happen if they move from dev to test without notifying me (if I'm notified, I can script out the extended properties and reapply them to the test after the migration is complete).
We're about 2 months in and 90% complete. One morning, ops decides to migrate to test and doesn't let me know. One of my team members logs into the system and tells me that everything has disappeared. At this point, I'm sweating bullets and I'm pretty sure that I'm going to be in some tough conference calls the next couple of days. It's a fixed fee project with a client that we have a really good relationship with and I don't want to be the one that destroys it. After about 15 minutes of pondering my demise and hoping that they back-up the test environment, I get notified from the ops team that they create back ups weekly and test monthly for all environments. Luckily for me, this was a Monday morning so we lost maybe a few hours of work.
That being said, that was a very stressful 15 minutes and taught me how important it is to:
1) Back-up every environment - storage is cheap and labor often isn't. Having to recreate work would have been way more expensive than having the systems in place to save the potential lost work. Additionally, you never know what data might be lost during testing or work done in test that you really wish you could retrieve.
2) Test your DR processes - their recovery was smooth and well executed and we had reasonable assurance it would work.
Spent a week recovering the data, a block at a time .. learned enough about the database filesystem to write an 'un-delete' tool, sold it after using it to successfully recover the original project, and have never looked back ..
The worst is when someone else notices it before you. We came in to work on Monday and some reported that they couldn't log into the system. We checked logs and noticed there were timeout errors on some SQL queries. Huh, that's odd, why is there a query "update Users set deletedat=now()" with no "where" clause running in production??? Turns out someone was in a hurry and tried to make a test pass without thinking to much about what the code was doing. Then - push right to prod on a Friday evening!
I have so many stories of data loss from many years working, that I do nothing without backups. From fat finger deletes to raid card batteries failing to multiple disks in the raid array failing at the same time. Data will be lost at some time with the only recovery method being a backup.
Tech: "Err, boss, I just totally broke their entire system"
Manager: "That's no problem, you got them to sign that contract, right? We always pad those with more services than they have employees or customers to use, so we can use that to cover any problems."
Tech: "..."
Manager: "Johnson, I'm not hearing the confirmation I expected."
Tech: "Sorry boss, there was an issue they were keen to have me look at and... well..."
Manager: "Johnson, you get your ass back here now. Do not pass go. Do not collect $200. The phone will not accurately convey the ass-chewing you are about to receive, and you don't deserve the paltry protection it would allow." <click>
Tech looks up to see local tech liason.
Tech: "Ehm. You need to restore from backup."
I've always admired that, but never emulated it...
On one contract about a decade ago, the manager used to proactively screw around with server hardware (eg pull a hdd out of a raid set) when bored, to ensure our recovery processes were always battle tested.
Didn't generally cause too many issues... until the one time he pulled a working hdd out of a raid5 set that already had a (real) failed drive, which we were already attempting to recover. It was a production database, but thankfully not a mission critical one :>.
From memory, he stopped testing things like that for a while afterwards. ;)
https://github.com/Netflix/SimianArmy/wiki/Chaos-Monkey
Also, there are now CoW Filesystems that prevent data loss because of human error. Btrfs and ZFS are good examples.
Small company, dev had access to test and prod databases. Was using SQL Developer or something similar, and had gotten into the habit of using auto-commit when on the test database. Must have switched over to prod without realizing it, and did a `DELETE FROM USERS` to truncate some production table.
You still need to apologize for a some data loss, though. So make sure that everything you do has one or two safety nets before it hits the customer.
I worked in a company where this was the standard, and we updated the code on the fly with eclipse to fix bugs :)
Last year I was on a flight en-route to an Ed-Tech convention in Philly. There was on board wifi and my phone had the wifi turned on. I go and check my emails before departure and get a debug page from tomcat. My initial reaction was panic, but then I remembered that the wifi runs separately from avionics. After all the mental pictures of the plane going down in flames due to someone leaving a server in DEBUG mode disappeared I simply closed the tab, turned the airplance mode on and went to sleep. Knowing in full that I had put my life in the hands of people who are pressured into writing code that works under impossible deadlines.
That's why I think that just like the romans poisoned themselves over time with lead in their pipes we will kill ourselves with buggy code from shitty projects.
Yes, shitty code will be our demise. I bet on AI.
Thankfully he'd just run it against a dev environment where the loss wasn't particularly severe (the prod environment is firewalled off, so he couldn't have done the same thing against that), but from the panicked tone of his messages before it was clear what had happened, I'm sure he's come to be extra careful about database credentials going forward....
I'd also would love to know how large is the database that was deleted. Doing a point in time restore of database that's a couple of hundred gigs should be relatively fast (depending on what hardware you are running on).
First graphic on this page includes a bright red box asking: "Is your data safe online?"
Evidently not a rhetorical question.
I do it all the time. "Give this access to this user to this db." 'Okay, but do you know they'll be able to drop your db?' "Oh shit okay wait a sec..."
On my own, I'd put CoW wherever possible. It's so easy to delete something on UNIX that it should also be easy to restore and CoW is without a doubt a no-brainer for this.
Restores, on the other hand...
(Hint: Solaris)
Of course there was no backup, because the sysadmin doesn't back up things in tmp directories.