MySQL cursor always exits out of loop

cursorsgreatest-n-per-groupMySQL

The cursor query and select value query returns rows if I run it in mysql but when in a cursor it always exits out of loop.

Anything wrong here?

I've added "BEFORE LOOP", "EXIT" and "IN LOOP" so it prints where it is but it always starts with BEFORE LOOP and then ends with EXIT.

CREATE PROCEDURE getTotal()
BEGIN

DECLARE HOSTID INTEGER;
DECLARE cITEMID INT;
declare finished bool default false;
DECLARE Total INT;
declare cur1 cursor for SELECT itemid  FROM items WHERE hostid = 10579;
declare continue handler for not found set finished = true;

open cur1;
   loop_1: loop
        fetch cur1 into cITEMID;
        SELECT "BEFORE LOOP";
        if finished then
            SELECT "EXIT";
            leave loop_1;
        end if;
        SELECT "IN LOOP";
-- Test query
      SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1;

-- Final select query will look like this.
-- SET @Total := @Total + (SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1);
-- SELECT @Total;

  end loop;
close cur1;

END //

DELIMITER ;

Queries:

SELECT itemid  FROM items WHERE hostid = 10579;
| itemid |
| 12345  |
| 12346  |
| 12347  |

SELECT value from history_uint WHERE itemid = 12345 ORDER BY itemid DESC LIMIT 1;
| value | 
| 1     |


SELECT * from history_uint;

| itemid | value | clock (unixtimestamp) |
| 12345  | 13     | 4364564654654        |
| 12346  | 1      | 4364564654657        |
| 12347  | 16     | 4364564654654        |
| 12345  | 13     | 4364564654756        |
| 12346  | 2      | 4364564654753        |
| 12347  | 15     | 4364564654756        |

Note: The clock column value is just made up.

Best Answer

Figured out the issue.

Variable should not be the same as any column in the query.

DECLARE HOSTID INT;

declare cur1 cursor for SELECT itemid  FROM items WHERE hostid = 10579;

Since the cur1 query has a column named hostid it will trigger the not found continue handler which sets finished variable to true.

Not only it triggers continue handler but in some cases it can produce syntax error. Source - https://stackoverflow.com/a/60988686/13109839

Solution rename the HOSTID variable to _HOSTID.

DECLARE _HOSTID INT;
Related Question