MySQL: ERROR 1175 (HY000): You are using safe update mode

What it means

sql_safe_updates is a session setting that guards against changing a whole table by accident. While it’s on, an UPDATE or DELETE is refused unless it either filters on a key column (one with an index the server can use, such as the primary key) or has a LIMIT. Error 1175 means your statement did neither. Nothing was changed.

The server doesn’t turn it on by itself; your client does:

MySQL Workbench’s SQL editor has it on by default (Preferences › SQL Editor, “Safe Updates”), which is why most people meet this error there, as Error Code: 1175.
mysql --safe-updates (also spelt --i-am-a-dummy), or safe-updates in an option file, turns it on for the command-line client, along with a 1,000-row limit on SELECT results.

Common causes

An UPDATE or DELETE with no WHERE, meant to change every row (clearing a table, resetting a flag).
A WHERE on a column without an index: WHERE email = 'nobody@x.com' or WHERE score > 3, when only id is indexed.
A WHERE the server can’t turn into a key lookup: WHERE 1=1, or WHERE id = 1 OR email = 'x' where email has no index.

How to fix it

Filter on a key column

Select the rows you mean by their primary key or another indexed column:

UPDATE emails SET score = 0 WHERE id = 1;
UPDATE emails SET score = 0 WHERE id IN (3, 7, 12);

A range on the key counts too, so WHERE id > 0 passes safe updates while still changing every row; it’s a way round the check, not a safer statement.

Add LIMIT

A LIMIT satisfies the check, which suits deleting in batches:

DELETE FROM sessions WHERE expires_at < NOW() LIMIT 1000;

Repeat until it affects 0 rows. Batches also keep each transaction’s locks short.

Turn it off for the statement you mean

When you do mean to change every row, switch it off for your session, run the statement, and switch it back on:

SET SQL_SAFE_UPDATES = 0;
UPDATE emails SET score = 0;
SET SQL_SAFE_UPDATES = 1;

In Workbench you can also untick the preference, but it only applies after you reconnect, and it stays off for every later query.

Add an index, if you filter on that column often

If WHERE email = … is a normal way to find rows, an index on email makes the statement pass safe updates and makes it faster. See adding an index.

Reproduce it

On MySQL 8.4.11, with emails (id int AUTO_INCREMENT PRIMARY KEY, email varchar(100), score int) and SET SESSION sql_safe_updates = 1:

UPDATE emails SET score = 0;
DELETE FROM emails;
UPDATE emails SET score = 0 WHERE score > 3;
DELETE FROM emails WHERE email = 'nobody@x.com';
UPDATE emails SET score = 0 WHERE 1=1;
UPDATE emails SET score = 0 WHERE id = 1 OR email = 'x';

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

These ran: WHERE id = 1, WHERE id > 0 (all rows), UPDATE emails SET score = 0 LIMIT 100, DELETE … WHERE score > 100 LIMIT 1000, and DELETE FROM emails WHERE id IN (SELECT 1). The same error came from mysql --safe-updates without any SET.

MariaDB 11.4.13 refused and allowed the same statements, with the message ending without a full stop:

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

In Inlet

Inlet guards against the same mistake its own way: on protected connections, the query editor asks before running an UPDATE or DELETE without WHERE (or a DROP or TRUNCATE) and says why, and connections tagged production open read-only until you unlock them, for ten minutes at a time. Edits in the grid are staged until you commit, with Review showing the exact SQL first.

ERROR 1175 (HY000): You are using safe update mode