MySQL Error 1175 occurs in safe-update mode when an UPDATE or DELETE lacks a WHERE clause referencing an indexed key column, blocking potentially destructive bulk changes.
MySQL Error 1175: ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE triggers when safe-update mode blocks an UPDATE or DELETE without a key-based WHERE clause. Add a keyed filter or temporarily disable safe-update mode to resolve the issue.
ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
MySQL raises error 1175 when the session variable SQL_SAFE_UPDATES is ON and an UPDATE or DELETE statement lacks a WHERE condition that filters on a primary key or indexed column.
The server aborts the statement to protect data from an unintended mass change.
The message usually reads: “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.” Fixing it quickly keeps production data safe and your deployment moving.
Safe-update mode is enabled at the client or session level by starting the MySQL client with --safe-updates or running SET SQL_SAFE_UPDATES = 1;.
This flips on strict protections.
When safe mode is active, MySQL checks UPDATE and DELETE commands. If the WHERE clause is missing or does not reference a keyed column, the engine throws error 1175 and cancels execution.
The safest fix is to rewrite the statement to include a WHERE clause that uses a primary key or indexed column.
This keeps safe-update mode on and fulfils the protection rule.
If a keyed WHERE is impractical, disable safe-update mode for the current session: SET SQL_SAFE_UPDATES = 0;. Re-enable it afterward to restore protection.
Bulk clean-up scripts commonly fail when developers forget to filter by an indexed ID. Adding WHERE id > 0 if id is indexed passes the safe-mode check.
GUI tools like MySQL Workbench enable safe mode by default.
Temporarily uncheck “Safe Updates” in preferences or execute the SQL shown above before running maintenance jobs.
Always test destructive statements with SELECT first, then convert to UPDATE/DELETE. Include LIMIT clauses during development.
Use transactions so you can roll back mistakes, and keep safe-update mode enabled in production to guard data integrity.
Galaxy’s SQL editor flags missing indexed filters before execution, reducing risk.
Error 1137 (HY000) happens when no matching rows are found for update in CI-based constraints. Unlike 1175, it is not tied to safe-update mode but to foreign-key checks.
Error 1205 (Lock wait timeout exceeded) may appear after disabling safe mode and executing long-running updates; reducing batch size or adding indexes resolves it.
.
Run SELECT @@SQL_SAFE_UPDATES;. A result of 1 means safe-update mode is on.
Add sql_safe_updates = 0 in the [mysqld] section of my.cnf and restart MySQL, but keep it on in production for safety.
No. LIMIT alone is insufficient. The WHERE clause must reference an indexed key, or safe-update mode will still block the query.
Galaxy’s linter warns users when UPDATE or DELETE statements lack indexed filters. It can auto-suggest key columns, preventing error 1175 before execution.
.avif)
.avif){kind=logo}
