<story><title>Why HN was down</title><text>Hacker News was down all last night.  The problem was not due to
the new server.  In fact the cause was embarrassingly stupid.&lt;p&gt;On a comment thread, a new user had posted some replies as siblings
instead of children.  I posted a comment explaining how HN worked.
But then I decided to just fix it for him by doing some surgery in
the repl.  Unfortunately I used the wrong id for one of the comments
and created a loop in the comment tree; I caused an item to be its
own grandchild.  After which, when anyone tried to view the thread,
the server would try to generate an infinitely long page.  The
story in question was on the frontpage, so this happened a lot.&lt;p&gt;For some reason I didn&apos;t check the comments after the surgery to
see if they were in the right place. I must have been distracted
by something.  So I didn&apos;t notice anything was wrong till a bit
later when the server seemed to be swamped.&lt;p&gt;When I tailed the logs to see what was going on, the pattern looked
a lot like what happens when HN runs short of memory and starts
GCing too much.  Whether it was that or something else, such problems
can usually be fixed by restarting HN.  So that&apos;s what I did.  But
first, since I had been writing code that day, I pushed the latest
version to the server.  As long as I was going to have to restart
HN, I might as well get a fresh version.&lt;p&gt;After I restarted HN, the problem was still there.  So I guessed
the problem must be due to something in the code I&apos;d written that
day, and tried reverting to the previous version, and restarting the
server again.  But the problem was still there.  Then we (because
by this point I&apos;d managed to get hold of Nick Sivo, YC&apos;s hacker in
residence) tried reverting to the version of HN that was on the old
server, and that didn&apos;t work either.  We knew that code had worked
fine, so we figured the problem must be with the new server.  So
we tried to switch back to the old server.  I don&apos;t know if Nick
succeeded, because in the middle of this I gave up and went to bed.&lt;p&gt;When I woke up this morning, Rtm had HN running on the new server.
The bad thread was still there, but it had been pushed off the
frontpage by newer stuff.  So HN as a whole wasn&apos;t dying, but there
were still signs something was amiss, e.g.  that /threads?id=pg
didn&apos;t work, because of the comment I made on the thread with the
loop in it.&lt;p&gt;Eventually Rtm noticed that the problem seemed to be related to a
certain item id.  When I looked at the item on disk I realized what
must have happened.&lt;p&gt;So I did some more surgery in the repl, this time more carefully,
and everything seems fine now.&lt;p&gt;Sorry about that.</text></story><parent_chain><item><author>mafro</author><text>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).&lt;p&gt;1. Always, always make a backup just before the hack.&lt;p&gt;2. Write a small set queries like 3pt14159&apos;s to check uniqueness and other pertinent properties.&lt;p&gt;3. Write a SELECT query to show the data you are going to change.&lt;p&gt;4. Borrow the WHERE clause from 3, and write your UPDATE statement.&lt;p&gt;5. Run 4, and then run 3 again to see that you successfully fixed it.&lt;p&gt;6. When it goes wrong, restore the backup from 1 :0</text></item><item><author>3pt14159</author><text>I&apos;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&lt;p&gt;&lt;pre&gt;&lt;code&gt;    select count(distinct column_name) having count(distinct column_name) &amp;#62; 1;
&lt;/code&gt;&lt;/pre&gt;
To make sure that there isn&apos;t an underlying uniqueness assumption.&lt;p&gt;Sure I &lt;i&gt;could&lt;/i&gt; do it in 10 seconds and save myself 290 seconds (a 97% savings!) but then one day I&apos;d have to scramble like crazy in the middle of the night trying to figure out what I screwed up for hours on end.&lt;p&gt;I&apos;m not saying don&apos;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.</text></item><item><author>lmm</author><text>Typing sudo won&apos;t save you, but using a higher-level interface will. Everyone I&apos;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.</text></item><item><author>lifeisstillgood</author><text>There are a number of comments that add up to &quot;what steps will you take to ensure this does not happen again&quot; - akin to a incident review.  As speculation that&apos;s fine, as advice, I don&apos;t think it &lt;i&gt;should&lt;/i&gt; be listened to.&lt;p&gt;I am reminded of an long-in-the-tooth sysadmin of my acquaintance who logged in everywhere as root.  His theory - &quot;they are my boxes. I screw it up, I fix it.&quot;  
I eventually realised that typing sudo every time he touched a box was no defence against doing the wrong thing.&lt;p&gt;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.&lt;p&gt;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.&lt;p&gt;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.&lt;p&gt;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&apos;s sandbox.  If the sand leaks it&apos;s his sand, and the only company this is mission critical to is YC.</text></item></parent_chain><comment><author>grey-area</author><text>Don&apos;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 &lt;i&gt;then&lt;/i&gt; 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.&lt;p&gt;Even if you can&apos;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.</text></comment>
<story><title>Why HN was down</title><text>Hacker News was down all last night.  The problem was not due to
the new server.  In fact the cause was embarrassingly stupid.&lt;p&gt;On a comment thread, a new user had posted some replies as siblings
instead of children.  I posted a comment explaining how HN worked.
But then I decided to just fix it for him by doing some surgery in
the repl.  Unfortunately I used the wrong id for one of the comments
and created a loop in the comment tree; I caused an item to be its
own grandchild.  After which, when anyone tried to view the thread,
the server would try to generate an infinitely long page.  The
story in question was on the frontpage, so this happened a lot.&lt;p&gt;For some reason I didn&apos;t check the comments after the surgery to
see if they were in the right place. I must have been distracted
by something.  So I didn&apos;t notice anything was wrong till a bit
later when the server seemed to be swamped.&lt;p&gt;When I tailed the logs to see what was going on, the pattern looked
a lot like what happens when HN runs short of memory and starts
GCing too much.  Whether it was that or something else, such problems
can usually be fixed by restarting HN.  So that&apos;s what I did.  But
first, since I had been writing code that day, I pushed the latest
version to the server.  As long as I was going to have to restart
HN, I might as well get a fresh version.&lt;p&gt;After I restarted HN, the problem was still there.  So I guessed
the problem must be due to something in the code I&apos;d written that
day, and tried reverting to the previous version, and restarting the
server again.  But the problem was still there.  Then we (because
by this point I&apos;d managed to get hold of Nick Sivo, YC&apos;s hacker in
residence) tried reverting to the version of HN that was on the old
server, and that didn&apos;t work either.  We knew that code had worked
fine, so we figured the problem must be with the new server.  So
we tried to switch back to the old server.  I don&apos;t know if Nick
succeeded, because in the middle of this I gave up and went to bed.&lt;p&gt;When I woke up this morning, Rtm had HN running on the new server.
The bad thread was still there, but it had been pushed off the
frontpage by newer stuff.  So HN as a whole wasn&apos;t dying, but there
were still signs something was amiss, e.g.  that /threads?id=pg
didn&apos;t work, because of the comment I made on the thread with the
loop in it.&lt;p&gt;Eventually Rtm noticed that the problem seemed to be related to a
certain item id.  When I looked at the item on disk I realized what
must have happened.&lt;p&gt;So I did some more surgery in the repl, this time more carefully,
and everything seems fine now.&lt;p&gt;Sorry about that.</text></story><parent_chain><item><author>mafro</author><text>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).&lt;p&gt;1. Always, always make a backup just before the hack.&lt;p&gt;2. Write a small set queries like 3pt14159&apos;s to check uniqueness and other pertinent properties.&lt;p&gt;3. Write a SELECT query to show the data you are going to change.&lt;p&gt;4. Borrow the WHERE clause from 3, and write your UPDATE statement.&lt;p&gt;5. Run 4, and then run 3 again to see that you successfully fixed it.&lt;p&gt;6. When it goes wrong, restore the backup from 1 :0</text></item><item><author>3pt14159</author><text>I&apos;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&lt;p&gt;&lt;pre&gt;&lt;code&gt;    select count(distinct column_name) having count(distinct column_name) &amp;#62; 1;
&lt;/code&gt;&lt;/pre&gt;
To make sure that there isn&apos;t an underlying uniqueness assumption.&lt;p&gt;Sure I &lt;i&gt;could&lt;/i&gt; do it in 10 seconds and save myself 290 seconds (a 97% savings!) but then one day I&apos;d have to scramble like crazy in the middle of the night trying to figure out what I screwed up for hours on end.&lt;p&gt;I&apos;m not saying don&apos;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.</text></item><item><author>lmm</author><text>Typing sudo won&apos;t save you, but using a higher-level interface will. Everyone I&apos;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.</text></item><item><author>lifeisstillgood</author><text>There are a number of comments that add up to &quot;what steps will you take to ensure this does not happen again&quot; - akin to a incident review.  As speculation that&apos;s fine, as advice, I don&apos;t think it &lt;i&gt;should&lt;/i&gt; be listened to.&lt;p&gt;I am reminded of an long-in-the-tooth sysadmin of my acquaintance who logged in everywhere as root.  His theory - &quot;they are my boxes. I screw it up, I fix it.&quot;  
I eventually realised that typing sudo every time he touched a box was no defence against doing the wrong thing.&lt;p&gt;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.&lt;p&gt;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.&lt;p&gt;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.&lt;p&gt;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&apos;s sandbox.  If the sand leaks it&apos;s his sand, and the only company this is mission critical to is YC.</text></item></parent_chain><comment><author>mkubler</author><text>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.&lt;p&gt;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.&lt;p&gt;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&apos;t backed up at all.&lt;p&gt;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.&lt;p&gt;In another epic fail on the same server I later moved the root folders by running the following as root (I&apos;d probably have been stupid and run the same command if not as root but I&apos;d have put sudo in front of it).
&amp;#62; cd /home/&amp;#60;username&amp;#62;/public_html/public_html
&amp;#62; mv /* ../&lt;p&gt;I was meant to mv ./* (files from the current directory into one below cause they&apos;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&apos;s another story.</text></comment>