1

I know that I can turn off Safe Mode in MySQL, so I’m not trying to work that one out.

I have a simple table:

create table rubbish(
    id int auto_increment primary key,
    stuff text,
    nonsense text
);

Here id is the primary key.

With Safe Mode turned on, I try the following:

update rubbish set nonsense=stuff where id=id;          -- fails
update rubbish set nonsense=stuff where id is not null; -- fails
update rubbish set nonsense=stuff where id<>0;          -- works

The error message, like most error messages in MySQL is unhelpful:

You are using safe update mode and you tried to update
a table without a WHERE that uses a KEY column

In all cases, I used the key column, so the message explains nothing. What does MySQL actually require me to do with the key column?

Manngo
  • 14,066
  • 10
  • 88
  • 110

1 Answers1

1

MySQL SQL_SAFE_UPDATES prevents you from misusing keys in UPDATE and DELETE statements. MySQL engine is optimized to understand some conditions given.

... WHERE `id` IS NOT NULL;

A primary key can never be null so this is always true. Same goes with

... WHERE `id`=`id`;

and

... WHERE TRUE;

These are considered as misuses of keys. Hense they are prohibited.

Roshana Pitigala
  • 8,437
  • 8
  • 49
  • 80
  • 1
    Great answer. Basically, it's trying to prevent you from blowing away the whole table accidentally. – ceejayoz Jul 21 '18 at 03:41
  • Note that the `WHERE TRUE` example wouldn’t satisfy the requirement anyway, as it doesn’t include the Key column. – Manngo Jul 21 '18 at 09:27
  • @Manngo I have just left it there to show that the above conditions are identical to that. So a future reader may understand it well. – Roshana Pitigala Jul 21 '18 at 13:03