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
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
|
#
# Test of init_connect variable
#
--source include/add_anonymous_users.inc
# should work with embedded server after mysqltest is fixed
-- source include/not_embedded.inc
connect (con0,localhost,root,,);
connection con0;
select hex(@a);
connect (con1,localhost,user_1,,);
connection con1;
select hex(@a);
connection con0;
set global init_connect="set @a=2;set @b=3";
connect (con2,localhost,user_1,,);
connection con2;
select @a, @b;
connection con0;
set GLOBAL init_connect=DEFAULT;
connect (con3,localhost,user_1,,);
connection con3;
select @a;
connection con0;
set global init_connect="drop table if exists t1; create table t1(a char(10));\
insert into t1 values ('\0');insert into t1 values('abc')";
connect (con4,localhost,user_1,,);
connection con4;
select hex(a) from t1;
connection con0;
set GLOBAL init_connect="adsfsdfsdfs";
connect (con5,localhost,user_1,,);
connection con5;
--error 2013,2006
select @a;
connection con0;
drop table t1;
disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;
disconnect con5;
--source include/delete_anonymous_users.inc
--echo End of 4.1 tests
#
# Test 5.* features
#
create table t1 (x int);
insert into t1 values (3), (5), (7);
create table t2 (y int);
create user mysqltest1@localhost;
grant all privileges on test.* to mysqltest1@localhost;
#
# Create a simple procedure
#
set global init_connect="create procedure p1() select * from t1";
connect (con1,localhost,mysqltest1,,);
connection con1;
call p1();
drop procedure p1;
connection con0;
disconnect con1;
#
# Create a multi-result set procedure
#
set global init_connect="create procedure p1(x int)\
begin\
select count(*) from t1;\
select * from t1;\
set @x = x;
end";
connect (con1,localhost,mysqltest1,,);
connection con1;
call p1(42);
select @x;
connection con0;
disconnect con1;
#
# Just call it - this will not generate any output
#
set global init_connect="call p1(4711)";
connect (con1,localhost,mysqltest1,,);
connection con1;
select @x;
connection con0;
disconnect con1;
#
# Drop the procedure
#
set global init_connect="drop procedure if exists p1";
connect (con1,localhost,mysqltest1,,);
connection con1;
--error ER_SP_DOES_NOT_EXIST
call p1();
connection con0;
disconnect con1;
#
# Execution of a more complex procedure
#
delimiter |;
create procedure p1(out sum int)
begin
declare n int default 0;
declare c cursor for select * from t1;
declare exit handler for not found
begin
close c;
set sum = n;
end;
open c;
loop
begin
declare x int;
fetch c into x;
if x > 3 then
set n = n + x;
end if;
end;
end loop;
end|
delimiter ;|
# Call the procedure with a cursor
set global init_connect="call p1(@sum)";
connect (con1,localhost,mysqltest1,,);
connection con1;
select @sum;
connection con0;
disconnect con1;
drop procedure p1;
#
# Test Dynamic SQL
#
delimiter |;
create procedure p1(tbl char(10), v int)
begin
set @s = concat('insert into ', tbl, ' values (?)');
set @v = v;
prepare stmt1 from @s;
execute stmt1 using @v;
deallocate prepare stmt1;
end|
delimiter ;|
# Call the procedure with prepared statements
set global init_connect="call p1('t1', 11)";
connect (con1,localhost,mysqltest1,,);
connection con1;
select * from t1;
connection con0;
disconnect con1;
drop procedure p1;
#
# Stored functions
#
delimiter |;
create function f1() returns int
begin
declare n int;
select count(*) into n from t1;
return n;
end|
delimiter ;|
# Invoke a function
set global init_connect="set @x = f1()";
connect (con1,localhost,mysqltest1,,);
connection con1;
select @x;
connection con0;
disconnect con1;
#
# Create a view
#
set global init_connect="create view v1 as select f1()";
connect (con1,localhost,mysqltest1,,);
connection con1;
select * from v1;
connection con0;
disconnect con1;
#
# Drop the view
#
set global init_connect="drop view v1";
connect (con1,localhost,mysqltest1,,);
connection con1;
--error ER_NO_SUCH_TABLE
select * from v1;
connection con0;
disconnect con1;
drop function f1;
# We can't test "create trigger", since this requires super privileges
# in 5.0, but with super privileges, init_connect is not executed.
# (However, this can be tested in 5.1)
#
#set global init_connect="create trigger trg1\
# after insert on t2\
# for each row\
# insert into t1 values (new.y)";
#connect (con1,localhost,mysqltest1,,);
#connection con1;
#insert into t2 values (2), (4);
#select * from t1;
#
#connection con0;
#disconnect con1;
create trigger trg1
after insert on t2
for each row
insert into t1 values (new.y);
# Invoke trigger
set global init_connect="insert into t2 values (13), (17), (19)";
connect (con1,localhost,mysqltest1,,);
connection con1;
select * from t1;
connection con0;
disconnect con1;
drop trigger trg1;
# Set init connect back to the value provided in init_connect-master.opt
# doesn't matter as server will be restarted
set global init_connect="set @a='a\\0c'";
revoke all privileges, grant option from mysqltest1@localhost;
drop user mysqltest1@localhost;
drop table t1, t2;
|
