MySQL - Why is Safe Update Mode blocking this UPDATE command?

Question

I have an actor table that looks like this:

| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
| 1        | Jack       | Nicholson | 2019-06-02 00:00:00 |

Column actor_id is a primary key with auto increment.

When I try to update the table like so:

UPDATE actor
SET last_name = 'foo'
WHERE last_update > '2019-06-02 00:00:00';

I get blocked by MySQL's safe update mode with this error:

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.

Indeed column last_update is not a KEY column, so based on this SO answer I've come up with the following workaround:

CREATE TEMPORARY TABLE IF NOT EXISTS ids AS (SELECT actor_id FROM actor WHERE last_update > '2019-06-02 00:00:00');

UPDATE actor
SET last_name = 'foo'
WHERE actor_id IN (SELECT actor_id FROM ids);

But again I'm blocked with a 1175 error.

Why is safe update mode blocking me here? Can I work around it without disabling safe update mode?

Just disable the safe mode if you know the statement is the right thing to do? If you want it in general, you can reenabale it afterwards. — sticky bit, Jun 02 '19 at 17:55
Thanks. That's very pragmatic of you but doesn't quite answer my question :) — urig, Jun 02 '19 at 18:01
[why](https://dev.mysql.com/doc/refman/5.5/en/mysql-tips.html#safe-updates) do they think that they need to 'protect' you from doing false (or slow) sql-statements? — Luuk, Jun 02 '19 at 18:10
@Luuk, because it's very common for beginners to make this sort of mistake, and recovering from the mistake might take a lot of work. — Bill Karwin, Jun 02 '19 at 18:12
@Bill, but some 'experts' hate this kind of things... LOL (I am not calling meself an expert here!) — Luuk, Jun 02 '19 at 18:13
I would also comment that a _true_ expert doesn't hate this sort of thing, because they understand that it helps to avoid mistakes. **"An expert is someone who knows some of the worst mistakes that can be made in his subject, and how to avoid them." — Werner Heisenberg** — Bill Karwin, Jun 02 '19 at 18:18

score 2 · Answer 1 · answered Jun 02 '19 at 18:09

You can work around this error by making the column a KEY column. In other words, create an index (aka key) on the column.

mysql> set sql_safe_updates=ON;

mysql> UPDATE actor SET last_name = 'foo' WHERE last_update > '2019-06-02 00:00:00';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

mysql> alter table actor add key (last_update);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE actor SET last_name = 'foo' WHERE last_update > '2019-06-02 00:00:00';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

The point of the error is to prevent you from unintentionally locking every row in the table when you have a condition on a non-indexed column.

The way locking works, it locks all rows that the query examines to test the condition, not just all rows that satisfy the condition. If you run a query with a condition that tests an unindexed query, it has to examine every row in the table, which probably locks way more than you intended it to lock.

Swell explanation, thank you! Though the workaround is a bit heavy handed for the task wouldn't you say? :) — urig, Jun 02 '19 at 18:15
You might find that it's helpful for other reasons to have an index on that column. Also, you can turn off [sql_safe_updates](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_safe_updates) just in your session, without changing the global setting for the option. — Bill Karwin, Jun 02 '19 at 18:16

MySQL - Why is Safe Update Mode blocking this UPDATE command?

1 Answers1