Executing stored procedure delete all rows from table despite WHERE clause setting

Question

I created this little stored procedure that deletes a row from my 'qrcode' table.

Due to the constraint, I update all child rows before, it works fine.

However, performing this procedure....deletes all rows from the table!!!!

I added a log and I see that the ID parameter is passed correctly.

This is how the delete query ignored the WHERE clause.

At worst, I should have an error, but there, no. It's quite violent.

I've searched everywhere, but I really don't understand what I'm doing here.

Anyone to help me?

Thanks in advance.

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_qrcode`(   IN ID INT, OUT AFFECTED_ROWS INT)
BEGIN

DECLARE EXIT HANDLER FOR sqlexception
BEGIN
    ROLLBACK;
    RESIGNAL;
END;
    
SELECT concat('ID=', ID) as log;

START TRANSACTION;

SET FOREIGN_KEY_CHECKS=0;
update `qrcode` set `parentId`=NULL where `parentId`=ID;
SET FOREIGN_KEY_CHECKS=1;

delete FROM `qrcode` where `id`=ID;

SET AFFECTED_ROWS = ROW_COUNT();
SELECT @AFFECTED_ROWS;

COMMIT;

END

The table definition :

CREATE TABLE `QRCode` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `parentId` int DEFAULT NULL,
  `rank` int DEFAULT '0',
  `enabled` tinyint NOT NULL DEFAULT '1',
  `createdBy` int DEFAULT NULL,
  `createdDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedBy` int DEFAULT NULL,
  `updatedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `IDX_QRCode_ParentId` (`parentId`),
  CONSTRAINT `qrcode_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `QRCode` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

By performing the same operation in the SQL Workbench editor, it works! Obviously, I don't understand anything! Please help.

SET @ID =28;
SET FOREIGN_KEY_CHECKS=0;
update `qrcode` set `parentId`=NULL where `parentId`=@ID;
delete FROM `qrcode` where `id`=@ID;
SET FOREIGN_KEY_CHECKS=1;

Answer 1

Try not use ID as parameter in stored procedure.

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_qrcode`(   IN delete_id INT, OUT AFFECTED_ROWS INT)
BEGIN

DECLARE EXIT HANDLER FOR sqlexception
BEGIN
    ROLLBACK;
    RESIGNAL;
END;
    
SELECT concat('ID=', delete_id) as log;

START TRANSACTION;

SET FOREIGN_KEY_CHECKS=0;
update `qrcode` set `parentId`=NULL where `parentId`=delete_id;
SET FOREIGN_KEY_CHECKS=1;

delete FROM `qrcode` where `id`=delete_id;

SET AFFECTED_ROWS = ROW_COUNT();
SELECT @AFFECTED_ROWS;

COMMIT;

END