MySQL Bugs: #99865: Server don' delete roqs because of safe

Bug #99865	Server don' delete roqs because of safe_mode even using primary key
Submitted:	13 Jun 2020 12:32	Modified:	14 Jun 2020 16:54
Reporter:	Pedro da Costa	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.20/5.7/5.6	OS:	Windows (10 2004)
Assigned to:		CPU Architecture:	x86 (Coore i5)

Description:
Mysql don't deletes Rows, because Error Code: 1175, but i use the primary key in the where clause.

How to repeat:
CREATE TABLE Discussant(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           Work_at_Event_id INT,      
           Person_id INT
       );
       
       INSERT INTO Discussant VALUES (NULL,1,1);
       INSERT INTO Discussant VALUES (NULL,1,2);
       INSERT INTO Discussant VALUES (NULL,1,3);
       INSERT INTO Discussant VALUES (NULL,1,4);
       INSERT INTO Discussant VALUES (NULL,1,5);

DELETE FROM Discussant 
WHERE id IN (SELECT id FROM (SELECT * FROM Discussant) d WHERE Work_at_Event_id = 1 AND Person_id NOT IN (1,2,3,4,6));

Error message
14:04:10	DELETE FROM Discussant  WHERE id IN (SELECT id FROM (SELECT * FROM Discussant) d WHERE Work_at_Event_id = 1 AND Person_id NOT IN (1,2,3,4,6))	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.	0.015 sec

       

Suggested fix:
The delete command should be executed, because it full fills the necessary requirements id is PRIMARY KEY

Thank you for the bug report. I couldn't repeat the test case provided:

Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE jjj;
Query OK, 1 row affected (0.02 sec)

mysql> USE jjj
Database changed
mysql> CREATE TABLE Discussant(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->            Work_at_Event_id INT,
    ->            Person_id INT
    ->        );
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql>        INSERT INTO Discussant VALUES (NULL,1,1);
Query OK, 1 row affected (0.02 sec)

mysql>        INSERT INTO Discussant VALUES (NULL,1,2);
Query OK, 1 row affected (0.00 sec)

mysql>        INSERT INTO Discussant VALUES (NULL,1,3);
Query OK, 1 row affected (0.00 sec)

mysql>        INSERT INTO Discussant VALUES (NULL,1,4);
Query OK, 1 row affected (0.00 sec)

mysql>        INSERT INTO Discussant VALUES (NULL,1,5);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> DELETE FROM Discussant
    -> WHERE id IN (SELECT id FROM (SELECT * FROM Discussant) d WHERE Work_at_Event_id = 1 AND Person_id NOT IN (1,2,3,4,6));
Query OK, 1 row affected (0.02 sec)

mysql>

Hi, soory i didn't mentioen, that i ran the code in mysql Workbench.

Where it still produces the error

Thank Shane for the note.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 Source distribution BUILT: 2020-MAY-31

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > SET sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > CREATE DATABASE hhh;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > USE hhh
Database changed
mysql 8.0 > CREATE TABLE Discussant(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->            Work_at_Event_id INT,
    ->            Person_id INT
    ->        );
Query OK, 0 rows affected (0.02 sec)

mysql 8.0 >
mysql 8.0 >        INSERT INTO Discussant VALUES (NULL,1,1);
Query OK, 1 row affected (0.01 sec)

mysql 8.0 >        INSERT INTO Discussant VALUES (NULL,1,2);
Query OK, 1 row affected (0.02 sec)

mysql 8.0 >        INSERT INTO Discussant VALUES (NULL,1,3);
Query OK, 1 row affected (0.00 sec)

mysql 8.0 >        INSERT INTO Discussant VALUES (NULL,1,4);
Query OK, 1 row affected (0.00 sec)

mysql 8.0 >        INSERT INTO Discussant VALUES (NULL,1,5);
Query OK, 1 row affected (0.00 sec)

mysql 8.0 >
mysql 8.0 > DELETE FROM Discussant
    -> WHERE id IN (SELECT id FROM (SELECT * FROM Discussant) d WHERE Work_at_Event_id = 1 AND Person_id NOT IN (1,2,3,4,6));
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 8.0 >