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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
|
#
# Demonstrate how RESIGNAL can be used to 'catch' and 're-throw' an error
#
--disable_warnings
drop database if exists demo;
--enable_warnings
create database demo;
use demo;
delimiter $$;
create procedure proc_top_a(p1 integer)
begin
## DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND
begin
end;
select "Starting ...";
call proc_middle_a(p1);
select "The end";
end
$$
create procedure proc_middle_a(p1 integer)
begin
DECLARE l integer;
# without RESIGNAL:
# Should be: DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND
DECLARE EXIT HANDLER for 1 /* not sure how to handle exceptions */
begin
select "Oops ... now what ?";
end;
select "In prod_middle()";
create temporary table t1(a integer, b integer);
select GET_LOCK("user_mutex", 10) into l;
insert into t1 set a = p1, b = p1;
call proc_bottom_a(p1);
select RELEASE_LOCK("user_mutex") into l;
drop temporary table t1;
end
$$
create procedure proc_bottom_a(p1 integer)
begin
select "In proc_bottom()";
if (p1 = 1) then
begin
select "Doing something that works ...";
select * from t1;
end;
end if;
if (p1 = 2) then
begin
select "Doing something that fail (simulate an error) ...";
drop table no_such_table;
end;
end if;
if (p1 = 3) then
begin
select "Doing something that *SHOULD* works ...";
select * from t1;
end;
end if;
end
$$
delimiter ;$$
#
# Code without RESIGNAL:
# errors are apparent to the caller,
# but there is no cleanup code,
# so that the environment (get_lock(), temporary table) is polluted ...
#
call proc_top_a(1);
# Expected
--error ER_BAD_TABLE_ERROR
call proc_top_a(2);
# Dirty state
--error ER_TABLE_EXISTS_ERROR
call proc_top_a(3);
# Dirty state
--error ER_TABLE_EXISTS_ERROR
call proc_top_a(1);
drop temporary table if exists t1;
delimiter $$;
create procedure proc_top_b(p1 integer)
begin
select "Starting ...";
call proc_middle_b(p1);
select "The end";
end
$$
create procedure proc_middle_b(p1 integer)
begin
DECLARE l integer;
DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND
begin
begin
DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND
begin
/* Ignore errors from the cleanup code */
end;
select "Doing cleanup !";
select RELEASE_LOCK("user_mutex") into l;
drop temporary table t1;
end;
RESIGNAL;
end;
select "In prod_middle()";
create temporary table t1(a integer, b integer);
select GET_LOCK("user_mutex", 10) into l;
insert into t1 set a = p1, b = p1;
call proc_bottom_b(p1);
select RELEASE_LOCK("user_mutex") into l;
drop temporary table t1;
end
$$
create procedure proc_bottom_b(p1 integer)
begin
select "In proc_bottom()";
if (p1 = 1) then
begin
select "Doing something that works ...";
select * from t1;
end;
end if;
if (p1 = 2) then
begin
select "Doing something that fail (simulate an error) ...";
drop table no_such_table;
end;
end if;
if (p1 = 3) then
begin
select "Doing something that *SHOULD* works ...";
select * from t1;
end;
end if;
end
$$
delimiter ;$$
#
# Code with RESIGNAL:
# errors are apparent to the caller,
# the but cleanup code did get a chance to act ...
#
call proc_top_b(1);
--error ER_BAD_TABLE_ERROR
call proc_top_b(2);
call proc_top_b(3);
call proc_top_b(1);
drop database demo;
|
