Last row of inner cursor fetched twice

Question

I'm new to MySQL, and I have a slight problem.

I have a stored procedure which has 2 cursors, one inside the other.

Problem is that the last row of the inner cursor is always fetched twice. This happens everytime the last row comes in the inner cursor, for each iteration of the outer cursor.

Here is the complete Stored Procedure:

CREATE PROCEDURE MAP_TITLES_TO_SRC_CATEGORIES()
BEGIN
    Block1:BEGIN
        DECLARE matched_titles_category_id INTEGER DEFAULT 0;
        DECLARE tmp_genre_category_id INTEGER DEFAULT 0;
        DECLARE index_wanted INT Default 0;
        DECLARE genre_string VARCHAR(255);
        SET matched_titles_category_id = (SELECT category_id FROM oc_category_description WHERE name='matched_titles' LIMIT 1);
        Block2:BEGIN
            DECLARE src_cursor_finished INTEGER DEFAULT 0;
            DECLARE src_cursor_src_code_value varchar(9) DEFAULT "";
            DECLARE src_cursor_genres_value varchar(100) DEFAULT "";
            DECLARE src_cursor CURSOR FOR SELECT it.src_id, it.Genres FROM src_table it order by it.Title asc;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET src_cursor_finished = 1;
            OPEN src_cursor;
                REPEAT
                    FETCH src_cursor INTO src_cursor_src_code_value, src_cursor_genres_value;
                    INSERT INTO src_log (log_entry) VALUES (CONCAT('Cursor #1 populated with :: src_cursor_src_code_value: ',src_cursor_src_code_value,' & src_cursor_genres_value: ',src_cursor_genres_value));
                    Block3:BEGIN
                        DECLARE products_cursor_finished INTEGER DEFAULT 0;
                        DECLARE products_cursor_id_value INTEGER DEFAULT 0;
                        DECLARE products_cursor_isbn_value varchar(9) DEFAULT "";
                        DECLARE products_cursor CURSOR FOR SELECT prod.product_id, prod.isbn FROM oc_product prod where prod.isbn !='' and prod.sku='1';
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET products_cursor_finished = 1;
                        OPEN products_cursor;
                            REPEAT
                                FETCH products_cursor INTO products_cursor_id_value, products_cursor_isbn_value;
                                INSERT INTO src_log (log_entry) VALUES (CONCAT('Cursor #2 populated with :: products_cursor_id_value: ',products_cursor_id_value,' & products_cursor_isbn_value: ',products_cursor_isbn_value));
                                SET index_wanted = 0;
                                IF products_cursor_isbn_value = src_cursor_src_code_value THEN
                                    INSERT INTO src_log (log_entry) VALUES (CONCAT('match entry for prod ',products_cursor_id_value,' in match cat id ',matched_titles_category_id,' BEGIN'));
                                    INSERT INTO oc_product_to_category VALUES (products_cursor_id_value, matched_titles_category_id);
                                    INSERT INTO src_log (log_entry) VALUES (CONCAT('match entry for prod ',products_cursor_id_value,' in match cat id ',matched_titles_category_id,' END'));
                                    genres_loop:LOOP
                                        SET index_wanted=index_wanted+1;
                                        SET genre_string=SPLIT_STR(src_cursor_genres_value,',',index_wanted);
                                        IF genre_string='' THEN
                                            LEAVE genres_loop;
                                        END IF;
                                        SET tmp_genre_category_id = (SELECT category_id FROM oc_category_description WHERE name = genre_string LIMIT 1);
                                        INSERT INTO src_log (log_entry) VALUES (CONCAT('genre entry for prod ',products_cursor_id_value,' and genre cat ID ',tmp_genre_category_id,' BEGIN'));
                                        INSERT INTO oc_product_to_category VALUES (products_cursor_id_value, tmp_genre_category_id);
                                        INSERT INTO src_log (log_entry) VALUES (CONCAT('genre entry for prod ',products_cursor_id_value,' and genre cat ID ',tmp_genre_category_id,' END'));
                                    END LOOP genres_loop;
                                END IF;
                            Until products_cursor_finished END REPEAT;
                        CLOSE products_cursor;
                    END Block3;
                UNTIL src_cursor_finished END REPEAT;
            CLOSE src_cursor;
        END Block2;
    END Block1;
END;

As you can see, I'm logging the data received after every FETCH, and the result I see through this indicates my observed issue.

Any ideas on the bug ?

Answer 1

A test src_cursor_finished must be done immediately after the FETCH command.
But the code tries to fetch from the cursor, then perform many operation (without checking if the fetch was successful), then checks the condition at the end in the UNTIL statement:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET src_cursor_finished = 1;
OPEN src_cursor;
  REPEAT
     FETCH products_cursor INTO products_cursor_id_value, products_cursor_isbn_value;

     -- The condition must be tested  HERE: 
     --    IF products_cursor_finished <> 1 THEN do something
     --        or even better:
     --    IF products_cursor_finished = 1 THEN LEAVE;

     ................
     ..............
     ..........
     ............
     ...................
  Until products_cursor_finished END REPEAT;