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) engine = myisam;
13
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1;
14
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
15
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2;
16
create table t3(f1 char(20),f2 char(20),f3 integer) engine = myisam;
17
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3;
18
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
19
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t4;
21
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
22
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6;
24
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = myisam;
25
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t7;
27
Warning 1265 Data truncated for column 'f3' at row 1
28
Warning 1265 Data truncated for column 'f3' at row 2
29
Warning 1265 Data truncated for column 'f3' at row 3
30
Warning 1265 Data truncated for column 'f3' at row 4
31
Warning 1265 Data truncated for column 'f3' at row 5
32
Warning 1265 Data truncated for column 'f3' at row 6
33
Warning 1265 Data truncated for column 'f3' at row 7
34
Warning 1265 Data truncated for column 'f3' at row 8
35
Warning 1265 Data truncated for column 'f3' at row 9
36
Warning 1265 Data truncated for column 'f3' at row 10
37
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = myisam;
38
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t8;
40
Warning 1265 Data truncated for column 'f3' at row 1
41
Warning 1265 Data truncated for column 'f3' at row 2
42
Warning 1265 Data truncated for column 'f3' at row 3
43
Warning 1265 Data truncated for column 'f3' at row 4
44
Warning 1265 Data truncated for column 'f3' at row 5
45
Warning 1265 Data truncated for column 'f3' at row 6
46
Warning 1265 Data truncated for column 'f3' at row 7
47
Warning 1265 Data truncated for column 'f3' at row 8
48
Warning 1265 Data truncated for column 'f3' at row 9
49
Warning 1265 Data truncated for column 'f3' at row 10
50
create table t9(f1 int, f2 char(25), f3 int) engine = myisam;
51
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9;
52
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
53
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10;
54
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = myisam;
55
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11;
57
Section 3.1.8 - SHOW statement checks:
58
--------------------------------------------------------------------------------
63
Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER
64
FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or
65
SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE
66
FUNCTION STATUS statement (respectively) is executed.
67
--------------------------------------------------------------------------------
68
DROP FUNCTION IF EXISTS fn_1;
69
DROP FUNCTION IF EXISTS fn_2;
70
DROP PROCEDURE IF EXISTS sp_1;
71
DROP PROCEDURE IF EXISTS sp_2;
72
CREATE PROCEDURE sp_1 (i1 int)
76
CREATE PROCEDURE sp_2 (i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER'
80
CREATE FUNCTION fn_1 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) returns year
86
CREATE FUNCTION fn_2 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real)
89
COMMENT 'created with INVOKER'
96
... now check what is stored:
97
-----------------------------
98
SELECT * FROM information_schema.routines;
101
ROUTINE_SCHEMA db_storedproc
103
ROUTINE_TYPE FUNCTION
105
CHARACTER_MAXIMUM_LENGTH NULL
106
CHARACTER_OCTET_LENGTH NULL
107
NUMERIC_PRECISION NULL
109
CHARACTER_SET_NAME NULL
111
DTD_IDENTIFIER year(4)
113
ROUTINE_DEFINITION BEGIN
119
EXTERNAL_LANGUAGE NULL
122
SQL_DATA_ACCESS CONTAINS SQL
124
SECURITY_TYPE DEFINER
126
LAST_ALTERED <modified>
129
DEFINER root@localhost
130
CHARACTER_SET_CLIENT latin1
131
COLLATION_CONNECTION latin1_swedish_ci
132
DATABASE_COLLATION latin1_swedish_ci
135
ROUTINE_SCHEMA db_storedproc
137
ROUTINE_TYPE FUNCTION
139
CHARACTER_MAXIMUM_LENGTH NULL
140
CHARACTER_OCTET_LENGTH NULL
141
NUMERIC_PRECISION NULL
143
CHARACTER_SET_NAME NULL
145
DTD_IDENTIFIER year(4)
147
ROUTINE_DEFINITION BEGIN
153
EXTERNAL_LANGUAGE NULL
156
SQL_DATA_ACCESS CONTAINS SQL
158
SECURITY_TYPE INVOKER
160
LAST_ALTERED <modified>
162
ROUTINE_COMMENT created with INVOKER
163
DEFINER root@localhost
164
CHARACTER_SET_CLIENT latin1
165
COLLATION_CONNECTION latin1_swedish_ci
166
DATABASE_COLLATION latin1_swedish_ci
169
ROUTINE_SCHEMA db_storedproc
171
ROUTINE_TYPE PROCEDURE
173
CHARACTER_MAXIMUM_LENGTH NULL
174
CHARACTER_OCTET_LENGTH NULL
175
NUMERIC_PRECISION NULL
177
CHARACTER_SET_NAME NULL
181
ROUTINE_DEFINITION BEGIN
185
EXTERNAL_LANGUAGE NULL
188
SQL_DATA_ACCESS CONTAINS SQL
190
SECURITY_TYPE DEFINER
192
LAST_ALTERED <modified>
195
DEFINER root@localhost
196
CHARACTER_SET_CLIENT latin1
197
COLLATION_CONNECTION latin1_swedish_ci
198
DATABASE_COLLATION latin1_swedish_ci
201
ROUTINE_SCHEMA db_storedproc
203
ROUTINE_TYPE PROCEDURE
205
CHARACTER_MAXIMUM_LENGTH NULL
206
CHARACTER_OCTET_LENGTH NULL
207
NUMERIC_PRECISION NULL
209
CHARACTER_SET_NAME NULL
213
ROUTINE_DEFINITION BEGIN
217
EXTERNAL_LANGUAGE NULL
220
SQL_DATA_ACCESS CONTAINS SQL
222
SECURITY_TYPE INVOKER
224
LAST_ALTERED <modified>
226
ROUTINE_COMMENT created with INVOKER
227
DEFINER root@localhost
228
CHARACTER_SET_CLIENT latin1
229
COLLATION_CONNECTION latin1_swedish_ci
230
DATABASE_COLLATION latin1_swedish_ci
231
SHOW CREATE FUNCTION fn_1;
234
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
240
character_set_client latin1
241
collation_connection latin1_swedish_ci
242
Database Collation latin1_swedish_ci
243
SHOW CREATE FUNCTION fn_2;
246
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
248
COMMENT 'created with INVOKER'
254
character_set_client latin1
255
collation_connection latin1_swedish_ci
256
Database Collation latin1_swedish_ci
257
SHOW CREATE PROCEDURE sp_1;
260
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
264
character_set_client latin1
265
collation_connection latin1_swedish_ci
266
Database Collation latin1_swedish_ci
267
SHOW CREATE PROCEDURE sp_2;
270
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
272
COMMENT 'created with INVOKER'
276
character_set_client latin1
277
collation_connection latin1_swedish_ci
278
Database Collation latin1_swedish_ci
279
SHOW FUNCTION STATUS LIKE 'fn_%';
283
Definer root@localhost
286
Security_type DEFINER
288
character_set_client latin1
289
collation_connection latin1_swedish_ci
290
Database Collation latin1_swedish_ci
294
Definer root@localhost
297
Security_type INVOKER
298
Comment created with INVOKER
299
character_set_client latin1
300
collation_connection latin1_swedish_ci
301
Database Collation latin1_swedish_ci
302
SHOW PROCEDURE STATUS LIKE 'sp_%';
306
Definer root@localhost
309
Security_type DEFINER
311
character_set_client latin1
312
collation_connection latin1_swedish_ci
313
Database Collation latin1_swedish_ci
317
Definer root@localhost
320
Security_type INVOKER
321
Comment created with INVOKER
322
character_set_client latin1
323
collation_connection latin1_swedish_ci
324
Database Collation latin1_swedish_ci
326
... now change some stuff:
327
--------------------------
328
ALTER PROCEDURE sp_1 SQL SECURITY INVOKER;
329
ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER';
330
ALTER PROCEDURE sp_2 SQL SECURITY DEFINER;
331
ALTER PROCEDURE sp_2 DROP COMMENT;
332
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
333
ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER';
334
ALTER PROCEDURE sp_2 READS SQL DATA;
335
ALTER FUNCTION fn_1 SQL SECURITY INVOKER;
336
ALTER FUNCTION fn_1 COMMENT 'new comment, FN changed to INVOKER';
337
ALTER FUNCTION fn_1 NO SQL;
338
ALTER FUNCTION fn_2 SQL SECURITY DEFINER;
339
ALTER FUNCTION fn_2 COMMENT 'FN changed to DEFINER';
340
ALTER FUNCTION fn_2 MODIFIES SQL DATA;
342
... now check what is stored:
343
-----------------------------
344
SELECT * FROM information_schema.routines;
347
ROUTINE_SCHEMA db_storedproc
349
ROUTINE_TYPE FUNCTION
351
CHARACTER_MAXIMUM_LENGTH NULL
352
CHARACTER_OCTET_LENGTH NULL
353
NUMERIC_PRECISION NULL
355
CHARACTER_SET_NAME NULL
357
DTD_IDENTIFIER year(4)
359
ROUTINE_DEFINITION BEGIN
365
EXTERNAL_LANGUAGE NULL
368
SQL_DATA_ACCESS NO SQL
370
SECURITY_TYPE INVOKER
372
LAST_ALTERED <modified>
374
ROUTINE_COMMENT new comment, FN changed to INVOKER
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 FUNCTION
385
CHARACTER_MAXIMUM_LENGTH NULL
386
CHARACTER_OCTET_LENGTH NULL
387
NUMERIC_PRECISION NULL
389
CHARACTER_SET_NAME NULL
391
DTD_IDENTIFIER year(4)
393
ROUTINE_DEFINITION BEGIN
399
EXTERNAL_LANGUAGE NULL
402
SQL_DATA_ACCESS MODIFIES SQL DATA
404
SECURITY_TYPE DEFINER
406
LAST_ALTERED <modified>
408
ROUTINE_COMMENT FN changed to DEFINER
409
DEFINER root@localhost
410
CHARACTER_SET_CLIENT latin1
411
COLLATION_CONNECTION latin1_swedish_ci
412
DATABASE_COLLATION latin1_swedish_ci
415
ROUTINE_SCHEMA db_storedproc
417
ROUTINE_TYPE PROCEDURE
419
CHARACTER_MAXIMUM_LENGTH NULL
420
CHARACTER_OCTET_LENGTH NULL
421
NUMERIC_PRECISION NULL
423
CHARACTER_SET_NAME NULL
427
ROUTINE_DEFINITION BEGIN
431
EXTERNAL_LANGUAGE NULL
434
SQL_DATA_ACCESS CONTAINS SQL
436
SECURITY_TYPE INVOKER
438
LAST_ALTERED <modified>
440
ROUTINE_COMMENT new comment, SP changed to INVOKER
441
DEFINER root@localhost
442
CHARACTER_SET_CLIENT latin1
443
COLLATION_CONNECTION latin1_swedish_ci
444
DATABASE_COLLATION latin1_swedish_ci
447
ROUTINE_SCHEMA db_storedproc
449
ROUTINE_TYPE PROCEDURE
451
CHARACTER_MAXIMUM_LENGTH NULL
452
CHARACTER_OCTET_LENGTH NULL
453
NUMERIC_PRECISION NULL
455
CHARACTER_SET_NAME NULL
459
ROUTINE_DEFINITION BEGIN
463
EXTERNAL_LANGUAGE NULL
466
SQL_DATA_ACCESS READS SQL DATA
468
SECURITY_TYPE DEFINER
470
LAST_ALTERED <modified>
472
ROUTINE_COMMENT SP changed to DEFINER
473
DEFINER root@localhost
474
CHARACTER_SET_CLIENT latin1
475
COLLATION_CONNECTION latin1_swedish_ci
476
DATABASE_COLLATION latin1_swedish_ci
477
SHOW CREATE FUNCTION fn_1;
480
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
483
COMMENT 'new comment, FN changed to INVOKER'
489
character_set_client latin1
490
collation_connection latin1_swedish_ci
491
Database Collation latin1_swedish_ci
492
SHOW CREATE FUNCTION fn_2;
495
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
497
COMMENT 'FN changed to DEFINER'
503
character_set_client latin1
504
collation_connection latin1_swedish_ci
505
Database Collation latin1_swedish_ci
506
SHOW CREATE PROCEDURE sp_1;
509
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
511
COMMENT 'new comment, SP changed to INVOKER'
515
character_set_client latin1
516
collation_connection latin1_swedish_ci
517
Database Collation latin1_swedish_ci
518
SHOW CREATE PROCEDURE sp_2;
521
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
523
COMMENT 'SP changed to DEFINER'
527
character_set_client latin1
528
collation_connection latin1_swedish_ci
529
Database Collation latin1_swedish_ci
530
SHOW FUNCTION STATUS LIKE 'fn_%';
534
Definer root@localhost
537
Security_type INVOKER
538
Comment new comment, FN changed to INVOKER
539
character_set_client latin1
540
collation_connection latin1_swedish_ci
541
Database Collation latin1_swedish_ci
545
Definer root@localhost
548
Security_type DEFINER
549
Comment FN changed to DEFINER
550
character_set_client latin1
551
collation_connection latin1_swedish_ci
552
Database Collation latin1_swedish_ci
553
SHOW PROCEDURE STATUS LIKE 'sp_%';
557
Definer root@localhost
560
Security_type INVOKER
561
Comment new comment, SP changed to INVOKER
562
character_set_client latin1
563
collation_connection latin1_swedish_ci
564
Database Collation latin1_swedish_ci
568
Definer root@localhost
571
Security_type DEFINER
572
Comment SP changed to DEFINER
573
character_set_client latin1
574
collation_connection latin1_swedish_ci
575
Database Collation latin1_swedish_ci
577
... change back to default and check result:
578
--------------------------------------------
579
ALTER FUNCTION fn_2 CONTAINS SQL;
581
... now check what is stored:
582
-----------------------------
583
SELECT * FROM information_schema.routines;
586
ROUTINE_SCHEMA db_storedproc
588
ROUTINE_TYPE FUNCTION
590
CHARACTER_MAXIMUM_LENGTH NULL
591
CHARACTER_OCTET_LENGTH NULL
592
NUMERIC_PRECISION NULL
594
CHARACTER_SET_NAME NULL
596
DTD_IDENTIFIER year(4)
598
ROUTINE_DEFINITION BEGIN
604
EXTERNAL_LANGUAGE NULL
607
SQL_DATA_ACCESS NO SQL
609
SECURITY_TYPE INVOKER
611
LAST_ALTERED <modified>
613
ROUTINE_COMMENT new comment, FN changed to INVOKER
614
DEFINER root@localhost
615
CHARACTER_SET_CLIENT latin1
616
COLLATION_CONNECTION latin1_swedish_ci
617
DATABASE_COLLATION latin1_swedish_ci
620
ROUTINE_SCHEMA db_storedproc
622
ROUTINE_TYPE FUNCTION
624
CHARACTER_MAXIMUM_LENGTH NULL
625
CHARACTER_OCTET_LENGTH NULL
626
NUMERIC_PRECISION NULL
628
CHARACTER_SET_NAME NULL
630
DTD_IDENTIFIER year(4)
632
ROUTINE_DEFINITION BEGIN
638
EXTERNAL_LANGUAGE NULL
641
SQL_DATA_ACCESS CONTAINS SQL
643
SECURITY_TYPE DEFINER
645
LAST_ALTERED <modified>
647
ROUTINE_COMMENT FN changed to DEFINER
648
DEFINER root@localhost
649
CHARACTER_SET_CLIENT latin1
650
COLLATION_CONNECTION latin1_swedish_ci
651
DATABASE_COLLATION latin1_swedish_ci
654
ROUTINE_SCHEMA db_storedproc
656
ROUTINE_TYPE PROCEDURE
658
CHARACTER_MAXIMUM_LENGTH NULL
659
CHARACTER_OCTET_LENGTH NULL
660
NUMERIC_PRECISION NULL
662
CHARACTER_SET_NAME NULL
666
ROUTINE_DEFINITION BEGIN
670
EXTERNAL_LANGUAGE NULL
673
SQL_DATA_ACCESS CONTAINS SQL
675
SECURITY_TYPE INVOKER
677
LAST_ALTERED <modified>
679
ROUTINE_COMMENT new comment, SP changed to INVOKER
680
DEFINER root@localhost
681
CHARACTER_SET_CLIENT latin1
682
COLLATION_CONNECTION latin1_swedish_ci
683
DATABASE_COLLATION latin1_swedish_ci
686
ROUTINE_SCHEMA db_storedproc
688
ROUTINE_TYPE PROCEDURE
690
CHARACTER_MAXIMUM_LENGTH NULL
691
CHARACTER_OCTET_LENGTH NULL
692
NUMERIC_PRECISION NULL
694
CHARACTER_SET_NAME NULL
698
ROUTINE_DEFINITION BEGIN
702
EXTERNAL_LANGUAGE NULL
705
SQL_DATA_ACCESS READS SQL DATA
707
SECURITY_TYPE DEFINER
709
LAST_ALTERED <modified>
711
ROUTINE_COMMENT SP changed to DEFINER
712
DEFINER root@localhost
713
CHARACTER_SET_CLIENT latin1
714
COLLATION_CONNECTION latin1_swedish_ci
715
DATABASE_COLLATION latin1_swedish_ci
716
SHOW CREATE FUNCTION fn_1;
719
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
722
COMMENT 'new comment, FN changed to INVOKER'
728
character_set_client latin1
729
collation_connection latin1_swedish_ci
730
Database Collation latin1_swedish_ci
731
SHOW CREATE FUNCTION fn_2;
734
Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
735
COMMENT 'FN changed to DEFINER'
741
character_set_client latin1
742
collation_connection latin1_swedish_ci
743
Database Collation latin1_swedish_ci
744
SHOW CREATE PROCEDURE sp_1;
747
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
749
COMMENT 'new comment, SP changed to INVOKER'
753
character_set_client latin1
754
collation_connection latin1_swedish_ci
755
Database Collation latin1_swedish_ci
756
SHOW CREATE PROCEDURE sp_2;
759
Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
761
COMMENT 'SP changed to DEFINER'
765
character_set_client latin1
766
collation_connection latin1_swedish_ci
767
Database Collation latin1_swedish_ci
768
SHOW FUNCTION STATUS LIKE 'fn_%';
772
Definer root@localhost
775
Security_type INVOKER
776
Comment new comment, FN changed to INVOKER
777
character_set_client latin1
778
collation_connection latin1_swedish_ci
779
Database Collation latin1_swedish_ci
783
Definer root@localhost
786
Security_type DEFINER
787
Comment FN changed to DEFINER
788
character_set_client latin1
789
collation_connection latin1_swedish_ci
790
Database Collation latin1_swedish_ci
791
SHOW PROCEDURE STATUS LIKE 'sp_%';
795
Definer root@localhost
798
Security_type INVOKER
799
Comment new comment, SP changed to INVOKER
800
character_set_client latin1
801
collation_connection latin1_swedish_ci
802
Database Collation latin1_swedish_ci
806
Definer root@localhost
809
Security_type DEFINER
810
Comment SP changed to DEFINER
811
character_set_client latin1
812
collation_connection latin1_swedish_ci
813
Database Collation latin1_swedish_ci
821
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
822
--------------------------------------------------------------------------------
823
DROP DATABASE IF EXISTS db_storedproc;
824
DROP DATABASE IF EXISTS db_storedproc_1;
826
. +++ END OF SCRIPT +++
827
--------------------------------------------------------------------------------