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