MySQL error 1175 has its own Stack Overflow question with more than three million views, and most of its answers explain how to switch safe-updates mode off. That's a shame, because the mode does catch the classic accident. It just doesn't catch all of it. And it blocks a fair amount of correct SQL, which is why people switch it off in the first place.
So we ran 22 write statements (UPDATE, DELETE, a CALL and a TRUNCATE) against MySQL 8.4 and MariaDB 11.4 with sql_safe_updates = 1, each one against a fresh 1000-row table, and counted what actually changed.
The test
One table: orders (id PRIMARY KEY, customer_id INDEXED, status, total), 1000 rows, 100 of them status = 'new' and 20 per customer. A small customers table for the join case. Default server settings, InnoDB, the official Docker images (MySQL 8.4.13 and MariaDB 11.4.13). Every statement runs in its own session after SET SESSION sql_safe_updates = 1, against a freshly rebuilt table.
The script is measure.sh with its list of cases, cases.sql.txt. Put both in one folder; it needs only Docker: ./measure.sh mysql:8.4, then ./measure.sh mariadb:11.4.
| statement | MySQL 8.4 | MariaDB 11.4 | rows changed when it ran |
|---|---|---|---|
UPDATE orders SET status='x' |
blocked | blocked | |
DELETE FROM orders |
blocked | blocked | |
... WHERE 1=1 / WHERE TRUE
|
blocked | blocked | |
... WHERE id = id |
blocked | blocked | |
... WHERE id IS NOT NULL |
blocked | blocked | |
... WHERE id = 5 OR 1 (the typo) |
blocked | blocked | |
... WHERE id > 0 |
ran | ran | 1000 of 1000 |
... WHERE customer_id > 0 |
ran | ran | 1000 of 1000 |
... WHERE id <> -1 |
ran | blocked | 1000 of 1000 |
UPDATE orders SET status='x' LIMIT 100000 |
ran | ran | 1000 of 1000 |
DELETE FROM orders LIMIT 100000 |
ran | blocked | 1000 of 1000 |
TRUNCATE TABLE orders |
ran | ran | 1000 of 1000 |
... WHERE customer_id = 7 |
ran | ran | 20 (correct) |
... WHERE status = 'new' |
blocked | blocked | (should be 100) |
... WHERE customer_id + 0 = 7 |
blocked | blocked | (should be 20) |
... WHERE status = 'new' LIMIT 100000 |
ran | ran | 100 (correct) |
multi-table UPDATE ... JOIN, with or without WHERE c.id > 0
|
blocked | blocked | |
UPDATE through a view, no WHERE |
blocked | blocked | |
CALL a procedure that updates with no WHERE |
blocked | blocked | |
... WHERE id IN (SELECT id FROM (SELECT id FROM orders) s) |
blocked | blocked |
One table shape, one size. Some of these outcomes are the optimizer's call, so on a bigger or differently indexed table some lines of this table can come out the other way. That's part of the finding, not a footnote to it.
What the table says
It isn't checking for a WHERE clause. WHERE 1=1 is a WHERE clause, and it's blocked. WHERE id > 0 selects the same 1000 rows, and it runs. What the server checks is whether it can find the rows through a key, which matches the MySQL manual: rows can be changed "only by specifying the key values that identify them, or a LIMIT clause, or both". A range on the primary key that covers the whole table passes. Nothing checks how much of the table the range covers.
A LIMIT is a pass, whatever the number. LIMIT 100000 on a 1000-row table is a full-table update with an extra word. Both servers ran it for UPDATE. On DELETE they split. MariaDB's documentation for sql_safe_updates says a LIMIT only counts together with an indexed column in the WHERE, and it held that line for the DELETE. It didn't for the UPDATE: the bare UPDATE ... LIMIT 100000 changed every row on MariaDB too. MySQL doesn't ask for the indexed column at all, and on our table it deleted everything.
The optimizer gets a vote. WHERE id <> -1 ran on MySQL and was blocked on MariaDB. MariaDB's docs say "simply mentioning an indexed column in a WHERE clause is not enough, optimizer must actually use it", and the MySQL manual warns that a statement can fail "even with a key specified in the WHERE clause, if the optimizer decides not to use the index". So the same statement can pass or fail depending on the table's size and statistics. Don't read a pass as "this is safe".
TRUNCATE isn't covered. It's DDL, not a DELETE, so the mode never sees it.
The false positives are real. WHERE status = 'new' is a correct, narrow update of 100 rows. It's blocked because status has no index. customer_id + 0 = 7 is blocked because the expression stops the index being used. These are the errors behind that Stack Overflow question, and they're why the "turn it off" answers are so popular. The usual workaround, tacking on a LIMIT, is the one form that slipped through on both servers.
What it catches well: the bare statement, 1=1, the OR 1 typo, and writes through views, procedures and joins. If the accident you're worried about is a forgotten WHERE, it works. If it's a wrong WHERE, it doesn't help.
Guards that read the SQL text do worse
Client-side guards that look for the word WHERE have the opposite problem: they can be fooled by spelling. In Supabase MCP issue #564, opened on November 16, 2026 and still open when this was written, the confirmation that fires for update films set ... doesn't fire for update public.films* set ... or update only (films) set ..., and the update changes every row.
Postgres has no server setting like sql_safe_updates at all. The pg-safeupdate extension adds one. Per its README, it raises an error when UPDATE or DELETE runs without conditions, including inside a CTE, and its example for updating every row on purpose is WHERE 1=1. We didn't run it for this piece.
The check that doesn't depend on the WHERE
Every guard above tries to decide from the statement whether it's too broad. The number that actually answers the question is the row count, and you can see it before it's final:
START TRANSACTION;
UPDATE orders SET status = 'x' WHERE id > 0;
SELECT ROW_COUNT(); -- 1000: that's the whole table
ROLLBACK;
On both servers that printed 1000, and after the rollback no row had changed. In Postgres (we checked on 16.16), BEGIN followed by the same UPDATE prints UPDATE 1000, and ROLLBACK undoes it. One caveat on the MySQL side: DDL commits implicitly. A TRUNCATE between START TRANSACTION and ROLLBACK left our test table empty. Postgres can roll a TRUNCATE back.
It's a habit, not a setting, so it's easy to skip at exactly the moment you need it. Which leads to the one opinion in this piece. For writes against a database that matters, keep safe-updates on if you use MySQL or MariaDB, because it does catch the forgotten WHERE. But don't treat a pass as approval. Make the row count the thing you look at before you commit.
Where Tabellum sits
Tabellum, the database client this blog belongs to, doesn't try to guess which writes are dangerous. Per its user guide, anything it "cannot prove to be a read" stops at an approval dialog that shows the statements, their kind "and, where the database can tell, how many rows they would change", with Run in transaction to run them all or none. The guide says the dialog "is the same whether you typed the SQL, edited cells, applied a design or the AI agent proposed it". So WHERE id > 0 and LIMIT 100000 stop there like any other write.
What it can't do is read the number for you. A dialog on every write is only as good as the glance at the count, and people click through dialogs they see often. The transaction above, in any client, is the same check by hand.
Originally published at https://tabellum.dudko.dev/blog/update-without-where-mysql-safe-updates.
Top comments (0)