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.