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 sql), then going back to the start of the line to remove the safety when I'm done.
I learned this technique the first time I actually worked with a real DBA many years ago and have used it ever since: one thing I like about it is that is mitigates the ‘fear factor’ of production changes, as (topically) ‘fear is the mind killer’ and makes mistakes more likely. Then there’s mitigating ‘complacency’ - “I do this all the time” - which can also lead to mistakes.
And yes, before that I had run an UPDATE without a WHERE in production..
I don’t even do this, it’s too easy to sidestep. For example using any GUI client where the current selection or statement is executed by default. (Yes this is very useful, no I am not interested in being convinced not to use these tools thank you.)
Instead I do my dry run queries with a read only user and I select the affected data, often using CTEs (or temp tables where performance is an issue) to model any intermediate state. I don’t ever run any writes of this kind without review, backups, and automation.
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!
I literally did this just yesterdayy, in production, too. Thankfully it was a greenfield deployment and the customers weren't really using the system yet.
I went in there to adjust some data so that one of our dev users could re-publish some data which would then be synchronized to another database(update foo set published = false) and I couldn't understand that it wouldn't update in the UI.
I ended up telling him I'd have to get back to him, and the issue didn't dawn on me until the app logs started spitting out errors about not being able to retrieve a connection from the pool. YIL about pending transactions in DBeaver.
I've also often used transactions to play it safe.
But our team learned the hard way that using transactions on the replica pg database actually locks it from getting updates globally during the duration of the transaction. And the whole idea of connecting to the replica was to not wreak havoc, oh well..
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.
I once gave thousands of government employees in Norway a long lunch break from what should have been a non-locking migration. Or so I thought. Apparently it was fixed in 12b (or something, a few years ago), and my tests in staging went fine. But in production we didn't run 12b as in staging, but 12a...
Can't remember all the details. But something about adding a new nullable column with or without a default value of some kind. It had to lock the table, but multiple queries were already running. Those queries had some locks already, but then needed those rows the migration had already gotten hold of. Leading to this huge deadlock I had no idea how to solve.
Worst part is, it was one of the first times we tried deploying during working hours. At the time we normally only got to deploy 4 times a year, but we pushed for a more modern approach. Luckily the server guys were on our side, and a few years later that government agency is one of the best technical places I've seen after a complete revamp.
Well, somebody has to have access to live prod. That somebody should avoid using it too.
Anyway, everybody should run their queries on a prod-like environment before running on prod, but on Oracle that's really not enough. Also the places that use expensive DBMSes tend not to have a lot of non-prod environments for people to test their scripts.
Nowadays Oracle supports you engineering your data so some of it is not on the bottleneck of anything and you can give some low amount of access to inexperienced people. But that's not the default situation.
My approach is to type the “limit conditions” first, then go to the start of the line and do as you say (predicate the command with the comment string). So in the case of sql it might be something like starting with:
WHERE foo=bar LIMIT 1;
Then ctrl-a, and fill in the rest:
—- UPDATE name=“new name” WHERE foo=bar LIMIT 1;
Then take a moment, read over what I typed, and hit ctrl-a again and remove the comment string.
Ideally I’m doing this in an editor (not the db shell) as well, and when I’m done pass the saved file in on the commandline. I try to type as little as possible in the db shell unless I’m logged in as a read only user.
I also set my db shell to display the current username and database so it’s always right in front of me. And I never, ever use command history in shells to construct new commands. I swear that bit me more frequently than I got it right when I used to. It’s like a footgun with an extra footgun attachment.
I take a similar approach. I usually want to see what I'm about to change before I change it, so I'll SELECT * FROM ... WHERE ... , then go back in the command history and delete the SELECT FROM.
Predicate order in SQL is pretty dodgy all around and makes it hard to build intuition around SQL queries. In select, the actual SELECT clause (field selection) can usually be conceptualized as “happening” after all other clauses, but of course it comes first in the query.
This problem is fixed in query languages like EdgeQL.[0]
> A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE.
Given the annoyance of this and its ability to really, really ruin your day, I don't know why someone hasn't updated their parser to allow 'update X where [cond] set [blah]' as an alternate phrasing.
> 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.
Well, dangerous if you are using a simple terminal interface and not using a transaction when doing updates. The latter is generally a bad idea even if you aren't also doing the former.
> 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.
I religiously write it out of order for this reason. The IDE complains for a bit, but it's better to deal with some squiggles for a few seconds until you've filled in the column assignments part.
The problem with this is the same reason I don’t write dangerous CLI commands with the safety parts first: you can’t know whether your safety parts are safe until you introduce the dangerous parts. It’s the same reason I don’t ignore the linter, even if it’s cognitive overhead that formatting will solve… it’s more cognitive overhead to find out whether it’s safe to ignore.
At least for me, it’s safer to just… write the thing correctly in an environment incapable of running it.
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.
What I usually do is start typing something like "aupdate" or "pdate" (which are wrong) and only come back at the beginning (it's just a ctrl+a) to fix the command when I'm sure the sentence is OK.
Of course if the update is not only important but critical, manually starting a transaction before is the first step ;)
Sql is not the best language. As someone put it once
"The entire database industry is hauling a massive SQL-shaped parachute behind them. This complexity creates a drag on everything downstream."
Linq is closest to the a more natural querying language we have come up with. Too bad it doesn't do update.
I'm kinda bummed out with these recent SQL tropes. Plenty of people look down on SQL and try or try to make the new fancy thing only for it to fall flat on it's face and for people to realize that SQL is pretty good. It's not great for programmatic access but it's great for analytics and manual querying.
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 you misunderstood the previous comment. I think that firstly "ls /path" is entered and if the result is ok, then "; rm $_" is added to the copy of the command then executed.
(where "escape" "dot" brings up last argument) without the need to fiddle around with dollar signs underscores, exclamation marks, etc. to prevent further mistakes (e.g. "was it $! or !$ ?", shell expansion, etc)
I use vim bindings for shell, so doing dangerous things I usually open it in edit mode and have to physically do :wq to execute. Much less chance of being destructive, and you can easily drop the output into clipboard or a file from here.
Otherwise, simply having a mindset of, I'm doing dangerous things also goes a long way.
Never do things if you are in a panicked/frantic state.
Edit: I guess running galera is actually a blessing. I can't run any write/update methods on our qa/prod data...
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 unplugged the MySQL server. The PROD server. It's funny to look back on that now, but at the time... oh boy, total panic.
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.
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.
Because tables and SQL operators are really sets and acting on sets, so naturally operations take place on the set unless a subset is specified. Once you completely and totally internalize this SQL is pretty intuitive, although I think that the clause order might be nicer in a different order.
For example, I would like SELECT to be FROM, WHERE, SELECT. UPDATE could be UPDATE, WHERE, SET. But then DELETE would end up inconsistent...
> If I just do 'SELECT *' and execute it, it does not rotate through all the tables.
Because you did not specify what set you wanted to operate in, and the set of sets isn't meaningful because of schematic differences.
> Same with 'SELECT from xyz' if I can not have it empty and return all.
SELECT is asking what pieces of the subsets (rows) you want to display. If you don't ask for any, you don't get any. You are asking for the number of rows in xyz times zero. That's zero. You can write SELECT 1 FROM xyz and get 1 returned for each subset.
Oh I get that, and that kind of was my point. I created those to show the inconsistency of the interface that SQL is. You have to internalize those inconsistent bits. Sometimes you specify, other times you get everything out of the different sets. The mental model of SQL is split. In some cases it is 'get these items from the set and if you dont you will get a syntax error' in other cases it is 'filter these items from the whole set and if you dont you get everything'. I see a lot of people new to SQL who that trips up. Some people are sayin 'well just for update/delete make the where condition not optional'. That at least seems fair and fixes the easy issue, but then that is one more inconsistency in the language that SQL is. I am saying just go one step further and make unconditional always. There are some who disagree. That is fine. My guess we get a mode in most SQLs with the update/delete one. I think that is at least a decent compromise.
Honestly, I think the problem is just that SQL isn't a particularly great language. This is an example of where the syntax and semantics are coupled so tightly that it's just gonna be confusing no matter what you do. My vote would be to not even bother with a "literate" kind of language.
My problem with that suggestion is that it becomes automatic, if I just know I need to short circuit every query to get results I think I'd fall into a bad habit. If update and delete require special thought, you need to take that thought specifically when you're doing those actions. We're adding friction for those dangerous commands, not every command.
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. :)
When doing interactive maintenance/querying (which I discouraged, but we all know it happens even if only in “break glass” scenarios), I would generally
select *
--update <blah blah> [or delete]
from <blah blah>
where <blah blah>
which allowed me to execute the entire query as a select (or if I accidentally hit "run the whole buffer" it was safe), but then allowed me to highlight and run just the update (or delete) statement and ensure I had the same where clause as determined by my select statement pre-flighting.
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.
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 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.
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".
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
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.
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.
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.
I think the only answer is the branch the universe, apply your changes, and if things go wrong, send a quantum tunneled bit signal to the control universe that those changes were bad. Then destroy the universe, nobody will ever know.
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.
Haven't used MySQL in a while, but when I was using I'd have this alias in my zshrc:
root@localhost [main]> update user set password = 'abc123';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
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.
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.
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).
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.
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!
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.
IIRC (I'm not about to go test it now) DROP TABLE is transactional. But yeah, TRUNCATE is an odd beast. In Postgresql 10:
> TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred. See Section 13.5 for more details.
> TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does 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.
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 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.
Its never actually happened to me, but I've often thought it probably has happened to some people.
I once saw a coworker get destroyed by a rendering glitch in SSMS - he had GPU acceleration enabled and didn't notice his text selection highlight glitching out, smashed F5 on a query that looked like it had a where clause selected but if you fiddled with the scrollbar suddenly it wasn't actually selected. I noticed his query blocking everything and killed it, walked over to his office like WTF and he reran it right in front of me reproducing the glitch exactly - he couldn't believe what he'd done. disabling GPU acceleration in SSMS fixed it.
The GPU accelerated text rendering in SSMS 18 is incredibly bad. The other thing it likes to do sometimes is continue showing the text from tab A when you switch to tab B, until you scroll (not possible in short snippets) or edit it (and only the edited part updates!)
I'm still so confused about why a glorified text editor would need GPU accelerated text rendering. It's never rendered slow for me before. Seems like not trusting the OS to do things right and re-inventing the wheel yourself for little gain.
I’ve had something similar happen with lag on the right-click menus. It’s very easy to actually be clicking on the wrong option, like “Delete database,” because there was lag in updating the cursor position. Happens in scary places like MMC too. Hope you don’t accidentally drop a cluster volume because windows wasn’t done drawing a pretty arrow!
I worked at a place where this happened pretty much monthly, in production. Outages, blown SLA $$$, uptime metrics blown. Certain teams’ entire toolbox was a sql script with various query templates commented out, that they tweaked, manually highlighted, and ran. Passwords, configs, user data, all got wiped out this way. No one got fired, no one got reprimanded, the workflow didn’t change despite this happening over and over. And each time the DBA team got to spend a few hours restoring a multi-TB backup so that someone could grab the prior data from the table they wiped out. Middle of the night? No problem, wake up the DBAs and go back to bed, it’s not like there would be any consequences.
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.
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.
With SQL, that could be as simple as starting a transaction, doing your commando stuff, and committing it when you are satisfied.
But don't do that. Why are you doing anything like that in production. Why why why.
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 ;-)
Yes, in an ideal world but so many companies don't value the small details which can make everything nicer. I have spent the day trying to work out why a container runs in one environment and not another. I still don't because basics like easy log access, dns debugging tools etc. are not all part of Kubernetes.
OK, this isn't about breaking an entire network but small details can be super helpful. I have only found a small number of companies who seem to care about error messages.
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.
Speaking of LIMIT. Another potential "footgun", but with SELECT, is "select * from table limit1". Instead of limiting the number of rows to 1, you aliased the table as "limit1".
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.
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.