Debian Codegraph @

mysql-test/suite/funcs_1/r/myisam_storedproc_08.result

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