MySQL procedure's cursor stops after first iteration

Question

I am writing a procedure that fetches a datatable to map a field and insert/update in another table.

My problem is that my cursor will stop after the first iteration without throwing any errors if the mapping function does not find any matches.

Here is my function :

BEGIN
    DECLARE mapped_name VARCHAR(255);   

    SELECT mapped_field INTO mapped_name
        FROM mapping_civility
        WHERE original_field = nameVar
    LIMIT 1;

    IF mapped_name IS NULL THEN
        RETURN 'INDEFINI';
    ELSE
        RETURN mapped_name;
    END IF;
END

By testing it I found out that if there is a corresponding field in my mapping table it works, but if the SELECT returns a NULL value because no mapped field is found, it will stop the cursor at the first iteration.

Then i tried it on another database, on another server, and everything went OK, so maybe a configuration problem ? Both have character set "latin1 -- cp1252 West European" collation "latin1_swedish_ci".

Here is my procedure code :

BLOCK1: BEGIN
    DECLARE no_more_rows1 INT;
    DECLARE my_name VARCHAR(255);
    DECLARE civility VARCHAR(255);

    DECLARE curseur1 CURSOR FOR
        SELECT `name`
        FROM source;

    DECLARE CONTINUE handler FOR NOT FOUND SET no_more_rows1 = TRUE;
      
    OPEN curseur1;
    LOOP1: LOOP
        FETCH curseur1 INTO my_name;
        IF no_more_rows1 THEN
            CLOSE curseur1;
            LEAVE LOOP1;
        END IF;

            SET civility = get_civility(my_name);

            INSERT INTO log (id, message, date) VALUES (NULL, CONCAT(my_name, ' : ', civility), NOW());

    END LOOP LOOP1;     
END BLOCK1;

This procedure will insert correctly if the name is well mapped, but it will stop after the first row if the name is not mapped.

You can test it with the following tables

-- ----------------------------
-- Table structure for `source`
-- ----------------------------
DROP TABLE IF EXISTS `source`;
CREATE TABLE `source` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of source
-- ----------------------------
INSERT INTO `source` VALUES ('1', 'Pierre');
INSERT INTO `source` VALUES ('2', 'David');
INSERT INTO `source` VALUES ('3', 'Kevin');
INSERT INTO `source` VALUES ('4', 'Pierre');
INSERT INTO `source` VALUES ('5', 'Donald Pierre');


-- ----------------------------
-- Table structure for `log`
-- ----------------------------
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `message` text COMMENT 'message',
  `date` varchar(64) DEFAULT NULL COMMENT 'date',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
-- Table structure for `mapping_civility`
-- ----------------------------
DROP TABLE IF EXISTS `mapping_civility`;
CREATE TABLE `mapping_civility` (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `original_field` varchar(255) DEFAULT NULL COMMENT 'original_field',
  `mapped_field` varchar(255) DEFAULT NULL COMMENT 'mapped_field',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of mapping_civility
-- ----------------------------
INSERT INTO `mapping_civility` VALUES ('1', 'kevin', 'H');
INSERT INTO `mapping_civility` VALUES ('2', 'pierre', 'H');
INSERT INTO `mapping_civility` VALUES ('3', 'isabelle', 'F');

Answer 1

I had exactly the same problem and the accepted solution didn't help me. We have a quite old version of MySQL so I solved this issue by creating another procedure. The inner loop procedure takes the input from the outer loop. Here's a template example that I've created:

DELIMITER $$

CREATE PROCEDURE first_procedure ()
BEGIN

    DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE v_some_variable SMALLINT DEFAULT 0;

    DEClARE first_cursor CURSOR FOR 
        select some_variable from some_table; --YOUR QUERY GOES HERE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
    OPEN first_cursor;
    get_st_c: LOOP
    set v_finished = 0;
    FETCH first_cursor INTO v_some_variable; -- CAN BE EXTENDED USING COMMAS (,)
    IF v_finished = 1 THEN 
    CLOSE first_cursor;
    LEAVE get_st_c;
    END IF;

        CALL second_procedure(v_some_variable);

    END LOOP get_st_c;

END$$

DELIMITER ;

This is the first procedure. This calls the second procedure within its loop. Here's the second procedure:

DELIMITER $$

CREATE PROCEDURE second_procedure (IN passed_variable int)
BEGIN

    DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE v_some_variable_one INT DEFAULT 0;
    DECLARE v_some_variable_two TIMESTAMP DEFAULT now();
    DECLARE v_some_variable_three INT DEFAULT 0;

    -- THIS LOOP IS NESTED BY THE FIRST PROCEDURE'S LOOP
    DEClARE second_cursor CURSOR FOR 
        SELECT some_variable_one, some_variable_two, some_variable_three FROM some_table WHERE variable = passed_variable; -- YOUR QUERY GOES HERE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
    OPEN second_cursor;
    get_stc_two: LOOP
    FETCH second_cursor INTO v_some_variable_one, v_some_variable_two, v_some_variable_three;
    IF v_finished = 1 THEN 
    CLOSE second_cursor;
    LEAVE get_stc_two;
    END IF;

        -- YOUR LOGIC GOES HERE

    END LOOP get_stc_two;   

END$$

DELIMITER ;

Answer 2

Before MySQL 5.6, stored procedures had only one handler, see the changelogs for 5.6:

In addition, several deficiencies in condition handler processing rules were corrected so that MySQL behavior is more like standard SQL:

• Block scope is used in determining which handler to select. Previously, a stored program was treated as having a single scope for handler selection.

So your NOT FOUND continue handler will unfortunately be triggered by not finding rows in mapping_civility in your function, because you used into there.

You can reinitialize the variable directly before you fetch new rows to reset everything that has happend before:

...
LOOP1: LOOP
    set no_more_rows1 = false;   -- add this
    FETCH curseur1 INTO my_name;
    IF no_more_rows1 THEN
...

If you have nested loops like in your original question, be aware that it will still only be one (active) handler, so use the same variable for both loops and reset it before each fetch.

For MySQL 5.6 and up, your current code would work as expected.