Published: 2026-09-26

MySQL Error 1175: Safe Update Mode and DELETE Without WHERE

Error Code: 1175 means the session has sql_safe_updates switched on and your UPDATE or DELETE has neither a WHERE condition MySQL can resolve through an index nor a LIMIT. The server refuses the statement before it touches a row. MySQL Workbench turns the mode on by default for its connections, so people often meet the error there without having set anything; the mysql command-line client turns it on with --safe-updates. The fixes are to target rows by key, add a LIMIT, or switch the check off for one statement. Every case below was reproduced on MySQL 8.0.46 and 9.5.0. After that: why PostgreSQL, SQL Server, Oracle and SQLite have no equivalent, and what 1,720 confirmation prompts in Jam SQL Studio show about guards like this one.

The exact error message

From the mysql client, against MySQL 8.0.46 and 9.5.0 alike:

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

In MySQL Workbench the same error carries an extra sentence that Workbench adds itself:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

The error's symbol is ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE, SQLSTATE HY000. Its message template in the MySQL 8.4 error reference ends in a %s, where MySQL appends the first diagnostic the optimizer produced — typically why it could not use an index that exists. We got this one by comparing a VARCHAR primary key with a number:

ERROR 1175 (HY000) at line 28: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'code'

MariaDB keeps the same variable and the same error number: MariaDB 10.2.21 returned ERROR 1175 (HY000) with the same text, minus the trailing period.

What sql_safe_updates checks

The reference manual puts it in one sentence: with the variable enabled, “UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error.” The variable facts, from the MySQL 8.4 manual (unchanged in the 9.7 and 26.7 manuals):

• Default: OFF on the server. Something has to turn it on — a client, an option file, or a DBA.
• Scope: global and session, dynamic. SET GLOBAL sql_safe_updates = ON makes it the default for connections opened afterwards, including your applications’ connections.
• Per statement: the SET_VAR optimizer hint applies to it, so a single statement can opt out (see the fixes below).

“Use a key” means the optimizer actually picks an index to find the rows. The check runs against the execution plan, not the statement text — which is why the results below include a couple of surprises.

When MySQL raises error 1175: ten statements, tested

We ran each statement below with mysql --safe-updates against a throwaway table in a scratch database on MySQL 8.0.46 and MySQL 9.5.0 (both from our local test containers), then dropped the database. Both versions gave identical results.

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT,
  status      VARCHAR(20),
  note        VARCHAR(50),
  KEY ix_customer (customer_id)
);
-- 4 rows: two 'open', one 'shipped', one 'cancelled'

Two rows in that table matter most. WHERE 1=1, the usual way to tell a tool “yes, I mean every row”, does not get past MySQL. And WHERE id > 0 passes while rewriting the whole table. Safe update mode stops statements the optimizer can’t drive through an index; it doesn’t count how many rows a statement will change.

The manual lists a few more edge cases worth knowing:

• A key in the WHERE clause can still fail if range access would need more memory than range_optimizer_max_mem_size allows — the optimizer falls back to a table scan, and safe updates rejects it.
• For multiple-table updates and deletes, the error fires only if a target table is read with a table scan.
• EXPLAIN UPDATE … and EXPLAIN DELETE … never raise the error, so EXPLAIN followed by SHOW WARNINGS is the way to see why an index wasn’t used.

What --safe-updates (and --i-am-a-dummy) also set

The mysql client option has three spellings — --safe-updates, --i-am-a-dummy and -U — and it does more than flip one variable. On connect, the client runs:

SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;

• sql_select_limit=1000 caps every SELECT without its own LIMIT at 1,000 rows. We checked: a plain SELECT COLUMN_NAME FROM information_schema.COLUMNS returned exactly 1,000 rows on a server where COUNT(*) of the same view returned 3,635. Nothing in the output says the result was cut.
• max_join_size=1000000 makes a multiple-table SELECT fail if the server estimates it must examine more than a million row combinations.

