23 ms·
We deleted the production database by accident
- smadge 6y agoSSH tunnel from localhost to prod on database port?
- rmrfrmrf 6y agovscode remote extension perhaps
- sfkdjf9j3j 6y agoThat's my guess too. Naughty naughty. That means his production creds are the same as his development creds!
- EdwardDiego 6y agoYeah that's my guess, and likewise biggest concern - if the credentials are the same in dev and prod, it increases risk surface.
- macNchz 6y agoA likely culprit. Having worked on a bunch of early-stage products where best practices are a distant future dream, I’ve developed a few “seatbelt” habits I use to avoid these kinds of things. One of them is to always use a random high-number local port if I’m tunneling to a production service. 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...
- rmrfrmrf 6y agowhat's a situation where you'd be tunneling to a production service?
- macNchz 6y agoI think the most common reason I’ve had in the past was to connect to the RabbitMQ web admin while dealing with some emergent issue with task throughput, a common problem area with hacked together web apps that start to get real traffic. It’s also handy to be able to use a more advanced SQL client that’s not installed on the server (pgcli, emacs, etc) when digging around for something that’s causing errors in production.
- sdepablos 6y agoVPN to production and same hostname for dev and prod?
- kerng 6y ago...and same credentials apparently also - there are lots of things that could have prevented something like this.
- k__ 6y agoKeep going, I'm writing this down!
- jlgaddis 6y agoTake the "drop database" bit (on the production database) away from your developers, too. As well as pretty much every other privilege they don't legitimately need to use on a daily basis -- which, for prod, should be most of them (quite possibly including "delete"). If or when they really need to delete a ton of rows all in one go, they can be given a (temporary) set of credentials that they can use to do that, once, and which are then revoked immediately afterwards -- after another set of eyes reviews the script they've written to actually perform the operation, of course. Basic best practices aren't difficult or some secret thing that only the experts know about. It is necessary to actually implement and follow them, however! Sure, it can be a pain in the ass sometimes. Will it be worth it when it eventually saves your ass one day, "after a couple of glasses of red wine"? Absolutely.
- asddubs 6y agothat's true, I totally forgot about SQL permissions, there's so many failsafes for this
- astura 6y agoAs a developer at all the jobs I've had, I've never even had access to the production database, full stop - only the server admins did. If I needed something from prod I'd go through them. I don't even consider it inconvenient.
- rullelito 6y agoLOL, I forgot how few safety measure startups have in place.
- xwdv 6y agoGiven the short time frames that startups aim to capture value in its not worth investing time in safety until you have plenty of capacity to spare later.
- tluyben2 6y agoLike this never happens in big corps: sure, mostly on departmental level but still enterprises, not startups. They try to escape the annoying and slow as molasses dba’s/devops and install/create/buy/saas systems to avoid the red tape. But then the same things as with startups go wrong. And rather often too; we often had these calls asking if we could restore. 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.
- fencepost 6y agoDoes the database name value allow specifying a host as part of it?
- mijoharas 6y agoThis is what I came to say. Postgres usually allows a connection string that contains all the details as the database name postgres://username:password@host:port/dbname and I think it will take priority over separately specified host, depending on the client. Tough way to learn that lesson though.
- deleted 6y ago[deleted]
- deleted 6y ago[deleted]
- muststopmyths 6y ago>Note that host is hardcoded to localhost. This means it should never connect to any machine other than the developer machine. We’re too tired to figure it out right now. The gremlins won this time. 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.
- amluto 6y agoI have a little metadata table in production that has a field that says “this is a production database”. The delete-everything script reads that flag via a SQL query that will error out of it’s set in the same transaction as the deletion. To prevent the flag from getting cleared in production, the production software stack will refuse to run if the “production” flag is not set.
- at_a_remove 6y agoNice. Very nice.
- jerf 6y agoThis is also one place where defense-in-depth is useful. "Has production flag" OR "name contains 'prod'" OR "hostname contains 'prod'" OR "one of my interfaces is in the production IP range" OR etc. etc. You really can't have too many clauses there. 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.
- asddubs 6y agoone silly last resort measure I did on a project a while back was having a IS_STAGING file somewhere, only existing on localhost, and every request it would check if the hostname is that of the live site, and if so, delete that file. the file itself wasn't enough to make the server think it's in staging mode, but it was the only thing in the chain that if it were to go wrong, would fix itself automatically almost immediately (and log an error)
- freitasm 6y agoLost seven hours of data? Daily backup with no transaction log backup? Whoa.
- deleted 6y ago[deleted]
- lisper 6y agoYeah, this. The problem is not that the production database was deleted by accident. The problem is that it was possible to (unrecoverably) delete the production database by accident.
- deleted 6y ago[deleted]
- jrockway 6y agoYou'll find that most users of cloud databases are in this boat. For example, on GCP, deleting the database instance deletes the backups! You have to write your own software if you want to survive that button click.
- xnyan 6y agolocalhost is an abstraction, it's a non-routable-outside-your-machine network...except it's not. It's nothing more than normal TCP traffic except with a message to the OS and other programs that whatever is on that local computer network, you don't want it routed outside the local computer. 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.
- defen 6y agoThe fact that the production db has the same username/password as the development one is perhaps more troubling.
- cortesoft 6y agoIt likely doesn't... it probably reads it from the environment or a config role, and since it was in production it had the production credentials.
- defen 6y agoThe code explicitly referenced “DevelopmentConfig” though
- kerng 6y agoYes! This is the biggest mistake probably
- Biganon 6y agoThey say in the article that it's not the case.
- NikolaeVarius 6y agoThinking that localhost is anything special is like year 1.5 developer mistake
- webel0 6y agoThis is my greatest fear when it comes to terraform: > terraform destroy (And either a confirmation or a flag) and everything is deleted. I know you can add some locks but still :/
- malwrar 6y agoYou can save yourself from scary operations like deleting everything by a.) not rooting your entire infra in the same main.tf and b.) using Terraform's lifecycle meta-argument: https://www.terraform.io/docs/configuration/resources.html#lifecycle-lifecycle-customizations https://www.terraform.io/docs/configuration/resources.html#l... 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).
- gkop 6y agoAlso a human should review every plan and confirm before applying, right?
- deleted 6y ago[deleted]
- deleted 6y ago[deleted]
- a-b 6y agoRecreate and seed test database is totally ok in RoR world. I think the main reason of this accident is lack of separation between development and operations.
- jacquesm 6y agoThis happens more often than you might think.
- cblconfederate 6y ago> Computers are just too complex and there are days when the complexity gremlins win. 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
- yunruse 6y agoI feel that computers make it easier for this danger to be more indirect, however. The examples you give are physical, and even the youngest of child would likely recognise they are not regular items. A production database, meanwhile, is visually identical to a test database, if measures are not made to make it distinct. Adults though we may be, we're human, and humans can make really daft mistakes without the right context to avoid them
- Waterluvian 6y agoAll of those items have a lot of safety software in them.
- PurpleFoxy 6y agoThere are also countless safety measures on physical items that have been iterated on over decades to prevent all kinds of accidents. Things like putting physical locks on switches to prevent machinery being turned on while people are working on it. 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.
- im3w1l 6y agoSo, silly idea. What if, to work on the production database, you had to go into the special production room, colored in the special production color, scented with the special production perfume, and sit on a just tiny bit uncomfortable production chair. Basically make it clear even to the caveman brain that things are different.
- jlgaddis 6y agoThe wonderful thing about computers is that they do exactly what they are told to do. The worst thing about computers? They do exactly what they are told to do.
- junglejoose 6y agoYou aren’t a real engineer until you do this. So congrats on the promotion! :)
- emerongi 6y agoI wrote a migration that dropped columns for a functionality that was no longer to be used. Then the client wanted that functionality back. Oops.
- Guest42 6y agoDefinitely, the likelihood of these things happens goes way up alongside the amount of concurrent tasks, meetings, or other forms of distraction. I’ve seen my fair share of production administration as a side task.
- zmmmmm 6y agoIndeed - after incidents like this I usually say, "This is called experience that you can't pay to get for any price. Learn from it well, and value your lesson."
- ISL 6y agoI'm a physicist, interested in consulting on both data analysis and precision metrology/hardware projects in general. 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.
- li4ick 6y agoYeah, imagine if a bridge engineer said the same thing: "You aren't an engineer until your bridge collapses. Congrats!" I am starting to hate tech culture. Nobody cares about correctness and discipline. Mention "math" and everybody spreads like cockroaches.
- Negitivefrags 6y agoIf you are using postgres, configure it to keep the WAL logs for at least 24 hours. They could have used point-in-time recovery to not lose any data from this at all.
- yjftsjthsd-h 6y agoIf you can do this, then yes by all means do it, but that has significant impact on disk usage.
- koolba 6y agoIt doesn’t have to be local. In fact it shouldn’t be local anyway as backups accessible to be deleted from the source aren’t real backups any way. You can the restore from a recent base backup and roll forward the WAL to just before the snafu.
- yjftsjthsd-h 6y agoYeah, but that can still create a situation where you can create WALs faster than they're uploaded. (And if there's a good solution to that, I'm interested, because we ran a server out of disk this way last month at work)
- xtracto 6y agoThis happened to me (someone in my team) a while ago but with mongo. The production database was ssh-tunneled to the default port of the guys computer and he ran tests that cleaned the database first. 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.
- fma 6y agoSame happened to me many years ago. QA dropped the prod db. It's been many years but if I recall, I believe in the dropdown menu of the MongoDB browser, exit & drop database were next to each other...Spent a whole night replaying the oplog. No one owned up to it, but had a pretty good idea who it was.
- vanviegen 6y ago> 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.
- rocqua 6y agoMisclicking is a tiny forgivable mistake. Not immediately calling up your boss to say "I fucked up big" is not a mistake, it is a conscious bad action.
- ojnabieoot 6y agoIt sounds like whoever did it might not even be aware they were responsible: > in the dropdown menu of the MongoDB browser, exit & drop database were next to each other So maybe they signed off for the night without realizing anything was wrong.
- deleted 6y ago[deleted]
- jlgaddis 6y agoConvenience and/or laziness, typically. Things like this would have been much less likely to happen in the past -- you know, when the developers only had access to the development database. But then someone had an idea... think of how great it would be if we got rid of the operations folks and gave responsiblity for prod to the developers, too!
- hannofcart 6y agoIt takes a great deal of integrity to admit that you deleted a database because you were mucking around in your infra after red wine. 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.
- bachmeier 6y ago> And it bodes well for your firm that that doesn't get you fired either. 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.
- hannofcart 6y agoPlease re-read what I wrote. I was praising their honesty. Not egging them on to be more sloppy.
- amingilani 6y agoHappens to all of us. Once I required logs from the server. The log file was a few gigs and still in use. so I carefully duplicated it, grepped just the lines I needed into another file and downloaded the smaller file. 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.
- tempestn 6y agoSeems unwise to have an employee doing anything with production servers on their first day, let alone while everyone else is asleep.
- amingilani 6y agoIt does but that was an exceptional role. The company needed emergency patches to a running product while they hired a whole engineering team. As such, I was the only one around doing things, and there wasn't any documentation for me to work off of. I actually waited until nightfall just incase I bumped the server offline because we had low traffic during those hours.
- nullsense 6y agoWhat's the story behind this company/job? Was it some sort of total dumpster fire?
- amingilani 6y agoI wouldn't classify it as that but they had had trouble in the past which lead to a lot of their team leaving, and were now looking to recover from it. I was only there for a short time though. Hopefully they figured things out.
- netheril96 6y ago
- codegladiator 6y ago> Computers are just too complex and there are days when the complexity gremlins win. > 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 ?
- ssalka 6y agoI like to think that, by addressing the known bugs as they pop up, over time you can box the complexity gremlins into tighter and more predictable spaces. Though as long as humans are building these systems, that box will always be there, and the predictability of those gremlins' behavior will only go so far.
- codegladiator 6y agoI am not sure what the bug was here ? Everything worked as intended to be.
- caconym_ 6y agoIn context, statement 1 regards proactively eliminating all bugs and risks. Statement 2 regards understanding the root cause of this particular incident and reactively fixing it so it won’t happen again. 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.
- suzzer99 6y agoWe have something similar with AWS Cognito. If a user signs up but doesn't go through with the verification process, there's no setting to say "remove them after X days". So we have to run a batch job. 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.
- 6nf 6y agoSoft deletes reduces the scariness
- f223ff23 6y agoHaha alcoholics complains they deleted a productive database by mistake :D
- fideloper 6y agoRDS is so very worth paying for this type of issue (in many cases, obviously $60 to multiple thousands a month isn’t great for everything). 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).
- latch 6y agoBarman (1) is really easy to setup and lets you avoid the many pitfalls of RDS (lower performance, high cost, no superadmin, no arbitrary extensions, waiting for releases, bad log interface). (1) https://www.pgbarman.org/ https://www.pgbarman.org/
- exabrial 6y agoIf you keep configuration in the environment (/etc/default/app-name) rather than in the application package, it's nearly impossible to make this mistake (especially with proper firewall rules). You can even package your config as a deb and keep it encrypted version control.
- danellis 6y ago> after a couple of glasses of red wine, we deleted the production database by accident > 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.
- yelloweyes 6y agolol it's a scoreboard app
- beervirus 6y agoSounds like they weren't trying to do work on production.
- cle 6y agoNo. Your systems and processes should protect you from doing something stupid, because we’ve all done stupid things. Most stupid things are done whilst sober. 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.
- TedDoesntTalk 6y ago> Coding after drinking is probably not a good idea I’ve done some of my most productive work this way. Not on production systems fortunately, and not in a long time.
- marcosdumay 6y agoYes, it can be productive (depends on exactly what you are doing). But I imagine you revise your work while sober before you deploy it.
- mbroshi 6y agoI love this post. This sort of thing happens to everyone, most people just are not willing to be so open about it. 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 ~`.
- meowface 6y agoSomewhat similar story from many years ago. Was in ~/somedirectory, wanted to clear the contents, ran `rm -rf *`. Turns out somewhere in between I had done a `cd ..`, but I thought I was still in the child directory. Fastest Ctrl+C ever once I saw some permission errors, but most of the home directory was wiped in that second or two. 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.
- wruza 6y agoIt would be beneficial for `rm` to have a way for making `trash` mode the default mode of operation. Unix shell is prone to errors like this beyond all reason. And nobody would die typing: fs.delete(shell.expand('*'), recursive:yes) fs.undo() or something like that with completion helpers. Our instruments usually lack any safety in general, and making them safe is hard, especially when you're just a worker and not a safety expert. All the world today benefits from mistake-friendly software, except for developers who constantly walk on ui minefields.
- anonunivgrad 6y agoI wouldn’t want to be on the wrong side of a lawsuit, defending drunk employees working on production data. What outrageous recklessness. And how imprudent to admit this to the public. Some things are best kept to yourself. No one needs to know that.
- colesantiago 6y agothank you for your highly valued expert opinion. edit: a stunning new record of bots flagging my post within 3 minutes. woo hoo... never change hn.
- tedk-42 6y agoIf you built good systems that have reliable backups or rollback mechanisms, dev should be able to have a beer on a friday and do a deployment without worrying about the fire they just caused. 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.
- robjan 6y agoThis is someone's side project. I'm doubtful there will be any lawsuits involved
- john_moscow 6y agoJust my 2 cents. I run a small software business that involves a few moderately-sized databases. The day I moved from a fully managed hosting to a Linux VPS, I have crontabbed a script like this to run several times a day: 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 The remote git repos are configured with denyNonFastForwards and denyDeletes, so regardless of what happens to the server, I have a full history of what happened to the databases, and can reliably go back in time. 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.
- Ayesh 6y agoThis is what I do too. 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.
- mgkimsal 6y agoAny issues with the privacy aspect of that data that's stored in multiple git repos? PII and such?
- john_moscow 6y agoThese are private repos on private machines communicating over SSL on non-standard ports with properly configured firewall. The risk is always there, but it's low.
- bufferoverflow 6y agoYou should really compress them instead of dumping them raw into Git. LZ4 or ZStandard are good.
- 6y ago
- michelpp 6y ago> Computers are just too complex and there are days when the complexity gremlins win. 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.
- sushshshsh 6y agoAs a mid level developer contributing to various large corporate stacks, I would say the systems are too complex and it's too easy to break things in non obvious ways. 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.
- Nextgrid 6y agoThis is why I am against the current trend of over-complicating stacks for political or marketing reasons. Every startup nowadays wants microservices and/or serverless and a mashup of dozens of different SaaS (some that can't easily be simulated locally) from day 1 while a "boring" monolithic app will get them running just fine.
- deleted 6y ago[deleted]
- sushshshsh 6y agoYup, 100% agree. It may be that you will eventually need an auto-scalable message queue and api gateway, but for most people a web server and csv will serve the first thousand customers
- dnautics 6y agoPlease don't use csv. At the very least use SQLite. But hosted sqls are probably the smart thing to do.
- dvdbloc 6y agoAt my job, the company computers are configured to send “localhost” to the local company DNS servers, of which they happily reply with the IP address of the last machine that’s gotten a DHCP lease with the hostname “localhost”. Which happens often. Needless to say, our IT dept isn’t the best.
- jzer0cool 6y agoCould someone explain more what caused the prod wipe? The snip here indicates it is using a 'dev' credential (it is a different pass than prod right?) - how does a db connection occur at all?
- fma 6y agoGood catch. Wouldn't surprise me if there's 1 username/password for all their DB environments.
- jzer0cool 6y agoWhen looking at the snip again, there is a note that says it uses different a username / password for prod & dev. I can't see how the OP indicates making a connection to db without the correct credentials to begin with.
- heelix 6y agoAh man, these things happen. One of our developers - very new to elastic - was asked to modify some indexes. Folks were a bit too busy to help or heading out on holiday. One stack overflow answer later... delete and recreate it... and she was off to the races. When the test was tried, it looked like things still worked. A quick script did the same to stage and prod, in both data centers. Turns out that is not a great way to go about it. It deleted the documents. We got lucky, as we still had not killed off the system we were migrating off of and it only took three days of turn and burn to get the data back on the system. 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. :)
- deleted 6y ago[deleted]
- yla92 6y agoI am sorry this happens. > 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
- sergiotapia 6y agoI did the same thing once by accident and thankfully only lost 1 hour of data. The single lowest point of my career and thinking about the details of that day makes my stomach sink even now as I type this. 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.
- colechristensen 6y agoThis is bad operations. 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.
- bArray 6y agoHmm, there seems to be some holes in their system. A database might go down for any reason. 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.
- emodendroket 6y agoThis post is embarrassing. "yeah we were drinking and accidentally nuked the prod DB. Not sure why. Shit happens!" Who would read this and think they should trust this company? Any number of protections could have been taken to prevent this and production access in any state other than fully alert and attentive shouldn't happen unless it is absolutely necessary for emergency reasons
- bstar77 6y agoI think it's kind of funny they chose to post this story rather than do a typical post mortem.
- tcbasche 6y agoYeah why should I treat anything this company does with any level of seriousness? Why should anyone? 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.
- Aeolun 6y ago> 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.
- tcbasche 6y agoNot really, they blamed 'complexity' and 'computer gremlins' rather than admitting that, perhaps, they made a shitty mistake. > It’s a function that deletes the local database and creates all the required tables from scratch Why would anyone have this? It's just dumb and embarrassing
- 6y ago
- prh8 6y agoVery unsettled by the flippancy of the entire article
- 3np 6y agoAnd all the emojis on top of that
- jugg1es 6y agoI totally sympathize with you and yours, I've made sphincter-clenching mistakes a handful of times during my 20 years of experience. 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.
- lysp 6y agoI had a client who had prod database access due to it being hosted internally. They called up saying "their system is no longer working". 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".
- luord 6y agoThis is what nightmares are made of. > 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.
- ummonk 6y agoWhat a frustrating post :( Provides just enough technical detail to pique our curiosity then leaves us hanging.
- oppositelock 6y agoYou have to put a lot of thought into protecting and backing up production databases, and backups are not good enough without regular testing of recovery. 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.
- azeirah 6y agoQuick tip for anyone learning from this thread. 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"
- x87678r 6y agoInteresting, I immediately thought they would have a transaction log, I didn't think it would have the delete as well. 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.
- mr_toad 6y ago> I have all these wonderful DB backups lying around, so I bring up a new DB from the backups 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.
- tzs 6y agoHere is how we had our database deletion error, about 15 years ago. Our DBs had been on leased servers at a hosting company in New York City. They were getting out of the datacenter business so we had to move. We were moving to colocated servers at a Seattle datacenter. 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.
- jw360 6y agoIf your script connects to a production database by accident, you have a whole different issue.
- boltefnovor 6y agoCompanies tend to get really good at backups after events like this.
- ineedasername 6y agoRelated: Little Bobby Tables (https://xkcd.com/327/ https://xkcd.com/327/)
- known 6y agoSack whoever is responsible;
- boltefnovor 6y agoSo sack management?
- Biganon 6y agoYeah, this person working alone on this hobby project should sack themself. Thank you for your macho no-bs insight.
- rawgabbit 6y agoThe article should be renamed to “We coded a function that drops database tables and were surprised when it broke production.”
- aszen 6y agoI had a narrow escape once doing something fancy with migrations. 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.
- heavenlyblue 6y agoAnd you didn’t even bother to do a query of the actual maximum length value of the columns you were mutating? Or at least query and see the text in there? 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.
- detaro 6y agoThe comment clearly states that they did.
- 6y ago
- EugeneOZ 6y agoI love the honesty, self-irony and transparency of the article. It's sad and annoying to see so many young naive devs writing ”oh they are so bad, it will never happen to me”. Yes, people are not perfect and computer systems are complex. Admit it and don't be so overconfident. ”Errare humanum est”, prepare your backups.
- beefbroccoli 6y agoAnyone else think this is just a clever ad for DigitalOcean?
- info781 6y agoNot really they lost a day of data, toy database.
- tijuco2 6y agoThat's the reason I never authorize dev machines connect to production. And that's the reason developers hate my security team.
- unnouinceput 6y agoQuote: "Note that host is hardcoded to localhost. This means it should never connect to any machine other than the developer machine. Also: of course we use different passwords and users for development and production. We’re too tired to figure it out right now. 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.
- toredash 6y ago2 cents his hosts file points localhost to the prod db IP
- pvorb 6y agoYep. Nowadays a kubectl port-forward makes something like this all too easy. They accidentally had the kubecontext point at the production cluster instead of dev, set up the port-forward to the database, and whoops! At least that's how this could happen to me, even with my years of experience in doing unexpected things to production databases.
- junon 6y agoThat whole ecosystem is a devexp nightmare. I try to stay away from it entirely having worked with it extensively. Docker + Kubernetes are the biggest socially-acceptable hacks in the industry at the moment.
- pvorb 6y agoWhy do you think they are hacks? Could you please elaborate? In my opinion, they nicely abstract over server hardware and services running on them, so one is able to have simple infrastructure-as-code management of otherwise complicated setups.
- tus88 6y agoWell u have backups right...or did the gremlins eat them?
- axegon_ 6y agoI did something similar once - I had fiddled with my /etc/hosts and subsequently connected to the production database without realizing. I dropped a table but thankfully it wasn't much of a deal - the monitoring rang the bell and I recreated it a few seconds later. All that happened was that I had logged out several hundred users.
- deleted 6y ago[deleted]
- skrebbel 6y agoI'm appalled at the way some people here receive an honest postmortem of a human fuck-up. The top 3 comments, as I write this, can be summarized as "no, it's your fault and you're stupid for making the fault". 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.
- t0mas88 6y agoThe software sector needs a bit of aviation safety culture: 50 years ago the conclusion "pilot error" as the main cause was virtually banned from accident investigation. The new mindset is that any system or procedure where a single human error can cause an incident is a broken system. So the blame isn't on the human pressing the button, the problem is the button or procedure design being unsuitable. The result was a huge improvement in safety across the whole industry. 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.
- suzakus 6y agoIt's a piece of software for scoreboards. Not the Therac-25, nor an airplane.
- t0mas88 6y agoIt isn't about the importance of this one database, it's about the cultural issue in most of the sector that the parent comment was pointing out: we far too often blame the user/operator calling them stupid, while every human makes mistake, it's inevitable.
- throwdbaaway 6y agoThe script also unnecessary complicates things. If it just does the equivalent of rake db:drop, this incident wouldn't have happened, since postgres wouldn't allow a database with active connections to be dropped.
- temptemptemp111 6y agoStep 0) automated backups Step 1) full manual testing of said automated backup Step 2) weekly test of the supposedly automated process ... Now you can continue to your hair-brained engineering "processes"
- glintik 6y ago"at around 10:45pm CET, after a couple of glasses of red wine, we deleted the production database by accident". That's not an accident, guys.. Stop drink and deploy something on production, especially at late evening time.
- madsbuch 6y agoThings like these happen, and we should be compassionate towards them. 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.
- restlessbytes 6y agoWhile I'm very sympathetic to "we accidentally nuked our prod DB" because, let's admit it: we've all been there at some point, I'm also a bit baffled here because I don't think that the problem lies with too much wine, Postgres permissions or scripts on localhosts but the fact that recreating a database by FIRST dropping all tables and THEN recreating them is like deliberately inviting those gremlins in. 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
- deleted 6y ago[deleted]
- ing33k 6y agoI run a replicated ClickHouse server setup, Clickhouse uses zookeeper to enable replication. The zookeeper instance was not replicated.it was a single node. The server on which zookeeper was running ran out of hard disk and Clickhouse went into read only mode. Luckily,no data was lost while this happened because we use RabbitMq to store the messages before it gets written to the db. Thanks to RabbitMq's ACK mechanism.
- gcc_programmer 6y agoThe maturity of the article is laughable. I'm sure my age is the same as the people who wrote it, but this is unacceptable: dropping databases in prod is a serious issue, not a joke. I think the culture of the company is toxic and not professional at any level. #change-my-mind
- azeirah 6y agoIt's one individual's side-project, chill.
- gcc_programmer 6y agoIt's not a side project, it's a production database owned by a company... I hope I never have to use products you buils either...
- noja 6y agoA function called database_model_create() should not drop something. Here it would have failed to create the already existing tables and raised an error.
- zerr 6y agoIt reads like the db was deleted intentionally for the sake of blog post - marketing that is. :)
- dschuetz 6y agoSomeone might have copy&pasted it elsewhere and that propagated away. This is why writing code can also be dangerous in open dev. Whoever programmed anything also should be sensible enough to judge their own code whether it could be dangerous in the wild. Once out there (or worse: on stackoverflow) it could wreak havoc.
- vox17 6y agoThis line's the winner for me : "Thankfully nobody’s job is at risk due to this disaster. The founder is not going to fire the developer – because they are one and the same person."
- ClumsyPilot 6y agoTo be fair he could, and least in theory - he could get someone else to do development, for money or equity, for the project and do something else himself.
- benhurmarcel 6y agoIt's just one guy working in the evening on a side project. There's no revenue, and it runs on $171/month [1]. He's not going to hire anyone. [1] https://keepthescore.co/blog/posts/monetizing-keepthescore/ https://keepthescore.co/blog/posts/monetizing-keepthescore/
- nix23 6y agoTo all the people who say "that could never happen to me" work less then 5y in the industry. That can happen to anyone anytime. Remember: You just fix the errors YOU can think of.
- majkinetor 6y agoOnce it happened to me, and now all scripts in last 10 years have if (Env.Name -like '*prod*' ) { then throw } and similar to all destructive stuff.
- ricksharp 6y agoAre you sure it was the production database that was affected? 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.
- jbverschoor 6y ago> Why? This is something we’re still trying to figure 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
- sanmak 6y agoThat's awesome and crazy at the same time. It has thrill along with foolishness as well. It happens with one of my colleague friend's and he was fired at that time only. Best of luck!
- flurdy 6y agoIn one of my first jobs I deleted the last 30 days of our production data. 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. :)
- rafamvc 6y agoA very similar thing happened to living social during their brightest years, but the replication and the backups had failed too. The oldest valid backup was about a week old. It took the whole company offline for 2 days. It took a team of 10 people and some extra consultants to come up with a half backed version of the latest database based on elastic cache instances, redis caches and other "not meant to be a backup" services. It was insane walking in an office that had hundreds of employees and see they all gone while we rebuild this cobbled together db. 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-download-livingsocial-goes-down-for-nearly-48-hours-after-critical-database-error/2013/11/15/2012bc4e-4c9b-11e3-9890-a1e0997fb0c0_story.html https://www.washingtonpost.com/business/capitalbusiness/the-...
- info781 6y agoWhy did they not have archive log mode on in production? Losing a db is one thing, but should have only been one hour of data lost.
- AdrianB1 6y agoFocus, time, expertise and cost. For a side project with no revenue, cost is a very important factor. The others come with having side projects.
- pachico 6y agoI still remember how many years ago, when someone in my team told me one Friday afternoon "there's not something like 'undo' for 'drop table', right?". He spent the weekend recreating the data.
- pachico 6y agoYes, Manuel, I'm talking about you! :)
- ystad 6y ago> Thankfully our database is a managed database from DigitalOcean, which means that DigitalOcean automatically do backups once a day. Do cloud providers provide a smaller window for backup. Are there better ways to reduce the backup window here for DBs? Would love to understand any techniques folks use to minimize the backup window?
- minkeymaniac 6y agoThat is why we can only access the prod db from a jump box in our shop.... And even then it's just certain people with less privileges than a sysadmin account. No way you can do this accidentally from your laptop then...
- jrochkind1 6y agoThe lack of the seriousness/professionalism of the postmortem seemed odd to me too. So, okay, what is this site? > 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/ 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.
- smarx007 6y agoA question to the DBA experts from a developer: is there a way in MySQL and Postgres to configure a log specifically for destructive SQL queries so that it's easier to investigate a situation like this? I.e. to log most queries except for usual SELECT/INSERTs. 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?
- fallingfrog 6y agoI once replaced a bunch of customer photos with a picture of Spock as part of a test on my first week on the job.. The dB admin had just overwritten a sales force dev dB from production and a previous developer had hardcoded the IP address of production in the code of a script somewhere..
- gregors 6y agoGithub did something similar. https://github.blog/2010-11-15-today-s-outage/ https://github.blog/2010-11-15-today-s-outage/
- aerxes 6y agoThis postmortem is incomplete: it fails to address the main three roots of the problem: 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.
- cmeacham98 6y agoWhile several other users have posted takeaways for how to prevent this from happening, I'd be interested in if anybody has an idea of how this happened given the code that was posted? 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.
- 0kl 6y ago> Note that host is hardcoded to localhost. This means it should never connect to any machine other than the developer machine. 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
- deleted 6y ago[deleted]
- xupybd 6y agoI don't understand how this happened if localhost is hard coded and the password is different. I don't think they fully understand why this happened. At least enough to prevent it from happening again.