[HN Gopher] An UPDATE without a WHERE, or something close to it
___________________________________________________________________
An UPDATE without a WHERE, or something close to it
Author : l0b0
Score : 85 points
Date : 2021-10-28 05:49 UTC (1 days ago)
(HTM) web link (rachelbythebay.com)
(TXT) w3m dump (rachelbythebay.com)
| unfunco wrote:
| 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: alias mysql="mysql --i-am-a-
| dummy"
|
| It hasn't happened since.
| thebrain wrote:
| Using --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.
| ts330 wrote:
| 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 ;-)
| T3RMINATED wrote:
| I 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.
| tyingq wrote:
| UPDATE also supports ORDER BY and LIMIT, which is kind of odd
| when you first see it.
| kwoff wrote:
| 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".
| shimonabi wrote:
| This has never happened to me.
|
| A SQL statement without a WHERE looks too suspicious to me to
| make a mistake like that.
| ts330 wrote:
| Surely this will become a non-problem as we migrate to NOSQL...
| no...? oh? we're migrating back? great.
| astine wrote:
| This is not about SQL; it's about user interfaces. The author
| is only using SQL as an explanatory tool.
| BossingAround wrote:
| The 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.
| ts330 wrote:
| sorry, the UK sarcasm mark-down doesn't always render
| correctly...
| lowbloodsugar wrote:
| 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.
|
| 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.
| codeulike wrote:
| 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 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.
| rspeele wrote:
| Oh yeah, it could happen. Happened to a, um, friend of mine.
| This friend never uses that feature anymore, no matter how
| convenient it looks.
| gfody wrote:
| 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.
| rspeele wrote:
| 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!)
| lbriner wrote:
| 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!
| codeulike wrote:
| There's always ad-hoc stuff that needs doing though
| bob1029 wrote:
| 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.
|
| 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 wrote:
| 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.
| jusssi wrote:
| 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.
| zimpenfish wrote:
| > 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.
| DoneWithAllThat wrote:
| 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.
| jesterpm wrote:
| 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.
| hanselot wrote:
| 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...
| bombcar wrote:
| 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.
| pletnes wrote:
| 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.
| magicalhippo wrote:
| 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.
| dcminter wrote:
| 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!
| magicalhippo wrote:
| Fair point, you don't want those transactions hanging
| around. Still, I prefer it to the alternative.
| to11mtm wrote:
| 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.
| marcosdumay wrote:
| 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.
| matsemann wrote:
| 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.
| dcminter wrote:
| > You shouldn't get access to prod if...
|
| Well, this was back in the dark ages. Nowadays I'm sold
| on the notion that we shouldn't be running ad hoc stuff
| against the live prod database at all.
|
| I'm sure there are exceptions, but I'll bet there are
| more temptations than solid reasons to do so!
| marcosdumay wrote:
| 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.
| matsemann wrote:
| 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..
| lol768 wrote:
| > 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.
| filomeno wrote:
| 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 ;)
| renewiltord wrote:
| DataGrip will not execute it without a LIMIT.
| mprovost wrote:
| 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 !$`.
| benjaminwai wrote:
| 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
| mprovost wrote:
| 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.
| BossingAround wrote:
| Point being that "ls /path; rm $!" deletes something
| entirely different than "/path".
|
| What you want is "ls /path; rm $_".
|
| Even then, the above is fairly pointless. At the time you
| look at what you are deleting, it's gone.
| witrak wrote:
| 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.
| colinmcd wrote:
| 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]
|
| [0] https://www.edgedb.com/docs/edgeql/commands/update
| masklinn wrote:
| > In select, the actual SELECT clause (field selection) can
| usually be conceptualized as "happening" after all other
| clauses
|
| Not quite true, DISTINCT, ORDER BY and TOP/LIMIT happen
| afterwards.
| hermanradtke wrote:
| Also, HAVING
| JeffRosenberg wrote:
| I 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 wrote:
| 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..
| [deleted]
| markstos wrote:
| 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:
|
| https://docs.ansible.com/ansible/latest/user_guide/intro_pat...
| eli wrote:
| 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!
| repler wrote:
| 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.
| RenThraysk wrote:
| 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.
| andreareina wrote:
| And be sure that you're not using something that implicitly
| commits the transaction, such as TRUNCATE in Redshift.
| RenThraysk wrote:
| 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.
| [deleted]
| breakfastduck wrote:
| Honestly the amount of massive disasters I've personally seen
| avoided (when someone thought their script was watertight) by
| simply doing this is astronomical.
| matsemann wrote:
| 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).
| jbverschoor wrote:
| 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.
|
| Happened to me 18 years ago. :-)
| jerf wrote:
| 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.
| rhinoceraptor wrote:
| "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
| torstenvl wrote:
| 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]
| qsort wrote:
| 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".
| Someone wrote:
| 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.
| jerf wrote:
| 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.
| bentcorner wrote:
| 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.
| mprovost wrote:
| 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.
| toast0 wrote:
| 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
| LanceH wrote:
| I have once typed "ROLLBACK" in order to get my heart started
| again -- I had updated the users table.
| yrgulation wrote:
| 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.
| willcipriano wrote:
| 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.
| lowbloodsugar wrote:
| _yet_
| blakesterz wrote:
| 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.
| ComodoHacker wrote:
| Were those days so early, backups weren't mandatory yet?
| jerf wrote:
| 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.
| silvestrov wrote:
| I 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.
| SketchySeaBeast wrote:
| 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.
| sumtechguy wrote:
| 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.
| SketchySeaBeast wrote:
| 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.
| Amezarak wrote:
| 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.
| cerved wrote:
| datagrip won't run such a query by default
| njs12345 wrote:
| A fun Postgres extension to require this:
| https://github.com/eradman/pg-safeupdate
| vincnetas wrote:
| What's fun about it? I cant imagine why this is not the
| first recommendation when you setting up a new postgres DB.
|
| And yeah, thank you. Today i learned new thing.
| is_true wrote:
| Always SELECT before making changes. Or don't if you like
| feeling the adrenaline rush when executing a query in prod.
| SketchySeaBeast wrote:
| SELECT, wrap it in a transaction, then either look at row
| count or SELECT again before COMMIT.
| sokoloff wrote:
| 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.
| sokoloff wrote:
| 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. :)
| blastro wrote:
| this happened recently at my place of work
| toast0 wrote:
| 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.
___________________________________________________________________
(page generated 2021-10-29 23:01 UTC)