Error code 1329 no data zero rows fetched selected or processed

• 2-Oct-2018

DROP PROCEDURE IF EXISTS test_error;

DELIMITER $$
CREATE PROCEDURE test_error() 
BEGIN 
    DECLARE temp_column_name VARCHAR(100);
        
	DECLARE c_example CURSOR FOR 
        SELECT   column_name
        FROM     information_schema.columns
        WHERE    column_name != column_name;
    -- Get data from example cursor
    OPEN c_example;
    FETCH c_example INTO temp_column_name;
    CLOSE c_example;
  
END$$
DELIMITER ;

CALL test_error();

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

DROP PROCEDURE IF EXISTS test_error;

DELIMITER $$
CREATE PROCEDURE test_error() 
BEGIN 
    DECLARE ch_done INT DEFAULT 0;
    DECLARE temp_column_name VARCHAR(100);
        
	DECLARE c_example CURSOR FOR 
        SELECT   column_name
        FROM     information_schema.columns
        WHERE    column_name != column_name;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET ch_done = 1;
    
    -- Get data from example cursor
    OPEN c_example;
    FETCH c_example INTO temp_column_name;
    CLOSE c_example;
    
    IF(ch_done = 1) THEN 
        -- handle the No data error!
        SELECT 'Oh no!';
    END IF;
  
END$$
DELIMITER ;

CALL test_error();

Answer by Sariyah Keith

Error: No data — zero rows fetched, selected, or processed,How can I get rid of this error message?,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/,

Stack Overflow
Public questions & answers

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;

Answer by Iris Weaver

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:,How can I get rid of this error message?,
 

mysql
 — 
sql
 — 
stored-procedures
,I don’t know if this fixes the cursor issue, but I ran into this warning with a stored function and found that if you use:

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

Answer by Milo Davis

This is a warning provided by MySQL. They can be a bit confusing, i suggest you read up on it: See http://dev.mysql.com/doc/refman/5./en/show-warnings.html,AngularJS view not updating on model change,Listen EADDRINUSE error while debugging in sails,Writing outputs to log file and console

I would use this approach instead

declare v_var VARCHAR(200); <-- if that whats it expected to be
open cur;

-- create a loop

repeat_cur: LOOP
FETCH cur INTO var;
-- checks if it is done
if finished then
    LEAVE repeat_cur;
end if;

// DO THE WORK

END LOOP;
close cur;

If above doesnt work, try adding this before the end of the loop.

SELECT 'var' INTO var;

I’m not too sure why your cursor isn’t working as expected (you don’t say whether your test table is populated with the results that you expect?), but it appears to me your procedure is simply implementing a multiple-table UPDATE (so can probably be entirely replaced with the following):

UPDATE LineOfBusiness
  LEFT JOIN medium ON LineOfBusiness.name = medium.LOB
  LEFT JOIN low    ON LineOfBusiness.name =    low.LOB
   SET medium.idLOB = LineOfBusiness.idLineOfBusiness
     ,    low.idLOB = LineOfBusiness.idLineOfBusiness

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;

Answer by Adler White

Return value from MySQL stored procedure

,This may be a super easy question to answer, but I am unsure how to do this correctly.,How do I store the return value of this statement (for example, the value in count is 5) into a variable? I want to be able to access the count value throughout the rest of my procedure.,

MySQL How to INSERT INTO [temp table] FROM [Stored Procedure]

Here is my query within the procedure:

SELECT COUNT(barcode) AS count FROM movieitems;

Answer by Kylen Bartlett

