Mysql procedure does not recognize WHERE and displays error

Question

Running procudure below Mysql Workbench shows 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."

However, the only UPDATE of the past uses the primary key of the table.

    DELIMITER $$ 
    CREATE PROCEDURE Editar_Produtos(
     IN Id_Pro INT,
     IN Cod_Int VARCHAR (21),
     IN Nom_Pro VARCHAR (55),
     IN Id_Cat INT,
     IN Id_Cla INT,
     IN Id_Sub INT,
     IN Id_Mil INT,
     IN Id_Med INT,
     IN Id_Mar INT,
     IN Vlr_Cust DECIMAL (9,2),
     IN Vlr_Vend DECIMAL (9,2),
     IN Id_Un INT,
     IN Id_Tpd INT
    )
    BEGIN
     UPDATE ktsproduto 
     SET COD_INT = Cod_Int, 
         NOM_PRO = Nom_Pro, 
         ID_CAT = Id_Cat, 
         ID_CLA = Id_Cla, 
         ID_SUB = Id_Sub, 
         ID_MIL = Id_Mil, 
         ID_MED = Id_Med, 
         ID_MAR = Id_Mar, 
         VLR_CUST = Vlr_Cust, 
         VLR_VEND = Vlr_Vend, 
         ID_UN = Id_Un,      
         ID_TPD = Id_Tpd 
     WHERE ID_PRO = Id_Pro;    
    END $$
    DELIMITER ;

    CALL Editar_Produtos(
    '417',"KTSKTSKTS","KENNEDY TEIXIERA",'1','1','1','1','1','1','250.99','500.99','1','1');

I do not understand why the mistake, since I am using the clause WHERE with the primary key of the table.

If possible, I don’t want to rotate SET SQL_SAFE_UPDATES=0;, for safety reasons.

Answer 1

Mysql is considering both ID_PRO and Id_Pro in your Where as Id_Pro which is passed as input from your database. In this case, if the Safe Update, all records would be updated if found any column with valid name. The same can happen when your application is used outside the MySQL Workbench. As there are "case sensitive" differences between Windows and Linux, this behavior may vary depending on your operating system.

One way to solve this problem is by identifying the tables next to the columns:

DELIMITER $$ 
CREATE PROCEDURE Editar_Produtos(
 IN Id_Pro INT,
 IN Cod_Int VARCHAR (21),
 IN Nom_Pro VARCHAR (55),
 IN Id_Cat INT,
 IN Id_Cla INT,
 IN Id_Sub INT,
 IN Id_Mil INT,
 IN Id_Med INT,
 IN Id_Mar INT,
 IN Vlr_Cust DECIMAL (9,2),
 IN Vlr_Vend DECIMAL (9,2),
 IN Id_Un INT,
 IN Id_Tpd INT
)
BEGIN
 UPDATE ktsproduto 
 SET ktsproduto.COD_INT = Cod_Int, 
     ktsproduto.NOM_PRO = Nom_Pro, 
     ktsproduto.ID_CAT = Id_Cat, 
     ktsproduto.ID_CLA = Id_Cla, 
     ktsproduto.ID_SUB = Id_Sub, 
     ktsproduto.ID_MIL = Id_Mil, 
     ktsproduto.ID_MED = Id_Med, 
     ktsproduto.ID_MAR = Id_Mar, 
     ktsproduto.VLR_CUST = Vlr_Cust, 
     ktsproduto.VLR_VEND = Vlr_Vend, 
     ktsproduto.ID_UN = Id_Un,      
     ktsproduto.ID_TPD = Id_Tpd 
 WHERE ktsproduto.ID_PRO = Id_Pro;    
END $$
DELIMITER ;

CALL Editar_Produtos(
'417',"KTSKTSKTS","KENNEDY TEIXIERA",'1','1','1','1','1','1','250.99','500.99','1','1');