1
2#
3# Demonstrate how RESIGNAL can be used to print a stack trace
4#
5
6# Save defaults
7
8SET @start_global_value = @@global.max_error_count;
9SELECT @start_global_value;
10SET @start_session_value = @@session.max_error_count;
11SELECT @start_session_value;
12
13--disable_warnings
14drop database if exists demo;
15--enable_warnings
16
17create database demo;
18
19use demo;
20
21delimiter $$;
22
23create procedure proc_1()
24begin
25  declare exit handler for sqlexception
26    resignal sqlstate '45000' set message_text='Oops in proc_1';
27
28  call proc_2();
29end
30$$
31
32create procedure proc_2()
33begin
34  declare exit handler for sqlexception
35    resignal sqlstate '45000' set message_text='Oops in proc_2';
36
37  call proc_3();
38end
39$$
40
41create procedure proc_3()
42begin
43  declare exit handler for sqlexception
44    resignal sqlstate '45000' set message_text='Oops in proc_3';
45
46  call proc_4();
47end
48$$
49
50create procedure proc_4()
51begin
52  declare exit handler for sqlexception
53    resignal sqlstate '45000' set message_text='Oops in proc_4';
54
55  call proc_5();
56end
57$$
58
59create procedure proc_5()
60begin
61  declare exit handler for sqlexception
62    resignal sqlstate '45000' set message_text='Oops in proc_5';
63
64  call proc_6();
65end
66$$
67
68create procedure proc_6()
69begin
70  declare exit handler for sqlexception
71    resignal sqlstate '45000' set message_text='Oops in proc_6';
72
73  call proc_7();
74end
75$$
76
77create procedure proc_7()
78begin
79  declare exit handler for sqlexception
80    resignal sqlstate '45000' set message_text='Oops in proc_7';
81
82  call proc_8();
83end
84$$
85
86create procedure proc_8()
87begin
88  declare exit handler for sqlexception
89    resignal sqlstate '45000' set message_text='Oops in proc_8';
90
91  call proc_9();
92end
93$$
94
95create procedure proc_9()
96begin
97  declare exit handler for sqlexception
98    resignal sqlstate '45000' set message_text='Oops in proc_9';
99
100  ## Do something that fails, to see how errors are reported
101  drop table oops_it_is_not_here;
102end
103$$
104
105delimiter ;$$
106
107-- error ER_SIGNAL_EXCEPTION
108call proc_1();
109
110# This is the interesting part:
111# the complete call stack from the origin of failure (proc_9)
112# to the top level caller (proc_1) is available ...
113
114show warnings;
115
116SET @@session.max_error_count = 5;
117SELECT @@session.max_error_count;
118
119-- error ER_SIGNAL_EXCEPTION
120call proc_1();
121show warnings;
122
123SET @@session.max_error_count = 7;
124SELECT @@session.max_error_count;
125
126-- error ER_SIGNAL_EXCEPTION
127call proc_1();
128show warnings;
129
130SET @@session.max_error_count = 9;
131SELECT @@session.max_error_count;
132
133-- error ER_SIGNAL_EXCEPTION
134call proc_1();
135show warnings;
136
137drop database demo;
138
139# Restore defaults
140
141SET @@global.max_error_count = @start_global_value;
142SELECT @@global.max_error_count;
143SET @@session.max_error_count = @start_session_value;
144SELECT @@session.max_error_count;
145
146