Info-System

Today I have come across a nasty bug in mysql cursor. It apeard in a method that i used for ages. Usually iterating through the cursor in mysql is based on defining a "continue handler" which gives a signal that a fetch from cursor is empty. Unfortunately it turns out that this method does not always work, because the continue handler is triggered not only when there is an empty fetch. In my case the cursor made only 2 loops instead of 40.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Here you can find samples with continue handler method.

It is possible to omit this buggy construction by using using "found_rows()" function, which counts the amount of rows fetch in last select. We base the iteration on the amount of rows not on the handler. Additional benefit of this function is that it reduces the amount od select calls (we could make another select with count to get the same effect).

IMPORTANT! It is important to call this function, immediatelly after opening the cursor.

Thats how it may look in practice:

drop procedure IF exists calc_props;

delimiter $$

CREATE PROCEDURE calc_props()
 BEGIN
    DECLARE actual_row_number INTEGER DEFAULT 0;
    DECLARE numRows INTEGER DEFAULT 0;
    declare calc_id BIGINT;
    declare form_to_change_id BIGINT;
    declare our_cursor CURSOR FOR 
        select id, form_id from calculator;
        
    OPEN our_cursor;
    SELECT FOUND_ROWS() INTO numRows;
	select numRows;
     	get_form: LOOP
	FETCH our_cursor INTO calc_id,form_to_change_id;
			-- cursor logic goes here
		set actual_row_number := actual_row_number+1;
          	IF actual_row_number>=numRows THEN
            	LEAVE get_form;
			END IF;
         END LOOP get_form;
	CLOSE our_cursor;
 END $$
 delimiter ;
 delete from calc_property;
 call calc_props();
 drop procedure IF exists calc_props;