--select-limit and --max-join-size override the two numbers, and --skip-safe-updates cancels the option when an option file turned it on. MariaDB’s client does the same: MariaDB 10.2.21 reported the same three session values after --safe-updates. Source: Using Safe-Updates Mode and mysql client options, MySQL 8.4 manual.

MySQL Workbench’s Safe Updates preference

Workbench sets sql_safe_updates for its own connections from a preference, which is why people who never touched the variable still see error 1175. Per the Workbench manual, the option lives under Preferences → SQL Editor, labelled “Safe Updates (rejects UPDATEs and DELETEs with no restrictions)”. It is enabled by default, and a change needs a reconnect (Query menu → Reconnect to Server) before it applies. The extra sentence in Workbench’s version of the error points at the same preference.

The Workbench setting affects Workbench only. The same account connecting from an application, from the mysql client without --safe-updates, or from another GUI gets the server’s value — normally OFF.

Three ways to fix error 1175

All three keep the guard in place for everything else you run.

1. Target the rows by key

Look up the keys first, then change exactly those rows. The SELECT doubles as a preview of what you’re about to modify:

SELECT id, status FROM orders WHERE status = 'open';   -- 1, 2
UPDATE orders SET note = 'x' WHERE id IN (1, 2);

If you filter on the column regularly, index it. After CREATE INDEX ix_status ON orders (status), the WHERE status = 'open' update that failed above ran on both versions. That depends on the optimizer choosing the index; on a column where most rows share a value it may still prefer a scan, and the error comes back.

2. Add a LIMIT

A LIMIT satisfies the check with or without a key. With ORDER BY it also turns a large delete into batches you can stop at any point:

DELETE FROM orders WHERE status = 'cancelled' ORDER BY id LIMIT 1000;
-- repeat until ROW_COUNT() returns 0

3. Switch the check off for one statement, not the session

When you genuinely mean every row, opt out for that statement only. On MySQL 8.0 and later the SET_VAR hint does it:

UPDATE /*+ SET_VAR(sql_safe_updates = 0) */ orders
SET note = 'reviewed'
WHERE status = 'open';

That statement ran on 8.0.46 and 9.5.0, and @@SESSION.sql_safe_updates read 1 straight afterwards — the next careless statement is still caught. MariaDB ignores the hint (we got 1175 from 10.2.21); its equivalent is SET STATEMENT sql_safe_updates = 0 FOR UPDATE …, which worked and also left the session value at 1.

The session-wide version — SET SESSION sql_safe_updates = 0;, your statement, then SET SESSION sql_safe_updates = 1; — works too, as long as you remember the second half. Unticking the Workbench preference is the widest switch of all: it turns the check off for every future Workbench connection.

Two related notes. TRUNCATE TABLE is not covered by safe update mode — it ran without complaint under --safe-updates on both versions. And on InnoDB tables, running the change inside START TRANSACTION … SELECT ROW_COUNT(); lets you check the count before you COMMIT or ROLLBACK.

Which other engines have a safe update mode?

MySQL and MariaDB are the only ones of the engines Jam SQL Studio supports with a server-side switch for this. Everywhere else, an unfiltered UPDATE or DELETE runs as written unless something outside the engine stops it (as of September 2026):

For four of the five engines, then, the guard lives in the client or nowhere. That is the gap a confirmation prompt in the SQL editor fills, with one important difference from MySQL’s version: it asks rather than refuses.

What Jam SQL Studio does instead

Jam SQL Studio never sets sql_safe_updates on a MySQL connection, so you only see error 1175 if your server or DBA has turned it on. What it does on every engine is hold certain statements behind a confirmation dialog in the Query Editor. The rules, as they are in the code today:

• Missing WHERE — a DELETE FROM … or UPDATE … SET with no WHERE clause, or one whose whole WHERE is 1=1. The dialog reads “Affects all rows in the table”.
• Destructive DDL — DROP TABLE / DATABASE / SCHEMA / INDEX, TRUNCATE TABLE, ALTER TABLE … DROP, plus each engine’s own (LOAD DATA INFILE and RESET REPLICA on MySQL, DROP TABLESPACE on Oracle, and so on).
• Routine drops — DROP VIEW / PROCEDURE / FUNCTION / TRIGGER (and EVENT on MySQL). A milder prompt that names the object type, with a Drop Anyway button, because code is lost rather than rows.
• Server-level commands — SET GLOBAL, FLUSH PRIVILEGES and PURGE BINARY LOGS on MySQL, sp_configure, xp_cmdshell and DBCC SHRINK on SQL Server, ALTER SYSTEM on PostgreSQL and Oracle.
• Scripts it can’t analyse — text that ends inside an unclosed quote or comment, where code and text can’t be told apart.
• SQL written by an AI agent in the built-in terminal, held to a stricter rule: any statement that can overwrite or delete existing rows asks, WHERE clause or not.

The dialog shows the statement that triggered it, a one-line reason and a tip matched to that reason; Cancel leaves the query in the tab unrun. The prompt applies to every connection on SQL Server, PostgreSQL, MySQL, Oracle and SQLite — not only ones you have marked as production — and there is no setting that turns it off. Comments and the bodies of CREATE PROCEDURE / FUNCTION / TRIGGER statements are ignored, because nothing in them runs at submit time. The full rule list is in the Query Editor docs.

Because the check reads statement text instead of an execution plan, it draws the line in a different place from MySQL. The same statements as above:

Neither approach catches WHERE id > 0, and neither should be read as a guarantee. The Jam SQL Studio check in particular matches the common spellings of an unfiltered statement, not every one: today it does not match multi-table forms such as DELETE o FROM orders o JOIN …, an UPDATE whose table name is quoted (UPDATE "orders" SET …), or — on every engine except PostgreSQL — an UPDATE with a schema-qualified name (UPDATE shop.orders SET …). On MySQL, if you want the server to refuse as well, turn on sql_safe_updates; the two don’t conflict.

What 1,720 confirmation prompts show

Jam SQL Studio records two anonymous events around that dialog: one when it appears, one with what the user did. Between August 13 and September 25, 2026, 167 installations saw the dialog 1,720 times. Users confirmed 1,569 of them (91%) and cancelled 151.

By engine: SQL Server 1,193 prompts from 116 installations, MySQL 224 from 22, PostgreSQL 182 from 24, Oracle 107 from 14, SQLite 14 from 3. On MySQL, 33 of the prompts were for a missing WHERE; 30 of those were confirmed.

Read plainly, the numbers say three things.

• Most prompts are for things people meant to do. Two-thirds are destructive DDL, and 93% of those were confirmed — dropping and recreating tables is routine work. Even the missing-WHERE prompt, the case safe update mode exists for, was confirmed 92% of the time. The typical installation saw the dialog 4 times in the window, but the 90th percentile saw it about 29 times, the busiest one 100 times, and the ten busiest installations account for 590 of the 1,720 prompts (34%). For those users, confirmation fatigue is a real risk: a dialog you have clicked through 90 times is easy to click through the 91st.
• Half the installations backed out at least once. 83 of the 167 installations (50%) cancelled at least one prompt. For the missing-WHERE prompt specifically, 18 of 51 installations (35%) cancelled at least once.
• “I can’t tell what this does” gets the most second looks. The couldn’t-analyse prompt was cancelled 27% of the time, against 7–8% for DDL and missing WHERE. An unclosed quote is often a real typo, which may explain part of that.

What the data can’t say is what a cancel meant. It may have been a caught mistake, a second thought, a statement the user wanted to edit first, or a mis-click — we don’t collect the SQL, so we can’t tell, and we don’t count cancels as prevented disasters. What it does support is keeping the prompt narrow: every false alarm (a DROP inside a comment, a DELETE inside a procedure body, a column widened with ALTER COLUMN) teaches people to click through the real ones, which is why the rules above skip them.

Methodology

• Window: August 13 – September 25, 2026 (44 days).
• Sample: the 167 anonymous installations that saw the Query Editor’s confirmation dialog at least once in the window. An installation is one anonymous device identifier, not a person. Installations that turned telemetry off (Settings → Privacy → Send Anonymous Usage Data) are not included.
• Events: feature:dangerous_query_prompted (reason kind, severity, engine, and which control started the run) and feature:dangerous_query_resolved (confirmed or cancelled, reason kind, severity, engine), each with the app version, OS and subscription tier. No SQL text, no table, schema or database names, no connection details and no error messages are sent — the reason is a fixed category, not the dialog’s wording.
• Scope: Query Editor tabs only, including scripts opened with Schema Compare’s Open & Execute. The Visual Query Editor and dashboard tiles have their own confirmation paths and are not counted here. An installation that used several engines counts once per engine in the by-engine figures.
• MySQL tests: run with the mysql client and --safe-updates against MySQL 8.0.46, MySQL 9.5.0 and MariaDB 10.2.21 containers, each in a scratch database created and dropped for the purpose.

Quick Answers

Short answers to common questions about MySQL error 1175 and safe update mode.

Q: What does MySQL error code 1175 mean?

A: It means the session has sql_safe_updates enabled and an UPDATE or DELETE was about to run without a WHERE condition that MySQL can resolve through an index and without a LIMIT clause. The server rejects the statement before changing any row. MySQL Workbench enables the mode by default, and the mysql command-line client enables it with --safe-updates.

Q: How do I fix error 1175 without turning safe update mode off?

A: Target the rows by a key column (for example WHERE id IN (1, 2) after looking the ids up with a SELECT), add a LIMIT clause, or index the column you filter on so the optimizer can use it. A LIMIT with ORDER BY also lets you delete in batches, repeating the statement until it affects zero rows.

Q: How do I turn off safe update mode for a single statement?

A: On MySQL 8.0 and later, add the optimizer hint /*+ SET_VAR(sql_safe_updates = 0) */ right after the UPDATE or DELETE keyword. The hint applies to that statement only, and the session keeps safe updates on. For a whole session, run SET SESSION sql_safe_updates = 0 and set it back to 1 afterwards. On MariaDB, which ignores that hint, write SET STATEMENT sql_safe_updates = 0 FOR in front of the statement.

Q: How do I disable Safe Updates in MySQL Workbench?

A: Open Preferences, go to SQL Editor, and clear the option Safe Updates (rejects UPDATEs and DELETEs with no restrictions). The change applies after you reconnect, for example with Reconnect to Server in the Query menu. The option is enabled by default.

Q: Does WHERE 1=1 get around error 1175?

A: No. With safe updates on, UPDATE ... WHERE 1=1 returned error 1175 on both MySQL 8.0.46 and 9.5.0, because the check looks at whether the optimizer uses a key, not at whether a WHERE clause is present. The reverse also holds: WHERE id > 0 passes the check and still updates every row.

Q: Do PostgreSQL, SQL Server, Oracle, or SQLite have a safe update mode?

A: Not built in. PostgreSQL has none in core; the third-party pg-safeupdate extension adds the check (its 1.7 release in July 2026 added PostgreSQL 19 support). SQL Server, Oracle Database, and SQLite have no setting that rejects an UPDATE or DELETE without a WHERE clause, so any guard has to come from the client or from permissions.

Q: Does Jam SQL Studio block DELETE without WHERE?

A: It asks first. On SQL Server, PostgreSQL, MySQL, Oracle, and SQLite connections, a DELETE or UPDATE with no WHERE clause (or only WHERE 1=1) opens a confirmation dialog, as do DROP, TRUNCATE, and scripts it cannot analyse. The prompt applies to every connection and has no off switch. It reads statement text, so some spellings, such as a quoted table name after UPDATE, are not matched yet. Jam SQL Studio does not turn on MySQL's sql_safe_updates itself.

Summary

Error 1175 is MySQL refusing an UPDATE or DELETE that it can’t drive through an index and that has no LIMIT. Fix it by targeting keys, adding a LIMIT, or opting one statement out with SET_VAR; don’t rely on WHERE 1=1, which MySQL rejects, and remember that a key range like WHERE id > 0 passes while touching every row. The other four engines have no switch of their own, so the check has to come from your client. For the broader set of habits — colour-coded connections, confirmation before destructive statements — see how to prevent running queries on the wrong database.