|
1
by brian
clean slate |
1 |
--source suite/funcs_1/storedproc/load_sp_tb.inc |
2 |
--------------------------------------------------------------------------------
|
|
3 |
||
4 |
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc |
|
5 |
--------------------------------------------------------------------------------
|
|
6 |
DROP DATABASE IF EXISTS db_storedproc; |
|
7 |
DROP DATABASE IF EXISTS db_storedproc_1; |
|
8 |
CREATE DATABASE db_storedproc; |
|
9 |
CREATE DATABASE db_storedproc_1; |
|
10 |
USE db_storedproc; |
|
11 |
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; |
|
12 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1; |
|
13 |
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; |
|
14 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2; |
|
15 |
create table t3(f1 char(20),f2 char(20),f3 integer) engine = innodb; |
|
16 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3; |
|
17 |
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; |
|
18 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t4; |
|
19 |
USE db_storedproc_1; |
|
20 |
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; |
|
21 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6; |
|
22 |
USE db_storedproc; |
|
23 |
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = innodb; |
|
24 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t7; |
|
25 |
Warnings: |
|
26 |
Warning 1265 Data truncated for column 'f3' at row 1 |
|
27 |
Warning 1265 Data truncated for column 'f3' at row 2 |
|
28 |
Warning 1265 Data truncated for column 'f3' at row 3 |
|
29 |
Warning 1265 Data truncated for column 'f3' at row 4 |
|
30 |
Warning 1265 Data truncated for column 'f3' at row 5 |
|
31 |
Warning 1265 Data truncated for column 'f3' at row 6 |
|
32 |
Warning 1265 Data truncated for column 'f3' at row 7 |
|
33 |
Warning 1265 Data truncated for column 'f3' at row 8 |
|
34 |
Warning 1265 Data truncated for column 'f3' at row 9 |
|
35 |
Warning 1265 Data truncated for column 'f3' at row 10 |
|
36 |
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = innodb; |
|
37 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t8; |
|
38 |
Warnings: |
|
39 |
Warning 1265 Data truncated for column 'f3' at row 1 |
|
40 |
Warning 1265 Data truncated for column 'f3' at row 2 |
|
41 |
Warning 1265 Data truncated for column 'f3' at row 3 |
|
42 |
Warning 1265 Data truncated for column 'f3' at row 4 |
|
43 |
Warning 1265 Data truncated for column 'f3' at row 5 |
|
44 |
Warning 1265 Data truncated for column 'f3' at row 6 |
|
45 |
Warning 1265 Data truncated for column 'f3' at row 7 |
|
46 |
Warning 1265 Data truncated for column 'f3' at row 8 |
|
47 |
Warning 1265 Data truncated for column 'f3' at row 9 |
|
48 |
Warning 1265 Data truncated for column 'f3' at row 10 |
|
49 |
create table t9(f1 int, f2 char(25), f3 int) engine = innodb; |
|
50 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9; |
|
51 |
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; |
|
52 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10; |
|
53 |
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = innodb; |
|
54 |
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11; |
|
55 |
||
56 |
Section 3.1.8 - SHOW statement checks: |
|
57 |
--------------------------------------------------------------------------------
|
|
58 |
||
59 |
Testcase 3.1.8.9: |
|
60 |
-----------------
|
|
61 |
||
62 |
Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER |
|
63 |
FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or |
|
64 |
SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE |
|
65 |
FUNCTION STATUS statement (respectively) is executed. |
|
66 |
--------------------------------------------------------------------------------
|
|
67 |
DROP FUNCTION IF EXISTS fn_1; |
|
68 |
DROP FUNCTION IF EXISTS fn_2; |
|
69 |
DROP PROCEDURE IF EXISTS sp_1; |
|
70 |
DROP PROCEDURE IF EXISTS sp_2; |
|
71 |
CREATE PROCEDURE sp_1 (i1 int) |
|
72 |
BEGIN
|
|
73 |
set @x=i1; |
|
74 |
END// |
|
75 |
CREATE PROCEDURE sp_2 (i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER' |
|
76 |
BEGIN
|
|
77 |
set @x=i1; |
|
78 |
END// |
|
79 |
CREATE FUNCTION fn_1 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) returns year |
|
80 |
BEGIN
|
|
81 |
set @x=i1; |
|
82 |
set @y=@x; |
|
83 |
return i4; |
|
84 |
END// |
|
85 |
CREATE FUNCTION fn_2 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) |
|
86 |
RETURNS YEAR |
|
87 |
SQL SECURITY INVOKER |
|
88 |
COMMENT 'created with INVOKER' |
|
89 |
BEGIN
|
|
90 |
set @x=i1; |
|
91 |
set @y=@x; |
|
92 |
return i4; |
|
93 |
END// |
|
94 |
||
95 |
... now check what is stored: |
|
96 |
-----------------------------
|
|
97 |
SELECT * FROM information_schema.routines; |
|
98 |
SPECIFIC_NAME fn_1 |
|
99 |
ROUTINE_CATALOG NULL |
|
100 |
ROUTINE_SCHEMA db_storedproc |
|
101 |
ROUTINE_NAME fn_1 |
|
102 |
ROUTINE_TYPE FUNCTION |
|
103 |
DATA_TYPE year |
|
104 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
105 |
CHARACTER_OCTET_LENGTH NULL |
|
106 |
NUMERIC_PRECISION NULL |
|
107 |
NUMERIC_SCALE NULL |
|
108 |
CHARACTER_SET_NAME NULL |
|
109 |
COLLATION_NAME NULL |
|
110 |
DTD_IDENTIFIER year(4) |
|
111 |
ROUTINE_BODY SQL |
|
112 |
ROUTINE_DEFINITION BEGIN |
|
113 |
set @x=i1; |
|
114 |
set @y=@x; |
|
115 |
return i4; |
|
116 |
END
|
|
117 |
EXTERNAL_NAME NULL |
|
118 |
EXTERNAL_LANGUAGE NULL |
|
119 |
PARAMETER_STYLE SQL |
|
120 |
IS_DETERMINISTIC NO |
|
121 |
SQL_DATA_ACCESS CONTAINS SQL |
|
122 |
SQL_PATH NULL |
|
123 |
SECURITY_TYPE DEFINER |
|
124 |
CREATED <created> |
|
125 |
LAST_ALTERED <modified> |
|
126 |
SQL_MODE
|
|
127 |
ROUTINE_COMMENT
|
|
128 |
DEFINER root@localhost |
|
129 |
CHARACTER_SET_CLIENT latin1 |
|
130 |
COLLATION_CONNECTION latin1_swedish_ci |
|
131 |
DATABASE_COLLATION latin1_swedish_ci |
|
132 |
SPECIFIC_NAME fn_2 |
|
133 |
ROUTINE_CATALOG NULL |
|
134 |
ROUTINE_SCHEMA db_storedproc |
|
135 |
ROUTINE_NAME fn_2 |
|
136 |
ROUTINE_TYPE FUNCTION |
|
137 |
DATA_TYPE year |
|
138 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
139 |
CHARACTER_OCTET_LENGTH NULL |
|
140 |
NUMERIC_PRECISION NULL |
|
141 |
NUMERIC_SCALE NULL |
|
142 |
CHARACTER_SET_NAME NULL |
|
143 |
COLLATION_NAME NULL |
|
144 |
DTD_IDENTIFIER year(4) |
|
145 |
ROUTINE_BODY SQL |
|
146 |
ROUTINE_DEFINITION BEGIN |
|
147 |
set @x=i1; |
|
148 |
set @y=@x; |
|
149 |
return i4; |
|
150 |
END
|
|
151 |
EXTERNAL_NAME NULL |
|
152 |
EXTERNAL_LANGUAGE NULL |
|
153 |
PARAMETER_STYLE SQL |
|
154 |
IS_DETERMINISTIC NO |
|
155 |
SQL_DATA_ACCESS CONTAINS SQL |
|
156 |
SQL_PATH NULL |
|
157 |
SECURITY_TYPE INVOKER |
|
158 |
CREATED <created> |
|
159 |
LAST_ALTERED <modified> |
|
160 |
SQL_MODE
|
|
161 |
ROUTINE_COMMENT created with INVOKER |
|
162 |
DEFINER root@localhost |
|
163 |
CHARACTER_SET_CLIENT latin1 |
|
164 |
COLLATION_CONNECTION latin1_swedish_ci |
|
165 |
DATABASE_COLLATION latin1_swedish_ci |
|
166 |
SPECIFIC_NAME sp_1 |
|
167 |
ROUTINE_CATALOG NULL |
|
168 |
ROUTINE_SCHEMA db_storedproc |
|
169 |
ROUTINE_NAME sp_1 |
|
170 |
ROUTINE_TYPE PROCEDURE |
|
171 |
DATA_TYPE
|
|
172 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
173 |
CHARACTER_OCTET_LENGTH NULL |
|
174 |
NUMERIC_PRECISION NULL |
|
175 |
NUMERIC_SCALE NULL |
|
176 |
CHARACTER_SET_NAME NULL |
|
177 |
COLLATION_NAME NULL |
|
178 |
DTD_IDENTIFIER NULL |
|
179 |
ROUTINE_BODY SQL |
|
180 |
ROUTINE_DEFINITION BEGIN |
|
181 |
set @x=i1; |
|
182 |
END
|
|
183 |
EXTERNAL_NAME NULL |
|
184 |
EXTERNAL_LANGUAGE NULL |
|
185 |
PARAMETER_STYLE SQL |
|
186 |
IS_DETERMINISTIC NO |
|
187 |
SQL_DATA_ACCESS CONTAINS SQL |
|
188 |
SQL_PATH NULL |
|
189 |
SECURITY_TYPE DEFINER |
|
190 |
CREATED <created> |
|
191 |
LAST_ALTERED <modified> |
|
192 |
SQL_MODE
|
|
193 |
ROUTINE_COMMENT
|
|
194 |
DEFINER root@localhost |
|
195 |
CHARACTER_SET_CLIENT latin1 |
|
196 |
COLLATION_CONNECTION latin1_swedish_ci |
|
197 |
DATABASE_COLLATION latin1_swedish_ci |
|
198 |
SPECIFIC_NAME sp_2 |
|
199 |
ROUTINE_CATALOG NULL |
|
200 |
ROUTINE_SCHEMA db_storedproc |
|
201 |
ROUTINE_NAME sp_2 |
|
202 |
ROUTINE_TYPE PROCEDURE |
|
203 |
DATA_TYPE
|
|
204 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
205 |
CHARACTER_OCTET_LENGTH NULL |
|
206 |
NUMERIC_PRECISION NULL |
|
207 |
NUMERIC_SCALE NULL |
|
208 |
CHARACTER_SET_NAME NULL |
|
209 |
COLLATION_NAME NULL |
|
210 |
DTD_IDENTIFIER NULL |
|
211 |
ROUTINE_BODY SQL |
|
212 |
ROUTINE_DEFINITION BEGIN |
|
213 |
set @x=i1; |
|
214 |
END
|
|
215 |
EXTERNAL_NAME NULL |
|
216 |
EXTERNAL_LANGUAGE NULL |
|
217 |
PARAMETER_STYLE SQL |
|
218 |
IS_DETERMINISTIC NO |
|
219 |
SQL_DATA_ACCESS CONTAINS SQL |
|
220 |
SQL_PATH NULL |
|
221 |
SECURITY_TYPE INVOKER |
|
222 |
CREATED <created> |
|
223 |
LAST_ALTERED <modified> |
|
224 |
SQL_MODE
|
|
225 |
ROUTINE_COMMENT created with INVOKER |
|
226 |
DEFINER root@localhost |
|
227 |
CHARACTER_SET_CLIENT latin1 |
|
228 |
COLLATION_CONNECTION latin1_swedish_ci |
|
229 |
DATABASE_COLLATION latin1_swedish_ci |
|
230 |
SHOW CREATE FUNCTION fn_1; |
|
231 |
Function fn_1 |
|
232 |
sql_mode
|
|
233 |
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) |
|
234 |
BEGIN
|
|
235 |
set @x=i1; |
|
236 |
set @y=@x; |
|
237 |
return i4; |
|
238 |
END
|
|
239 |
character_set_client latin1 |
|
240 |
collation_connection latin1_swedish_ci |
|
241 |
Database Collation latin1_swedish_ci |
|
242 |
SHOW CREATE FUNCTION fn_2; |
|
243 |
Function fn_2 |
|
244 |
sql_mode
|
|
245 |
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) |
|
246 |
SQL SECURITY INVOKER |
|
247 |
COMMENT 'created with INVOKER' |
|
248 |
BEGIN
|
|
249 |
set @x=i1; |
|
250 |
set @y=@x; |
|
251 |
return i4; |
|
252 |
END
|
|
253 |
character_set_client latin1 |
|
254 |
collation_connection latin1_swedish_ci |
|
255 |
Database Collation latin1_swedish_ci |
|
256 |
SHOW CREATE PROCEDURE sp_1; |
|
257 |
Procedure sp_1 |
|
258 |
sql_mode
|
|
259 |
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) |
|
260 |
BEGIN
|
|
261 |
set @x=i1; |
|
262 |
END
|
|
263 |
character_set_client latin1 |
|
264 |
collation_connection latin1_swedish_ci |
|
265 |
Database Collation latin1_swedish_ci |
|
266 |
SHOW CREATE PROCEDURE sp_2; |
|
267 |
Procedure sp_2 |
|
268 |
sql_mode
|
|
269 |
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) |
|
270 |
SQL SECURITY INVOKER |
|
271 |
COMMENT 'created with INVOKER' |
|
272 |
BEGIN
|
|
273 |
set @x=i1; |
|
274 |
END
|
|
275 |
character_set_client latin1 |
|
276 |
collation_connection latin1_swedish_ci |
|
277 |
Database Collation latin1_swedish_ci |
|
278 |
SHOW FUNCTION STATUS LIKE 'fn_%'; |
|
279 |
Db db_storedproc |
|
280 |
Name fn_1 |
|
281 |
Type FUNCTION |
|
282 |
Definer root@localhost |
|
283 |
Modified <modified> |
|
284 |
Created <created> |
|
285 |
Security_type DEFINER |
|
286 |
Comment
|
|
287 |
character_set_client latin1 |
|
288 |
collation_connection latin1_swedish_ci |
|
289 |
Database Collation latin1_swedish_ci |
|
290 |
Db db_storedproc |
|
291 |
Name fn_2 |
|
292 |
Type FUNCTION |
|
293 |
Definer root@localhost |
|
294 |
Modified <modified> |
|
295 |
Created <created> |
|
296 |
Security_type INVOKER |
|
297 |
Comment created with INVOKER |
|
298 |
character_set_client latin1 |
|
299 |
collation_connection latin1_swedish_ci |
|
300 |
Database Collation latin1_swedish_ci |
|
301 |
SHOW PROCEDURE STATUS LIKE 'sp_%'; |
|
302 |
Db db_storedproc |
|
303 |
Name sp_1 |
|
304 |
Type PROCEDURE |
|
305 |
Definer root@localhost |
|
306 |
Modified <modified> |
|
307 |
Created <created> |
|
308 |
Security_type DEFINER |
|
309 |
Comment
|
|
310 |
character_set_client latin1 |
|
311 |
collation_connection latin1_swedish_ci |
|
312 |
Database Collation latin1_swedish_ci |
|
313 |
Db db_storedproc |
|
314 |
Name sp_2 |
|
315 |
Type PROCEDURE |
|
316 |
Definer root@localhost |
|
317 |
Modified <modified> |
|
318 |
Created <created> |
|
319 |
Security_type INVOKER |
|
320 |
Comment created with INVOKER |
|
321 |
character_set_client latin1 |
|
322 |
collation_connection latin1_swedish_ci |
|
323 |
Database Collation latin1_swedish_ci |
|
324 |
||
325 |
... now change some stuff: |
|
326 |
--------------------------
|
|
327 |
ALTER PROCEDURE sp_1 SQL SECURITY INVOKER; |
|
328 |
ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER'; |
|
329 |
ALTER PROCEDURE sp_2 SQL SECURITY DEFINER; |
|
330 |
ALTER PROCEDURE sp_2 DROP COMMENT; |
|
331 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP COMMENT' at line 1 |
|
332 |
ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER'; |
|
333 |
ALTER PROCEDURE sp_2 READS SQL DATA; |
|
334 |
ALTER FUNCTION fn_1 SQL SECURITY INVOKER; |
|
335 |
ALTER FUNCTION fn_1 COMMENT 'new comment, FN changed to INVOKER'; |
|
336 |
ALTER FUNCTION fn_1 NO SQL; |
|
337 |
ALTER FUNCTION fn_2 SQL SECURITY DEFINER; |
|
338 |
ALTER FUNCTION fn_2 COMMENT 'FN changed to DEFINER'; |
|
339 |
ALTER FUNCTION fn_2 MODIFIES SQL DATA; |
|
340 |
||
341 |
... now check what is stored: |
|
342 |
-----------------------------
|
|
343 |
SELECT * FROM information_schema.routines; |
|
344 |
SPECIFIC_NAME fn_1 |
|
345 |
ROUTINE_CATALOG NULL |
|
346 |
ROUTINE_SCHEMA db_storedproc |
|
347 |
ROUTINE_NAME fn_1 |
|
348 |
ROUTINE_TYPE FUNCTION |
|
349 |
DATA_TYPE year |
|
350 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
351 |
CHARACTER_OCTET_LENGTH NULL |
|
352 |
NUMERIC_PRECISION NULL |
|
353 |
NUMERIC_SCALE NULL |
|
354 |
CHARACTER_SET_NAME NULL |
|
355 |
COLLATION_NAME NULL |
|
356 |
DTD_IDENTIFIER year(4) |
|
357 |
ROUTINE_BODY SQL |
|
358 |
ROUTINE_DEFINITION BEGIN |
|
359 |
set @x=i1; |
|
360 |
set @y=@x; |
|
361 |
return i4; |
|
362 |
END
|
|
363 |
EXTERNAL_NAME NULL |
|
364 |
EXTERNAL_LANGUAGE NULL |
|
365 |
PARAMETER_STYLE SQL |
|
366 |
IS_DETERMINISTIC NO |
|
367 |
SQL_DATA_ACCESS NO SQL |
|
368 |
SQL_PATH NULL |
|
369 |
SECURITY_TYPE INVOKER |
|
370 |
CREATED <created> |
|
371 |
LAST_ALTERED <modified> |
|
372 |
SQL_MODE
|
|
373 |
ROUTINE_COMMENT new comment, FN changed to INVOKER |
|
374 |
DEFINER root@localhost |
|
375 |
CHARACTER_SET_CLIENT latin1 |
|
376 |
COLLATION_CONNECTION latin1_swedish_ci |
|
377 |
DATABASE_COLLATION latin1_swedish_ci |
|
378 |
SPECIFIC_NAME fn_2 |
|
379 |
ROUTINE_CATALOG NULL |
|
380 |
ROUTINE_SCHEMA db_storedproc |
|
381 |
ROUTINE_NAME fn_2 |
|
382 |
ROUTINE_TYPE FUNCTION |
|
383 |
DATA_TYPE year |
|
384 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
385 |
CHARACTER_OCTET_LENGTH NULL |
|
386 |
NUMERIC_PRECISION NULL |
|
387 |
NUMERIC_SCALE NULL |
|
388 |
CHARACTER_SET_NAME NULL |
|
389 |
COLLATION_NAME NULL |
|
390 |
DTD_IDENTIFIER year(4) |
|
391 |
ROUTINE_BODY SQL |
|
392 |
ROUTINE_DEFINITION BEGIN |
|
393 |
set @x=i1; |
|
394 |
set @y=@x; |
|
395 |
return i4; |
|
396 |
END
|
|
397 |
EXTERNAL_NAME NULL |
|
398 |
EXTERNAL_LANGUAGE NULL |
|
399 |
PARAMETER_STYLE SQL |
|
400 |
IS_DETERMINISTIC NO |
|
401 |
SQL_DATA_ACCESS MODIFIES SQL DATA |
|
402 |
SQL_PATH NULL |
|
403 |
SECURITY_TYPE DEFINER |
|
404 |
CREATED <created> |
|
405 |
LAST_ALTERED <modified> |
|
406 |
SQL_MODE
|
|
407 |
ROUTINE_COMMENT FN changed to DEFINER |
|
408 |
DEFINER root@localhost |
|
409 |
CHARACTER_SET_CLIENT latin1 |
|
410 |
COLLATION_CONNECTION latin1_swedish_ci |
|
411 |
DATABASE_COLLATION latin1_swedish_ci |
|
412 |
SPECIFIC_NAME sp_1 |
|
413 |
ROUTINE_CATALOG NULL |
|
414 |
ROUTINE_SCHEMA db_storedproc |
|
415 |
ROUTINE_NAME sp_1 |
|
416 |
ROUTINE_TYPE PROCEDURE |
|
417 |
DATA_TYPE
|
|
418 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
419 |
CHARACTER_OCTET_LENGTH NULL |
|
420 |
NUMERIC_PRECISION NULL |
|
421 |
NUMERIC_SCALE NULL |
|
422 |
CHARACTER_SET_NAME NULL |
|
423 |
COLLATION_NAME NULL |
|
424 |
DTD_IDENTIFIER NULL |
|
425 |
ROUTINE_BODY SQL |
|
426 |
ROUTINE_DEFINITION BEGIN |
|
427 |
set @x=i1; |
|
428 |
END
|
|
429 |
EXTERNAL_NAME NULL |
|
430 |
EXTERNAL_LANGUAGE NULL |
|
431 |
PARAMETER_STYLE SQL |
|
432 |
IS_DETERMINISTIC NO |
|
433 |
SQL_DATA_ACCESS CONTAINS SQL |
|
434 |
SQL_PATH NULL |
|
435 |
SECURITY_TYPE INVOKER |
|
436 |
CREATED <created> |
|
437 |
LAST_ALTERED <modified> |
|
438 |
SQL_MODE
|
|
439 |
ROUTINE_COMMENT new comment, SP changed to INVOKER |
|
440 |
DEFINER root@localhost |
|
441 |
CHARACTER_SET_CLIENT latin1 |
|
442 |
COLLATION_CONNECTION latin1_swedish_ci |
|
443 |
DATABASE_COLLATION latin1_swedish_ci |
|
444 |
SPECIFIC_NAME sp_2 |
|
445 |
ROUTINE_CATALOG NULL |
|
446 |
ROUTINE_SCHEMA db_storedproc |
|
447 |
ROUTINE_NAME sp_2 |
|
448 |
ROUTINE_TYPE PROCEDURE |
|
449 |
DATA_TYPE
|
|
450 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
451 |
CHARACTER_OCTET_LENGTH NULL |
|
452 |
NUMERIC_PRECISION NULL |
|
453 |
NUMERIC_SCALE NULL |
|
454 |
CHARACTER_SET_NAME NULL |
|
455 |
COLLATION_NAME NULL |
|
456 |
DTD_IDENTIFIER NULL |
|
457 |
ROUTINE_BODY SQL |
|
458 |
ROUTINE_DEFINITION BEGIN |
|
459 |
set @x=i1; |
|
460 |
END
|
|
461 |
EXTERNAL_NAME NULL |
|
462 |
EXTERNAL_LANGUAGE NULL |
|
463 |
PARAMETER_STYLE SQL |
|
464 |
IS_DETERMINISTIC NO |
|
465 |
SQL_DATA_ACCESS READS SQL DATA |
|
466 |
SQL_PATH NULL |
|
467 |
SECURITY_TYPE DEFINER |
|
468 |
CREATED <created> |
|
469 |
LAST_ALTERED <modified> |
|
470 |
SQL_MODE
|
|
471 |
ROUTINE_COMMENT SP changed to DEFINER |
|
472 |
DEFINER root@localhost |
|
473 |
CHARACTER_SET_CLIENT latin1 |
|
474 |
COLLATION_CONNECTION latin1_swedish_ci |
|
475 |
DATABASE_COLLATION latin1_swedish_ci |
|
476 |
SHOW CREATE FUNCTION fn_1; |
|
477 |
Function fn_1 |
|
478 |
sql_mode
|
|
479 |
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) |
|
480 |
NO SQL |
|
481 |
SQL SECURITY INVOKER |
|
482 |
COMMENT 'new comment, FN changed to INVOKER' |
|
483 |
BEGIN
|
|
484 |
set @x=i1; |
|
485 |
set @y=@x; |
|
486 |
return i4; |
|
487 |
END
|
|
488 |
character_set_client latin1 |
|
489 |
collation_connection latin1_swedish_ci |
|
490 |
Database Collation latin1_swedish_ci |
|
491 |
SHOW CREATE FUNCTION fn_2; |
|
492 |
Function fn_2 |
|
493 |
sql_mode
|
|
494 |
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) |
|
495 |
MODIFIES SQL DATA |
|
496 |
COMMENT 'FN changed to DEFINER' |
|
497 |
BEGIN
|
|
498 |
set @x=i1; |
|
499 |
set @y=@x; |
|
500 |
return i4; |
|
501 |
END
|
|
502 |
character_set_client latin1 |
|
503 |
collation_connection latin1_swedish_ci |
|
504 |
Database Collation latin1_swedish_ci |
|
505 |
SHOW CREATE PROCEDURE sp_1; |
|
506 |
Procedure sp_1 |
|
507 |
sql_mode
|
|
508 |
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) |
|
509 |
SQL SECURITY INVOKER |
|
510 |
COMMENT 'new comment, SP changed to INVOKER' |
|
511 |
BEGIN
|
|
512 |
set @x=i1; |
|
513 |
END
|
|
514 |
character_set_client latin1 |
|
515 |
collation_connection latin1_swedish_ci |
|
516 |
Database Collation latin1_swedish_ci |
|
517 |
SHOW CREATE PROCEDURE sp_2; |
|
518 |
Procedure sp_2 |
|
519 |
sql_mode
|
|
520 |
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) |
|
521 |
READS SQL DATA |
|
522 |
COMMENT 'SP changed to DEFINER' |
|
523 |
BEGIN
|
|
524 |
set @x=i1; |
|
525 |
END
|
|
526 |
character_set_client latin1 |
|
527 |
collation_connection latin1_swedish_ci |
|
528 |
Database Collation latin1_swedish_ci |
|
529 |
SHOW FUNCTION STATUS LIKE 'fn_%'; |
|
530 |
Db db_storedproc |
|
531 |
Name fn_1 |
|
532 |
Type FUNCTION |
|
533 |
Definer root@localhost |
|
534 |
Modified <modified> |
|
535 |
Created <created> |
|
536 |
Security_type INVOKER |
|
537 |
Comment new comment, FN changed to INVOKER |
|
538 |
character_set_client latin1 |
|
539 |
collation_connection latin1_swedish_ci |
|
540 |
Database Collation latin1_swedish_ci |
|
541 |
Db db_storedproc |
|
542 |
Name fn_2 |
|
543 |
Type FUNCTION |
|
544 |
Definer root@localhost |
|
545 |
Modified <modified> |
|
546 |
Created <created> |
|
547 |
Security_type DEFINER |
|
548 |
Comment FN changed to DEFINER |
|
549 |
character_set_client latin1 |
|
550 |
collation_connection latin1_swedish_ci |
|
551 |
Database Collation latin1_swedish_ci |
|
552 |
SHOW PROCEDURE STATUS LIKE 'sp_%'; |
|
553 |
Db db_storedproc |
|
554 |
Name sp_1 |
|
555 |
Type PROCEDURE |
|
556 |
Definer root@localhost |
|
557 |
Modified <modified> |
|
558 |
Created <created> |
|
559 |
Security_type INVOKER |
|
560 |
Comment new comment, SP changed to INVOKER |
|
561 |
character_set_client latin1 |
|
562 |
collation_connection latin1_swedish_ci |
|
563 |
Database Collation latin1_swedish_ci |
|
564 |
Db db_storedproc |
|
565 |
Name sp_2 |
|
566 |
Type PROCEDURE |
|
567 |
Definer root@localhost |
|
568 |
Modified <modified> |
|
569 |
Created <created> |
|
570 |
Security_type DEFINER |
|
571 |
Comment SP changed to DEFINER |
|
572 |
character_set_client latin1 |
|
573 |
collation_connection latin1_swedish_ci |
|
574 |
Database Collation latin1_swedish_ci |
|
575 |
||
576 |
... change back to default and check result: |
|
577 |
--------------------------------------------
|
|
578 |
ALTER FUNCTION fn_2 CONTAINS SQL; |
|
579 |
||
580 |
... now check what is stored: |
|
581 |
-----------------------------
|
|
582 |
SELECT * FROM information_schema.routines; |
|
583 |
SPECIFIC_NAME fn_1 |
|
584 |
ROUTINE_CATALOG NULL |
|
585 |
ROUTINE_SCHEMA db_storedproc |
|
586 |
ROUTINE_NAME fn_1 |
|
587 |
ROUTINE_TYPE FUNCTION |
|
588 |
DATA_TYPE year |
|
589 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
590 |
CHARACTER_OCTET_LENGTH NULL |
|
591 |
NUMERIC_PRECISION NULL |
|
592 |
NUMERIC_SCALE NULL |
|
593 |
CHARACTER_SET_NAME NULL |
|
594 |
COLLATION_NAME NULL |
|
595 |
DTD_IDENTIFIER year(4) |
|
596 |
ROUTINE_BODY SQL |
|
597 |
ROUTINE_DEFINITION BEGIN |
|
598 |
set @x=i1; |
|
599 |
set @y=@x; |
|
600 |
return i4; |
|
601 |
END
|
|
602 |
EXTERNAL_NAME NULL |
|
603 |
EXTERNAL_LANGUAGE NULL |
|
604 |
PARAMETER_STYLE SQL |
|
605 |
IS_DETERMINISTIC NO |
|
606 |
SQL_DATA_ACCESS NO SQL |
|
607 |
SQL_PATH NULL |
|
608 |
SECURITY_TYPE INVOKER |
|
609 |
CREATED <created> |
|
610 |
LAST_ALTERED <modified> |
|
611 |
SQL_MODE
|
|
612 |
ROUTINE_COMMENT new comment, FN changed to INVOKER |
|
613 |
DEFINER root@localhost |
|
614 |
CHARACTER_SET_CLIENT latin1 |
|
615 |
COLLATION_CONNECTION latin1_swedish_ci |
|
616 |
DATABASE_COLLATION latin1_swedish_ci |
|
617 |
SPECIFIC_NAME fn_2 |
|
618 |
ROUTINE_CATALOG NULL |
|
619 |
ROUTINE_SCHEMA db_storedproc |
|
620 |
ROUTINE_NAME fn_2 |
|
621 |
ROUTINE_TYPE FUNCTION |
|
622 |
DATA_TYPE year |
|
623 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
624 |
CHARACTER_OCTET_LENGTH NULL |
|
625 |
NUMERIC_PRECISION NULL |
|
626 |
NUMERIC_SCALE NULL |
|
627 |
CHARACTER_SET_NAME NULL |
|
628 |
COLLATION_NAME NULL |
|
629 |
DTD_IDENTIFIER year(4) |
|
630 |
ROUTINE_BODY SQL |
|
631 |
ROUTINE_DEFINITION BEGIN |
|
632 |
set @x=i1; |
|
633 |
set @y=@x; |
|
634 |
return i4; |
|
635 |
END
|
|
636 |
EXTERNAL_NAME NULL |
|
637 |
EXTERNAL_LANGUAGE NULL |
|
638 |
PARAMETER_STYLE SQL |
|
639 |
IS_DETERMINISTIC NO |
|
640 |
SQL_DATA_ACCESS CONTAINS SQL |
|
641 |
SQL_PATH NULL |
|
642 |
SECURITY_TYPE DEFINER |
|
643 |
CREATED <created> |
|
644 |
LAST_ALTERED <modified> |
|
645 |
SQL_MODE
|
|
646 |
ROUTINE_COMMENT FN changed to DEFINER |
|
647 |
DEFINER root@localhost |
|
648 |
CHARACTER_SET_CLIENT latin1 |
|
649 |
COLLATION_CONNECTION latin1_swedish_ci |
|
650 |
DATABASE_COLLATION latin1_swedish_ci |
|
651 |
SPECIFIC_NAME sp_1 |
|
652 |
ROUTINE_CATALOG NULL |
|
653 |
ROUTINE_SCHEMA db_storedproc |
|
654 |
ROUTINE_NAME sp_1 |
|
655 |
ROUTINE_TYPE PROCEDURE |
|
656 |
DATA_TYPE
|
|
657 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
658 |
CHARACTER_OCTET_LENGTH NULL |
|
659 |
NUMERIC_PRECISION NULL |
|
660 |
NUMERIC_SCALE NULL |
|
661 |
CHARACTER_SET_NAME NULL |
|
662 |
COLLATION_NAME NULL |
|
663 |
DTD_IDENTIFIER NULL |
|
664 |
ROUTINE_BODY SQL |
|
665 |
ROUTINE_DEFINITION BEGIN |
|
666 |
set @x=i1; |
|
667 |
END
|
|
668 |
EXTERNAL_NAME NULL |
|
669 |
EXTERNAL_LANGUAGE NULL |
|
670 |
PARAMETER_STYLE SQL |
|
671 |
IS_DETERMINISTIC NO |
|
672 |
SQL_DATA_ACCESS CONTAINS SQL |
|
673 |
SQL_PATH NULL |
|
674 |
SECURITY_TYPE INVOKER |
|
675 |
CREATED <created> |
|
676 |
LAST_ALTERED <modified> |
|
677 |
SQL_MODE
|
|
678 |
ROUTINE_COMMENT new comment, SP changed to INVOKER |
|
679 |
DEFINER root@localhost |
|
680 |
CHARACTER_SET_CLIENT latin1 |
|
681 |
COLLATION_CONNECTION latin1_swedish_ci |
|
682 |
DATABASE_COLLATION latin1_swedish_ci |
|
683 |
SPECIFIC_NAME sp_2 |
|
684 |
ROUTINE_CATALOG NULL |
|
685 |
ROUTINE_SCHEMA db_storedproc |
|
686 |
ROUTINE_NAME sp_2 |
|
687 |
ROUTINE_TYPE PROCEDURE |
|
688 |
DATA_TYPE
|
|
689 |
CHARACTER_MAXIMUM_LENGTH NULL |
|
690 |
CHARACTER_OCTET_LENGTH NULL |
|
691 |
NUMERIC_PRECISION NULL |
|
692 |
NUMERIC_SCALE NULL |
|
693 |
CHARACTER_SET_NAME NULL |
|
694 |
COLLATION_NAME NULL |
|
695 |
DTD_IDENTIFIER NULL |
|
696 |
ROUTINE_BODY SQL |
|
697 |
ROUTINE_DEFINITION BEGIN |
|
698 |
set @x=i1; |
|
699 |
END
|
|
700 |
EXTERNAL_NAME NULL |
|
701 |
EXTERNAL_LANGUAGE NULL |
|
702 |
PARAMETER_STYLE SQL |
|
703 |
IS_DETERMINISTIC NO |
|
704 |
SQL_DATA_ACCESS READS SQL DATA |
|
705 |
SQL_PATH NULL |
|
706 |
SECURITY_TYPE DEFINER |
|
707 |
CREATED <created> |
|
708 |
LAST_ALTERED <modified> |
|
709 |
SQL_MODE
|
|
710 |
ROUTINE_COMMENT SP changed to DEFINER |
|
711 |
DEFINER root@localhost |
|
712 |
CHARACTER_SET_CLIENT latin1 |
|
713 |
COLLATION_CONNECTION latin1_swedish_ci |
|
714 |
DATABASE_COLLATION latin1_swedish_ci |
|
715 |
SHOW CREATE FUNCTION fn_1; |
|
716 |
Function fn_1 |
|
717 |
sql_mode
|
|
718 |
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) |
|
719 |
NO SQL |
|
720 |
SQL SECURITY INVOKER |
|
721 |
COMMENT 'new comment, FN changed to INVOKER' |
|
722 |
BEGIN
|
|
723 |
set @x=i1; |
|
724 |
set @y=@x; |
|
725 |
return i4; |
|
726 |
END
|
|
727 |
character_set_client latin1 |
|
728 |
collation_connection latin1_swedish_ci |
|
729 |
Database Collation latin1_swedish_ci |
|
730 |
SHOW CREATE FUNCTION fn_2; |
|
731 |
Function fn_2 |
|
732 |
sql_mode
|
|
733 |
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) |
|
734 |
COMMENT 'FN changed to DEFINER' |
|
735 |
BEGIN
|
|
736 |
set @x=i1; |
|
737 |
set @y=@x; |
|
738 |
return i4; |
|
739 |
END
|
|
740 |
character_set_client latin1 |
|
741 |
collation_connection latin1_swedish_ci |
|
742 |
Database Collation latin1_swedish_ci |
|
743 |
SHOW CREATE PROCEDURE sp_1; |
|
744 |
Procedure sp_1 |
|
745 |
sql_mode
|
|
746 |
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) |
|
747 |
SQL SECURITY INVOKER |
|
748 |
COMMENT 'new comment, SP changed to INVOKER' |
|
749 |
BEGIN
|
|
750 |
set @x=i1; |
|
751 |
END
|
|
752 |
character_set_client latin1 |
|
753 |
collation_connection latin1_swedish_ci |
|
754 |
Database Collation latin1_swedish_ci |
|
755 |
SHOW CREATE PROCEDURE sp_2; |
|
756 |
Procedure sp_2 |
|
757 |
sql_mode
|
|
758 |
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) |
|
759 |
READS SQL DATA |
|
760 |
COMMENT 'SP changed to DEFINER' |
|
761 |
BEGIN
|
|
762 |
set @x=i1; |
|
763 |
END
|
|
764 |
character_set_client latin1 |
|
765 |
collation_connection latin1_swedish_ci |
|
766 |
Database Collation latin1_swedish_ci |
|
767 |
SHOW FUNCTION STATUS LIKE 'fn_%'; |
|
768 |
Db db_storedproc |
|
769 |
Name fn_1 |
|
770 |
Type FUNCTION |
|
771 |
Definer root@localhost |
|
772 |
Modified <modified> |
|
773 |
Created <created> |
|
774 |
Security_type INVOKER |
|
775 |
Comment new comment, FN changed to INVOKER |
|
776 |
character_set_client latin1 |
|
777 |
collation_connection latin1_swedish_ci |
|
778 |
Database Collation latin1_swedish_ci |
|
779 |
Db db_storedproc |
|
780 |
Name fn_2 |
|
781 |
Type FUNCTION |
|
782 |
Definer root@localhost |
|
783 |
Modified <modified> |
|
784 |
Created <created> |
|
785 |
Security_type DEFINER |
|
786 |
Comment FN changed to DEFINER |
|
787 |
character_set_client latin1 |
|
788 |
collation_connection latin1_swedish_ci |
|
789 |
Database Collation latin1_swedish_ci |
|
790 |
SHOW PROCEDURE STATUS LIKE 'sp_%'; |
|
791 |
Db db_storedproc |
|
792 |
Name sp_1 |
|
793 |
Type PROCEDURE |
|
794 |
Definer root@localhost |
|
795 |
Modified <modified> |
|
796 |
Created <created> |
|
797 |
Security_type INVOKER |
|
798 |
Comment new comment, SP changed to INVOKER |
|
799 |
character_set_client latin1 |
|
800 |
collation_connection latin1_swedish_ci |
|
801 |
Database Collation latin1_swedish_ci |
|
802 |
Db db_storedproc |
|
803 |
Name sp_2 |
|
804 |
Type PROCEDURE |
|
805 |
Definer root@localhost |
|
806 |
Modified <modified> |
|
807 |
Created <created> |
|
808 |
Security_type DEFINER |
|
809 |
Comment SP changed to DEFINER |
|
810 |
character_set_client latin1 |
|
811 |
collation_connection latin1_swedish_ci |
|
812 |
Database Collation latin1_swedish_ci |
|
813 |
||
814 |
... cleanup |
|
815 |
-----------
|
|
816 |
DROP FUNCTION fn_1; |
|
817 |
DROP FUNCTION fn_2; |
|
818 |
DROP PROCEDURE sp_1; |
|
819 |
||
820 |
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc |
|
821 |
--------------------------------------------------------------------------------
|
|
822 |
DROP DATABASE IF EXISTS db_storedproc; |
|
823 |
DROP DATABASE IF EXISTS db_storedproc_1; |
|
824 |
||
825 |
. +++ END OF SCRIPT +++ |
|
826 |
--------------------------------------------------------------------------------
|
|
827 |