1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
3 --source suite/funcs_1/storedproc/load_sp_tb.inc
4 --------------------------------------------------------------------------------
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;
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;
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;
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' into table t7;
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' into table t8;
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;
66 Section 3.1.8 - SHOW statement checks:
67 --------------------------------------------------------------------------------
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)
85 CREATE PROCEDURE sp_2 (i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER'
89 CREATE FUNCTION fn_1 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) returns year
95 CREATE FUNCTION fn_2 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real)
98 COMMENT 'created with INVOKER'
105 ... now check what is stored:
106 -----------------------------
107 SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc';
110 ROUTINE_SCHEMA db_storedproc
112 ROUTINE_TYPE FUNCTION
113 DTD_IDENTIFIER year(4)
115 ROUTINE_DEFINITION BEGIN
121 EXTERNAL_LANGUAGE NULL
124 SQL_DATA_ACCESS CONTAINS SQL
126 SECURITY_TYPE DEFINER
128 LAST_ALTERED <created>
129 SQL_MODE NO_ENGINE_SUBSTITUTION
131 DEFINER root@localhost
132 CHARACTER_SET_CLIENT latin1
133 COLLATION_CONNECTION latin1_swedish_ci
134 DATABASE_COLLATION latin1_swedish_ci
137 ROUTINE_SCHEMA db_storedproc
139 ROUTINE_TYPE FUNCTION
140 DTD_IDENTIFIER year(4)
142 ROUTINE_DEFINITION BEGIN
148 EXTERNAL_LANGUAGE NULL
151 SQL_DATA_ACCESS CONTAINS SQL
153 SECURITY_TYPE INVOKER
155 LAST_ALTERED <created>
156 SQL_MODE NO_ENGINE_SUBSTITUTION
157 ROUTINE_COMMENT created with INVOKER
158 DEFINER root@localhost
159 CHARACTER_SET_CLIENT latin1
160 COLLATION_CONNECTION latin1_swedish_ci
161 DATABASE_COLLATION latin1_swedish_ci
164 ROUTINE_SCHEMA db_storedproc
166 ROUTINE_TYPE PROCEDURE
169 ROUTINE_DEFINITION BEGIN
173 EXTERNAL_LANGUAGE NULL
176 SQL_DATA_ACCESS CONTAINS SQL
178 SECURITY_TYPE DEFINER
180 LAST_ALTERED <created>
181 SQL_MODE NO_ENGINE_SUBSTITUTION
183 DEFINER root@localhost
184 CHARACTER_SET_CLIENT latin1
185 COLLATION_CONNECTION latin1_swedish_ci
186 DATABASE_COLLATION latin1_swedish_ci
189 ROUTINE_SCHEMA db_storedproc
191 ROUTINE_TYPE PROCEDURE
194 ROUTINE_DEFINITION BEGIN
198 EXTERNAL_LANGUAGE NULL
201 SQL_DATA_ACCESS CONTAINS SQL
203 SECURITY_TYPE INVOKER
205 LAST_ALTERED <created>
206 SQL_MODE NO_ENGINE_SUBSTITUTION
207 ROUTINE_COMMENT created with INVOKER
208 DEFINER root@localhost
209 CHARACTER_SET_CLIENT latin1
210 COLLATION_CONNECTION latin1_swedish_ci
211 DATABASE_COLLATION latin1_swedish_ci
212 SHOW CREATE FUNCTION fn_1;
214 sql_mode NO_ENGINE_SUBSTITUTION
215 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
221 character_set_client latin1
222 collation_connection latin1_swedish_ci
223 Database Collation latin1_swedish_ci
224 SHOW CREATE FUNCTION fn_2;
226 sql_mode NO_ENGINE_SUBSTITUTION
227 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
229 COMMENT 'created with INVOKER'
235 character_set_client latin1
236 collation_connection latin1_swedish_ci
237 Database Collation latin1_swedish_ci
238 SHOW CREATE PROCEDURE sp_1;
240 sql_mode NO_ENGINE_SUBSTITUTION
241 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
245 character_set_client latin1
246 collation_connection latin1_swedish_ci
247 Database Collation latin1_swedish_ci
248 SHOW CREATE PROCEDURE sp_2;
250 sql_mode NO_ENGINE_SUBSTITUTION
251 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
253 COMMENT 'created with INVOKER'
257 character_set_client latin1
258 collation_connection latin1_swedish_ci
259 Database Collation latin1_swedish_ci
260 SHOW FUNCTION STATUS LIKE 'fn_%';
264 Definer root@localhost
267 Security_type DEFINER
269 character_set_client latin1
270 collation_connection latin1_swedish_ci
271 Database Collation latin1_swedish_ci
275 Definer root@localhost
278 Security_type INVOKER
279 Comment created with INVOKER
280 character_set_client latin1
281 collation_connection latin1_swedish_ci
282 Database Collation latin1_swedish_ci
283 SHOW PROCEDURE STATUS LIKE 'sp_%';
287 Definer root@localhost
290 Security_type DEFINER
292 character_set_client latin1
293 collation_connection latin1_swedish_ci
294 Database Collation latin1_swedish_ci
298 Definer root@localhost
301 Security_type INVOKER
302 Comment created with INVOKER
303 character_set_client latin1
304 collation_connection latin1_swedish_ci
305 Database Collation latin1_swedish_ci
307 ... now change some stuff:
308 --------------------------
309 ALTER PROCEDURE sp_1 SQL SECURITY INVOKER;
310 ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER';
311 ALTER PROCEDURE sp_2 SQL SECURITY DEFINER;
312 ALTER PROCEDURE sp_2 DROP COMMENT;
313 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
314 ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER';
315 ALTER PROCEDURE sp_2 READS SQL DATA;
316 ALTER FUNCTION fn_1 SQL SECURITY INVOKER;
317 ALTER FUNCTION fn_1 COMMENT 'new comment, FN changed to INVOKER';
318 ALTER FUNCTION fn_1 NO SQL;
319 ALTER FUNCTION fn_2 SQL SECURITY DEFINER;
320 ALTER FUNCTION fn_2 COMMENT 'FN changed to DEFINER';
321 ALTER FUNCTION fn_2 MODIFIES SQL DATA;
323 ... now check what is stored:
324 -----------------------------
325 SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc';
328 ROUTINE_SCHEMA db_storedproc
330 ROUTINE_TYPE FUNCTION
331 DTD_IDENTIFIER year(4)
333 ROUTINE_DEFINITION BEGIN
339 EXTERNAL_LANGUAGE NULL
342 SQL_DATA_ACCESS NO SQL
344 SECURITY_TYPE INVOKER
346 LAST_ALTERED <created>
347 SQL_MODE NO_ENGINE_SUBSTITUTION
348 ROUTINE_COMMENT new comment, FN changed to INVOKER
349 DEFINER root@localhost
350 CHARACTER_SET_CLIENT latin1
351 COLLATION_CONNECTION latin1_swedish_ci
352 DATABASE_COLLATION latin1_swedish_ci
355 ROUTINE_SCHEMA db_storedproc
357 ROUTINE_TYPE FUNCTION
358 DTD_IDENTIFIER year(4)
360 ROUTINE_DEFINITION BEGIN
366 EXTERNAL_LANGUAGE NULL
369 SQL_DATA_ACCESS MODIFIES SQL DATA
371 SECURITY_TYPE DEFINER
373 LAST_ALTERED <created>
374 SQL_MODE NO_ENGINE_SUBSTITUTION
375 ROUTINE_COMMENT FN changed to DEFINER
376 DEFINER root@localhost
377 CHARACTER_SET_CLIENT latin1
378 COLLATION_CONNECTION latin1_swedish_ci
379 DATABASE_COLLATION latin1_swedish_ci
382 ROUTINE_SCHEMA db_storedproc
384 ROUTINE_TYPE PROCEDURE
387 ROUTINE_DEFINITION BEGIN
391 EXTERNAL_LANGUAGE NULL
394 SQL_DATA_ACCESS CONTAINS SQL
396 SECURITY_TYPE INVOKER
398 LAST_ALTERED <created>
399 SQL_MODE NO_ENGINE_SUBSTITUTION
400 ROUTINE_COMMENT new comment, SP changed to INVOKER
401 DEFINER root@localhost
402 CHARACTER_SET_CLIENT latin1
403 COLLATION_CONNECTION latin1_swedish_ci
404 DATABASE_COLLATION latin1_swedish_ci
407 ROUTINE_SCHEMA db_storedproc
409 ROUTINE_TYPE PROCEDURE
412 ROUTINE_DEFINITION BEGIN
416 EXTERNAL_LANGUAGE NULL
419 SQL_DATA_ACCESS READS SQL DATA
421 SECURITY_TYPE DEFINER
423 LAST_ALTERED <created>
424 SQL_MODE NO_ENGINE_SUBSTITUTION
425 ROUTINE_COMMENT SP changed to DEFINER
426 DEFINER root@localhost
427 CHARACTER_SET_CLIENT latin1
428 COLLATION_CONNECTION latin1_swedish_ci
429 DATABASE_COLLATION latin1_swedish_ci
430 SHOW CREATE FUNCTION fn_1;
432 sql_mode NO_ENGINE_SUBSTITUTION
433 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
436 COMMENT 'new comment, FN changed to INVOKER'
442 character_set_client latin1
443 collation_connection latin1_swedish_ci
444 Database Collation latin1_swedish_ci
445 SHOW CREATE FUNCTION fn_2;
447 sql_mode NO_ENGINE_SUBSTITUTION
448 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
450 COMMENT 'FN changed to DEFINER'
456 character_set_client latin1
457 collation_connection latin1_swedish_ci
458 Database Collation latin1_swedish_ci
459 SHOW CREATE PROCEDURE sp_1;
461 sql_mode NO_ENGINE_SUBSTITUTION
462 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
464 COMMENT 'new comment, SP changed to INVOKER'
468 character_set_client latin1
469 collation_connection latin1_swedish_ci
470 Database Collation latin1_swedish_ci
471 SHOW CREATE PROCEDURE sp_2;
473 sql_mode NO_ENGINE_SUBSTITUTION
474 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
476 COMMENT 'SP changed to DEFINER'
480 character_set_client latin1
481 collation_connection latin1_swedish_ci
482 Database Collation latin1_swedish_ci
483 SHOW FUNCTION STATUS LIKE 'fn_%';
487 Definer root@localhost
490 Security_type INVOKER
491 Comment new comment, FN changed to INVOKER
492 character_set_client latin1
493 collation_connection latin1_swedish_ci
494 Database Collation latin1_swedish_ci
498 Definer root@localhost
501 Security_type DEFINER
502 Comment FN changed to DEFINER
503 character_set_client latin1
504 collation_connection latin1_swedish_ci
505 Database Collation latin1_swedish_ci
506 SHOW PROCEDURE STATUS LIKE 'sp_%';
510 Definer root@localhost
513 Security_type INVOKER
514 Comment new comment, SP changed to INVOKER
515 character_set_client latin1
516 collation_connection latin1_swedish_ci
517 Database Collation latin1_swedish_ci
521 Definer root@localhost
524 Security_type DEFINER
525 Comment SP changed to DEFINER
526 character_set_client latin1
527 collation_connection latin1_swedish_ci
528 Database Collation latin1_swedish_ci
530 ... change back to default and check result:
531 --------------------------------------------
532 ALTER FUNCTION fn_2 CONTAINS SQL;
534 ... now check what is stored:
535 -----------------------------
536 SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc';
539 ROUTINE_SCHEMA db_storedproc
541 ROUTINE_TYPE FUNCTION
542 DTD_IDENTIFIER year(4)
544 ROUTINE_DEFINITION BEGIN
550 EXTERNAL_LANGUAGE NULL
553 SQL_DATA_ACCESS NO SQL
555 SECURITY_TYPE INVOKER
557 LAST_ALTERED <created>
558 SQL_MODE NO_ENGINE_SUBSTITUTION
559 ROUTINE_COMMENT new comment, FN changed to INVOKER
560 DEFINER root@localhost
561 CHARACTER_SET_CLIENT latin1
562 COLLATION_CONNECTION latin1_swedish_ci
563 DATABASE_COLLATION latin1_swedish_ci
566 ROUTINE_SCHEMA db_storedproc
568 ROUTINE_TYPE FUNCTION
569 DTD_IDENTIFIER year(4)
571 ROUTINE_DEFINITION BEGIN
577 EXTERNAL_LANGUAGE NULL
580 SQL_DATA_ACCESS CONTAINS SQL
582 SECURITY_TYPE DEFINER
584 LAST_ALTERED <created>
585 SQL_MODE NO_ENGINE_SUBSTITUTION
586 ROUTINE_COMMENT FN changed to DEFINER
587 DEFINER root@localhost
588 CHARACTER_SET_CLIENT latin1
589 COLLATION_CONNECTION latin1_swedish_ci
590 DATABASE_COLLATION latin1_swedish_ci
593 ROUTINE_SCHEMA db_storedproc
595 ROUTINE_TYPE PROCEDURE
598 ROUTINE_DEFINITION BEGIN
602 EXTERNAL_LANGUAGE NULL
605 SQL_DATA_ACCESS CONTAINS SQL
607 SECURITY_TYPE INVOKER
609 LAST_ALTERED <created>
610 SQL_MODE NO_ENGINE_SUBSTITUTION
611 ROUTINE_COMMENT new comment, SP changed to INVOKER
612 DEFINER root@localhost
613 CHARACTER_SET_CLIENT latin1
614 COLLATION_CONNECTION latin1_swedish_ci
615 DATABASE_COLLATION latin1_swedish_ci
618 ROUTINE_SCHEMA db_storedproc
620 ROUTINE_TYPE PROCEDURE
623 ROUTINE_DEFINITION BEGIN
627 EXTERNAL_LANGUAGE NULL
630 SQL_DATA_ACCESS READS SQL DATA
632 SECURITY_TYPE DEFINER
634 LAST_ALTERED <created>
635 SQL_MODE NO_ENGINE_SUBSTITUTION
636 ROUTINE_COMMENT SP changed to DEFINER
637 DEFINER root@localhost
638 CHARACTER_SET_CLIENT latin1
639 COLLATION_CONNECTION latin1_swedish_ci
640 DATABASE_COLLATION latin1_swedish_ci
641 SHOW CREATE FUNCTION fn_1;
643 sql_mode NO_ENGINE_SUBSTITUTION
644 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
647 COMMENT 'new comment, FN changed to INVOKER'
653 character_set_client latin1
654 collation_connection latin1_swedish_ci
655 Database Collation latin1_swedish_ci
656 SHOW CREATE FUNCTION fn_2;
658 sql_mode NO_ENGINE_SUBSTITUTION
659 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
660 COMMENT 'FN changed to DEFINER'
666 character_set_client latin1
667 collation_connection latin1_swedish_ci
668 Database Collation latin1_swedish_ci
669 SHOW CREATE PROCEDURE sp_1;
671 sql_mode NO_ENGINE_SUBSTITUTION
672 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
674 COMMENT 'new comment, SP changed to INVOKER'
678 character_set_client latin1
679 collation_connection latin1_swedish_ci
680 Database Collation latin1_swedish_ci
681 SHOW CREATE PROCEDURE sp_2;
683 sql_mode NO_ENGINE_SUBSTITUTION
684 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
686 COMMENT 'SP changed to DEFINER'
690 character_set_client latin1
691 collation_connection latin1_swedish_ci
692 Database Collation latin1_swedish_ci
693 SHOW FUNCTION STATUS LIKE 'fn_%';
697 Definer root@localhost
700 Security_type INVOKER
701 Comment new comment, FN changed to INVOKER
702 character_set_client latin1
703 collation_connection latin1_swedish_ci
704 Database Collation latin1_swedish_ci
708 Definer root@localhost
711 Security_type DEFINER
712 Comment FN changed to DEFINER
713 character_set_client latin1
714 collation_connection latin1_swedish_ci
715 Database Collation latin1_swedish_ci
716 SHOW PROCEDURE STATUS LIKE 'sp_%';
720 Definer root@localhost
723 Security_type INVOKER
724 Comment new comment, SP changed to INVOKER
725 character_set_client latin1
726 collation_connection latin1_swedish_ci
727 Database Collation latin1_swedish_ci
731 Definer root@localhost
734 Security_type DEFINER
735 Comment SP changed to DEFINER
736 character_set_client latin1
737 collation_connection latin1_swedish_ci
738 Database Collation latin1_swedish_ci
746 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
747 --------------------------------------------------------------------------------
748 DROP DATABASE IF EXISTS db_storedproc;
749 DROP DATABASE IF EXISTS db_storedproc_1;
751 . +++ END OF SCRIPT +++
752 --------------------------------------------------------------------------------