Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

UPDATE or DELETE rejected: 'You are using safe update mode'

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

Short answer

Error 1175 means safe update mode is on and your UPDATE or DELETE has no WHERE condition on a key column and no LIMIT. Best fix: add a key condition or LIMIT. If you really want a wide change, turn Safe Updates off in MySQL Workbench preferences and reconnect, or set sql_safe_updates to 0 for the session.

  • Applies to MySQL 8.4, MySQL Workbench 8.0
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Check whether safe updates is on for this session.

    SHOW VARIABLES LIKE 'sql_safe_updates';
  2. 2Match what you see, then jump to its fix

    1. 1The error appears for UPDATE or DELETE with no WHERE, or a WHERE on a non-indexed column.The statement has no key column in WHERE and no LIMITFix
    2. 2The error appears in Workbench's SQL editor even for a statement you consider safe.MySQL Workbench has Safe Updates switched onFix
    3. 3The error appears in the command-line client, and an alias or option file adds --safe-updates (or --i-am-a-dummy).The mysql client was started with --safe-updatesFix

What does the error look like?

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

Cause 1 of 3

The statement has no key column in WHERE and no LIMIT

1Check

In safe mode an UPDATE or DELETE must have a key constraint in WHERE, a LIMIT, or both. A WHERE on a column with no index does not count.

SHOW INDEX FROM orders;
EXPLAIN UPDATE orders SET status = 'old' WHERE created_at < '2025-01-01';

2Fix

Add a condition on the primary key or an indexed column, or add LIMIT. To touch many rows by a non-key filter, first select the keys, then update by key.

-- by key
UPDATE orders SET status = 'old' WHERE id = 1042;

-- bounded by LIMIT
UPDATE orders SET status = 'old' WHERE created_at < '2025-01-01' LIMIT 500;

-- or add an index for the filter column so the check passes
ALTER TABLE orders ADD INDEX idx_orders_created_at (created_at);

3Verify

Run the statement again. A key-based WHERE passes the check; if the optimizer still does not use the index, EXPLAIN with SHOW WARNINGS gives the reason.

EXPLAIN UPDATE orders SET status = 'old' WHERE created_at < '2025-01-01';
SHOW WARNINGS;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

MySQL Workbench has Safe Updates switched on

1Check

The Safe Updates option is enabled by default in Workbench's SQL Editor preferences, and it sets the session when Workbench connects.

SELECT @@sql_safe_updates;

2Fix

Open Preferences, then SQL Editor, and in the Other section clear the box 'Safe Updates (rejects UPDATEs and DELETEs with no restrictions)'. The option needs a new connection: reconnect to the server afterwards.

-- the same change for the current session only, from SQL
SET SESSION sql_safe_updates = 0;

3Verify

After reconnecting, the variable reads 0.

SELECT @@sql_safe_updates;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The mysql client was started with --safe-updates

1Check

That option sets sql_safe_updates=1, sql_select_limit=1000 and max_join_size=1000000 when the client connects, so selects are also capped at 1000 rows.

SELECT @@sql_safe_updates, @@sql_select_limit, @@max_join_size;

2Fix

Remove the option from the command or option file for a normal session, or switch it off for the current session. sql_safe_updates is a dynamic variable with global and session scope.

SET SESSION sql_safe_updates = 0;
SET SESSION sql_select_limit = DEFAULT;

3Verify

Read the variables again.

SELECT @@sql_safe_updates, @@sql_select_limit;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Leave safe updates on in the tools you use against production data; change one statement to use a key instead of turning the guard off.
  • Run SELECT with the same WHERE first and read the row count before the UPDATE or DELETE.
  • Wrap risky changes in a transaction so you can roll back.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.