Thanks for the report.
Same happens on all of MariaDB 5.5-10.4 and MySQL 5.6-8.0 didn't try MySQL 5.5, but most likely there, too).
There is definitely room for improvement here.
First of all, documentation in regard to sql_safe_updates is insufficient, there is definitely more to it than the description says. MySQL manual is somewhat better in this regard, as it at least has a separate section for safe updates, but it's also incomplete, as this report shows. The feature is vague, it needs to be described as precisely as possible.
Further, for the particular example from the bug description, one can argue that the keys aren't actually used here, as the table is empty. It can be seen if we switch off sql_slave_updates and run EXPLAIN, e.g. on 10.3:
MariaDB [test]> CREATE TABLE `bork` (`a` INT, `b` INT, PRIMARY KEY (`a`), KEY (`b`));
|
Query OK, 0 rows affected (0.18 sec)
|
|
MariaDB [test]> EXPLAIN DELETE FROM bork WHERE `a`=1 or `b`=2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | bork | ALL | PRIMARY,b | NULL | NULL | NULL | 1 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.02 sec)
|
Even so, the error message is bad:
MariaDB [test]> SET sql_safe_updates= ON;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2;
|
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
|
It's all wrong, the UPDATE has a WHERE clause which uses key columns; optimizer chooses not to use keys, but that's another story.
But further, even if we insert some data into the table and make DELETE use the keys, it still doesn't work:
MariaDB [test]> SET sql_safe_updates= OFF;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> CREATE TABLE `bork` (`a` INT, `b` INT, PRIMARY KEY (`a`), KEY (`b`)) ENGINE=MyISAM;
|
Query OK, 0 rows affected (0.05 sec)
|
|
MariaDB [test]> INSERT INTO bork VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
|
Query OK, 8 rows affected (0.00 sec)
|
Records: 8 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> EXPLAIN DELETE FROM bork WHERE `a`=1 or `b`=2;
|
+------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
|
| 1 | SIMPLE | bork | index_merge | PRIMARY,b | PRIMARY,b | 4,5 | NULL | 2 | Using union(PRIMARY,b); Using where |
|
+------+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> SET sql_safe_updates= ON;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> DELETE FROM bork WHERE `a`=1 or `b`=2;
|
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
|
So, obviously there the condition is stricter than just using a key. Maybe the problem is that it uses index_merge, more than one key. I'm not sure if it's intentional or not, but it does need to be either fixed or documented.
Finally, on a somewhat separate but related note, it's inconvenient that EXPLAIN doesn't work either:
MariaDB [test]> SET sql_safe_updates= ON;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> EXPLAIN DELETE FROM bork WHERE `a`=1 or `b`=2;
|
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
|
This part was actually fixed recently in upstream 5.7+, in the scope of bug #91080.
So, to summarize,
- documentation definitely needs improvement;
- the error message could be made better;
- the code itself might need to be fixed or not, depending on what the intended behavior is;
- EXPLAIN would be good to fix.
MDEV-26745 sql_safe_updates blocks queries that actually do use a key column in WHERE
Thanks for the report.
Same happens on all of MariaDB 5.5-10.4 and MySQL 5.6-8.0 didn't try MySQL 5.5, but most likely there, too).
There is definitely room for improvement here.
First of all, documentation in regard to sql_safe_updates is insufficient, there is definitely more to it than the description says. MySQL manual is somewhat better in this regard, as it at least has a separate section for safe updates, but it's also incomplete, as this report shows. The feature is vague, it needs to be described as precisely as possible.
Further, for the particular example from the bug description, one can argue that the keys aren't actually used here, as the table is empty. It can be seen if we switch off sql_slave_updates and run EXPLAIN, e.g. on 10.3:
Even so, the error message is bad:
It's all wrong, the UPDATE has a WHERE clause which uses key columns; optimizer chooses not to use keys, but that's another story.
But further, even if we insert some data into the table and make DELETE use the keys, it still doesn't work:
Records: 8 Duplicates: 0 Warnings: 0So, obviously there the condition is stricter than just using a key. Maybe the problem is that it uses index_merge, more than one key. I'm not sure if it's intentional or not, but it does need to be either fixed or documented.
Finally, on a somewhat separate but related note, it's inconvenient that EXPLAIN doesn't work either:
This part was actually fixed recently in upstream 5.7+, in the scope of bug #91080.
So, to summarize,