An UPDATE without a WHERE, or something close to it
rachelbythebay.com
An UPDATE without a WHERE, or something close to it
1–10 of 112 posts
Re: An UPDATE without a WHERE, or something close to it
#2[deleted]
Re: An UPDATE without a WHERE, or something close to it
#3There 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 trash "every router you have" in one fell swoop, or to trash "every server in every data center", and yet at the same time, you need the ability to manipulate "every server" at the same time with the same tool because who can afford to log in and manually change 20,000 machines? It's really difficult to have "the power to administer" 20,000 machines without having "the power to destroy" them all.
You can't even easily ask "are you sure?" because if you're asking that for every little thing it ceases to be a useful guard. You need tools that detect if you're doing something stupid and dangerous and only ask then, but in the limit, that's strong-AI hard for ops people. That is, there are some obvious ones you can try to catch... "did you really mean to unassign all IP addresses?", but in general there's always something that will go wrong more cleverly than your detection code.
Hooking machines up to orchestration code is something I have to do. I operate at scales that Facebook would laugh at, but they're still well beyond what is practical to manually manage. In my opinion that scale taps out somewhere in the large single digits per ops person, which is nothing nowadays. But it always makes me nervous to do so, too, because I can see I'm putting all my eggs in one basket in the process, and the traditional "watch that basket really hard!" answer for when you're stuck in that situation is visibly not adequate.
I don't have a solution to propose. The tension seems fundamental to me. All I can suggest is that everyone sitting in front of any devops tool always be keeping the possibilities in mind, despite your brain's desires to say "hey, the last 1000 deploys went fine, I can stop being so vigilant about this one", and that any guard rails that can be added should be, even though they can never be 100% effective.
Re: An UPDATE without a WHERE, or something close to it
#4Waaay 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 unplugged the MySQL server. The PROD server. It's funny to look back on that now, but at the time... oh boy, total panic.
Re: An UPDATE without a WHERE, or something close to it
#5There 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…
If the table or field name contains "PASS" or "PWD" all updates and deletes should require a WHERE clause, even if it's WHERE key=key or something
Re: An UPDATE without a WHERE, or something close to it
#6Waaay 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.
Re: An UPDATE without a WHERE, or something close to it
#7Waaay 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…
Always SELECT before making changes. Or don't if you like feeling the adrenaline rush when executing a query in prod.
Re: An UPDATE without a WHERE, or something close to it
#8There 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…
If the table or field name contains "PASS" or "PWD" all updates and deletes should require a WHERE clause, even if it's WHERE key=key or something
[deleted]
Re: An UPDATE without a WHERE, or something close to it
#9There 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…
I think another way to frame it is asking what the default should be.
Continuing the SQL analogy from the OP, you absolutely should be able to UPDATE an entire table, but probably the syntax is at least somewhat to blame, because very rarely you want to run an update on every row. A simple change could be that an UPDATE without a WHERE clause is a syntax error (you could still add "WHERE TRUE", if that's what you mean to do).
Another example is how the React API uses funny method names like "dangerouslySetInnerHTML" for things you aren't usually supposed to do.
I'm a big believer in making invalid states unrepresentable, and a straightforward extension of that modus operandi could be "make unlikely states hard to reach".
Re: An UPDATE without a WHERE, or something close to it
#10Waaay 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…
We had an instance where a dev updated our content management system such that all values became "193". That got a conference room named after the event. :)