MySQL Cursor fetching only one row

database-cursor, mysql, sql

Solution

I just had the same problem with a stored procedure in MySQL. It was supposed to get all records from a table that have a null value in a certain column, and then fill that value from another table. However, it stopped after one record:

CREATE PROCEDURE `updateRecord`()
    BEGIN

    DECLARE done INT DEFAULT FALSE;
    DECLARE recordId, newValue INT;
    DECLARE current_record CURSOR FOR SELECT id FROM A where b_id is null;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 

    OPEN current_record;

    main_loop: LOOP
        FETCH current_record INTO recordId;

        IF done THEN
             LEAVE main_loop;
        END IF;

        -- fetching some value from table b
        select id into newValue from B where ...

       -- setting new value in record
       update A set b_id = newValue where id = recordId;    

      END LOOP;
    END

The answer is that the "handler for not found" is not only executed if the cursor returned no rows, but also if the select on table B returned no rows. My solution was to use a second variable that was set by the handler and reset the "done" variable after the select:

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE, noBfound = TRUE;    

           [...]

           select id into newValue from B where ...

           IF (noBfound = TRUE) THEN 
               SET done = FALSE;
           END IF;

           [...]

Addition: It's obviously possible to do without a second variable by simply resetting the "done" after the select:

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 

           [...]

           select id into newValue from B where ...

           SET done = FALSE;

           [...]

Problem

My SQL: ``` CREATE PROCEDURE INV_MIN_PURCHASE_PRICE() BEGIN DECLARE done INT; DECLARE current_inventory_ID INT; DECLARE cur1 CURSOR FOR SELECT inventory_ID FROM _inventory; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; SET done = 0; OPEN cur1; REPEAT FETCH cur1 INTO current_inventory_ID; UPDATE _debug SET rows=rows+1; UNTIL done END REPEAT; CLOSE cur1; END; ``` When I call this procedure MySQL is fetching only one row (_debug `rows` are increased by 1). Why?? Is it a bug?

Original source