MySQL: Loop over cursor results ends ahead of schedule I'm executing a loop that iterates over a cursor's results. The code is inside a trigger function, and the part that matters looks like that: 

EDIT: Sorry, stupid mistake. The trigger is executed after deleting on "one_table". This is not the same table in which I perform the 'delete' or 'update' operations inside the trigger's code (see second listing below)

create trigger my_trigger after delete on one_table
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 
select found_rows() into num_rows; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

if num_rows &gt; 0 then: 
  repeat 
    fetch my_cursor into my_value; 
    -- Do stuff 
  until done end repeat; 
end if; 

close my_cursor; 

end 


The loop should be executed 11 times, because the query returns 11 values. This is checked by the 'insert' clause. The var 'num_rows' is equal to 11. But the problem is the loop is only executed 3 times. 

These numbers (11, 3) are not important. If I change the query to return a different amount of results, the problem remains: the loop ends before scheduled. 

Does it make any sense? I may do something inside the loop (the 'do stuff' part) that causes the end of the loop. It's the only thing that sounds logic for me. 

EDIT: I include the 'Do stuff' part. The problem is probably caused because, inside this part, some 'select' sentences are executed. When one of those sentences returns an empty result, the defined handler is executed, setting done to 'true' and breaking the loop.

The "Do stuff" part is like this:

select some_value into some_field from other_table where some_conditions;

if (some_field is null) then
  delete from my_table where my_condition;
else
  update my_table set key1 = value1 where condition1;


Thanks in advance, and best regards,
You should strictly check for the end of the loop inside the loop

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 
    until done
end repeat; 

close my_cursor; 

end 


This would also eliminate have to check the count

Give it a Try !!!

Looking at your pseudcode

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 

    select some_value into some_field from other_table where some_conditions;
    if (some_field is null) then
        delete from my_table where my_condition;
    else
        update my_table set key1 = value1 where condition1;

    until done
end repeat; 

close my_cursor; 

end 


It may not be a good idea to perform an UPDATE on my_table when you are right in the middle of an after DELETE trigger on the same table. Also, notice you are also causing a DELETE under if (some_field is null) then on the same table.

You are probably better off writing this trigger as a Stored Procedure and manually using the Call to it instead of nesting UPDATE of a table inside DELETE on the same table.