MySQL Safe Mode: What Satisfies the Requirements?

Question

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?

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