This is not good! We don't want to scare people into writing less of these. We want to encourage people to write more of them. An MBA style "due to a human error, we lost a day of your data, we're tremendously sorry, we're doing everything in our power yadayada" isn't going to help anybody.
Yes, there's all kinds of things they could have done to prevent this from happening. Yes, some of the things they did (not) do were clearly mistakes that a seasoned DBA or sysadmin would not make. Possibly they aren't seasoned DBAs or sysadmins. Or they are but they still made a mistake.
This stuff happens. It sucks, but it still does. Get over yourselves and wish these people some luck.
In software there is still a certain arrogance of quickly calling the user (or other software professional) stupid, thinking it can't happen to you. But in reality given enough time, everyone makes at least one stupid mistake, it's how humans work.
Even when it was a suicidal pilot flying the plane into a mountain on purpose. Someone had to supervise him (there are two crew members in the cockpit for a reason), someone gave him a medical, there is automation in the cockpit that could have at least caused an alarm, etc.
So even when the accident is ultimately caused by a pilot's actions, there is always a chain of events where if any of the segments were broken the accident wouldn't have happened.
While we can't prevent a bonkers pilot from crashing a plane, we could perhaps prevent a bonkers crew member from flying the plane in the first place.
Aka the Swiss cheese model. You don't want to let the holes to align.
This approach is widely used in accident investigations and not only in aviation. Most industrial accidents are investigated like this, trying to understand the entire chain of events in order that processes could be improved and the problem prevented in the future.
Oh and there is one more key part in aviation that isn't elsewhere. The goal of an accident or incident investigation IS NOT TO APPORTION BLAME. It is to learn from it. That's why pilots in airlines with a healthy safety culture are encouraged to report problems, unsafe practices, etc. and this is used to fix the process instead of firing people. Once you start to play the blame game, people won't report problems - and you are flying blind into a disaster sooner or later.
The common example is C: "C is a sharp tool, but with a sufficiently smart, careful and experienced developer it does what you want (you're holding it wrong").
Developers still do this to each other.
What happened is that users started blaming themselves for what was going wrong, or start thinking they needed a new PC because problems would become more frequent.
From the perspective of a software guy, it was obvious that windows was the culprit but people would assign blame elsewhere and frequently point the finger at themselves.
so yes - an FAA investigation would end up unraveling the nonsense and point to windows.
That said, aviation level of safety is reliable and dependable and few single points of failure and... there are no private kit jets darnit!
There is a continuum from nothing changes & everything works to everything changes & nothing works. You have to choose the appropriate place on the dial for the task. Sounds like this is a one-man band.
I understand what you are taking about, but aviation has also strong expectations on pilots.
Also, if the guy or gal has alcohol problems, it would likely be visible on their flying performance over time, it should be noticed during the periodic medicals, etc.
So while a drunk pilot could be the immediate cause of a crash, it is not the only one. If any of those other things I have mentioned functioned as designed (or were in place to start with - not all flying is airline flying!), the accident wouldn't have happened.
If you focus only on the "drunk pilot, case closed", you will never identify deficiencies you may have elsewhere and which have contributed to the problem.
Get some sleep, do a thorough investigation, and the results of that are the post mortem that we would like published and where you learn from.
Publishing some premature thoughts without actual insight is not helping anybody. It will just invite the hate that you are seeing in this thread.
It seems that people annoyed mostly by "complexity gremlins". They are so annoyed, that they miss previous sentence "we’re too tired to figure it out right now." Guys fucked up their system, they restored it the best they could, they tried to figure out what happened, but failed. So they decided to do PR right now, to explain what they know, and to continue the investigation later.
But people see just "complexity gremlins". The lesson learned is do not try any humor in a postmortem. Be as serious, grave, and dull as you can.
What is to stop developers for checking into Github "drop database; drop table; alter index; create table; create database; alter permission;"? They are automating environment builds and so that is more efficient right? In my career, I have seen a Fortune 100 company's core system down and out for a week because of hubris like this. In large companies, data flows downstream from a core system. When you have to restore from backup, that cascades into restores in all the child systems.
Similarly, I once had to convince a Microsoft Evangelist who was hired into my company, not to redeploy our production database, every-time we had a production deployment. He was a pure developer and did not see any problems of dropping the database, recreating the database, and re-inserting all the data. I argued that a) this would take 10+ hours b) the production database has data going back many years and that the schema/keys/rules/triggers have evolved during that time-- meaning that many of the inserts would fail because they didn't meet the current schema. He was unconvinced but luckily my bosses overruled him.
My bosses were business types and understood accounting. In accounting, once you "post" a transaction to the ledger that becomes permanent. If you need to correct that transaction, then you create a new one that "credits" or corrects the entry. You don't take out the eraser.
For example, if i open the comments about a “14 hours ago” post, I usually see a top comment about other comments (like yours).
I then feel so out of the loop because i don’t see the “commenters” your are referring too - so the thread that follows seem off topic to me.
Culturally speaking we like to pat people on their back when they do something stupid and comfort them. But most of the time this isn’t productive because it doesn’t instil the requisite fear required when working out what decision to make.
What happens is we have growing complacency and disassociation from consequences.
Do you press the button on something potentially destructive because your are confident it is ok through analysis, good design and testing or confidence it is ok through trite complacency?
The industry is mostly the latter and it has to stop. And the first thing is calling bad processes, bad software and stupidity out for what it is.
Honestly these guys did good but most will try and hide this sort of fuck up or explain it away with weasel words.
People like you keep making the same mistake, creating companies/organisations/industries/societies that run on fear of failure. We've tried it a thousand times, and it never works.
You can't solve crime by making all punishments hearsh death, we've tried that in 1700 in Britain and crimerate was sky high.
This culture gave us disasters in USSR and famine in China.
The only thing that can solve this problem is structural change.
Blog posts analysing real-world mistakes should not be met with beratement.
In a blame free environment you find the underlying issue and fix it. In a blame full environment you cover up the mistake to avoid being fired and some other person does it again later down the line
The "comfort" will come from taking responsibility and owning and correcting the problem such that you have confidence it won't happen again.
Platitudes to make someone feel better without action helps nobody.
The fear is a valuable feedback mechanism and shouldn't been ignored. It's there to remind you of the potential consequences of a careless fuckup.
Lots here misunderstood this I think.. clearly the point is not to berate people for making mistakes or to foster a "fear culture" insofar as fear of personal attack but rather to not ignore the internal/personal fear of fucking up because you give a shit.
> It’s tempting to blame the disaster on the couple of glasses of red wine. However, the function that wiped the database was written whilst sober.
It was _written_ then, but you're still admitting to the world that your employees do work on production systems after they've been drinking. Since they were working so late, one might think this was emergency work, but it says "doing some late evening coding". I think this really highlights the need to separate work time from leisure time.
In this case there were like 10 relatively easy things that could have prevented this. Your ability to mentally compile and evaluate your code before you hit enter is not a reliable way to protect your production systems.
Coding after drinking is probably not a good idea of course, but “think better” is not the right takeaway from this.
I’ve done some of my most productive work this way. Not on production systems fortunately, and not in a long time.
Maybe such a breathalyzer interlock could be installed on your workstation too. After all, your systems and processes should prevent you from stupid things.
That is because most companies these days have processes around drinking in workplace, coming in drunk and working drunk.
Most mistakes are done sober only in environments where drinking couple of vine cups and then doing production change is considered unacceptable. In environment where drunk people work, mistakes are made when drunk.
Also I agree with other comments: doing some work after a glass or two should be fine because you should have other defences in place. “Not being drunk” shouldn’t be the only protection you have against disaster.
But it's just a side-project and I will continue late night coding with a glass of wine. I find it hugely enjoyable.
I would have a different mind-set if I was writing software for power stations as a professional.
Normally, this would be fine. But, it appears the site has paying members. Presumably, it's not "just a side-project" to them. You owe them better than tinkering with prod while tipsy.
Of course the next day, when re-reading the same pages, I was always discovering that the previous day I had everything wrong, nothing was obvious, and all my reasoning when with alcohol was false because simplistic and oblivious of any mathematical rigor.
Though I'm talking one or two drinks here, not firing up vscode after a night out or going through a bottle of rum.
The gremlins won this time."
No they didn't. Instead one of your gremlins ran this function directly on the production machine. This isn't rocket science, just the common sense conclusion. Now it would be a good time to check those auditing logs / access logs you're suppose to have them enabled on said production machine.
Exactly my first hypothesis too. But then keepthescore claims,
> of course we use different passwords and users for development and production.
How would this hypothesis explain that?
---
Metadialogue:
John Watson: "I deduce that someone changed the dev config source so that it uses the production config values."
Sherlock Holmes: "My dear Watson, while that is sensible, it seems to me that the balance of probability leans towards their production instance also having the development access credentials."
---
Just my way of saying, I think this case isn't as shut and closed as most comments (including parent) imply. I personally find the /etc/host mapping a likelier hypothesis but even that can't explain how different credentials failed to prevent this. Without more details coming from a proper investigation, we are just piling assumptions on top of assumptions. We are making bricks, without enough clay, as Holmes would say.
database = config.DevelopmentConfig.DB_DATABASE
user = config.DevelopmentConfig.DB_USERNAME
password = config.DevelopmentConfig.DB_PASSWORD
Just an idea.
Docker + Kubernetes are the biggest socially-acceptable hacks in the industry at the moment.
It doesn't even make sense to connect to a managed database using 'localhost'.
Managed databases are never localhost. They are hosted outside your VPS and you use a DNS name to connect to them.
Plus of course using git with a hook specifically for preview versus production (i.e. "git push production") that way local specific scripts can be stripped even if in same repo.
I'm sorry for your data loss, but this is a false and dangerous conclusion to make. You can avoid this problem. There are good suggestions in this thread, but I suggest you use Postgres's permission system to REVOKE DROP action on production except for a very special user that can only be logged in by a human, never a script.
And NEVER run your scripts or application servers as a superuser. This is a dangerous antipattern embraced by many and ORM and library. Grant CREATE and DROP to non-super users.
Gone are the days of me just being able to run a simple script that accesses data read only an exports the result elsewhere as an output.
I build and maintain our entire employee database with a python script, from a weird non-standard XML”like” daily dump from our payment system, and a few web-services that hold employee data in other requires systems. Our IT then builds/maintains our AD from a few powershell scripts, and finally we have a range of “micro services” that are really just independent scripts that send user data changes to the 500 systems that depend on our central record.
Sure, sure, we’re moving it to azure services for better monitoring, but basically it’s a few hundred lines of scripting that, combined with AD and ADDS, does more than a 1 million USD a year license IDM.
Just a few weeks ago, I set up a read-only user for myself, and moved all modify permission to role one must explicitly assume. Really helped me with peace of mind while developing the simple scripts that access data read only. This was on our managed AWS RDS database,
https://www.youtube.com/watch?v=X6NJkWbM1xk
By all means, find ways to fool-proof the architecture. But be prepared for scenarios where some destructive action happens to a production database.
The article isn’t claiming that the problem is impossible to solve.
On the contrary: “However, we will figure out what went wrong and ensure that this particular error doesn’t happen again.”.
No, you can't. No matter how good you are, you can always "rm -rf" your world.
Yes, we can make it harder, but, at the end of the day, some human, somewhere, has to pull the switch on the stuff that pushes to prod.
You can clobber prod manually, or you accidentally write an erroneous script that clobbers prod. Either way--prod is toast.
The word of the day is "backups".
Yes, backups are vitally important, but no it is not possible to accidentally rm -rf with proper design.
It's possible to have the most dangerous credentials possible and still make it difficult to do catastrophic global changes. Hell it's my job to make sure this is the case.
Until we get our shit together and start formally verifying the semantics of everything, their conclusion is 100% correct, both literally and practically.
for db in `mysql [...] | grep [...]`
do
mysqldump [...] > $db.sql
done
git commit -a -m "Automatic backup"
git push [backup server #1]
git push [backup server #2]
git push [backup server #3]
git gc
I also have a single-entry-point script that turns a blank Linux VM into a production/staging server. If your business is more than a hobby project and you're not doing something similar, you are sitting on a ticking time bomb.
You can always switch to a more specialized solution if the repository size starts bugging you, but don't fall into the trap of premature optimization.
The mysqldump command is tweaked to use individual INSERT clauses as opposed to one bulk one, so the diff hunks are smaller.
You can also sed and remove the mysqldump timestamp, so there will be no commits if there are no database changes, saving the git repo space.
Wow. But then again it's not like programmers handle dangerous infrastructure like trucks, military rockets or nuclear power plants. Those are reserved for adults
Can you imagine if instead of a physical lock it just said “are you sure you wish to turn on this machine”. “Of course I want to turn it on, that’s why I pressed the button”
Some software makes it a lot harder for the user to mess up now. When deleting a repo on GitLab you have to type the name of the repo before pressing delete and then it puts it in a pending deletion state for a month before it’s actually deleted. Unfortunately for developers we typically get minimal cli tools which will instantly cause a lot of damage without any way to undo.
Basically make it clear even to the caveman brain that things are different.
Seems like they though a casual "everyman" type of explanation would suffice, but really who would trust them after this?
These is pretty common, as devs tool belts have grown longer over time.
I think at some point we will stop automating or reverse some of the automation.
Literally just the automation of the test suite. That's 1 automation.
> These is pretty common
? Waiting for FB to delete their db
Obviously, somehow the script ran on the database host.
some practices I've followed in the past to keep this kind of thing from happening:
* A script that deletes all the data can never be deployed to production.
* scripts that alter the DB rename tables/columns rather than dropping them (you write a matching rollback script ), for at least one schema upgrade cycle. you can always restore from backups, but this can make rollbacks quick when you spot a problem at deployment time.
* the number of people with access to the database in prod is severely restricted. I suppose this is obvious, so I'm curious how the particular chain of events in TFA happened.
Unfortunately, the "wipe & recreate database" script, while dangerous, is very useful; it's a core part of most of my automated testing because automated testing wipes & recreates a lot.
More likely, I'd suspect, is something like an SSH tunnel with port forwarding was running, perhaps as part of another script.
I.e. if Alice is your senior DBA who would have full access to everything including deleting the main production database, then it does not mean that the user 'alice' should have the permission to execute 'drop database production' - if that needs to be done, she can temporarily escalate the permissions to do that (e.g. a separate account, or separate role added to the account and removed afterwards, etc).
Arguably, if your DB structure changes generally are deployed with some automated tools, then the everyday permissions of senior DBA/developer accounts in the production environment(s) should be read-only for diagnostics. If you need a structural change, make a migration and deploy it properly; if you need an urgent ad-hoc fix to data for some reason (which you hopefully shouldn't need to do very often), then do that temporary privilege elevation thing; perhaps it's just "symbolic" but it can't be done accidentally.
And of those people, there should be an even fewer number with the "drop database" privilege on prod.
Also, from a first glance, it looks like using different database names and (especially!) credentials between the dev and prod environments would be a good idea too.
During this operation, the server ran out of memory—presumably because of all the files I'd created—and before I know it I'd managed to crash 3 services and corrupted the database—which was also on this host—on my first day. All while everyone else in the company was asleep :)
Over the next few hours, I brought the site back online by piecing commands together from the `.bash_history` file.
I actually waited until nightfall just incase I bumped the server offline because we had low traffic during those hours.
It's lucky it's just some online scoreboard because I'm sure as shit this stuff has happened before with more critical systems and it scares the hell out of me that engineers are fine blaming "gremlins" instead of taking responsibility for their own incompetence.
I think they’re doing that with this post? At least I find it hard to imagine myself writing down that I’d drunk a few glasses of wine and dropped the production database.
You cannot expect all engineers to be fully versed in the vagarities of database administration. Especially if they’re the only ones working on something.
That said, I will happily accept consulting fees in return for deleting someone's database in prod, should they so desire.
Edit: Heck, being a white-hat licensed-to-create-mayhem chaos monkey for a few hours a week sounds pretty fun. Email in profile.
Then the client wanted that functionality back. Oops.
Another is to change my terminal theme to a red background before connecting to anything in production...never want to click the ‘psql ...’ tab, run “truncate table app_user cascade” and realize afterwards it was a lingering connection to production...
We had several MySQL string columns as long text type in our database but they should have been varchar(255) or so. So I was assigned to convert these columns to their appropriate size.
Being the good developer I was, I decided to download a snapshot of the prod database locally and checked the maximum string length we had for each column via a script. Using this script it made a migration query that would alter column types to match their maximum used length keeping the minimum length as varchar (255).
I tested that migration and everything looked good, it passed code review and was run on prod. Soon after we start getting complaints from users that their old email texts have been truncated. I then realize the stupidity of the whole thing, the local dump of production database always wiped out many columns clean for privacy like the email body column. So the script thought it had max length of 0 and decided to convert the column to varchar(255).
I realize the whole thing may look incredibly stupid, that's only because the naming for db columns was in a foreign european language so I didn't know even know the semantics of each column.
Thankfully my seniors managed to restore that column and took the responsibility themselves since they had passed the review.
We still did fix those unusually large columns but this time by simple duplicate alter queries for each of those columns instead of using fancy scripts.
I think a valuable lesson was learned that day to not rely on hacky scripts just to reduce some duplicate code.
I now prefer clarity and explicitness when writing such scripts instead of trying to be too clever and automating everything.
Basically you just blindly ran the migration on the data and checked if it didn’t fail?
The lesson here is not about cleverness unfortunately.
So yes I could have noticed their length 0 if I had looked carefully amidst hundreds of rows but since my faulty logic of prod db = local db didn't even consider this possible I didn't bother.
If it had been just 10 to 20 migrations queries that would have been a lot easier to validate but then I wouldn't even have attempted to write a script
The parent is either misrepresenting the situation or they didn’t do what they say they did.
Also in any production setup, before the migration in the same transaction you would have something along the lines of “check if the column size is larger than and then abort”, because you never know when that can be added while working on the database.
Now... our scenario was such that we could NOT lose those 7 hours because each customer record lost meant $5000 usd penalty.
What saved us is that I knew about the oplog (binlog in mysql) so after restoring the backup i isolated the last N hours lost from the log and replayed it on the database.
Lesson learned and a lucky save.
No one owned up to it, but had a pretty good idea who it was.
That sounds like you're putting (some of) the blame on whoever misclicked. As opposed to everyone who has allowed this insanely dangerous situation to exist.
I haven't seen this design in practice using MongoDB Atlas or Compass, but would hope for an "Are you really sure?" confirmation in an admin UI.
There's absolutely nothing stopping anything with access to localhost from routing it anywhere that process wants. Does not even take a malicious actor, all kinds of legit programs expose localhost. It's really not something you should use for anything except as a signal to other well-behaving programs that you are using the network stack as a machine-local IPC bus.
I'll give them the benefit of the doubt and suggest that it wasn't the case and that it's unlikely that a dev or OPs person was tunnelling through a bastion of some kind to run the script.
If they pulled their credentials from a store and populated the config object that way, it's very possible someone actually loaded the production credentials by mistake into the development secrets file. The CI/pipeline system has permissions/network access to deploy to any environment, hence how it ran the script to drop the table.
I'm purely speculating another alternative to the much worse case of the tunnelling scenario outline.
I have been running Postgres in production supporting $millions in business for years. Here's how it's set up. These days I use RDS in AWS, but the same is doable anywhere.
First, the primary server is configured to send write ahead logs (WAL) to a secondary server. What this means is that before a transaction completes on the master, she slave has written it too. This is a hot spare in case something happens to the master.
Secondly, WAL logs will happily contain a DROP DATABASE in them, they're just the transaction log, and don't prevent bad mistakes, so I also send the WAL logs to backup storage via WAL-E. In the tale of horror in the linked article, I'd be able to recover the DB by restoring from the last backup, and applying the WAL delta. If the WAL contains a "drop database", then some manual intervention is required to only play them back up to the statement before that drop.
Third is a question of access control for developers. Absolutely nobody should have write credentials for a prod DB except for the prod services. If a developer needs to work with data to develop something, I have all these wonderful DB backups lying around, so I bring up a new DB from the backups, giving the developer a sandbox to play in, and also testing my recovery procedure, double-win. Now, there are emergencies where this rule is broken, but it's an anomalous situation handled on a case by case basis, and I only let people who know what they're doing touch that live prod DB.
If you're using MySQL, it's called a binary log and not a Write Ahead Log, it was very difficult to find meaningful Google results for "MySQL WAL"
Its a real problem that we used to have trained DBAs to own the data where now devs and automatic tools are relied upon, there isn't a culture or toolset built up yet to handle it.
It’s nice to have that capability, but some databases are just too big to have multiple copies lying around, or to able to create a sandbox for everyone.
> However, we will figure out what went wrong and ensure that that particular error doesn’t happen again.
How can you say statement 2 just after statement 1 ? Isn't statement 1 just plain acceptance of defeat ?
And looking at all the replies here, is this a feel good thread for the mistakes you made ?
Acknowledging statement 1 doesn’t mean giving up—it simply means being clear and realistic about the nature and scale of the problem we’re facing when we try to build complex software systems. In the face of that we can give up, or we can just do the best we can, and it sounds more like these people are doing the latter.
I was once sshed to the production server, and was cleaning up some old files that got created by an errant script, one which file was '~'. So, to clean it up, I type `rm -rf ~`.
Didn't have a backup of it unfortunately, though thankfully there wasn't anything too critical in there. Mostly just lost a bunch of utility scripts and dotfiles. I feel like it's beneficial in the long run for everyone to make a mistake like this once early on in their career.
fs.delete(shell.expand('*'), recursive:yes)
fs.undo()
They could have used point-in-time recovery to not lose any data from this at all.
You can the restore from a recent base backup and roll forward the WAL to just before the snafu.
I'd rather a culture where people admit to their mistakes than one where they try hide or get whipped for owning up to them.
We're people after all and some of us like a glass of wine and unwind while still performing our duties as engineers. After all, it's not like we're in charge of life support or critical systems which absolutely cannot fail.
The worst thing about computers? They do exactly what they are told to do.
> terraform destroy
(And either a confirmation or a flag) and everything is deleted.
I know you can add some locks but still :/
I like to use the lifecycle feature for suuuper core things that will never be deleted (VPC, r53 zone, etc) and eventually when I start targeting multiple DCs w/ lots of infra I'll eventually move to many state roots (or use tools like Terragrunt, which make things mildly scary again).
I can't see how the OP indicates making a connection to db without the correct credentials to begin with.
[1] https://keepthescore.co/blog/posts/monetizing-keepthescore/
And it bodes well for your firm that that doesn't get you fired either.
These things happen to the best of us but having dealt with it responsibly and honestly as a team is something you can be proud of IMO.
Maybe there's more than one interpretation of "bodes well" but not knowing how to do the one thing customers were paying you to do isn't consistent with my definition.
> having dealt with it responsibly and honestly as a team is something you can be proud of IMO.
"We were drinking wine and deleted the database and now your data's gone LOL" is not something that should make you proud.
Whoa.
Obviously there are plenty of large enterprise wide data breaches, which, I would say is actually worse than losing a day of data in a lot of cases. So also not so many satefy measures, again, worse than startups; at least they have an excuse of being understaffed and underfunded.
Otherwise having a binlog based backup (or WAL, I guess, but i don’t know PG that well) is critical.
The key point there is they provide point in time recovery possibilities (and even the ability to rewrite history).
If I screw up one parameter, instead of deleting only unconfirmed users, I could delete all users. I have two redundant checks, first when the query is run to get the unconfirmed users, and then again checking the user's confirmed status before deleting them. And then I check one more time further down in the code for good measure. Not because I think the result will be different, but just in case one of the lines of code is altered somehow.
I put BIG LOUD comments everywhere of course. But it still terrifies me.
I think the main reason of this accident is lack of separation between development and operations.
Tough way to learn that lesson though.
That it happened meant that there were many things wrong with the architecture, and summing up the problem to “these things happen” is irresponsible, most importantly your response to a critical failure needs to be in the mindset of figuring out how you would have prevented the error without knowing it was going to happen and doing so in several redundant ways.
Fixing the specific bug does almost nothing for your future reliability.
If you are not sure how a hard coded script that was targeting localhost affected a production database, how do you know you were even viewing the production database as the one dropped?
Maybe you were simply connected to the wrong database server?
I’ve done that many times - where I had an initial “oh no“ moment and then realized I was just looking at the wrong thing, and everything was ok.
I’ve also accidentally deployed a client website with the wrong connection string and it was quite confusing.
In an even more extreme case: I had been deploying a serverless stack to the entirely wrong aws account - I thought I was using an aws named profile and I was actually using the default (which changed when I got a new desktop system). I.e. aws cli uses —profile flag, but serverless cli uses —aws-profile flag. (Thankfully this all happened during development.)
I now have deleted default profiles from my aws config.
> KeepTheScore is an online software for scorekeeping. Create your own scoreboard for up to 150 players and start tracking points. It's mostly free and requires no user account.
And also:
> Sat Sep 5, 2020, Running Keepthescore.co costs around 171 USD each month, whilst the revenue is close to zero (we do make a little money by building custom scoreboards now and then). This is an unsustainable situation which needs to be fixed – we hope this is understandable! To put it another way: Keepthescore.co needs to start making money to continue to exist.
https://keepthescore.co/blog/posts/monetizing-keepthescore/
So okay, it's basically a hobby site, for a service that most users probably won't really mind losing 7 hours of data, and that has few if any paying customers.
That context makes it make a little bit more sense.
So many lessons learned that day. I trust her with the master keys at this point, as nobody is more careful with production than her now. :)
After about an hour of investigation, I find one of the primary database tables is empty - completely blank.
I then spend the next hour looking through code to see if there's any chance of a bug that would wipe their data and couldn't find anything that would do that.
I then had to make "the phone call" to the client saying that their primary data table had been wiped and I didn't know what we did wrong.
Their response: "Oh I wrote a query and accidentally did that, but thought I stopped it".
Often small changes to the structure drastically reduce probability of stuff like this happening.
Eg. we use docker to setup test and dev databases and seed from (processed) dumps. When we need to clean our database, we simply put down the docker container. Ie. we do not need to implement destructive database cleanup eliminating structure that could potentially fail.
Having policies about not accessing production database directly (and allow the extra time for building tooling around that policy), good preview / staging environments, etc. All fail eliminating structure.
At one point someone called it "good enough" and they basically had to honor the customer word if they had purchased something and it wasn't there.
It was a mess.
It was on all major news, and it was really bad press. In the end, they actually had a massive bump in their sales afterwards. Everyone went to checkout their own purchases and ended up buying something else, and the news was like free ads.
https://www.washingtonpost.com/business/capitalbusiness/the-...
I also have daily backups, but I write logs (locally and regularly copy from the production server) for all database actions to disk for the purpose of checking through them if something goes wrong, or having the option to replay them encase something like this happens. SO you have your database backups as "save points" and the logs to replay all the "actions" for that day.
> local_db = PostgresqlDatabase(database=database, user=user, password=password, host='localhost', port=port)
I am guessing this part. Even though the host is hardcoded as "localhost" , when you do a ssh port-forwarding, the localhost might actually be the real production. e.g sudo ssh user@myserverip -L 3333:localhost:3306
Just to help with the postmortem:
1) “localhost” is just a loopback to whatever machine you’re on
2) the user and pw are pulled from config
So someone was running this from the production server or had the production DB mapped to localhost and ran it with a production config for some reason (working with prod data maybe). The hard coding to localhost will only ensure that it works for the machine it’s called on - in this case the prod server.
Things you might do to avoid this in the future include a wide spread of things, the main recommendations I’d have are:
1) only put production artifacts on prod
2) limit developer access to prod data
Best of luck
But, as I said, that happens and blaming doesn't fix anything, so, for the future:
1. make a temporary backup of your database 2. create tables with new data 3. drop old tables
Stop drink and deploy something on production, especially at late evening time.
Also, @oppositelock pointed out that WAL would contain the destructive query too. How does one remove a single query from a WAL for replay or how does one correctly use WAL to recover after a 23-hour old backup was restored?
Finally, how does one work on the WAL level with managed DBs on AWS or DO if they block SSH access?
This is an abject lesson that understanding human psychology is actually a huge part of good architecture. Automating everything you do in production with a script that is QA tested prior to use is the best way to avoid catastrophic production outages.
It does take a bit longer to get from start to finish, and younger devs often try to ignore it, but it is worth putting a layer of oversight between your employees and your source of revenue.
I ran a rake db schema dump command of some kind and instead of it returning the schema of the database, it decided to just completely nuke my entire database. Instantly. It's very easy to fuck up so cover your ass gents, and backup often and run your restores periodically to make you can actually do them in case of an emergency.
Shit happens. You learn and try to never repeat it. And share with others so hopefully they learn.
Ps. Don't do knee-jerk late at night quick patches. For example don't stop a database that has run out of disk space, try to migrate the data in memory first... And also do proper backup monitoring, and restores. Having 30 days of 0 byte backups is not that helpful. :)
> We’ve learned that having a function that deletes your database is too dangerous to have lying around.
Indeed, anything that might compromise the data, anything that might involve deletion anyway, should require manual confirmation whether you manage the database or it's a service provided.
Sadly, I learned this the hard way too, but at least it was a single column with a non-critical date and not the entire database.
Presumably, a managed DB service should essentially never be available on `localhost`. Additionally, it would be very weird for `config.DevelopmentConfig` to return the production database credentials.
Yes, people are not perfect and computer systems are complex. Admit it and don't be so overconfident.
”Errare humanum est”, prepare your backups.
1. This business is too flippant with their write-able production access.
2. No user should have DROP DATABASE grants on production.
3. Clearly one of their employees was using a port forward to access production.
Remember: You just fix the errors YOU can think of.
if (Env.Name -like '*prod*' ) { then throw }
Probably the admin has set the hba config to trust localhost. Solution - don't use the same db name in prod jsut to be sure
Here it would have failed to create the already existing tables and raised an error.
This was the procedure:
1. Restore DB backups in Seattle.
2. Set up replication from NYC to Seattle.
3. Start changing things to read from Seattle, with writes still going to NYC.
4. After everything is reading from Seattle and has been doing so with no problems for a while, change the replication to be two-way between NYC and Seattle.
5. Start switching writes to Seattle.
6. After both reads and writes are all going to Seattle and it has been that way for a while with no problems, turn off replication.
7. Notify me that I can wipe the NYC servers, for which we had root access but not console access. I wasn't in the IT department and wasn't involved in the first 6 steps, but had the most Unix experience and was thought to be the best at doing a thorough server wipe.
My server wipe procedure was something like this.
8. "DELETE FROM table_name" for each DB table.
9. "DROP TABLE table_name" for each DB table.
10. Stop the DB server.
11. Overwrite all the DB data files with random data.
12. Delete all the DB data files.
13. Delete everything else of ours.
14. Uninstall all packages we installed after the base system install.
15. Delete every data file I could find that #14 left behind.
16. Write files of random data to fill up all the free space.
The problem was with step #6. They declared it done and turned it over to me for step #7 without actually having done the "turn off replication" part of step #6. Step #8 was replicated to Seattle.
It took them a while to figure out that data was being deleted and why that was happened.
We were split across three office buildings, and the one I was in had not yet had phones installed in all the offices, and mine was one of the ones with a phone. None of the people whose offices did have phones were in, so they lost a few more minutes before realizing that someone would have to run a couple blocks to my office to tell me to stop the wipe.
It took about 12 hours or so afterwards for them to restore Seattle from the latest backup, and then replay the logs from between the backup time and the start of the deletes.
After that they were overly cautious, taking a long time to let me resume the NYC wipe. They went right up to the point where I told them if we didn't start now we might not finish, and reminded them that those machines had sensitive customer personal information on them and were probably going to end up being auctioned off on eBay by the hosting company. They came to their senses and told me to go ahead.