mysqlsafe-mode

MySQL Safe Mode: What Satisfies the Requirements?

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?

Solution

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