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

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.