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

51–60 of 112 posts

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

#51

Post by codeulike »

lbriner wrote: post #29
codeulike wrote: post #14

re: UPDATE without a WHERE In SSMS (the main query console tool thing for Sql Server) you can highlight a bit of code with the cursor (as if you want to copy/paste it) and press CTRL-E to execute it, its really handy when you've got a big sketchpad-like series of SELECT statements and you're doing exploratory tinkering. But if your UPDATE statement is accross three lines, its a little bit too easy to accidentally sel…

I have, although nothing deadly serious. I guess that SQL statements in a text file are not really the correct management tool for a production database. If we are needing to do these sorts of things, we should have an admin app or tool that can ensure we do things correctly. I haven't built one so I'm not complaining!

There's always ad-hoc stuff that needs doing though

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

#52

Post by yrgulation »

in my early days of SQL i used to type in limit 1; and then go back to the beginning of the line and write the delete or update statement. Overkill but kept me safe from unwanted updates :) later on i started doing a select count followed by a limit to a number of records i knew should be updated. cant be too careful.

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

#53

Post by marcosdumay »

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!

Oh, locking is what Oracle does. That and hanging because the work of keeping the transaction increases faster than linearly with the amount of data.

You shouldn't get access to prod if you didn't have experience with Oracle. The database is just too fragile to survive inexperienced people changing it.

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

#54

Post by Someone »

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…

Tools could be better at supporting rolling updates.

With 20.000 servers, for example, an automated process could roll out updates to 1,000 every hour during 20 hours, check that response times, system load, etc. are within limits for the updated set during each hour, and send out alerts and pause updating when they do not.

So, the user still would press one button to do the update, but the change would slowly take effect, allowing both the system and humans to take action if needed.

Main problem there is to keep things flexible enough to allow somewhat out of the box updates. And of course, that requires that you can run with half your servers on a different version of your software.

you probably also will have to forget doing the entire update in a single transaction.

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

#55

Post by mprovost »

benjaminwai wrote: post #49
mprovost wrote: post #21

Earlier quoted context omitted.

One quick trick I use a lot in the shell is to run a known safe command with the argument first, and then run the dangerous command with the the !$ variable (the last argument of the previous command) so there's no possibility of a mistake in copying and pasting. Something like: `ls /tmp/junk; rm !$`.

I think !$ takes the last argument on the previous command in history, not previous one on the same line.. at least in bash. For example, if you do: $ ls / $ ls /tmp/junk; rm !$ It's taking "/" rather than "/tmp/junk".. EDIT: formatting

Yes that's right it's the previous command from history. That way you run ls and see if it's the expected output, then rm to delete.

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

#56

Post by to11mtm »

magicalhippo wrote: post #31
dcminter wrote: post #24

Earlier quoted context omitted.

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.

What I usually do instead?

Start with ROLLBACK at the bottom (With a BEGIN TRANSACTION up top if the DB/env warrants it)

Only when I know I'm happy, change the ROLLBACK to a COMMIT.

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

#57

Post by toast0 »

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…

Cancelling queries with MySQL is hard, in my pretty dated experience.

There's no signal you can do from the client directly. You can do a kill thread ID from another client, but that's only checked at some points, and I wouldn't expect it to stop an update in progress.

Kill -9 the unix process should work, but pulling the cord might mean less chance of changes persisting to disk.

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

#58

Post by toast0 »

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…

There's a middle ground between manually changing things and SkyNet pushing a broken config to the config pusher or breaking BGP on all colos simultaneously.

If the number of pushes is small and the time to make the change is small, automating the change, but running it one at a time in a loop works ok. When things start breaking, you can stop the loop before too many servers fall over. If you have a lot of servers, you can split your hosts and run up to about 10 terminals doing loops before it gets really hard to supervise. Often, you can easily parallelize the prep part of the update, and leave only a quick change to be serialized.

I'll have to see if I can find it, but yinst-pw was opensourced somewhere and is really useful for sudo password prompts if you're doing it half-way like this. Edit: ahah, remembered it got renamed to autopw https://github.com/jschauma/sshscan/blob/master/src/autopw

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

#59

Post by jerf »

Someone wrote: post #54
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…

Tools could be better at supporting rolling updates. With 20.000 servers, for example, an automated process could roll out updates to 1,000 every hour during 20 hours, check that response times, system load, etc. are within limits for the updated set during each hour, and send out alerts and pause updating when they do not. So, the user still would press one button to do the update, but the change would slowly take e…

This is an example of what I mean by "AI Hard". Yes, obviously, rolling updates is an improvement over non-rolling updates at scale.

However, you still have things like "this update severs the machine from the management system due to unexpected XYZ", "this update is fine until it's rolled out to 80% of the world, at which point interactions with the other deployed systems hammer the system so hard the management interface can't get in properly", "this update looked fine because it was using almost entirely cached data but once the caches all expired it turned out to be a disaster, now restoring is a nightmare because we had to roll back the version, empty the cache, and regenerate everything", and all the other edge cases that no matter what you do, will cause cascading failures at a huge scale.

No matter what rule set you write, something's going to get past it.

Or, to put it another way, if you aren't yet on the Pareto frontier between power and safety, sure, by all means go get your free safety and power. But you will hit a limit on the two before you have all the power and all the safety, and the limit you will hit is going to be uncomfortable in at least one direction.

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

#60

Post by sumtechguy »

SketchySeaBeast wrote: post #11
jerf wrote: post #6

Earlier quoted context omitted.

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.

Yup. Even if it's just to add a WHERE 1 = 1, at least then you've deliberately loaded and then set off your footgun.

In fact I would go one step further and say why does 'where' default to ALL? You did not say what you wanted. So you should get nothing. This should be true for select and delete as well as well as update.

If I just do 'SELECT *' and execute it, it does not rotate through all the tables. I did not specify it. Same with 'SELECT from xyz' if I can not have it empty and return all. Yet where is special somehow.

Post reply on HN