Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

An UPDATE without a WHERE, or something close to it

rachelbythebay.com

Post reply on HN
Print view View original

31–40 of 112 posts

Re: An UPDATE without a WHERE, or something close to it

#31

Post by magicalhippo »

dcminter wrote: post #24
magicalhippo wrote: post #20

Earlier quoted context omitted.

I consider any SQL client that doesn't have implicit transactions as broken. Just too easy to make mistakes. However even with that, I agree with your second point. I always type -- commit in my SQL client and then highlight "commit" and execute only the highlighted text. That way I don't commit anything if I accidentally trigger the "execute all" action.

Yes, but mind you I once locked up the database for my colleagues by not realising the consequences of implicit transactions in a DB client. It was a long time ago, but I think it was with SQLPlus on Oracle - and presumably staging or something non-prod since it's not a particularly vivid memory!

Fair point, you don't want those transactions hanging around. Still, I prefer it to the alternative.

Re: An UPDATE without a WHERE, or something close to it

#34

Post by pletnes »

bombcar wrote: post #19
jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

I've taken to using "echo" and then when I run the command run it with a space at the front - you may have to run setopt HIST_IGNORE_SPACE or similar to get this in zsh, for example. That way ctrl+r won't bring back a destructive command.

Echoing the command also lets you see that shell escaping, parameter expansion etc. works as you expected. I do the same when writing a one-off loop, too.

Re: An UPDATE without a WHERE, or something close to it

#35

Post by silvestrov »

jerf wrote: post #6
blakesterz wrote: post #4

Waaay back in the .com boom days of the late 90s I was working at a place where the CTO did something like: UPDATE users SET password = '23r23r23rdsf'; Somehow he missed the "WHERE email = 'someone';" I forget exactly how many users had their password changed that day. For some reason (maybe MySQL didn't let you cancel an UPDATE like that way back in the old days?) to stop that query he ran into the other room and un…

This isn't quite up there with the "billion dollar" NULL mistake, but in hindsight it would have saved a lot of people a lot of trouble if the SQL grammar for UPDATE and DELETE required a WHERE clause.

I would really love if they accepted this as a non-backwards compatible change.

Or at least (for now) a configurable option in the database config, so each site can switch it on as they like.

Adding "UPDATE xxx WHERE yyy SET z=42" to the grammar would be a nice addition too.

Re: An UPDATE without a WHERE, or something close to it

#36

Post by rhinoceraptor »

jerf wrote: post #3

There is certainly a tension in modern devops tools, between the desire to do things easily across large sets of machines (and especially across large sets of diverse machines) easily, and the fact that you lose all the advantages of inertia and difficulty in making stupid changes the better you get at that. As you scale up this tension gets worse and worse. You don't really want to create a tool that allows you to t…

"To make error is human. To propagate error to all server in automatic way is #devops." - DevOps Borat

https://twitter.com/devops_borat/status/41587168870797312

Re: An UPDATE without a WHERE, or something close to it

#37

Post by breakfastduck »

RenThraysk wrote: post #17

With databases, start a transaction first, then update, and look at the number of rows affected. If it's an oh shit moment, rollback, if not commit.

Honestly the amount of massive disasters I've personally seen avoided (when someone thought their script was watertight) by simply doing this is astronomical.

Re: An UPDATE without a WHERE, or something close to it

#38

Post by cerved »

jerf wrote: post #6
blakesterz wrote: post #4

Waaay back in the .com boom days of the late 90s I was working at a place where the CTO did something like: UPDATE users SET password = '23r23r23rdsf'; Somehow he missed the "WHERE email = 'someone';" I forget exactly how many users had their password changed that day. For some reason (maybe MySQL didn't let you cancel an UPDATE like that way back in the old days?) to stop that query he ran into the other room and un…

This isn't quite up there with the "billion dollar" NULL mistake, but in hindsight it would have saved a lot of people a lot of trouble if the SQL grammar for UPDATE and DELETE required a WHERE clause.

datagrip won't run such a query by default

Re: An UPDATE without a WHERE, or something close to it

#39

Post by mprovost »

jerf wrote: post #3

There is certainly a tension in modern devops tools, between the desire to do things easily across large sets of machines (and especially across large sets of diverse machines) easily, and the fact that you lose all the advantages of inertia and difficulty in making stupid changes the better you get at that. As you scale up this tension gets worse and worse. You don't really want to create a tool that allows you to t…

The issue is that these declarative languages (SQL, Terraform, Cisco IOS...) abstract away the loop. Sure it's tedious to do the same operation to 1000 servers, but computers are good at loops. And computers can help write loops. At previous jobs we had tools that would generate lists of servers based on different parameters and then you could pipe that list to a command to do some operation across the whole list. But it gave you the chance to generate the list first and make sure that it looked accurate. If you were expecting to update the 10 webservers but there are 1000 lines in the file, you know something went wrong. I suppose the other issue with these languages is that they often substitute a lack of a list for "everything" instead of an empty list that does nothing.

Re: An UPDATE without a WHERE, or something close to it

#40

Post by RenThraysk »

andreareina wrote: post #23
RenThraysk wrote: post #17

With databases, start a transaction first, then update, and look at the number of rows affected. If it's an oh shit moment, rollback, if not commit.

And be sure that you're not using something that implicitly commits the transaction, such as TRUNCATE in Redshift.

TRUNCATE in a few databases effectively drops the table, and recreates it.

If typing in an interactive session usually use a specific user account for it, with minimal set of privileges. And wouldn't have the DROP privilege or any other DDL statement privilege. DDL would be scripted out, tested and ran using another user account that had only privileges on specific databases it needed.

Post reply on HN