BEGINdeclare typeerr int default 0;declare end_ char(1) default '0';declare total int default 0;declare Id_ int(20);declare MerchantCode_ varchar(20);declare TransactionNum_ varchar(10);declare CentralDateTime_ varchar(12);declare MerchantContact_ varchar(10);declare CardNum_ varchar(30);declare Account_ varchar(20);declare TerminalNum_ varchar(10);declare IssuingBank_ varchar(10);declare BankCode_ varchar(5);declare ClearingMark_ varchar(2);declare ReferenceNum_ varchar(20);declare DealingCode_ varchar(5);declare SendOrganization_ varchar(10);declare Poundage_ varchar(20);declare NetAmount_ varchar(20);declare MaintainOrgan_ varchar(10);declare IsIC_ varchar(5);declare IsRF_ varchar(5);declare UnionpayPro_ varchar(5);DECLARE State_ varchar(2);declare exist_ int(10);declare/*定義游標*/ mycur CURSOR for SELECT Id,MerchantCode,TransactionNum,CentralDateTime,MerchantContact,CardNum,Account,TerminalNum,IssuingBank,BankCode,ClearingMark,ReferenceNum,DealingCode,SendOrganization,Poundage,NetAmount,MaintainOrgan,IsIC,IsRF,UnionpayPro,State from bank_record where State ='0';DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET end_ = '1'; OPEN mycur;/*開游標*/fetch mycur/*游標向下走一步*//*把當前游標提取的值賦值給變量*/ into Id_,MerchantCode_,TransactionNum_,CentralDateTime_,MerchantContact_,CardNum_,Account_,TerminalNum_,IssuingBank_,BankCode_,ClearingMark_,ReferenceNum_,DealingCode_,SendOrganization_,Poundage_,NetAmount_,MaintainOrgan_,IsIC_,IsRF_,UnionpayPro_,State_;while end_ <> '1' DO/*循環體*//*首先test表與excel表進行比對*/select count(*) into exist_ from merchant_info where MerchantCode=MerchantCode_ and TerminalNum=TerminalNum_;/*如果EXCEL表中已經存在記錄，忽略，不存在，添加*/if exist_=0 THEN      insert into merchant_info (MerchantCode,MerchantContact,TerminalNum,CreateTime)                      values(MerchantCode_,MerchantContact_,TerminalNum_,SYSDATE());end if;/*將兩個表的內容拼接到record中*/insert into record (MerchantCode,TerminalNum,MerchantContact,Account,Poundage,NetAmount,IsIC,IsRF)               values(MerchantCode_,TerminalNum_,MerchantContact_,Account_,Poundage_,NetAmount_,IsIC_,IsRF_);update bank_record set State = '1' where id = Id_;set total = total + 1;fetch mycur/*游標向下走一步*//*把當前游標提取的值賦值給變量*/ into Id_,MerchantCode_,TransactionNum_,CentralDateTime_,MerchantContact_,CardNum_,Account_,TerminalNum_,IssuingBank_,BankCode_,ClearingMark_,ReferenceNum_,DealingCode_,SendOrganization_,Poundage_,NetAmount_,MaintainOrgan_,IsIC_,IsRF_,UnionpayPro_,State_;end while;/*循環結束*/close mycur;/*關閉游標*/END

You are using ‘NOT FOUND’ and SQLSTATE ‘02000’. There were bugs reports that addressed this:

• http://bugs.mysql.com/bug.php?id=42834 (March 5, 2009 : STATUS Closed)
• http://bugs.mysql.com/bug.php?id=55843 (August 9, 2010 : STATUS Closed)
• http://bugs.mysql.com/bug.php?id=59169 (December 25, 2010 : STATUS Duplicate)

Here is something enlightening about the whay MySQL does error handling.

The book MySQL Stored Procedure Programming has a whole chapter on this subject. On your particular problem, you need what’s on pages 132,133 under the subheading Handler Conditions.

It states on page 132 that there are three ways to define an error:

• MySQL error code
• ANSI-standard SQLSTATE code
• Named Condition

You must change the code to

Declare continue handler for 1329 
Begin 
  set Done = 1; 
End ; 

I found that error code here : http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html

It says as follows:

Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Message: No data — zero rows fetched, selected, or processed

The book I mentioned recommends getting away from ANSI error codes and using the direct MySQL error codes. The reason? Page 133 has a box «SQLSTATE or MySQL Error Code?», which says the following:

In theory, using the SQLSTATE codes will make your code more portable
to other database platforms and might therefore seem to be the best
choice. Hoewver, there are a number of reasons to use MySQL error
codes rather than the SQLSTATE codes when writing MySQL stored
programs:

In reality, it is unlikely that you will move your stored programs to
another RDBMS. The Oracle and SQL Server stored program languages are
totally incompatible with MySQL. The DB2 stored program language is
somewhat compatible (both are based on the SQL:2003 standard). It is
very likely, however, that you will use MySQL-specific syntax as you
write your application, which will prevent your stored code from being
portable.

Not all MySQL error codes have SQLSTATE equivalents. Although every
MySQL error code is associated with some SQLSTATE error code, often it
will be a general-purpose SQLSTATE that is not specfic (such as
HY000). Therefore, you will almost certainly have to code some
handlers that refer directly to MySQL error codes. You’ll probably
find that the advantages of using a consistent handler format will
outweigh the theorectical portability advantage of SQLSTATE error
codes.

