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
--------------------------------------------------------------------------------