Comment by lifeisstillgood | Hacker News Reader

Comment by lifeisstillgood | Hacker News Reader

lifeisstillgood

There are a number of comments that add up to "what steps will you take to ensure this does not happen again" - akin to a incident review. As speculation that's fine, as advice, I don't think it should be listened to.

I am reminded of an long-in-the-tooth sysadmin of my acquaintance who logged in everywhere as root. His theory - "they are my boxes. I screw it up, I fix it." I eventually realised that typing sudo every time he touched a box was no defence against doing the wrong thing.

An awful lot of sites at 1.2m views would have outsourced the running and development of the whole thing - there are entreprenuers who say its not even worth our time to code up the MVP. I find this approach sensible from a business point of view, but still it does not sit right with me.

I am supposed to have a nice website with lots of good content to attract inbound marketing - so I tried getting someone on textbroker to write an article for me. It read like a High School essay - no life, no anime. And so I will probably write my own CMS and my own content.

And pg sits there and writes his site in his own language, with his own moderation tools. Apart from the hilarious idea he could find a ten person ruby shop to outsource to, its nice to see someone taking the time to play again. Its why I like to see jgc on here too.

I am not entirely sure those thoughts are joined up (I am procrasting like crazy) but if they come to mean anything its we are playing in pg's sandbox. If the sand leaks it's his sand, and the only company this is mission critical to is YC.

Typing sudo won't save you, but using a higher-level interface will. Everyone I've ever known to change something in the database by hand, everyone at all, even on a hobby project that they know like the back of their hand, has screwed it up sooner or later. At some point the pain tells you you should stop doing that, and you create an admin tool that lets you do what you need to repeatably and safely.

I've never screwed it up on a live database, but I do take about 5 mins, first reviewing the keys, the type, whether or not something can be null, checking to see if critical columns have

    select count(distinct column_name) having count(distinct column_name) > 1;

To make sure that there isn't an underlying uniqueness assumption.

Sure I could do it in 10 seconds and save myself 290 seconds (a 97% savings!) but then one day I'd have to scramble like crazy in the middle of the night trying to figure out what I screwed up for hours on end.

I'm not saying don't build an admin tool, obviously those are needed for things like banning users, but just get in there and carefully fix the data if something is wrong.

This. Back in the day when I was in more of an analyst role, I ended up /having/ to hack on the live DB frequently (reasons for this were myriad).

1. Always, always make a backup just before the hack.

2. Write a small set queries like 3pt14159's to check uniqueness and other pertinent properties.

3. Write a SELECT query to show the data you are going to change.

4. Borrow the WHERE clause from 3, and write your UPDATE statement.

5. Run 4, and then run 3 again to see that you successfully fixed it.

6. When it goes wrong, restore the backup from 1 :0

This is pretty much exactly how I do it.

I still sometimes get that sinking feeling in the stomach that I have screwed something up, usually just after I hit the 'execute' button. And I really don't want to have to take the site down to run the restoration.

Is there any other way??? :)

I thought everyone did this - well... for small datasets, skip the back, use a transaction. Rollback if your step 5 failed and try again.

6. Should be ROLLBACK, a life saver. Works in postgres.

This reminds me of a feature that I wish that database systems supported: Make it impossible to execute DELETE or UPDATE statements without a WHERE clause.

I'm going to add step 5b - save what SQL you executed (against what server, and for what reason), ideally in source control, as an audit trail.

Otherwise, I end up having this conversation (which actually happened):

Him: <Big Client> is having troubles! Features X, Y, and Z aren't working! Me: Hmm, has anything changed? It was all OK on Friday. Him: No, nothing's changed. Me: Really? Him: Well I ran a bunch of scripts on Saturday while I was visiting them. Me: OK, so what exactly did you run? Him: Just a bunch of scripts.

As a tip - Also backup your staging database and have all backups using something like Rsnapshot or maybe even in a version control system, something which does point in time backups.

I learnt this after I inherited a project which had been written by some Romanians and it was pretty horrible. There was no MVC framework, it was a hacked together mess.

