MySQL error 1175: Safe update mode blocked the statement
sql_safe_updates is on, and the UPDATE or DELETE has no WHERE on a key column and no LIMIT, so MySQL refuses to change possibly every row.
- Error number
1175- Name
ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE- SQLSTATE
HY000- Category
- Server limits and settings
What it means
With sql_safe_updates = 1 (MySQL Workbench turns it on by default, and so does mysql --safe-updates), MySQL rejects UPDATE and DELETE statements that don't filter on a key column or use LIMIT. It's a guard against accidentally changing a whole table.
Common causes
- An
UPDATEorDELETEwithoutWHERE. - A
WHEREon a column that isn't indexed, so MySQL can't prove it is selective. - Running in MySQL Workbench, which enables safe updates by default.
How to fix it
-
Filter on a key column, or add a
LIMIT.UPDATE customers SET status = 'inactive' WHERE id IN (17, 42);
-
If you really mean to change every matching row, turn the guard off for the statement and back on afterwards.
SET SQL_SAFE_UPDATES = 0; UPDATE customers SET status = 'inactive' WHERE last_login < '2020-01-01'; SET SQL_SAFE_UPDATES = 1;
Related errors
More server limits and settings errors
1040Too many connections1203User has more than max_user_connections1153Got a packet bigger than max_allowed_packet1114The table is full
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.