| Bug #91080 | sql_safe_updates behaves inconsistently between delete and select | ||
|---|---|---|---|
| Submitted: | 30 May 2018 17:37 | Modified: | 27 Sep 2018 17:55 |
| Reporter: | Nakoa Mccullough | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.7.22, 8.0.11 | OS: | Oracle Linux |
| Assigned to: | CPU Architecture: | Any | |
[7 Jun 2018 5:39]
MySQL Verification Team
Hello Nakoa, Thank you for the report and test case. Observed the issue on 5.7.22 when --safe-updates on. As you noted, for DELETE - issue occurring when 37136 values in the 'IN' clause(up-to 37135 no issues observed). Thanks, Umesh
[8 Jun 2018 7:06]
MySQL Verification Team
-- 5.7.22 - default settings
mysql> create database if not exists test;
mysql> use test;
mysql> drop table if exists del_test;
mysql> CREATE TABLE `del_test` (`id` int(11) NOT NULL, UNIQUE KEY `id` (`id`))ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> set @id:=0;
mysql> insert into `del_test` values
-> (@id:=@id+1)
-> , (@id:=@id+1)
-> , (@id:=@id+1)
-> , (@id:=@id+1);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into `del_test`(`id`) select @id:=@id+1 from `del_test` k1, `del_test` k2, `del_test` k3, `del_test` k4,`del_test` k5,`del_test` k6, `del_test` k7, `del_test` k8, `del_test` k9,`del_test` k0,`del_test` ka, `del_test` kb, `del_test` kc, `del_test` kd limit 10000000;
Query OK, 10000000 rows affected (1 min 13.07 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
# 91080_DELETE.sql - DELETE FROM test.del_test WHERE id IN (1,2....37135); - this works
bin/mysql -uroot -S /tmp/mysql_ushastry57.sock -e "SELECT COUNT(*) from test.del_test;"
+----------+
| COUNT(*) |
+----------+
| 10000004 |
+----------+
bin/mysql -uroot -S /tmp/mysql_ushastry57.sock --safe-updates=1 < /tmp/91080_DELETE.sql
bin/mysql -uroot -S /tmp/mysql_ushastry57.sock -e "SELECT COUNT(*) from test.del_test;"
+----------+
| COUNT(*) |
+----------+
| 9962869 |
+----------+
# 91080_DELETE.sql - DELETE FROM test.del_test WHERE id IN (1,2....37136); - this complains
bin/mysql -uroot -S /tmp/mysql_ushastry57.sock -e "SELECT COUNT(*) from test.del_test;"
+----------+
| COUNT(*) |
+----------+
| 10000004 |
+----------+
bin/mysql -uroot -S /tmp/mysql_ushastry57.sock --safe-updates=1 < /tmp/91080_DELETE.sql
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
bin/mysql -uroot -S /tmp/mysql_ushastry57.sock -e "SELECT COUNT(*) from test.del_test;"
+----------+
| COUNT(*) |
+----------+
| 10000004 |
+----------+
[8 Jun 2018 10:06]
Sreeharsha Ramanavarapu
Posted by developer:
DELETE FROM test.del_test WHERE id IN (1,372....37136);
g++ -g -std=c++0x genereate_delete.cpp
b sql_delete.cc:141
b sql_delete.cc:328
mysql> source /home/sreeharsha/changes/17668844/code-5.7/del_explain.txt;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
+---------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3170 | Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query. |
| Error | 1175 | You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
So range optimizer couldn't be used because it exceeded the max memory size.
The below code from "Sql_cmd_delete::mysql_delete" will only check whether range optimizer is being used. If not it assumes all tables will be deleted. Hence the error. This fails even for EXPLAIN DELETE...
/* If running in safe sql mode, don't allow updates without keys */
if (table->quick_keys.is_clear_all())
{
thd->server_status|=SERVER_QUERY_NO_INDEX_USED;
if (safe_update && !using_limit)
{
free_underlaid_joins(thd, select_lex);
my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
DBUG_RETURN(TRUE);
}
}
[20 Jun 2018 5:26]
Sreeharsha Ramanavarapu
Posted by developer: This problem can happen even with data truncation: CREATE TABLE del_test (c1 INT NOT NULL, c2 VARCHAR(200) NOT NULL, UNIQUE KEY idx1 (c1), UNIQUE KEY idx2 (c2)); Query OK, 0 rows affected (0.18 sec) mysql> INSERT INTO del_test VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN DELETE FROM del_test WHERE c2 IN(1,2222); +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | DELETE | del_test | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> DELETE FROM del_test WHERE c2 IN(1,2222); Query OK, 0 rows affected, 4 warnings (0.01 sec) mysql> show warnings; +---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'b' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'c' | | Warning | 1292 | Truncated incorrect DOUBLE value: 'd' | +---------+------+---------------------------------------+ 4 rows in set (0.00 sec) mysql> mysql> SET SESSION sql_safe_updates=1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> DELETE FROM del_test WHERE c2 IN(1,2222); ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
[25 Jun 2018 6:05]
Sreeharsha Ramanavarapu
Posted by developer: Our documentation states: https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_safe-updat... "Permit only those UPDATE and DELETE statements that specify which rows to modify by using key values. If you have set this option in an option file, you can override it by using --safe-updates on the command line. See Section 4.5.1.6, “mysql Tips”, for more information about this option. " https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html "It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents. " Notice the difference. One place mentions " which rows to modify by using key values" and another says "you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause" This is interpreted in the code for single table UPDATE/DELETE as does table have any "quick_keys" set. For multi-table UPDATE/DELETE this becomes a check for "tab->quick()". Safe updates error is returned when range optimization or index lookup isn't possible. This can happen with: 1) RightNow's DML exceeds 'range_optimizer_max_mem_size' and because of this quick_keys isn't set. UPDATE/DELETE code interprets this as no "key values" 2) Data truncation when column with index is a varchar and value is an integer. Example posted on bug page. Possible fix approaches: --------------------------------- 1) Ignore the requirement for "key values" or index usage that is present now. Just add an condition to check if the WHERE conditions are present and they aren't constant before returning the safe updates error. Patch for this approach can be seen on the bug page. Chaithra has commented on some of her concerns to my patch on mail. Pros: a) Simplifies the conditions for the user. Cons: a) Approach to deal with multi-table UPDATE/DELETE isn't clear. (See error_if_full_join()) b) Documentation will need to be modified. Will it be interpreted as change of behavior in GA version? 2) Append the warning (data truncation / 'range_optimizer_max_mem_size' exceeded) to the safe update error. Pros: a) Informs user clearly why safe updates error is thrown. Cons: a) User might say that table scan is a perfectly legitimate way delete/update a subset of the rows.
[25 Jun 2018 19:30]
Nakoa Mccullough
This is just very inconsistent behavior. For instance, the query will successfully if a limit of the size of the 'IN' clause is added. I tweaked the above test case to demonstrate this:
[koa.mccullough@bastionhg01 ~]$ let x=37130; while [ 0==0 ]; do echo -n '.'; seq 1 $x|awk 'BEGIN{printf("DELETE FROM `del_test` WHERE id IN(")}{if(NR>1){printf(",")}{printf("%d", $1)}}END{printf(");\n")}'| mysql; if [ $? -ne 0 ]; then echo; echo $x; seq 1 $x|awk 'BEGIN{printf("SELECT * FROM `del_test` WHERE id IN (")}{if(NR>1){printf(",")}{printf("%d",$1)}}END{printf(");\n")}'|mysql; seq 1 $x|awk 'BEGIN{printf("DELETE FROM `del_test` WHERE id IN (")}{if(NR>1){printf(",")}{printf("%d",$1)}; last_x=$1}END{printf(") LIMIT %d;\n", last_x)}'|mysql; break; fi; let x=$x+1; done
.......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
37136
The problem with adding limits every where is that this will trigger RBR when running in MIXED mode. Not to mention that it makes the code that generates these statement much more complicated. MySQL has this information and should be able to apply an implicit limit if the index is unique once it hits the optimizer.
--Koa McCullough
[26 Jun 2018 4:29]
Sreeharsha Ramanavarapu
Posted by developer: Koa, The workaround for this problem would be to increase the value of range_optimizer_max_mem_size (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_optimize...). Alternately, you can turn off the safe-updates option altogether: https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_safe-updat... . We are working on clarifying the documentation and returning a better error message.
[26 Jun 2018 15:57]
Nakoa Mccullough
Turning off safe updates has always been an option but, that defeats the purpose of having the feature in the first place. The purpose of safe updates is to make sure that you are using a key to delete data. We hand the server a list of primary keys (the best possible choice for a key to delete off of) and get an error saying we are not deleting off of a key is a very poor user experience. Changing the value of range_optimizer_mem_size is a bandaid not a fix. We don't want to be in the position of having to change server configuration when ever a dev decides to write a test that pushes the bounds of what the system can handle. We want to encourage that kind of testing not make it painful. --Koa McCullough
[27 Sep 2018 17:55]
Paul DuBois
Posted by developer: Fixed in 5.7.24, 8.0.13. For DELETE and UPDATE that produced an error due to sql_safe_updates being enabled, the error message was insufficiently informative. The message now indicates that data truncation occurred or the range_optimizer_max_mem_size value was exceeded. Additionally: (1) Using EXPLAIN for such statements does not produce an error, enabling users to see from EXPLAIN output why an index is not used; (2) For multiple-table deletes and updates, an error is produced with safe updates enabled only if the target table or tables use a table scan.
Description: With a large 'IN' clause on an indexed column a DELETE will fail with a: 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 A SELECT with an identical WHERE clause will run just fine. The exact number of values that triggers this bug looks to be configuration dependent so finding the exact value to trigger the bug takes a while. There is a bash/awk script in to help reproduce the error in the 'How to repeat' section. On the system we tested on the error occurred at 37136 values in the 'IN' clause. You may need to adjust the start point for x in the script below otherwise it can take a very long time to find the boundary condition. Note the last DELETE fails but, the SELECT succeeds. How to repeat: #Set your my.cnf to point to a test DB echo 'CREATE TABLE `del_test` (`id` int(11) NOT NULL, UNIQUE KEY `id` (`id`))ENGINE=InnoDB DEFAULT CHARSET=latin1' | mysql let x=37000; while [ 0==0 ]; do echo -n '.'; seq 1 $x|awk 'BEGIN{printf("DELETE FROM `del_test` WHERE id IN (")}{if(NR>1){printf(",")}{printf("%d", $1)}}END{printf(");\n")}'|mysql; if [ $? -ne 0 ]; then echo; echo $x; seq 1 $x|awk 'BEGIN{printf("SELECT * FROM `del_test` WHERE id IN (")}{if(NR>1){printf(",")}{printf("%d", $1)}}END{printf(");\n")}'|mysql;break; fi; let x=$x+1; done Suggested fix: Make a DELETE and a SELECT use the same criteria to determine if a query uses a key column in respect to sql_safe_updates