2 --source suite/funcs_1/storedproc/load_sp_tb.inc
3 --------------------------------------------------------------------------------
5 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
6 --------------------------------------------------------------------------------
7 DROP DATABASE IF EXISTS db_storedproc;
8 DROP DATABASE IF EXISTS db_storedproc_1;
9 CREATE DATABASE db_storedproc;
10 CREATE DATABASE db_storedproc_1;
12 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
13 engine = <engine_to_be_tested>;
14 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
15 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
16 engine = <engine_to_be_tested>;
17 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
18 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
19 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
20 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
21 engine = <engine_to_be_tested>;
22 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
24 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
25 engine = <engine_to_be_tested>;
26 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
28 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
29 engine = <engine_to_be_tested>;
30 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
32 Warning 1265 Data truncated for column 'f3' at row 1
33 Warning 1265 Data truncated for column 'f3' at row 2
34 Warning 1265 Data truncated for column 'f3' at row 3
35 Warning 1265 Data truncated for column 'f3' at row 4
36 Warning 1265 Data truncated for column 'f3' at row 5
37 Warning 1265 Data truncated for column 'f3' at row 6
38 Warning 1265 Data truncated for column 'f3' at row 7
39 Warning 1265 Data truncated for column 'f3' at row 8
40 Warning 1265 Data truncated for column 'f3' at row 9
41 Warning 1265 Data truncated for column 'f3' at row 10
42 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
43 engine = <engine_to_be_tested>;
44 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
46 Warning 1265 Data truncated for column 'f3' at row 1
47 Warning 1265 Data truncated for column 'f3' at row 2
48 Warning 1265 Data truncated for column 'f3' at row 3
49 Warning 1265 Data truncated for column 'f3' at row 4
50 Warning 1265 Data truncated for column 'f3' at row 5
51 Warning 1265 Data truncated for column 'f3' at row 6
52 Warning 1265 Data truncated for column 'f3' at row 7
53 Warning 1265 Data truncated for column 'f3' at row 8
54 Warning 1265 Data truncated for column 'f3' at row 9
55 Warning 1265 Data truncated for column 'f3' at row 10
56 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
57 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
58 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
59 engine = <engine_to_be_tested>;
60 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
61 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
62 engine = <engine_to_be_tested>;
63 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
65 Section 3.1.8 - SHOW statement checks:
66 --------------------------------------------------------------------------------
71 Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER
72 FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or
73 SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE
74 FUNCTION STATUS statement (respectively) is executed.
75 --------------------------------------------------------------------------------
76 DROP FUNCTION IF EXISTS fn_1;
77 DROP FUNCTION IF EXISTS fn_2;
78 DROP PROCEDURE IF EXISTS sp_1;
79 DROP PROCEDURE IF EXISTS sp_2;
80 CREATE PROCEDURE sp_1 (i1 int)
84 CREATE PROCEDURE sp_2 (i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER'
88 CREATE FUNCTION fn_1 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) returns year
94 CREATE FUNCTION fn_2 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real)
97 COMMENT 'created with INVOKER'
104 ... now check what is stored:
105 -----------------------------
106 SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc';
109 ROUTINE_SCHEMA db_storedproc
111 ROUTINE_TYPE FUNCTION
112 DTD_IDENTIFIER year(4)
114 ROUTINE_DEFINITION BEGIN
120 EXTERNAL_LANGUAGE NULL
123 SQL_DATA_ACCESS CONTAINS SQL
125 SECURITY_TYPE DEFINER
127 LAST_ALTERED <created>
130 DEFINER root@localhost
131 CHARACTER_SET_CLIENT latin1
132 COLLATION_CONNECTION latin1_swedish_ci
133 DATABASE_COLLATION latin1_swedish_ci
136 ROUTINE_SCHEMA db_storedproc
138 ROUTINE_TYPE FUNCTION
139 DTD_IDENTIFIER year(4)
141 ROUTINE_DEFINITION BEGIN
147 EXTERNAL_LANGUAGE NULL
150 SQL_DATA_ACCESS CONTAINS SQL
152 SECURITY_TYPE INVOKER
154 LAST_ALTERED <created>
156 ROUTINE_COMMENT created with INVOKER
157 DEFINER root@localhost
158 CHARACTER_SET_CLIENT latin1
159 COLLATION_CONNECTION latin1_swedish_ci
160 DATABASE_COLLATION latin1_swedish_ci
163 ROUTINE_SCHEMA db_storedproc
165 ROUTINE_TYPE PROCEDURE
168 ROUTINE_DEFINITION BEGIN
172 EXTERNAL_LANGUAGE NULL
175 SQL_DATA_ACCESS CONTAINS SQL
177 SECURITY_TYPE DEFINER
179 LAST_ALTERED <created>
182 DEFINER root@localhost
183 CHARACTER_SET_CLIENT latin1
184 COLLATION_CONNECTION latin1_swedish_ci
185 DATABASE_COLLATION latin1_swedish_ci
188 ROUTINE_SCHEMA db_storedproc
190 ROUTINE_TYPE PROCEDURE
193 ROUTINE_DEFINITION BEGIN
197 EXTERNAL_LANGUAGE NULL
200 SQL_DATA_ACCESS CONTAINS SQL
202 SECURITY_TYPE INVOKER
204 LAST_ALTERED <created>
206 ROUTINE_COMMENT created with INVOKER
207 DEFINER root@localhost
208 CHARACTER_SET_CLIENT latin1
209 COLLATION_CONNECTION latin1_swedish_ci
210 DATABASE_COLLATION latin1_swedish_ci
211 SHOW CREATE FUNCTION fn_1;
214 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
220 character_set_client latin1
221 collation_connection latin1_swedish_ci
222 Database Collation latin1_swedish_ci
223 SHOW CREATE FUNCTION fn_2;
226 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
228 COMMENT 'created with INVOKER'
234 character_set_client latin1
235 collation_connection latin1_swedish_ci
236 Database Collation latin1_swedish_ci
237 SHOW CREATE PROCEDURE sp_1;
240 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
244 character_set_client latin1
245 collation_connection latin1_swedish_ci
246 Database Collation latin1_swedish_ci
247 SHOW CREATE PROCEDURE sp_2;
250 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
252 COMMENT 'created with INVOKER'
256 character_set_client latin1
257 collation_connection latin1_swedish_ci
258 Database Collation latin1_swedish_ci
259 SHOW FUNCTION STATUS LIKE 'fn_%';
263 Definer root@localhost
266 Security_type DEFINER
268 character_set_client latin1
269 collation_connection latin1_swedish_ci
270 Database Collation latin1_swedish_ci
274 Definer root@localhost
277 Security_type INVOKER
278 Comment created with INVOKER
279 character_set_client latin1
280 collation_connection latin1_swedish_ci
281 Database Collation latin1_swedish_ci
282 SHOW PROCEDURE STATUS LIKE 'sp_%';
286 Definer root@localhost
289 Security_type DEFINER
291 character_set_client latin1
292 collation_connection latin1_swedish_ci
293 Database Collation latin1_swedish_ci
297 Definer root@localhost
300 Security_type INVOKER
301 Comment created with INVOKER
302 character_set_client latin1
303 collation_connection latin1_swedish_ci
304 Database Collation latin1_swedish_ci
306 ... now change some stuff:
307 --------------------------
308 ALTER PROCEDURE sp_1 SQL SECURITY INVOKER;
309 ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER';
310 ALTER PROCEDURE sp_2 SQL SECURITY DEFINER;
311 ALTER PROCEDURE sp_2 DROP COMMENT;
312 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
313 ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER';
314 ALTER PROCEDURE sp_2 READS SQL DATA;
315 ALTER FUNCTION fn_1 SQL SECURITY INVOKER;
316 ALTER FUNCTION fn_1 COMMENT 'new comment, FN changed to INVOKER';
317 ALTER FUNCTION fn_1 NO SQL;
318 ALTER FUNCTION fn_2 SQL SECURITY DEFINER;
319 ALTER FUNCTION fn_2 COMMENT 'FN changed to DEFINER';
320 ALTER FUNCTION fn_2 MODIFIES SQL DATA;
322 ... now check what is stored:
323 -----------------------------
324 SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc';
327 ROUTINE_SCHEMA db_storedproc
329 ROUTINE_TYPE FUNCTION
330 DTD_IDENTIFIER year(4)
332 ROUTINE_DEFINITION BEGIN
338 EXTERNAL_LANGUAGE NULL
341 SQL_DATA_ACCESS NO SQL
343 SECURITY_TYPE INVOKER
345 LAST_ALTERED <created>
347 ROUTINE_COMMENT new comment, FN changed to INVOKER
348 DEFINER root@localhost
349 CHARACTER_SET_CLIENT latin1
350 COLLATION_CONNECTION latin1_swedish_ci
351 DATABASE_COLLATION latin1_swedish_ci
354 ROUTINE_SCHEMA db_storedproc
356 ROUTINE_TYPE FUNCTION
357 DTD_IDENTIFIER year(4)
359 ROUTINE_DEFINITION BEGIN
365 EXTERNAL_LANGUAGE NULL
368 SQL_DATA_ACCESS MODIFIES SQL DATA
370 SECURITY_TYPE DEFINER
372 LAST_ALTERED <created>
374 ROUTINE_COMMENT FN changed to DEFINER
375 DEFINER root@localhost
376 CHARACTER_SET_CLIENT latin1
377 COLLATION_CONNECTION latin1_swedish_ci
378 DATABASE_COLLATION latin1_swedish_ci
381 ROUTINE_SCHEMA db_storedproc
383 ROUTINE_TYPE PROCEDURE
386 ROUTINE_DEFINITION BEGIN
390 EXTERNAL_LANGUAGE NULL
393 SQL_DATA_ACCESS CONTAINS SQL
395 SECURITY_TYPE INVOKER
397 LAST_ALTERED <created>
399 ROUTINE_COMMENT new comment, SP changed to INVOKER
400 DEFINER root@localhost
401 CHARACTER_SET_CLIENT latin1
402 COLLATION_CONNECTION latin1_swedish_ci
403 DATABASE_COLLATION latin1_swedish_ci
406 ROUTINE_SCHEMA db_storedproc
408 ROUTINE_TYPE PROCEDURE
411 ROUTINE_DEFINITION BEGIN
415 EXTERNAL_LANGUAGE NULL
418 SQL_DATA_ACCESS READS SQL DATA
420 SECURITY_TYPE DEFINER
422 LAST_ALTERED <created>
424 ROUTINE_COMMENT SP changed to DEFINER
425 DEFINER root@localhost
426 CHARACTER_SET_CLIENT latin1
427 COLLATION_CONNECTION latin1_swedish_ci
428 DATABASE_COLLATION latin1_swedish_ci
429 SHOW CREATE FUNCTION fn_1;
432 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
435 COMMENT 'new comment, FN changed to INVOKER'
441 character_set_client latin1
442 collation_connection latin1_swedish_ci
443 Database Collation latin1_swedish_ci
444 SHOW CREATE FUNCTION fn_2;
447 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
449 COMMENT 'FN changed to DEFINER'
455 character_set_client latin1
456 collation_connection latin1_swedish_ci
457 Database Collation latin1_swedish_ci
458 SHOW CREATE PROCEDURE sp_1;
461 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
463 COMMENT 'new comment, SP changed to INVOKER'
467 character_set_client latin1
468 collation_connection latin1_swedish_ci
469 Database Collation latin1_swedish_ci
470 SHOW CREATE PROCEDURE sp_2;
473 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
475 COMMENT 'SP changed to DEFINER'
479 character_set_client latin1
480 collation_connection latin1_swedish_ci
481 Database Collation latin1_swedish_ci
482 SHOW FUNCTION STATUS LIKE 'fn_%';
486 Definer root@localhost
489 Security_type INVOKER
490 Comment new comment, FN changed to INVOKER
491 character_set_client latin1
492 collation_connection latin1_swedish_ci
493 Database Collation latin1_swedish_ci
497 Definer root@localhost
500 Security_type DEFINER
501 Comment FN changed to DEFINER
502 character_set_client latin1
503 collation_connection latin1_swedish_ci
504 Database Collation latin1_swedish_ci
505 SHOW PROCEDURE STATUS LIKE 'sp_%';
509 Definer root@localhost
512 Security_type INVOKER
513 Comment new comment, SP changed to INVOKER
514 character_set_client latin1
515 collation_connection latin1_swedish_ci
516 Database Collation latin1_swedish_ci
520 Definer root@localhost
523 Security_type DEFINER
524 Comment SP changed to DEFINER
525 character_set_client latin1
526 collation_connection latin1_swedish_ci
527 Database Collation latin1_swedish_ci
529 ... change back to default and check result:
530 --------------------------------------------
531 ALTER FUNCTION fn_2 CONTAINS SQL;
533 ... now check what is stored:
534 -----------------------------
535 SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc';
538 ROUTINE_SCHEMA db_storedproc
540 ROUTINE_TYPE FUNCTION
541 DTD_IDENTIFIER year(4)
543 ROUTINE_DEFINITION BEGIN
549 EXTERNAL_LANGUAGE NULL
552 SQL_DATA_ACCESS NO SQL
554 SECURITY_TYPE INVOKER
556 LAST_ALTERED <created>
558 ROUTINE_COMMENT new comment, FN changed to INVOKER
559 DEFINER root@localhost
560 CHARACTER_SET_CLIENT latin1
561 COLLATION_CONNECTION latin1_swedish_ci
562 DATABASE_COLLATION latin1_swedish_ci
565 ROUTINE_SCHEMA db_storedproc
567 ROUTINE_TYPE FUNCTION
568 DTD_IDENTIFIER year(4)
570 ROUTINE_DEFINITION BEGIN
576 EXTERNAL_LANGUAGE NULL
579 SQL_DATA_ACCESS CONTAINS SQL
581 SECURITY_TYPE DEFINER
583 LAST_ALTERED <created>
585 ROUTINE_COMMENT FN changed to DEFINER
586 DEFINER root@localhost
587 CHARACTER_SET_CLIENT latin1
588 COLLATION_CONNECTION latin1_swedish_ci
589 DATABASE_COLLATION latin1_swedish_ci
592 ROUTINE_SCHEMA db_storedproc
594 ROUTINE_TYPE PROCEDURE
597 ROUTINE_DEFINITION BEGIN
601 EXTERNAL_LANGUAGE NULL
604 SQL_DATA_ACCESS CONTAINS SQL
606 SECURITY_TYPE INVOKER
608 LAST_ALTERED <created>
610 ROUTINE_COMMENT new comment, SP changed to INVOKER
611 DEFINER root@localhost
612 CHARACTER_SET_CLIENT latin1
613 COLLATION_CONNECTION latin1_swedish_ci
614 DATABASE_COLLATION latin1_swedish_ci
617 ROUTINE_SCHEMA db_storedproc
619 ROUTINE_TYPE PROCEDURE
622 ROUTINE_DEFINITION BEGIN
626 EXTERNAL_LANGUAGE NULL
629 SQL_DATA_ACCESS READS SQL DATA
631 SECURITY_TYPE DEFINER
633 LAST_ALTERED <created>
635 ROUTINE_COMMENT SP changed to DEFINER
636 DEFINER root@localhost
637 CHARACTER_SET_CLIENT latin1
638 COLLATION_CONNECTION latin1_swedish_ci
639 DATABASE_COLLATION latin1_swedish_ci
640 SHOW CREATE FUNCTION fn_1;
643 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
646 COMMENT 'new comment, FN changed to INVOKER'
652 character_set_client latin1
653 collation_connection latin1_swedish_ci
654 Database Collation latin1_swedish_ci
655 SHOW CREATE FUNCTION fn_2;
658 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
659 COMMENT 'FN changed to DEFINER'
665 character_set_client latin1
666 collation_connection latin1_swedish_ci
667 Database Collation latin1_swedish_ci
668 SHOW CREATE PROCEDURE sp_1;
671 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
673 COMMENT 'new comment, SP changed to INVOKER'
677 character_set_client latin1
678 collation_connection latin1_swedish_ci
679 Database Collation latin1_swedish_ci
680 SHOW CREATE PROCEDURE sp_2;
683 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
685 COMMENT 'SP changed to DEFINER'
689 character_set_client latin1
690 collation_connection latin1_swedish_ci
691 Database Collation latin1_swedish_ci
692 SHOW FUNCTION STATUS LIKE 'fn_%';
696 Definer root@localhost
699 Security_type INVOKER
700 Comment new comment, FN changed to INVOKER
701 character_set_client latin1
702 collation_connection latin1_swedish_ci
703 Database Collation latin1_swedish_ci
707 Definer root@localhost
710 Security_type DEFINER
711 Comment FN changed to DEFINER
712 character_set_client latin1
713 collation_connection latin1_swedish_ci
714 Database Collation latin1_swedish_ci
715 SHOW PROCEDURE STATUS LIKE 'sp_%';
719 Definer root@localhost
722 Security_type INVOKER
723 Comment new comment, SP changed to INVOKER
724 character_set_client latin1
725 collation_connection latin1_swedish_ci
726 Database Collation latin1_swedish_ci
730 Definer root@localhost
733 Security_type DEFINER
734 Comment SP changed to DEFINER
735 character_set_client latin1
736 collation_connection latin1_swedish_ci
737 Database Collation latin1_swedish_ci
745 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
746 --------------------------------------------------------------------------------
747 DROP DATABASE IF EXISTS db_storedproc;
748 DROP DATABASE IF EXISTS db_storedproc_1;
750 . +++ END OF SCRIPT +++
751 --------------------------------------------------------------------------------