MySQL cursor always exits out of loop

Question

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.

• nbk: It looks ok to me, could you alos post your tables with data, at least with the minimum of columns to check your problem?
• Rick James: What version of MySQL?
• Rick James: This does not make sense: WHERE itemid = 12345 ORDER BY itemid -- You have the value (12345), so why do you need the ORDER BY, much less the LIMIT??
• Rick James: And... If this is a "groupwise-max" problem, then there are solutions that don't need cursors.
• R0bert2: I figured out the issue btw. It is due to variable HOSTID and declare cur1 .. has a column named hostid. It seems we can't have variable names same as column names in the query and due to that handler not found gets called. Changing the HOSTID variable to _HOSTID resolved the issue.

Answer 1

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;