MySQL/ MariaDB Error Code: 1329. No data – zero rows fetched, selected, or processed

The above error can occur when calling a cursor results in no rows, or no more rows if called in a loop. Whilst the error message is descriptive about what has happened physically, you may wish to catch the error so that you can do something else, or simply replace the generic database error with something more meaningful. 

If you’re not sure what I’m talking about, run the following code on a MySQL or MariaDB database:

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();

The response is:

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

In order to trap the error we need to define a CONTINUE HANDLER and DECLARE a variable for the CONTINUE HANDLER to set. We can then manage the No data exception simply by checking the variable.

Following on from the above example, we’ve introduced a variable ch_done. When this variable is set to 1 then the last cursor to be FETCHed returned No data. If it returns a zero then data was returned and all is well.

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();

Remember, if you call multiple cursors in a row, you may need to reset the ch_done back to 0.