summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/signal_demo3.test
blob: a685111c3c1478e5c892c67ed80459e339efd736 (plain)
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
#
# Demonstrate how RESIGNAL can be used to print a stack trace
#

# Save defaults

SET @start_global_value = @@global.max_error_count;
SELECT @start_global_value;
SET @start_session_value = @@session.max_error_count;
SELECT @start_session_value;

--disable_warnings
drop database if exists demo;
--enable_warnings

create database demo;

use demo;

delimiter $$;

create procedure proc_1()
begin
  declare exit handler for sqlexception
    resignal sqlstate '45000' set message_text='Oops in proc_1';

  call proc_2();
end
$$

create procedure proc_2()
begin
  declare exit handler for sqlexception
    resignal sqlstate '45000' set message_text='Oops in proc_2';

  call proc_3();
end
$$

create procedure proc_3()
begin
  declare exit handler for sqlexception
    resignal sqlstate '45000' set message_text='Oops in proc_3';

  call proc_4();
end
$$

create procedure proc_4()
begin
  declare exit handler for sqlexception
    resignal sqlstate '45000' set message_text='Oops in proc_4';

  call proc_5();
end
$$

create procedure proc_5()
begin
  declare exit handler for sqlexception
    resignal sqlstate '45000' set message_text='Oops in proc_5';

  call proc_6();
end
$$

create procedure proc_6()
begin
  declare exit handler for sqlexception
    resignal sqlstate '45000' set message_text='Oops in proc_6';

  call proc_7();
end
$$

create procedure proc_7()
begin
  declare exit handler for sqlexception
    resignal sqlstate '45000' set message_text='Oops in proc_7';

  call proc_8();
end
$$

create procedure proc_8()
begin
  declare exit handler for sqlexception
    resignal sqlstate '45000' set message_text='Oops in proc_8';

  call proc_9();
end
$$

create procedure proc_9()
begin
  declare exit handler for sqlexception
    resignal sqlstate '45000' set message_text='Oops in proc_9';

  ## Do something that fails, to see how errors are reported
  drop table oops_it_is_not_here;
end
$$

delimiter ;$$

-- error ER_SIGNAL_EXCEPTION
call proc_1();

# This is the interesting part:
# the complete call stack from the origin of failure (proc_9)
# to the top level caller (proc_1) is available ...

show warnings;

SET @@session.max_error_count = 5;
SELECT @@session.max_error_count;

-- error ER_SIGNAL_EXCEPTION
call proc_1();
show warnings;

SET @@session.max_error_count = 7;
SELECT @@session.max_error_count;

-- error ER_SIGNAL_EXCEPTION
call proc_1();
show warnings;

SET @@session.max_error_count = 9;
SELECT @@session.max_error_count;

-- error ER_SIGNAL_EXCEPTION
call proc_1();
show warnings;

drop database demo;

# Restore defaults

SET @@global.max_error_count = @start_global_value;
SELECT @@global.max_error_count;
SET @@session.max_error_count = @start_session_value;
SELECT @@session.max_error_count;