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.<p>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 sql), then going back to the start of the line to remove the safety when I'm done.
Waaay back in the .com boom days of the late 90s I was working at a place where the CTO did something like:<p>UPDATE users SET password = '23r23r23rdsf';<p>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.
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 <i>diverse</i> machines) easily, and the fact that you lose all the <i>advantages</i> 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 <i>need</i> 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.<p>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.<p>Hooking machines up to orchestration code is something I have to do. I operate at scales that Facebook would laugh at, but they're <i>still</i> 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.<p>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.
I've done this on a MySQL database in production back in 2012, on a customer table, for a website that has been mentioned on HN a total of 22 times (not huge but some here have an interest) – the head of technology quickly put up a maintenance page and used the point in time restore feature in RDS and there was no damage, I didn't get in trouble, we were back fully-functional within an hour.<p>Haven't used MySQL in a while, but when I was using I'd have this alias in my zshrc:<p><pre><code> alias mysql="mysql --i-am-a-dummy"
</code></pre>
It hasn't happened since.
When working in prod, first I write a select to grab all of the data I wish to modify, confirm that is correct, then add the set portion, then add the UPDATE last. Never had a issue and I work in prod all the time.
I made a feature recently that had an interface where one could change some configurations. And other stuff would link to a specific configuration. Mostly a configuration is only used one or two places, but nothing stops someone from reusing it for lots of items, so of course someone do.<p>When I thought I was done with the page for editing configurations, the UX person said it missed a popup confirming the config changes if it affected more than X items.<p>I'd prefer skipping it. It's more state to hold, data to be fetched up front etc. But it has probably saved us multiple times already from someone trying to change something used lots of places by accident (instead of making a new separate config for whatever they want to change).
Speaking of insane defaults: I've been working with azure for my latest project at work, using azure app services, which allow you to create "deployment slots" for different deployments(dev, staging, prod or just other customers).<p>When dealing with azure app services on the command line, you specify the slot with the `-s <slot>` flag.. but if you don't, it defaults to the production slot.<p>I'm really not sure what kind of moron though that was a good default, because the az CLI doesn't ask you for confirmation for anything. If you run `az webapp restart` you just restarted the production system.
In BigQuery, all UPDATE queries require a WHERE. "WHERE TRUE" is the recommended workaround if you really want to set a value everywhere. Makes a lot of sense!
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.
re: UPDATE without a WHERE<p>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.<p>But if your UPDATE statement is accross three lines, its a little bit too easy to accidentally select just the first two lines and not select the third line that has the WHERE clause. Then CTRL-E and you've footgunned yourself.<p>Its never actually happened to me, but I've often thought it probably has happened to some people.
This is all well and fine, but if you're going to go commando and update a production system by hand you should have a backup plan in mind before you start.<p>With SQL, that could be as simple as starting a transaction, doing your commando stuff, and committing it when you are satisfied.<p>But don't do that. Why are you doing anything like that in production. Why why why.
Ansible playbooks are often written to update all the servers in a group unless you explicitly to tell it to limit it only to a sub-set:<p><a href="https://docs.ansible.com/ansible/latest/user_guide/intro_patterns.html" rel="nofollow">https://docs.ansible.com/ansible/latest/user_guide/intro_pat...</a>
SQL operates on sets of records, so it's up to the user to specify and restrict the set.<p>One can form a set by specifying just a table, or tune more by JOINs and further with WHERE clause.<p>Well, of course a JOIN is just one way to say WHERE. Still, when properly joined, the resulting set may not need a WHERE in UPDATE.
Who are all you crack-junkies, running SQL queries against live...? I've seen enough literature saying we don't do this anymore... ooooooooohhhhhh. right... yes, the people writing about that aren't the ones actually shipping and making the clock tick ;-)
If this is a PROD database, you are running a script, not typing it into a console, and that script has been tested in PRE, and reviewed by others.<p>Edit: Gah, what have I become! Listen to me! I am so old. Fuck it, YOLO right? Oh, wait, I've got kids in college.
If you put a small program/interface around SQL entry, you can trivially verify that a user entered a reasonable query before allowing to execute.<p>I think validation that a query is going to do what you want is up to the user of the database, not the database vendor.
If you use a tool that allows you "execute editor" and "execute selection", you might just have selected the update part without the where, or accidentally press return so it executes as two separate statements.<p>Happened to me 18 years ago. :-)
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.