0

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.

8
  • It looks ok to me, could you alos post your tables with data, at least with the minimum of columns to check your problem? – nbk Apr 1 '20 at 21:02
  • What version of MySQL? – Rick James Apr 11 '20 at 6:13
  • 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 Apr 11 '20 at 6:21
  • And... If this is a "groupwise-max" problem, then there are solutions that don't need cursors. – Rick James Apr 11 '20 at 6:22
  • 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. – john doe Apr 11 '20 at 10:39
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.