CHANGE HISTORY

Every time I called the below Stored Procedure, I received the following SQL Error:

SQL Error (1329): No data - zero rows fetched, selected, or processed

It turned out I was missing a continue handler when the cursor finished looping. Adding this to the top fixed it:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

delimiter $$

DROP PROCEDURE IF EXISTS db.ProcedureLeadTransform$$

CREATE
        DEFINER = 'user'@'localhost'
        PROCEDURE db.ProcedureLeadTransform()
    READS SQL DATA
    COMMENT 'Convert comma delimited procedure ids in a field into a separate table'
BEGIN

		

		DECLARE l_id INT;
        DECLARE hasComma TINYINT;
        DECLARE commaPos SMALLINT;
        DECLARE thisPos SMALLINT;
        DECLARE numOfIds SMALLINT;
        DECLARE done INT DEFAULT 0;
        DECLARE l_procedureIds VARCHAR(1024);
        DECLARE l_db_added_dt DATETIME;
        DECLARE l_isActive TINYINT;
        DECLARE sequence TINYINT;
        DECLARE thisLength SMALLINT;
        DECLARE thisElementCount SMALLINT;
        DECLARE thisProcedureId MEDIUMINT;
        DECLARE ErrorCode1452 TINYINT DEFAULT FALSE;
		


        
-- Lead Procedures
        DECLARE cur_leads_procs CURSOR FOR
        SELECT leads.id, leads.procedureIds, leads.date, leads.isActive
        FROM db.leads
        LEFT JOIN db.leadprocedures p ON p.leadId = leads.id
        WHERE p.leadId IS NULL AND leads.procedureIds IS NOT NULL AND leads.procedureIds <> ""
        ORDER BY leads.id;

        
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
                BEGIN
                        SET ErrorCode1452 = TRUE;
                END;

        
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;




		
-- Lead procedures
        SET done = 0;

        
OPEN cur_leads_procs;
        leadsProcsLoop: LOOP
        FETCH cur_leads_procs INTO
                l_id, l_procedureIds, l_db_added_dt, l_isActive;

                
IF done = 1 THEN
                        SET done = 0;
                    LEAVE leadsProcsLoop;
                END IF;

                
-- Loop through list of ids
                SET sequence = 1;
                SET numOfIds = 0;
                SET commaPos = LOCATE(",",l_procedureIds);
                SET thisPos = 1;

                
IF (commaPos > 0) THEN
                        SET thisLength = commaPos-1;
                ELSE
                        SET thisLength = length(l_procedureIds);
                END IF;

                
SET thisElementCount = 1;
                SET numOfIds = (length(l_procedureIds)-length(replace(l_procedureIds,',',''))) + 1;

                
LOOPLIST: LOOP

                        
SET thisProcedureId = SUBSTRING(l_procedureIds, thisPos, thisLength);


                        
IF (thisProcedureId REGEXP "^[0-9]+$" AND thisProcedureId <> 0) THEN
                                REPLACE INTO db.leadprocedures
                                (leadId, procedureID, createdAt, deletedAt)
                                VALUES (l_id, thisProcedureId, l_db_added_dt, IF(l_isActive = 0, now(), NULL));

                                
IF ErrorCode1452 THEN
                                        #SHOW ENGINE INNODB STATUS;
                                        SELECT "Message: Cannot add or update a child row: a foreign key constraint fails";
                                        SELECT l_id AS leadId, thisProcedureId AS procedureID, l_db_added_dt AS createdAt, IF(l_isActive = 0, now(), NULL) AS deletedAt;
                                        LEAVE leadsProcsLoop;
                                END IF;
                        END IF;

                        
IF (thisElementCount = numOfIds) THEN
                                LEAVE LOOPLIST;
                        END IF;

                        
SET sequence = sequence + 1;
                        SET thisPos = commaPos+1;
                        SET commaPos = LOCATE(",",l_procedureIds, thisPos);
                        IF (commaPos > 0) THEN
                                SET thisLength = commaPos-thisPos;
                        ELSE
                                SET thisLength = length(l_procedureIds);
                        END IF;
                        SET thisElementCount = thisElementCount + 1;

                
END LOOP LOOPLIST;

        
END LOOP leadsProcsLoop;
        CLOSE cur_leads_procs;

end;