Description:
Very weird behaviour of stored procedure in case declared cursor or simple "select into" has no (more) data.
In case I declare "handler for sqlstate '02000'" (or 'not found') I suppose that no warnings or even errors will be generated as it is/was in my previous MySQL version 5.1.39-community-log. But warning is still generated instead.
How to repeat:
Store procedure (example causing problem):
************************************************************
delimiter $$
drop procedure if exists prc_test$$
create procedure prc_test() sql security invoker
begin
declare l_id mediumint;
declare l_done tinyint default 0;
declare c_source cursor for
select 1 from dual where 1 = 0;
declare continue handler for sqlstate '02000' set l_done = 1;
open c_source;
repeat
fetch c_source into l_id;
if not l_done then
-- some code
set l_id = 0;
end if;
until l_done end repeat;
close c_source;
end$$
delimiter ;
************************************************************
And results are:
select version();
+-----------+
| version() |
+-----------+
| 5.5.8-log |
+-----------+
1 row in set (0.00 sec)
call prc_test();
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
************************************************************
select version();
+----------------------+
| version() |
+----------------------+
| 5.1.39-community-log |
+----------------------+
1 row in set (0.00 sec)
mysql> call prc_test();
Query OK, 0 rows affected (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
Suggested fix:
Not generate any warning or error in case of correctly declared continue handler.
Description: Very weird behaviour of stored procedure in case declared cursor or simple "select into" has no (more) data. In case I declare "handler for sqlstate '02000'" (or 'not found') I suppose that no warnings or even errors will be generated as it is/was in my previous MySQL version 5.1.39-community-log. But warning is still generated instead. How to repeat: Store procedure (example causing problem): ************************************************************ delimiter $$ drop procedure if exists prc_test$$ create procedure prc_test() sql security invoker begin declare l_id mediumint; declare l_done tinyint default 0; declare c_source cursor for select 1 from dual where 1 = 0; declare continue handler for sqlstate '02000' set l_done = 1; open c_source; repeat fetch c_source into l_id; if not l_done then -- some code set l_id = 0; end if; until l_done end repeat; close c_source; end$$ delimiter ; ************************************************************ And results are: select version(); +-----------+ | version() | +-----------+ | 5.5.8-log | +-----------+ 1 row in set (0.00 sec) call prc_test(); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------+ | Warning | 1329 | No data - zero rows fetched, selected, or processed | +---------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) ************************************************************ select version(); +----------------------+ | version() | +----------------------+ | 5.1.39-community-log | +----------------------+ 1 row in set (0.00 sec) mysql> call prc_test(); Query OK, 0 rows affected (0.00 sec) mysql> show warnings; Empty set (0.00 sec) Suggested fix: Not generate any warning or error in case of correctly declared continue handler.