5 ms·

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'; Someho

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.

blastro 5y ago
this happened recently at my place of work
toast0 5y ago
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.
jerf 5y ago
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 5y ago
  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.
- cerved 5y ago
  datagrip won't run such a query by default
- SketchySeaBeast 5y ago
  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 5y ago
    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 5y ago
      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 5y ago
      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.
- njs12345 5y ago
  A fun Postgres extension to require this: https://github.com/eradman/pg-safeupdate https://github.com/eradman/pg-safeupdate
  - vincnetas 5y ago
    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 5y ago
Always SELECT before making changes. Or don't if you like feeling the adrenaline rush when executing a query in prod.
- SketchySeaBeast 5y ago
  SELECT, wrap it in a transaction, then either look at row count or SELECT again before COMMIT.
- sokoloff 5y ago
  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 5y ago
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. :)
ComodoHacker 5y ago
Were those days so early, backups weren't mandatory yet?

blastro 5y ago

this happened recently at my place of work

toast0 5y ago

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.

jerf 5y ago

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 5y ago
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.
cerved 5y ago
datagrip won't run such a query by default
SketchySeaBeast 5y ago
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 5y ago
  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 5y ago
    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 5y ago
    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.
njs12345 5y ago
A fun Postgres extension to require this: https://github.com/eradman/pg-safeupdate https://github.com/eradman/pg-safeupdate
- vincnetas 5y ago
  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 5y ago

Always SELECT before making changes. Or don't if you like feeling the adrenaline rush when executing a query in prod.

SketchySeaBeast 5y ago
SELECT, wrap it in a transaction, then either look at row count or SELECT again before COMMIT.
sokoloff 5y ago
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 5y ago

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

ComodoHacker 5y ago

Were those days so early, backups weren't mandatory yet?