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