Using stored procedure parameters in delete queries deletes all records

Question

Alright, y'all, here's the deal.

I want to create a stored procedure to delete values from certain tables. This is what it looks like:

CREATE OR REPLACE PROCEDURE `delete_sap`(sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

    START TRANSACTION;
    SELECT sap_to_delete as ' '; -- Just want to make sure it's coming in. It is.
    DELETE FROM data01 WHERE sapid = sap_to_delete;
    DELETE FROM sap_has_owners WHERE sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_reviewers WHERE sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_readonly WHERE sap_sapid=sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_editors WHERE sap_sapid=sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM page WHERE sapid=sap_to_delete;
    DELETE FROM element WHERE element.sapid=sap_to_delete;
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END//

DELIMITER ;

I realize I could alter some of these tables to ON DELETE CASCADE based on foreign keys, but this isn't my database so ...

Anyway, currently, when I call with CALL delete_sap("idtodelete"), EVERYTHING is deleted. Or, all content from every table that I only mean to delete certain records from. I receive no warnings. I've mistyped something in some way that makes this delete everything.

Let's say my data01 table is built like so:

DROP TABLE IF EXISTS `data01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `data01` (
  `sapid` char(10) NOT NULL,
  `pageid` char(10) NOT NULL,
  `elemid` char(10) NOT NULL,
  `json` longtext DEFAULT NULL,
  PRIMARY KEY (`sapid`,`pageid`,`elemid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

and I've put a few things in it:

| sapid      | pageid     | elemid     | json  
--------------------------------------------------------------------------        
| 3Fdk...jo8 | HhN...ajn8 | xYh5...uUz | { "os": "Mac", "name": "Safari" }                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| 6moo...Vwm | 0wR...2n47 | Bo3F...1lC | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 6moo...Vwm | 0wR...2n47 | DRvb...oje | NULL   
| 6moo...Vwm | kYAy...AJq | GhJG...KzA | {"title":"My test sap","preparedBy":"Ghost McFee","date":"2023-09-13T03:23:21.062Z"}
| p4th...fvW | Cokg...Ly4 | thF...CmpT | "What is the goal!"       
      

Again, when I use the statement CALL delete_sap("6moo...Vwm") I would hope only the records with that sapid are deleted. That's not the case. Everything's gone.

What am I missing? TIA!

Edit to add, when I change to this, nothing is deleted (not even what I expect to go), but no errors.

DELIMITER //

CREATE OR REPLACE PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

    START TRANSACTION;
    SELECT sap_to_delete as ' ';
    DELETE FROM data01 WHERE data01.sapid = sap_to_delete;
    DELETE FROM sap_has_owners WHERE sap_has_owners.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_reviewers WHERE sap_has_reviewers.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_readonly WHERE sap_has_readonly.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM sap_has_editors WHERE sap_has_editors.sap_sapid = sap_to_delete COLLATE utf8mb4_unicode_ci;
    DELETE FROM page WHERE page.sapid = sap_to_delete;
    DELETE FROM element WHERE element.sapid = sap_to_delete;
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END//

DELIMITER ;

Based on some questions (and I've made a few changes to try to weed out any bad apples), this is what I can see from SHOW CREATE PROCEDURE delete_sap:

| Procedure  | sql_mode                                                                                  | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| delete_sap | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`admin`@`localhost` PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    
    START TRANSACTION;
    DELETE FROM data01 WHERE data01.sapid LIKE sap_to_delete;
    DELETE from sap WHERE sap.sapid LIKE sap_to_delete;
    DELETE FROM page WHERE page.sapid LIKE sap_to_delete;
    DELETE FROM element WHERE element.sapid LIKE sap_to_delete;
    IF `_rollback` THEN
    ROLLBACK;
    ELSE
    COMMIT;
    END IF;
END | utf8mb3              | utf8mb3_general_ci   | utf8mb4_general_ci |

Does it matter if the collation is utf8mb3_general_ci? How do I change it for this procedure definition?

Answer 1

Ultimately, my issue was collation, as I discovered from following @NandalalSeth 's suggestion to check out SHOW CREATE PROCEDURE delete_sap. Running that query revealed

| Procedure  | sql_mode                                                                                  | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+------------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| delete_sap | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`admin`@`localhost` PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45))
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    
    START TRANSACTION;
    DELETE FROM data01 WHERE data01.sapid LIKE sap_to_delete;
    DELETE from sap WHERE sap.sapid LIKE sap_to_delete;
    DELETE FROM page WHERE page.sapid LIKE sap_to_delete;
    DELETE FROM element WHERE element.sapid LIKE sap_to_delete;
    IF `_rollback` THEN
    ROLLBACK;
    ELSE
    COMMIT;
    END IF;
END | utf8mb3              | utf8mb3_general_ci   | utf8mb4_general_ci |

But that procedure is also EXTREMELY chunky and cumbersome. After updating some foreign keys, I only needed to have one delete statement.

Also, I can simply exit and rollback on a sqlexception, instead of updating a boolean.

Ultimately, my solution is as follows.

DELIMITER //

CREATE OR REPLACE PROCEDURE `delete_sap`(IN sap_to_delete CHAR(45))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SHOW ERRORS;
        ROLLBACK;
    END;
    
    START TRANSACTION;
    SELECT sap_to_delete;
    DELETE from sap WHERE sap.sapid LIKE sap_to_delete COLLATE utf8mb4_unicode_ci;
    COMMIT;
END//

DELIMITER ;

Thank you all for providing guiding questions / suggestions that helped me get here.