Mysql Question posted in
The official MySQL documentation can be found here.

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

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;
Tags:

3

Answers


  1. 0

    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

    ... where `id`=ID ...

    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 case qrcode.id cannot be treated as a variable..

    Login or Signup to reply.
  2. 0

    Try not use ID as parameter in store 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
    Login or Signup to reply.
  3. 0

    1. 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.

    2. As explained by Akina in his answer, you need to be mindful of variable scope.

    3. There is no need to turn off FOREIGN_KEY_CHECKS.

    4. SELECT @AFFECTED_ROWS; should be SELECT AFFECTED_ROWS;

    Here’s a db<>fiddle.

    You can achieve the desired functionality with ON DELETE SET NULL.

    FOREIGN KEY (`parentId`) REFERENCES `QRCode` (`id`) ON DELETE SET NULL

    Here’s another db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search