How to get rid of "Error 1329: No data - zero rows fetched, selected, or processed"

I have a stored procedure which does not need to return any values. It runs smoothly and without any problem. However, it outputs an error message after finishing its run:

Error: No data - zero rows fetched, selected, or processed

How can I get rid of this error message?

CREATE PROCEDURE `testing_proc`()  
    READS SQL DATA  
BEGIN  
    DECLARE done INT DEFAULT 0;
    DECLARE l_name VARCHAR(20);
    DECLARE my_cur CURSOR FOR
        SELECT name FROM customer_tbl;
    OPEN my_cur;
        my_cur_loop:
        LOOP FETCH my_cur INTO l_name;
            IF done = 1 THEN
                LEAVE my_cur_loop;
            END IF;
            INSERT INTO names_tbl VALUES(l_name);
        END LOOP my_cur_loop;
    CLOSE my_cur;
END

I guess you just forgot to include the following line in your post:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Your code is correct, but bug/strange behaviour of mysql causes the warning to appear even if it was handled. You can avoid that if you add a "dummy" statement to the end of your procedure that invovles a table and is successful, this will clear the warning. (See http://dev.mysql.com/doc/refman/5.5/en/show-warnings.html) In your case:

SELECT name INTO l_name FROM customer_tbl LIMIT 1;

after the end of the loop. On MySQL 5.5.13 the warning disappears, on Linux and Windows. I commented on MySQL Bug 60840 and I hope they will fix it some time in the future...

You need to define a continue handler like:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

So it would look like:

DECLARE done INT DEFAULT 0;
DECLARE l_name VARCHAR(20);
DECLARE my_cur CURSOR FOR
    SELECT name FROM customer_tbl;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN my_cur;
    my_cur_loop:
    LOOP FETCH my_cur INTO l_name;
        IF done = 1 THEN
            LEAVE my_cur_loop;
        END IF;
        INSERT INTO names_tbl VALUES(l_name);
    END LOOP my_cur_loop;
CLOSE my_cur;

I ran into this and pulled out my hair till I ran across this in the official mysql docs

Before MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked. The following discussion demonstrates the issue and provides a workaround.

Click the link and scroll to the bottom for details but the fix was to include a successful select INSIDE the CONTINUE HANDLER:

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
   SELECT 1 INTO @handler_invoked FROM (SELECT 1) AS t;
END;

I tried the solutions in here and none, including the continue handler worked for me. I still get the messages in the MySQL error log. I discovered this also with my "select ... into ..." which made sense, but I really thought the continue handler would work for the cursors. Either way I found using "found_rows()" to find out if any rows were returned worked perfectly. This mean that the simple "select into" statements have to be converted to cursors, but it isn't much work and does solve the problem.

DECLARE v_rowcount      integer unsigned;
DECLARE cur_entries cursor for
        select app_name, proc_name, error_code, sum(occurrences) occurrences
        from that_table...; 
open cur_entries; 
set v_rowcount = found_rows();
if v_rowcount > 0 then
  fetch cur_entries into v_app_name, v_proc_name, v_error_code, v_occurrences;
  ...
end if;
close cur_entries;

I wrote this up on my personal blog here: http://tinky2jed.wordpress.com/technical-stuff/mysql/mysql-no-data-zero-rows-fetched-how-to-code-for-it/

Normally this happens when you overshoot a cursor range, so checkout the loop conditions where the FETCH statement is

How to get rid of "Error 1329: No data - zero rows fetched, selected, or processed"

Related

Recent Posts