Bug #34817 nested DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' is not triggered
Submitted: 25 Feb 2008 19:31 Modified: 7 Aug 2012 18:25
Reporter: Bogdan Kecman Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-5.1-telco-6.2 OS:Any
Assigned to: CPU Architecture:Any
Tags: 5.0, 5.1, 5.1.23-ndb-6.2.12-telco, 6.0 BK, mccge
Triage: Triaged: D2 (Serious)

[25 Feb 2008 19:31] Bogdan Kecman
Description:
nested DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' is not triggered on insert into ndbcluster table

How to repeat:
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
`i1` int(3) unsigned NOT NULL,
`i2` int(3) unsigned NOT NULL,
`f1` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`i1`,`i2`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`i1` int(3) unsigned,
`i2` int(3) unsigned,
`f1` int(10) unsigned
) ENGINE=innodb DEFAULT CHARSET=latin1;

INSERT INTO `t1` VALUES (1,1,1), (1,1,2), (1,1,1), (1,1,2), (1,2,1), (1,2,10), (2,2,1), (1,1,3), (2,2,5);

drop procedure `p1`;
delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
MAIN_BLOCK:BEGIN
   DECLARE NO_MORE_ROWS INT DEFAULT 0;
   DECLARE l_a INT;
   DECLARE l_b INT;
   DECLARE l_c INT;
   DECLARE crs CURSOR FOR SELECT i1,i2,f1 FROM `t1`;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_MORE_ROWS := 1;
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT ('THIS SHOULD NOT HAPPEN'); 

   SET NO_MORE_ROWS = 0;
   SET SQL_LOG_BIN = 0;
   
   OPEN crs;
   REPEAT
   FETCH crs INTO l_a, l_b, l_c;
   IF NOT NO_MORE_ROWS THEN
      MAJ_CUMUL:BEGIN    
         DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT 'DO THE UPDATE HERE';
         INSERT INTO `c` (i1,i2,f1)  VALUES (l_a, l_b, l_c);
      END MAJ_CUMUL;
   END IF;
   UNTIL NO_MORE_ROWS END REPEAT;
   CLOSE crs;
   
   SET SQL_LOG_BIN = 1;
   
END MAIN_BLOCK //
delimiter ;

call p1();

The wrong output - 
+------------------------+
| THIS SHOULD NOT HAPPEN |
+------------------------+
| THIS SHOULD NOT HAPPEN | 
+------------------------+

if we define `c` as innodb or myisam:
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
`i1` int(3) unsigned NOT NULL,
`i2` int(3) unsigned NOT NULL,
`f1` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`i1`,`i2`)
) ENGINE=innodb DEFAULT CHARSET=latin1;

the right path is taken and the output is:
+--------------------+
| DO THE UPDATE HERE |
+--------------------+
| DO THE UPDATE HERE | 
+--------------------+

call p1();

Suggested fix:
n/a
[25 Feb 2008 19:39] Bogdan Kecman
Looks like a variant of bug 15011
[27 Feb 2008 19:15] Sveta Smirnova
Thank you for the report.

Verified as described. Problem exists only if use NDB tables.
[7 Aug 2012 18:24] Matthew Montgomery
Current 5.1.63-ndb-7.1.23 is still affected  (Won't fix)
5.5.25a-ndb-7.2.7 is unaffected.  (Fixed @ 5.5.6 by Bug #36185, Bug #5889, Bug #9857 and Bug #23032.)