UPDATE or DELETE rejected: 'You are using safe update mode'
You 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.
Fix it fast
1Find out which cause you have
Check whether safe updates is on for this session.
SHOW VARIABLES LIKE 'sql_safe_updates';2Match what you see, then jump to its fix
- 1
The 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
The error appears in Workbench's SQL editor even for a statement you consider safe.MySQL Workbench has Safe Updates switched onFix - 3
The 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
- 1
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
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix