--- title: "MySQL Error 1175: Safe Update Mode and DELETE Without WHERE" description: "MySQL error 1175 means safe update mode blocked an UPDATE or DELETE with no key in its WHERE. What triggers it, three safe fixes, and other engines." url: "https://jamsql.com/blog/2026-09-26-mysql-error-1175-safe-update-mode/" html_url: "https://jamsql.com/blog/2026-09-26-mysql-error-1175-safe-update-mode/" generated: "2026-10-02T21:40:14.933Z" --- 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](https://dev.mysql.com/doc/mysql-errors/8.4/en/server-error-reference.html) 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](https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_sql_safe_updates) 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. ```sql 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' ``` Statement Result with safe updates on `DELETE FROM orders;` Error 1175 `UPDATE orders SET note='x' WHERE status='open';` Error 1175 — `status` has no index, so this is a table scan `UPDATE orders SET note='x' WHERE id=1;` Runs, 1 row `UPDATE orders SET note='y' WHERE customer_id=10;` Runs, 2 rows — a secondary index counts as a key `UPDATE orders SET note='x' WHERE status='open' LIMIT 10;` Runs, 2 rows — a `LIMIT` is enough on its own `DELETE FROM orders ORDER BY id DESC LIMIT 1;` Runs, 1 row — no `WHERE` needed with a `LIMIT` `UPDATE orders SET note='z' WHERE 1=1;` Error 1175 — a `WHERE` clause that uses no key doesn’t count `UPDATE orders SET note='z' WHERE id > 0;` Runs, and updates **every row** in the table — the range is on the primary key `UPDATE codes SET v=9 WHERE code = 2222;` (`code` is a `VARCHAR` primary key) Error 1175 plus “Cannot use range access on index 'PRIMARY' due to type or collation conversion” `UPDATE orders o JOIN cust c ON c.id = o.customer_id SET o.note = c.name;` Error 1175 — the target table `orders` is scanned; adding `WHERE o.id = 1` makes it run 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: ```sql 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](https://dev.mysql.com/doc/refman/8.4/en/mysql-tips.html) and [mysql client options](https://dev.mysql.com/doc/refman/8.4/en/mysql-command-options.html), 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](https://dev.mysql.com/doc/workbench/en/wb-preferences-sql-editor.html), 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: ```sql 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: ```sql 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: ```sql 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): Engine Built-in guard What people use instead MySQL / MariaDB `sql_safe_updates` — off by default on the server, on by default in Workbench — PostgreSQL None in core The third-party [pg-safeupdate](https://github.com/eradman/pg-safeupdate) extension: `LOAD 'safeupdate';` per session, or `shared_preload_libraries` / `session_preload_libraries`. Raises `UPDATE requires a WHERE clause`, accepts `WHERE 1=1` as the escape hatch, and can be switched off with `SET safeupdate.enabled=0`. Release 1.7 (July 13, 2026) added PostgreSQL 19 support; you build it from source, and managed services only offer it if they list it. SQL Server None — no server or session option (confirmed in answers to a [2025 Microsoft Q&A feature request](https://learn.microsoft.com/en-us/answers/questions/2180290/request-safe-updates-feature-in-sql)) Client-side add-ins for SSMS, such as SSMSBoost’s [Fatal Actions Guard](https://www.ssmsboost.com/Features/ssms-add-in-fatal-actions-guard), which checks for `DELETE`/`UPDATE` without `WHERE` and `TRUNCATE` Oracle Database None — no initialization or session parameter for it Privileges (no `DELETE` grant on the table), triggers, or client-side checks SQLite None The `sqlite3` shell’s [`--safe`](https://sqlite.org/cli.html) option is a different thing: it blocks `ATTACH`, `.shell`, `readfile()` and other access to files outside the named database, not unfiltered writes 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](/docs/query-editor/#destructive-confirmation). 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: Statement MySQL safe updates Jam SQL Studio `DELETE FROM orders;` Refused (1175) Asks `UPDATE orders SET note='z' WHERE 1=1;` Refused (1175) Asks `UPDATE orders SET note='x' WHERE status='open';` Refused (1175) — no index Runs — it has a real filter `DELETE FROM orders ORDER BY id DESC LIMIT 1;` Runs Asks — there is no `WHERE` `UPDATE orders SET note='z' WHERE id > 0;` Runs, every row Runs, every row `TRUNCATE TABLE orders;` / `DROP TABLE orders;` Runs — not covered Asks 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**. Reason for the prompt Prompts Installations Confirmed Cancelled Destructive DDL (`DROP`, `TRUNCATE`, …) 1,165 122 1,084 (93%) 82 Missing `WHERE` 306 51 282 (92%) 24 Couldn’t analyse the script 111 32 81 (73%) 30 Routine drop (view, procedure, …) 99 26 89 (90%) 10 Server-level command 19 9 16 3 Written by an AI agent 20 1–2 17 2 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](/blog/prevent-accidental-production-queries/). ### A Confirmation Before Unfiltered Writes Jam SQL Studio asks before a `DELETE` or `UPDATE` without `WHERE`, a `DROP` or a `TRUNCATE` runs — on MySQL, SQL Server, PostgreSQL, Oracle and SQLite. Free for personal use. [Download Free](/#download) [Jam SQL Studio for MySQL](/databases/mysql/) ### Related - [Prevent Queries on the Wrong Database](/blog/prevent-accidental-production-queries/) - [Destructive-Statement Confirmation](/docs/query-editor/#destructive-confirmation) - [MySQL Client](/databases/mysql/) - [MySQL Workbench Alternative](/alternatives/mysql-workbench/) - [What's New in MySQL 26.7](/blog/2026-08-22-whats-new-mysql-26-7/) ## A Confirmation Before DELETE Without WHERE, on Every Engine Jam SQL Studio asks before it runs a DELETE or UPDATE with no WHERE clause, a DROP or TRUNCATE, or a script it cannot analyse — on SQL Server, PostgreSQL, MySQL, Oracle and SQLite connections alike. [Download Jam SQL Studio Free](/#download) [Destructive-statement confirmation](/docs/query-editor/#destructive-confirmation) Free for personal use • No account required • Mac, Windows, Linux