Somehow the live site started using the staging database instead of the production database, both were on the same server. Every time we (the devs) pushed to staging a script would grab the latest version of the live database and overwrite (drop tables) the staging database. The assumption being that the staging database is a bit like a demo server, changes made to it are temporary and just for testing, but that it should look as similar to the main website (but updated) as possible. The production database was backed up in about 5 different ways, but the staging database wasn't backed up at all.

After about a week of vanishing books, books which authors had uploaded to the self publishing portable with descriptions and other information, we realised what was wrong. Their files stayed but their accounts and book details were wiped.

In another epic fail on the same server I later moved the root folders by running the following as root (I'd probably have been stupid and run the same command if not as root but I'd have put sudo in front of it). > cd /home/<username>/public_html/public_html > mv /* ../

I was meant to mv ./* (files from the current directory into one below cause they'd been copied across into the wrong folder. Needless to say moving the root folders such as /etc and especially /lib and /bin is a BAD idea. Although is fixable, but that's another story.

Don't you have a dev db somewhere that you can replicate the live db to? Time spent setting that up will be more than repaid by the time and stress saved when you have to do a quick fix - you can simply run your changes, check it all works on your replicated site, and then make the changes on your live db (preferably with some sort of migration tool which applies the same sql and backs up first). If you have a regular backup process you could tie into that to populate the dev database.

Even if you can't replicate the entire live db, if you can automate backup, deployment of changes and test first elsewhere it makes the entire process far less fraught.

Maybe I'm old school, but shouldn't this be done in an dev or acceptance environment?

I hack on the "live" DB every day, and by live I mean i sync this DB to another environment, try it out, run it on prod.

One of the things I prefer to do is to only write UPDATE statements that update a single row. For example instead of:

UPDATE line_items SET quantity = 1 WHERE quantity < 1;

I'd script the following updates:

UPDATE line_items SET quantity = 1 WHERE quantity < 1 AND id = 123;

For each of the individual rows that needed to be changed. Then I have a check that I'm really updating just the rows I expect, this is especially important to me where the UPDATE involves joins, as I find this is the trickiest to get right.

> I eventually realised that typing sudo every time he touched a box was no defence against doing the wrong thing.

IIRC, sudo logs all commands to syslog. Which might come in handy. Yes, root commands will be logged by bash to .bash_history, but there are limits of # of commands lots, what happens if you are logged in multiple times into same account etc.

Anyway, that's why I like sudo.

Plus security. With root login disabled a remote attacker won't have a known username to attack.

Beyond the logging, which I love, I use it for the differentiation of states. I'm just a little more attentive when I type "sudo" before something.

At work, a relatively young engineer accidentally typed a command meant for a test database server into a production window. There was a big rush to restore from backups, and there was a small amount of data loss.

One thing that came out of the retrospective, requested by the engineer in question, was the production hat. Before you opened up a connection to a production machine, you had to put on the large pirate hat. You could only put it back when you had closed the connections. I didn't really need it, but it was a great way for people to learn the necessary caution.

It also ended up being a nice exclusive lock on futzing with production, and seeing it in use led to some good discussions that otherwise might not have happened. But the main thing was developing a strong differentiation of states in everybody's heads.

Another cheap solution is colorizing the prompt red for dangerous consoles and green for dev machines. This makes it very easy to notice when you selected the wrong terminal.

I use a different background color for the terminal in question; in this case, I use a dark red to signify a production system, and a dark blue to signify a development system. I find it quite useful. You can do this through Xterm profiles (Edit -> Profiles, Terminal -> Change Profile) in Linux and OS X, and I'm sure there's a way to do it in Windows / PuTTY.

I have tried the red / green console but never a pirate hat.

My son now definitely thinks work is like his school :-)

I find most of the time I'm using sudo I don't want to type it before every command and so I use sudo -i which pretty much negates the benefit of logging anything other than to tell that I was sudo at some point.

> It read like a High School essay - no life, no anime. ... I am not entirely sure those thoughts are joined up (I am procrasting like crazy)

Your procrasting like crazy has much anime.

s/anime/animus/ ? or is this a new usage of "anime"?

s/anime/anima - as in soul, vitality

(Not so much Jung's inner woman)

I think the sentence does read better if it is complaining there are not enough cyberpunk Japanese comics on my site though :-)

Here I was thinking you were referring to the Japanese meme "No ___, No Life!"

Reply on news.ycombinator.com