MySQL No data – zero rows fetched : how to code for it

When I first started development in MySQL I found something I really liked when coding. I could just “select … into <variable>” and then check if the variable was NULL to determine if I had anything of value (e.g. No rows). If it was NULL then I knew the data didn’t exist and I could move on. It didn’t create an error compiling and the procedure didn’t error when it ran. Unfortunately after writing a lot of code this way, and then creating events for them we discovered the downside in the MySQL log:

130514 11:11:59 [ERROR] Event Scheduler: [myuser@%][cust.event_my_proc] No data - zero rows fetched, selected, or processed
130514 13:11:59 [ERROR] Event Scheduler: [myuser@%][cust.event_my_proc] No data - zero rows fetched, selected, or processed

Obviously tons of these are no fun.  I also encountered the same “error”
when opening cursors and fetching from them. In both cases I did research and found solutions for the cursors – using found_rows() – but it never seemed I found these easily and written simply.

FYI – I did have a continue handler for “not found”, but these errors still appear in the MySQL log. I thought the continue handler would avoid issues too with fetching inside a cursor, but it doesn’t – you really need to check if there are any rows returned. As for the “select … into …” there is just no way around it.

So, my advice is never use the “select … into <variable>” syntax unless you are absolutely positive you’ll get rows back (and in IT absolute is a sure fire way to be wrong). Instead it is probably best to just use a cursor.

Here is an example of the “select … into …” (my cursor loop was like this only with just a loop and fetch, the continue handler didn’t prevent errors in the log):

DECLARE continue handler for not found set x_not_exist = true;
select app_name, proc_name, error_code, sum(occurrences) occurrences
from that_table
where ts > current_timestamp - interval v_hoursback hour
and (processed = false or processed is null)
group by app_name, proc_name, error_code
into v_app_name, v_proc_name, v_error_code, v_occurrences;

Instead I recommend coding like this (my cursor loop was like this with the continue handler above and just a loop and fecth plus the error in the error log):

DECLARE v_rowcount      integer unsigned;
DECLARE cur_entries cursor for
        select app_name, proc_name, error_code, sum(occurrences) occurrences
        from that_table
        where ts > current_timestamp - interval v_hoursback hour
        and (processed = false or processed is null)
        group by app_name, proc_name, error_code
        order by app_name, proc_name, error_code; 
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;

It is really all just responsible coding. The trick is to remember that when something seems to good to be true, it probably is.

If anyone has other options please let me know.

7 responses to “MySQL No data – zero rows fetched : how to code for it

  1. Pingback: MySQL No data – zero rows fetched : how to code for it | Jed's

  2. Hello

    I got the same message “Error 1329: No data – zero rows fetched, selected” when Im inserting data into table. i can not figure out what’s the problem !

  3. I cannot find where, but I read somewhere that using a cursor when you don’t intend to use it in a loop is generally a bad idea. (I cannot remember the exact reason either. I am too much of a rookie.)
    One possible alternative solution:
    IF(SELECT 1 FROM that_table
    WHERE
    ts > current_timestamp – interval v_hoursback hour
    and (processed = false or processed is null) ) IS NOT NULL THEN
    (if we get here the dreaded select into-s should be fine.)
    ELSE (throw an error, and a tantrum, possibly a brick as well.)
    END IF;

    • What is interesting is that I continue to do “select into” and I don’t seem to have the issue with the errors in the error log. Maybe MySQL changed something, I’m not sure.

      • If I select something nonexistent into a variable, I still get the 1329, but if I use this that I described above, probing ahead, then it is all fine. Mind you, I am using MariaDB, and the newest version of MySQL may have things that MariaDB did not and could not inherit, as they are diverging forks now.

        I wonder if the default value of a column can influence this 1329 thing…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s