An UPDATE without a WHERE, or something close to it • Hacker News Board

Re: An UPDATE without a WHERE, or something close to it

#61

Post by unfunco » Fri, Oct 29, 2021, 6:52 PM UTC

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.

Re: An UPDATE without a WHERE, or something close to it

#62

Post by lowbloodsugar » Fri, Oct 29, 2021, 6:53 PM UTC

willcipriano wrote: post #22

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.

yet

Re: An UPDATE without a WHERE, or something close to it

#63

Post by vincnetas » Fri, Oct 29, 2021, 6:53 PM UTC

njs12345 wrote: post #18

jerf wrote: post #6

Earlier quoted context omitted.

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.

A fun Postgres extension to require this: https://github.com/eradman/pg-safeupdate

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.

Re: An UPDATE without a WHERE, or something close to it

#64

Post by lowbloodsugar » Fri, Oct 29, 2021, 6:56 PM UTC

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.

Re: An UPDATE without a WHERE, or something close to it

#65

Post by SketchySeaBeast » Fri, Oct 29, 2021, 6:58 PM UTC

sumtechguy wrote: post #60

SketchySeaBeast wrote: post #11

Earlier quoted context omitted.

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.

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.

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.

Re: An UPDATE without a WHERE, or something close to it

#66

Post by repler » Fri, Oct 29, 2021, 7:03 PM UTC

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.

Re: An UPDATE without a WHERE, or something close to it

#67

Post by jbverschoor » Fri, Oct 29, 2021, 7:07 PM UTC

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. :-)

Re: An UPDATE without a WHERE, or something close to it

#68

Post by thebrain » Fri, Oct 29, 2021, 7:16 PM UTC

unfunco wrote: post #61

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 w…

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.

Re: An UPDATE without a WHERE, or something close to it

#69

Post by DoneWithAllThat » Fri, Oct 29, 2021, 7:20 PM UTC

jusssi wrote: post #16

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…

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.

Re: An UPDATE without a WHERE, or something close to it

#70

Post by masklinn » Fri, Oct 29, 2021, 7:23 PM UTC

colinmcd wrote: post #48

jusssi wrote: post #16

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…

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

> 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.