Comment by srean | Hacker News Reader

Comment by srean | Hacker News Reader

I learned a rather unusual trick to keep myself safe from unintended glob matches. It is not "fool"-proof, but it will probably dilute an unmitigated disaster into an incomplete disaster: Keep a file named -i in the sensitive directories. When glob picks it up, which should be fairly early, it will be treated like a command line argument. Has saved me on occasions.

I also had a friend in school who used to, for whatever reasons, name his files using characters drawn from a pool of only two characters. One being "." the other "*". Please don't ask me why. He would then try to delete some particular file. You can well imagine what would happen next. This happened multiple times, till I went ahead and populated his directories with "-i" files. That worked great.

I usually keep rm aliased to 'rm -i', but once I did get burned. It was not because of hitting return early but because of having a space between a directory and the trailing "/".....while running rm as root. It was taking a bit longer than I had imagined, so I looked again at the prompt to see what had I typed..$#@!&~ :)

Quoting the directories would also stop this, eg.

  rm -rf /usr /lib/nvidia-current/xorg/xorg # KABOOM!
  rm -rf "/usr /lib/nvidia-current/xorg/xorg" # Harmless.

I hate that feeling you get in the pit of your stomach after you realize what's happening.

Same thing when you forget the WHERE on a DELETE FROM.

Use the Dummy switch:

http://sql-info.de/mysql/notes/I-am-a-dummy.html

Sweet! Just "alias mysql='mysql --i-am-a-dummy'" in /etc/profile and never make mistakes again!

Over the years I've been steadily training myself to type "WHERE" earlier in the process, until I have finally settled on the obvious-in-hindsight solution: Always simply start with the WHERE clause.

(Of course every effort not to be on the SQL shell of a production server in the first place should be taken, but sometimes you need a sledgehammer and nothing else will work.)

I do that now, too. I have a trigger-happy semicolon finger.

My method is to write each DELETE as a SELECT first. This has the benefit of actually verifying the DELETE you were about to write.

Doesn't work when you're writing a stored proc.

Same - I now write DELETE FROM WHERE and then fill in the blanks.

The habit I learned was: before running any DELETE or UPDATE statement, run a SELECT with the same WHERE. (e.g. if I meant to say DELETE FROM puppies WHERE cute = 0, first run SELECT FROM puppies WHERE cute = 0.)

I find I remember to do that because of the direct benefit (getting a sneak preview of what I'm going to delete), but it also means I end up thinking twice about the WHERE statement, so I'm much less likely to miss it out or get it dramatically wrong.

I'm a couple of levels more paranoid than that. First, I'll write the DELETE as a regular SELECT (to preview number of rows), then turn it into a SELECT INTO to save the soon-to-be-deleted rows into a table with a backup_date_ prefix (So old backups can be deleted occasionally). Next, before changing anything, I wrap the statement in a BEGIN TRAN, and ROLLBACK TRAN. After all that, I will finally modify the SELECT INTO into a DELETE statement, run it once while wrapped in the transaction (to verify that the number of rows modified hasn't changed), and then finally run without the transaction to delete the rows. Overkill?

Possibly overkill. But at least there won't be any tears.

What? No volume snapshot first?

I do that for 'rm'. I do an 'ls' first, then reedit the command and put an 'rm'.

Ooh! I'm going to start doing that.

I do the same thing. Not sure that it has ever saved me from a disaster, but I do like the sneak peak and am DELETE FROM disaster free. knocking on my desk

I've always written my sensitive delete queries like this:

		select *
		-- delete x
		from Table x
		where x.whatever = 1

That way by default it's just a select, and then AFTER you verify the result set you can highlight from the delete on and then run the query (as long as you're in a shell that will run only the highlighted part. I was working in SMS.) This was a common idiom where I worked.

Good tip.

Unfortunately when I was working as a PHP programmer, I once made a typo in a variable name, a variable that had my WHERE clause in DELETE...

Back then I fixed it with a box of chocolates and flowers.

I always (with sql server at least) add a begin tran/commit/rollback before any prod statements, because of getting burned in the past.

Even if you add the WHERE, but put it on a second line and only run the first, the transaction will help...

Of course, if it's going to lock the data, do all of the statements together: BEGIN TRAN UPDATE ... WHERE ... SELECT ... WHERE .... -- show that the update worked ROLLBACK

Then run the actual statement

I made that mistake once. My DBA never let me live it down.

Thank goodness for daily backups.

I wish there was a way to put a limit on table updates in mysql's config file.

"Only let me update 5 rows at a time." or somesuch.

I now usually type the command out-of-order.

So I'll type:

where user_id = $f00 limit 10;

then prepend the "update foo set bar = " to it...

  BEGIN TRAN
  DELETE FROM ...
  SELECT FROM ...
  ROLLBACK TRAN -- things look bad, itchy finger, etc
  COMMIT TRAN -- things look good, skip previous line

That, and I have 15-minute backups for all databases I care about.

DELETE FROM? Try TRUNCATE TABLE CUSTOMER ... that one skips the transaction log too, no ROLLBACK possible.

You can't accidentally write a TRUNCATE statement though.

You can have dev-environment scripts that accidentally point to a production instance.

ALL of my PROD environments are unreachable from DEV boxes, for a good reason.

I'll bet I could.

SQL is desperately in need of some updates.

For starters, DELETE FROM should never be allowed without a WHERE clause. DBs should simply define that as malformed sql.

or at least interactive sql shells could ask you if you are really sure

Exactly. There is no need given TRUNCATE exists.

well, the only difference is that truncate also resets the auto-increment to zero. But you could allow where 1=1 to make it explicit if people really wanted an unbounded DELETE FROM.

No entirely. DELETE will work with cascading foreign keys, while TRUNCATE will not, at least on SQL Server. Also, DELETE is logged and (I believe) TRUNCATE is not. Having said that, I agree that a WHERE clause should be required - you can always say "WHERE 1=1" or similar if you really mean to delete all rows.

For me it's not the stomach. First, breath stops, then for a few seconds numbness in chest and jaw, then face turns pale and soon red. A small nausea follows and then regret sets in. The rest of the day is ruined.

How eloquent.

The simplest hack I've ever used for this kind of thing is to keep a notepad on my desk and whack it instead of the enter key whenever I'm doing critical work.

In the time it takes to thump the notepad, it gives my brain a vital few seconds to triple check what I just typed before I blast monkies into space. Saved my behind more than a few times.

If you are on a Mac, you must know that you should never put the `-i` option at the end:

  $ ls | wc -l
         5
  $ rm * -i
  rm: -i: No such file or directory
  $ ls | wc -l
         0

Yeah, it is a GNU extension to rm/ls/and others to have the options at the end of the list. Never did like that, but it does mean that people who log into my BSD boxes can never force delete or recursive delete ;-)

Just make sure no one is sadistic enough to follow you around running

    touch -- --

I've run a TRUNCATE in production by accident thinking I was logged into the dev system. I truncated the 4 core user / transaction tables. I've never felt the blood drain from my body so quickly.

Good old binary logs and an office floor to sleep on. I make copies of the data whenever deleting or truncating now. I think we all have to do something ridiculously stupid to start taking precautions.

Reply on news.ycombinator.com