MySQL: Loop over cursor results ends ahead of schedule

Question

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 > 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,

• RolandoMySQLDBA: +1 for solving your own problem and doing the needed research. By the way, welcome to the DBA StackExchange.

Answer 1

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.

• Jorge Arévalo: Thanks for your response. It didnt' work, but I think I've found the problem: inside the "Do stuff" part there's some 'select' sentences. If one of these sentences returns an empty result, the declared handler is executed and the loop is interrupted. Trying to figure out how to avoid that...
• RolandoMySQLDBA: Please insert the select stuff in the question so I can have a look...
• Jorge Arévalo: I've included it, cutting the irrelevant parts.
• Jorge Arévalo: Oh, sorry. The 'after delete' trigger is executed over one table different from the table in which I execute the 'update'/'delete' operations. Already edited that.

Answer 2

Solved. As said, the problem was the inner 'SELECT INTO...' sentence returns 0 records, triggering the CONTINUE HANDLER and setting done to TRUE. I fixed it by putting 'set done = false' just before fetching data from cursor, as suggested at http://dev.mysql.com/doc/refman/5.0/en/cursors.html (comment posted by David Bergan on February 23 2012 10:00pm).

So, the code finally looks like

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 not found set done = true; 
open my_cursor; 
my_loop: loop
 set done = false;
 fetch my_cursor into my_value; 
 if done then
 leave my_loop;
 end if;
 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;
 end if;
end loop my_loop; 
close my_cursor; 
end

Thanks to RolandoMySQLDBA for his answers.

• RolandoMySQLDBA: +1 for posting the research and the final code you made !!!