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;
3
Answers
Use local variables names which are equal to the columns names NEVER !!!
When they have the same name (case-insensitively) then the name is unconditionally treated as a variable name. Hence your
compares a variable and a variable, so it is always TRUE (until the variable is NULL).
Or at least specify the table alias:
... where qrcode.id=ID ...
. In this caseqrcode.id
cannot be treated as a variable..Try not use
ID
as parameter in store procedure.Be mindful of identifier case-sensitivity. If you are going to call your table
QRCode
, then reference it as such, otherwise you will get bitten when you find yourself deploying to a platform that is case-sensitive.As explained by Akina in his answer, you need to be mindful of variable scope.
There is no need to turn off
FOREIGN_KEY_CHECKS
.SELECT @AFFECTED_ROWS;
should beSELECT AFFECTED_ROWS;
Here’s a db<>fiddle.
You can achieve the desired functionality with
ON DELETE SET NULL
.Here’s another db<>fiddle.