Hacker News Reader

Comment by lmm | Hacker News Reader

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.

View on HN

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.

Reply on news.ycombinator.com