8 ms·
An UPDATE without a WHERE, or something close to it
- lowbloodsugar 5y agoIf 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. 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.
- ts330 5y agoWho 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 ;-)
- eli 5y agoIn 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!
- ts330 5y agoSurely this will become a non-problem as we migrate to NOSQL... no...? oh? we're migrating back? great.
- astine 5y agoThis is not about SQL; it's about user interfaces. The author is only using SQL as an explanatory tool.
- ts330 5y agosorry, the UK sarcasm mark-down doesn't always render correctly...
- BossingAround 5y agoThe author is using SQL as an explanatory tool so well I (and at least a few other folks in this thread) have zero idea what they are talking about.
- LanceH 5y agoI have once typed "ROLLBACK" in order to get my heart started again -- I had updated the users table.
- shimonabi 5y agoThis has never happened to me. A SQL statement without a WHERE looks too suspicious to me to make a mistake like that.
- yrgulation 5y agoin 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.
- unfunco 5y agoI'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: alias mysql="mysql --i-am-a-dummy" It hasn't happened since.
- thebrain 5y agoUsing --i-am-a-dummy is the best recommendation: 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.
- repler 5y agoThis 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.
- jbverschoor 5y agoIf 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. Happened to me 18 years ago. :-)
- markstos 5y agoAnsible 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: https://docs.ansible.com/ansible/latest/user_guide/intro_patterns.html https://docs.ansible.com/ansible/latest/user_guide/intro_pat...
- matsemann 5y agoI 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).
- zoomablemind 5y agoSQL operates on sets of records, so it's up to the user to specify and restrict the set. One can form a set by specifying just a table, or tune more by JOINs and further with WHERE clause. 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.
- IceDane 5y agoSpeaking 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.
- deleted 5y ago[deleted]
- jerf 5y agoThere 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.
- rhinoceraptor 5y ago"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 https://twitter.com/devops_borat/status/41587168870797312
- mprovost 5y ago
- blakesterz 5y agoWaaay 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.
- blastro 5y agothis happened recently at my place of work
- toast0 5y agoCancelling 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.
- jerf 5y agoThis 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.
- silvestrov 5y agoI 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.
- cerved 5y agodatagrip won't run such a query by default
- codeulike 5y agore: 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 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.
- gfody 5y agoI 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.
- hotsauceror 5y agoI’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! Powershell FTW.
- rspeele 5y agoThe 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!)
- 5y ago
- bob1029 5y agoIf you put a small program/interface around SQL entry, you can trivially verify that a user entered a reasonable query before allowing to execute. 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.
- lbriner 5y agoYes, 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.
- jusssi 5y agoA 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.
- JeffRosenberg 5y agoI try to start any UPDATE or DELETE by writing out a transaction first: BEGIN TRAN; -- TODO: update statement ROLLBACK; Usually I'll run it with the ROLLBACK first, to confirm that it impacts the number of rows I'd expect, and only then change my ROLLBACK to a COMMIT.
- stuartd 5y agoI 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..
- eyelidlessness 5y agoI 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.
- tehlike 5y agoI do this when I test my migrations locally. Run code first, throw an error to force rollback the transaction...
- RenThraysk 5y agoWith 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.
- breakfastduck 5y agoHonestly the amount of massive disasters I've personally seen avoided (when someone thought their script was watertight) by simply doing this is astronomical.
- deleted 5y ago[deleted]
- andreareina 5y agoAnd be sure that you're not using something that implicitly commits the transaction, such as TRUNCATE in Redshift.
- RenThraysk 5y agoTRUNCATE 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.
- andreareina 5y agoIIRC (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.
- willcipriano 5y agoWhen 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.
- lowbloodsugar 5y agoyet
- tyingq 5y agoUPDATE also supports ORDER BY and LIMIT, which is kind of odd when you first see it.
- kwoff 5y agoSpeaking 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".
- T3RMINATED 5y agoI never made that mistake and I used SQL for 20yrs. If you do make that mistake, please quit IT Industry right now and go sell cars.