Warning and error information in stored procedures

UPD: Information in this blog entry is not true since version 5.6. Please read revisited blog about warning and error information in stored procedures.


I got this question second time this week, so I think it is worth writing a blog post about this topic.
Question was "Why I get error in EVENT which calls a STORED PROCEDURE which already has HANDLER?"
This changed in version 5.5 after fix of Bug #36185, Bug #5889, Bug #9857 and Bug #23032. Explanation is in this commit:


  The problem was in the way warnings/errors during stored routine execution
were handled. Prior to this patch the logic was as follows:
- when a warning/an error happens: if we're executing a stored routine,
and there is a handler for that warning/error, remember the handler,
ignore the warning/error and continue execution.
- after a stored routine instruction is executed: check for a remembered
handler and activate one (if any).
This logic caused several problems:
- if one instruction generates several warnings (errors) it's impossible
to choose the right handler -- a handler for the first generated
condition was chosen and remembered for activation.
- mess with handling conditions in scopes different from the current one.
- not putting generated warnings/errors into Warning Info (Diagnostic
Area) is against The Standard.
The patch changes the logic as follows:
- Diagnostic Area is cleared on the beginning of each statement that
either is able to generate warnings, or is able to work with tables.
- at the end of a stored routine instruction, Diagnostic Area is left
intact.
- Diagnostic Area is checked after each stored routine instruction. If
an instruction generates several condition, it's now possible to take a
look at all of them and determine an appropriate handler.

This means you will notice error which was last handled in stored routine after its execution.
In case of EVENT error would be written to the error log file.


Assume you have a table and two stored procedures:


create table t1(f1 int);
insert into t1 values (1), (2), (3), (4), (5);
\\d |
create procedure search_t1()
begin
declare done int default 0;
declare val int;
declare cur cursor for select f1 from t1;
declare continue handler for not found set done=1;
open cur;
repeat
fetch cur into val;
if not done then
select val;
end if;
until done end repeat;
select done;
end
|
create procedure call_search()
begin
call search_t1();
end
|

If you call call_search you will get a warning from search_t1:


mysql> \\W
Show warnings enabled.
mysql> call call_search()|
+------+
| val  |
+------+
|    1 |
+------+
1 row in set (0.33 sec)
...
Query OK, 0 rows affected, 1 warning (0.33 sec)
Error (Code 1329): No data - zero rows fetched, selected, or processed

This happens because Diagnostic Area now is not cleared after stored procedure execution. One of advantages of this fix, in addition to what it is compatible with SQL Standard now, is you can handle errors from stored routines called inside another routine.
Imagine you have same 2 routines, but defined as follow:


create procedure search_t1()
begin
    declare val int;
    declare cur cursor for select f1 from t1;
   
    open cur;
    repeat
        fetch cur into val;
        select val;
    until 0=1 end repeat;
end
|
create procedure call_search()
begin
    declare nf int default 0;
    declare continue handler for not found set nf=1;
    call search_t1();
    select nf;
end
|

Before version 5.5 call of call_search would fail:


mysql> call call_search()|
+------+
| val |

+------+
| 1 |
+------+
1 row in set (0.01 sec)
...
+------+
| val |
+------+
| 5 |
+------+
1 row in set (0.01 sec)

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed


Pay attention no query after call of search_t1 was executed. Since version 5.5 same call would not fail:


mysql> call call_search();
+------+
| val  |
+------+
|    1 |
+------+
1 row in set (0.12 sec)
...
+------+
| val  |
+------+
|    5 |
+------+
1 row in set (0.12 sec)

+------+
| nf   |
+------+
|    1 |
+------+
1 row in set (0.12 sec)

Query OK, 0 rows affected, 1 warning (0.12 sec)


Of course this is just generic example and actually shows bad code practice, but real life can provide us not so generic errors

Comments:

Yes thank you...It's not easy to find current information on this...

Posted by Magento定制 on September 07, 2011 at 05:48 AM MSD #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today