1set sql_mode=""; 2 3--source suite/funcs_1/storedproc/load_sp_tb.inc 4-------------------------------------------------------------------------------- 5 6--source suite/funcs_1/storedproc/cleanup_sp_tb.inc 7-------------------------------------------------------------------------------- 8DROP DATABASE IF EXISTS db_storedproc; 9DROP DATABASE IF EXISTS db_storedproc_1; 10CREATE DATABASE db_storedproc; 11CREATE DATABASE db_storedproc_1; 12USE db_storedproc; 13create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) 14engine = <engine_to_be_tested>; 15load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1; 16create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) 17engine = <engine_to_be_tested>; 18load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2; 19create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>; 20load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3; 21create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) 22engine = <engine_to_be_tested>; 23load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4; 24USE db_storedproc_1; 25create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) 26engine = <engine_to_be_tested>; 27load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; 28USE db_storedproc; 29create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) 30engine = <engine_to_be_tested>; 31load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; 32Warnings: 33Warning 1265 Data truncated for column 'f3' at row 1 34Warning 1265 Data truncated for column 'f3' at row 2 35Warning 1265 Data truncated for column 'f3' at row 3 36Warning 1265 Data truncated for column 'f3' at row 4 37Warning 1265 Data truncated for column 'f3' at row 5 38Warning 1265 Data truncated for column 'f3' at row 6 39Warning 1265 Data truncated for column 'f3' at row 7 40Warning 1265 Data truncated for column 'f3' at row 8 41Warning 1265 Data truncated for column 'f3' at row 9 42Warning 1265 Data truncated for column 'f3' at row 10 43create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) 44engine = <engine_to_be_tested>; 45load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; 46Warnings: 47Warning 1265 Data truncated for column 'f3' at row 1 48Warning 1265 Data truncated for column 'f3' at row 2 49Warning 1265 Data truncated for column 'f3' at row 3 50Warning 1265 Data truncated for column 'f3' at row 4 51Warning 1265 Data truncated for column 'f3' at row 5 52Warning 1265 Data truncated for column 'f3' at row 6 53Warning 1265 Data truncated for column 'f3' at row 7 54Warning 1265 Data truncated for column 'f3' at row 8 55Warning 1265 Data truncated for column 'f3' at row 9 56Warning 1265 Data truncated for column 'f3' at row 10 57create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>; 58load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9; 59create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) 60engine = <engine_to_be_tested>; 61load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10; 62create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) 63engine = <engine_to_be_tested>; 64load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11; 65 66Section 3.1.8 - SHOW statement checks: 67-------------------------------------------------------------------------------- 68 69Testcase 3.1.8.9: 70----------------- 71 72Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER 73FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or 74SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE 75FUNCTION STATUS statement (respectively) is executed. 76-------------------------------------------------------------------------------- 77DROP FUNCTION IF EXISTS fn_1; 78DROP FUNCTION IF EXISTS fn_2; 79DROP PROCEDURE IF EXISTS sp_1; 80DROP PROCEDURE IF EXISTS sp_2; 81CREATE PROCEDURE sp_1 (i1 int) 82BEGIN 83set @x=i1; 84END// 85CREATE PROCEDURE sp_2 (i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER' 86BEGIN 87set @x=i1; 88END// 89CREATE FUNCTION fn_1 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) returns year 90BEGIN 91set @x=i1; 92set @y=@x; 93return i4; 94END// 95CREATE FUNCTION fn_2 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) 96RETURNS YEAR 97SQL SECURITY INVOKER 98COMMENT 'created with INVOKER' 99BEGIN 100set @x=i1; 101set @y=@x; 102return i4; 103END// 104 105... now check what is stored: 106----------------------------- 107SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; 108SPECIFIC_NAME fn_1 109ROUTINE_CATALOG def 110ROUTINE_SCHEMA db_storedproc 111ROUTINE_NAME fn_1 112ROUTINE_TYPE FUNCTION 113DATA_TYPE year 114CHARACTER_MAXIMUM_LENGTH NULL 115CHARACTER_OCTET_LENGTH NULL 116NUMERIC_PRECISION NULL 117NUMERIC_SCALE NULL 118DATETIME_PRECISION NULL 119CHARACTER_SET_NAME NULL 120COLLATION_NAME NULL 121DTD_IDENTIFIER year(4) 122ROUTINE_BODY SQL 123ROUTINE_DEFINITION BEGIN 124set @x=i1; 125set @y=@x; 126return i4; 127END 128EXTERNAL_NAME NULL 129EXTERNAL_LANGUAGE NULL 130PARAMETER_STYLE SQL 131IS_DETERMINISTIC NO 132SQL_DATA_ACCESS CONTAINS SQL 133SQL_PATH NULL 134SECURITY_TYPE DEFINER 135CREATED <modified> 136LAST_ALTERED <created> 137SQL_MODE 138ROUTINE_COMMENT 139DEFINER root@localhost 140CHARACTER_SET_CLIENT latin1 141COLLATION_CONNECTION latin1_swedish_ci 142DATABASE_COLLATION latin1_swedish_ci 143SPECIFIC_NAME fn_2 144ROUTINE_CATALOG def 145ROUTINE_SCHEMA db_storedproc 146ROUTINE_NAME fn_2 147ROUTINE_TYPE FUNCTION 148DATA_TYPE year 149CHARACTER_MAXIMUM_LENGTH NULL 150CHARACTER_OCTET_LENGTH NULL 151NUMERIC_PRECISION NULL 152NUMERIC_SCALE NULL 153DATETIME_PRECISION NULL 154CHARACTER_SET_NAME NULL 155COLLATION_NAME NULL 156DTD_IDENTIFIER year(4) 157ROUTINE_BODY SQL 158ROUTINE_DEFINITION BEGIN 159set @x=i1; 160set @y=@x; 161return i4; 162END 163EXTERNAL_NAME NULL 164EXTERNAL_LANGUAGE NULL 165PARAMETER_STYLE SQL 166IS_DETERMINISTIC NO 167SQL_DATA_ACCESS CONTAINS SQL 168SQL_PATH NULL 169SECURITY_TYPE INVOKER 170CREATED <modified> 171LAST_ALTERED <created> 172SQL_MODE 173ROUTINE_COMMENT created with INVOKER 174DEFINER root@localhost 175CHARACTER_SET_CLIENT latin1 176COLLATION_CONNECTION latin1_swedish_ci 177DATABASE_COLLATION latin1_swedish_ci 178SPECIFIC_NAME sp_1 179ROUTINE_CATALOG def 180ROUTINE_SCHEMA db_storedproc 181ROUTINE_NAME sp_1 182ROUTINE_TYPE PROCEDURE 183DATA_TYPE 184CHARACTER_MAXIMUM_LENGTH NULL 185CHARACTER_OCTET_LENGTH NULL 186NUMERIC_PRECISION NULL 187NUMERIC_SCALE NULL 188DATETIME_PRECISION NULL 189CHARACTER_SET_NAME NULL 190COLLATION_NAME NULL 191DTD_IDENTIFIER NULL 192ROUTINE_BODY SQL 193ROUTINE_DEFINITION BEGIN 194set @x=i1; 195END 196EXTERNAL_NAME NULL 197EXTERNAL_LANGUAGE NULL 198PARAMETER_STYLE SQL 199IS_DETERMINISTIC NO 200SQL_DATA_ACCESS CONTAINS SQL 201SQL_PATH NULL 202SECURITY_TYPE DEFINER 203CREATED <modified> 204LAST_ALTERED <created> 205SQL_MODE 206ROUTINE_COMMENT 207DEFINER root@localhost 208CHARACTER_SET_CLIENT latin1 209COLLATION_CONNECTION latin1_swedish_ci 210DATABASE_COLLATION latin1_swedish_ci 211SPECIFIC_NAME sp_2 212ROUTINE_CATALOG def 213ROUTINE_SCHEMA db_storedproc 214ROUTINE_NAME sp_2 215ROUTINE_TYPE PROCEDURE 216DATA_TYPE 217CHARACTER_MAXIMUM_LENGTH NULL 218CHARACTER_OCTET_LENGTH NULL 219NUMERIC_PRECISION NULL 220NUMERIC_SCALE NULL 221DATETIME_PRECISION NULL 222CHARACTER_SET_NAME NULL 223COLLATION_NAME NULL 224DTD_IDENTIFIER NULL 225ROUTINE_BODY SQL 226ROUTINE_DEFINITION BEGIN 227set @x=i1; 228END 229EXTERNAL_NAME NULL 230EXTERNAL_LANGUAGE NULL 231PARAMETER_STYLE SQL 232IS_DETERMINISTIC NO 233SQL_DATA_ACCESS CONTAINS SQL 234SQL_PATH NULL 235SECURITY_TYPE INVOKER 236CREATED <modified> 237LAST_ALTERED <created> 238SQL_MODE 239ROUTINE_COMMENT created with INVOKER 240DEFINER root@localhost 241CHARACTER_SET_CLIENT latin1 242COLLATION_CONNECTION latin1_swedish_ci 243DATABASE_COLLATION latin1_swedish_ci 244SHOW CREATE FUNCTION fn_1; 245Function fn_1 246sql_mode 247Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 248BEGIN 249set @x=i1; 250set @y=@x; 251return i4; 252END 253character_set_client latin1 254collation_connection latin1_swedish_ci 255Database Collation latin1_swedish_ci 256SHOW CREATE FUNCTION fn_2; 257Function fn_2 258sql_mode 259Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 260 SQL SECURITY INVOKER 261 COMMENT 'created with INVOKER' 262BEGIN 263set @x=i1; 264set @y=@x; 265return i4; 266END 267character_set_client latin1 268collation_connection latin1_swedish_ci 269Database Collation latin1_swedish_ci 270SHOW CREATE PROCEDURE sp_1; 271Procedure sp_1 272sql_mode 273Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) 274BEGIN 275set @x=i1; 276END 277character_set_client latin1 278collation_connection latin1_swedish_ci 279Database Collation latin1_swedish_ci 280SHOW CREATE PROCEDURE sp_2; 281Procedure sp_2 282sql_mode 283Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) 284 SQL SECURITY INVOKER 285 COMMENT 'created with INVOKER' 286BEGIN 287set @x=i1; 288END 289character_set_client latin1 290collation_connection latin1_swedish_ci 291Database Collation latin1_swedish_ci 292SHOW FUNCTION STATUS LIKE 'fn_%'; 293Db db_storedproc 294Name fn_1 295Type FUNCTION 296Definer root@localhost 297Modified <modified> 298Created <created> 299Security_type DEFINER 300Comment 301character_set_client latin1 302collation_connection latin1_swedish_ci 303Database Collation latin1_swedish_ci 304Db db_storedproc 305Name fn_2 306Type FUNCTION 307Definer root@localhost 308Modified <modified> 309Created <created> 310Security_type INVOKER 311Comment created with INVOKER 312character_set_client latin1 313collation_connection latin1_swedish_ci 314Database Collation latin1_swedish_ci 315SHOW PROCEDURE STATUS LIKE 'sp_%'; 316Db db_storedproc 317Name sp_1 318Type PROCEDURE 319Definer root@localhost 320Modified <modified> 321Created <created> 322Security_type DEFINER 323Comment 324character_set_client latin1 325collation_connection latin1_swedish_ci 326Database Collation latin1_swedish_ci 327Db db_storedproc 328Name sp_2 329Type PROCEDURE 330Definer root@localhost 331Modified <modified> 332Created <created> 333Security_type INVOKER 334Comment created with INVOKER 335character_set_client latin1 336collation_connection latin1_swedish_ci 337Database Collation latin1_swedish_ci 338 339... now change some stuff: 340-------------------------- 341ALTER PROCEDURE sp_1 SQL SECURITY INVOKER; 342ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER'; 343ALTER PROCEDURE sp_2 SQL SECURITY DEFINER; 344ALTER PROCEDURE sp_2 DROP COMMENT; 345ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DROP COMMENT' at line 1 346ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER'; 347ALTER PROCEDURE sp_2 READS SQL DATA; 348ALTER FUNCTION fn_1 SQL SECURITY INVOKER; 349ALTER FUNCTION fn_1 COMMENT 'new comment, FN changed to INVOKER'; 350ALTER FUNCTION fn_1 NO SQL; 351ALTER FUNCTION fn_2 SQL SECURITY DEFINER; 352ALTER FUNCTION fn_2 COMMENT 'FN changed to DEFINER'; 353ALTER FUNCTION fn_2 MODIFIES SQL DATA; 354 355... now check what is stored: 356----------------------------- 357SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; 358SPECIFIC_NAME fn_1 359ROUTINE_CATALOG def 360ROUTINE_SCHEMA db_storedproc 361ROUTINE_NAME fn_1 362ROUTINE_TYPE FUNCTION 363DATA_TYPE year 364CHARACTER_MAXIMUM_LENGTH NULL 365CHARACTER_OCTET_LENGTH NULL 366NUMERIC_PRECISION NULL 367NUMERIC_SCALE NULL 368DATETIME_PRECISION NULL 369CHARACTER_SET_NAME NULL 370COLLATION_NAME NULL 371DTD_IDENTIFIER year(4) 372ROUTINE_BODY SQL 373ROUTINE_DEFINITION BEGIN 374set @x=i1; 375set @y=@x; 376return i4; 377END 378EXTERNAL_NAME NULL 379EXTERNAL_LANGUAGE NULL 380PARAMETER_STYLE SQL 381IS_DETERMINISTIC NO 382SQL_DATA_ACCESS NO SQL 383SQL_PATH NULL 384SECURITY_TYPE INVOKER 385CREATED <modified> 386LAST_ALTERED <created> 387SQL_MODE 388ROUTINE_COMMENT new comment, FN changed to INVOKER 389DEFINER root@localhost 390CHARACTER_SET_CLIENT latin1 391COLLATION_CONNECTION latin1_swedish_ci 392DATABASE_COLLATION latin1_swedish_ci 393SPECIFIC_NAME fn_2 394ROUTINE_CATALOG def 395ROUTINE_SCHEMA db_storedproc 396ROUTINE_NAME fn_2 397ROUTINE_TYPE FUNCTION 398DATA_TYPE year 399CHARACTER_MAXIMUM_LENGTH NULL 400CHARACTER_OCTET_LENGTH NULL 401NUMERIC_PRECISION NULL 402NUMERIC_SCALE NULL 403DATETIME_PRECISION NULL 404CHARACTER_SET_NAME NULL 405COLLATION_NAME NULL 406DTD_IDENTIFIER year(4) 407ROUTINE_BODY SQL 408ROUTINE_DEFINITION BEGIN 409set @x=i1; 410set @y=@x; 411return i4; 412END 413EXTERNAL_NAME NULL 414EXTERNAL_LANGUAGE NULL 415PARAMETER_STYLE SQL 416IS_DETERMINISTIC NO 417SQL_DATA_ACCESS MODIFIES SQL DATA 418SQL_PATH NULL 419SECURITY_TYPE DEFINER 420CREATED <modified> 421LAST_ALTERED <created> 422SQL_MODE 423ROUTINE_COMMENT FN changed to DEFINER 424DEFINER root@localhost 425CHARACTER_SET_CLIENT latin1 426COLLATION_CONNECTION latin1_swedish_ci 427DATABASE_COLLATION latin1_swedish_ci 428SPECIFIC_NAME sp_1 429ROUTINE_CATALOG def 430ROUTINE_SCHEMA db_storedproc 431ROUTINE_NAME sp_1 432ROUTINE_TYPE PROCEDURE 433DATA_TYPE 434CHARACTER_MAXIMUM_LENGTH NULL 435CHARACTER_OCTET_LENGTH NULL 436NUMERIC_PRECISION NULL 437NUMERIC_SCALE NULL 438DATETIME_PRECISION NULL 439CHARACTER_SET_NAME NULL 440COLLATION_NAME NULL 441DTD_IDENTIFIER NULL 442ROUTINE_BODY SQL 443ROUTINE_DEFINITION BEGIN 444set @x=i1; 445END 446EXTERNAL_NAME NULL 447EXTERNAL_LANGUAGE NULL 448PARAMETER_STYLE SQL 449IS_DETERMINISTIC NO 450SQL_DATA_ACCESS CONTAINS SQL 451SQL_PATH NULL 452SECURITY_TYPE INVOKER 453CREATED <modified> 454LAST_ALTERED <created> 455SQL_MODE 456ROUTINE_COMMENT new comment, SP changed to INVOKER 457DEFINER root@localhost 458CHARACTER_SET_CLIENT latin1 459COLLATION_CONNECTION latin1_swedish_ci 460DATABASE_COLLATION latin1_swedish_ci 461SPECIFIC_NAME sp_2 462ROUTINE_CATALOG def 463ROUTINE_SCHEMA db_storedproc 464ROUTINE_NAME sp_2 465ROUTINE_TYPE PROCEDURE 466DATA_TYPE 467CHARACTER_MAXIMUM_LENGTH NULL 468CHARACTER_OCTET_LENGTH NULL 469NUMERIC_PRECISION NULL 470NUMERIC_SCALE NULL 471DATETIME_PRECISION NULL 472CHARACTER_SET_NAME NULL 473COLLATION_NAME NULL 474DTD_IDENTIFIER NULL 475ROUTINE_BODY SQL 476ROUTINE_DEFINITION BEGIN 477set @x=i1; 478END 479EXTERNAL_NAME NULL 480EXTERNAL_LANGUAGE NULL 481PARAMETER_STYLE SQL 482IS_DETERMINISTIC NO 483SQL_DATA_ACCESS READS SQL DATA 484SQL_PATH NULL 485SECURITY_TYPE DEFINER 486CREATED <modified> 487LAST_ALTERED <created> 488SQL_MODE 489ROUTINE_COMMENT SP changed to DEFINER 490DEFINER root@localhost 491CHARACTER_SET_CLIENT latin1 492COLLATION_CONNECTION latin1_swedish_ci 493DATABASE_COLLATION latin1_swedish_ci 494SHOW CREATE FUNCTION fn_1; 495Function fn_1 496sql_mode 497Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 498 NO SQL 499 SQL SECURITY INVOKER 500 COMMENT 'new comment, FN changed to INVOKER' 501BEGIN 502set @x=i1; 503set @y=@x; 504return i4; 505END 506character_set_client latin1 507collation_connection latin1_swedish_ci 508Database Collation latin1_swedish_ci 509SHOW CREATE FUNCTION fn_2; 510Function fn_2 511sql_mode 512Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 513 MODIFIES SQL DATA 514 COMMENT 'FN changed to DEFINER' 515BEGIN 516set @x=i1; 517set @y=@x; 518return i4; 519END 520character_set_client latin1 521collation_connection latin1_swedish_ci 522Database Collation latin1_swedish_ci 523SHOW CREATE PROCEDURE sp_1; 524Procedure sp_1 525sql_mode 526Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) 527 SQL SECURITY INVOKER 528 COMMENT 'new comment, SP changed to INVOKER' 529BEGIN 530set @x=i1; 531END 532character_set_client latin1 533collation_connection latin1_swedish_ci 534Database Collation latin1_swedish_ci 535SHOW CREATE PROCEDURE sp_2; 536Procedure sp_2 537sql_mode 538Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) 539 READS SQL DATA 540 COMMENT 'SP changed to DEFINER' 541BEGIN 542set @x=i1; 543END 544character_set_client latin1 545collation_connection latin1_swedish_ci 546Database Collation latin1_swedish_ci 547SHOW FUNCTION STATUS LIKE 'fn_%'; 548Db db_storedproc 549Name fn_1 550Type FUNCTION 551Definer root@localhost 552Modified <modified> 553Created <created> 554Security_type INVOKER 555Comment new comment, FN changed to INVOKER 556character_set_client latin1 557collation_connection latin1_swedish_ci 558Database Collation latin1_swedish_ci 559Db db_storedproc 560Name fn_2 561Type FUNCTION 562Definer root@localhost 563Modified <modified> 564Created <created> 565Security_type DEFINER 566Comment FN changed to DEFINER 567character_set_client latin1 568collation_connection latin1_swedish_ci 569Database Collation latin1_swedish_ci 570SHOW PROCEDURE STATUS LIKE 'sp_%'; 571Db db_storedproc 572Name sp_1 573Type PROCEDURE 574Definer root@localhost 575Modified <modified> 576Created <created> 577Security_type INVOKER 578Comment new comment, SP changed to INVOKER 579character_set_client latin1 580collation_connection latin1_swedish_ci 581Database Collation latin1_swedish_ci 582Db db_storedproc 583Name sp_2 584Type PROCEDURE 585Definer root@localhost 586Modified <modified> 587Created <created> 588Security_type DEFINER 589Comment SP changed to DEFINER 590character_set_client latin1 591collation_connection latin1_swedish_ci 592Database Collation latin1_swedish_ci 593 594... change back to default and check result: 595-------------------------------------------- 596ALTER FUNCTION fn_2 CONTAINS SQL; 597 598... now check what is stored: 599----------------------------- 600SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; 601SPECIFIC_NAME fn_1 602ROUTINE_CATALOG def 603ROUTINE_SCHEMA db_storedproc 604ROUTINE_NAME fn_1 605ROUTINE_TYPE FUNCTION 606DATA_TYPE year 607CHARACTER_MAXIMUM_LENGTH NULL 608CHARACTER_OCTET_LENGTH NULL 609NUMERIC_PRECISION NULL 610NUMERIC_SCALE NULL 611DATETIME_PRECISION NULL 612CHARACTER_SET_NAME NULL 613COLLATION_NAME NULL 614DTD_IDENTIFIER year(4) 615ROUTINE_BODY SQL 616ROUTINE_DEFINITION BEGIN 617set @x=i1; 618set @y=@x; 619return i4; 620END 621EXTERNAL_NAME NULL 622EXTERNAL_LANGUAGE NULL 623PARAMETER_STYLE SQL 624IS_DETERMINISTIC NO 625SQL_DATA_ACCESS NO SQL 626SQL_PATH NULL 627SECURITY_TYPE INVOKER 628CREATED <modified> 629LAST_ALTERED <created> 630SQL_MODE 631ROUTINE_COMMENT new comment, FN changed to INVOKER 632DEFINER root@localhost 633CHARACTER_SET_CLIENT latin1 634COLLATION_CONNECTION latin1_swedish_ci 635DATABASE_COLLATION latin1_swedish_ci 636SPECIFIC_NAME fn_2 637ROUTINE_CATALOG def 638ROUTINE_SCHEMA db_storedproc 639ROUTINE_NAME fn_2 640ROUTINE_TYPE FUNCTION 641DATA_TYPE year 642CHARACTER_MAXIMUM_LENGTH NULL 643CHARACTER_OCTET_LENGTH NULL 644NUMERIC_PRECISION NULL 645NUMERIC_SCALE NULL 646DATETIME_PRECISION NULL 647CHARACTER_SET_NAME NULL 648COLLATION_NAME NULL 649DTD_IDENTIFIER year(4) 650ROUTINE_BODY SQL 651ROUTINE_DEFINITION BEGIN 652set @x=i1; 653set @y=@x; 654return i4; 655END 656EXTERNAL_NAME NULL 657EXTERNAL_LANGUAGE NULL 658PARAMETER_STYLE SQL 659IS_DETERMINISTIC NO 660SQL_DATA_ACCESS CONTAINS SQL 661SQL_PATH NULL 662SECURITY_TYPE DEFINER 663CREATED <modified> 664LAST_ALTERED <created> 665SQL_MODE 666ROUTINE_COMMENT FN changed to DEFINER 667DEFINER root@localhost 668CHARACTER_SET_CLIENT latin1 669COLLATION_CONNECTION latin1_swedish_ci 670DATABASE_COLLATION latin1_swedish_ci 671SPECIFIC_NAME sp_1 672ROUTINE_CATALOG def 673ROUTINE_SCHEMA db_storedproc 674ROUTINE_NAME sp_1 675ROUTINE_TYPE PROCEDURE 676DATA_TYPE 677CHARACTER_MAXIMUM_LENGTH NULL 678CHARACTER_OCTET_LENGTH NULL 679NUMERIC_PRECISION NULL 680NUMERIC_SCALE NULL 681DATETIME_PRECISION NULL 682CHARACTER_SET_NAME NULL 683COLLATION_NAME NULL 684DTD_IDENTIFIER NULL 685ROUTINE_BODY SQL 686ROUTINE_DEFINITION BEGIN 687set @x=i1; 688END 689EXTERNAL_NAME NULL 690EXTERNAL_LANGUAGE NULL 691PARAMETER_STYLE SQL 692IS_DETERMINISTIC NO 693SQL_DATA_ACCESS CONTAINS SQL 694SQL_PATH NULL 695SECURITY_TYPE INVOKER 696CREATED <modified> 697LAST_ALTERED <created> 698SQL_MODE 699ROUTINE_COMMENT new comment, SP changed to INVOKER 700DEFINER root@localhost 701CHARACTER_SET_CLIENT latin1 702COLLATION_CONNECTION latin1_swedish_ci 703DATABASE_COLLATION latin1_swedish_ci 704SPECIFIC_NAME sp_2 705ROUTINE_CATALOG def 706ROUTINE_SCHEMA db_storedproc 707ROUTINE_NAME sp_2 708ROUTINE_TYPE PROCEDURE 709DATA_TYPE 710CHARACTER_MAXIMUM_LENGTH NULL 711CHARACTER_OCTET_LENGTH NULL 712NUMERIC_PRECISION NULL 713NUMERIC_SCALE NULL 714DATETIME_PRECISION NULL 715CHARACTER_SET_NAME NULL 716COLLATION_NAME NULL 717DTD_IDENTIFIER NULL 718ROUTINE_BODY SQL 719ROUTINE_DEFINITION BEGIN 720set @x=i1; 721END 722EXTERNAL_NAME NULL 723EXTERNAL_LANGUAGE NULL 724PARAMETER_STYLE SQL 725IS_DETERMINISTIC NO 726SQL_DATA_ACCESS READS SQL DATA 727SQL_PATH NULL 728SECURITY_TYPE DEFINER 729CREATED <modified> 730LAST_ALTERED <created> 731SQL_MODE 732ROUTINE_COMMENT SP changed to DEFINER 733DEFINER root@localhost 734CHARACTER_SET_CLIENT latin1 735COLLATION_CONNECTION latin1_swedish_ci 736DATABASE_COLLATION latin1_swedish_ci 737SHOW CREATE FUNCTION fn_1; 738Function fn_1 739sql_mode 740Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 741 NO SQL 742 SQL SECURITY INVOKER 743 COMMENT 'new comment, FN changed to INVOKER' 744BEGIN 745set @x=i1; 746set @y=@x; 747return i4; 748END 749character_set_client latin1 750collation_connection latin1_swedish_ci 751Database Collation latin1_swedish_ci 752SHOW CREATE FUNCTION fn_2; 753Function fn_2 754sql_mode 755Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 756 COMMENT 'FN changed to DEFINER' 757BEGIN 758set @x=i1; 759set @y=@x; 760return i4; 761END 762character_set_client latin1 763collation_connection latin1_swedish_ci 764Database Collation latin1_swedish_ci 765SHOW CREATE PROCEDURE sp_1; 766Procedure sp_1 767sql_mode 768Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) 769 SQL SECURITY INVOKER 770 COMMENT 'new comment, SP changed to INVOKER' 771BEGIN 772set @x=i1; 773END 774character_set_client latin1 775collation_connection latin1_swedish_ci 776Database Collation latin1_swedish_ci 777SHOW CREATE PROCEDURE sp_2; 778Procedure sp_2 779sql_mode 780Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) 781 READS SQL DATA 782 COMMENT 'SP changed to DEFINER' 783BEGIN 784set @x=i1; 785END 786character_set_client latin1 787collation_connection latin1_swedish_ci 788Database Collation latin1_swedish_ci 789SHOW FUNCTION STATUS LIKE 'fn_%'; 790Db db_storedproc 791Name fn_1 792Type FUNCTION 793Definer root@localhost 794Modified <modified> 795Created <created> 796Security_type INVOKER 797Comment new comment, FN changed to INVOKER 798character_set_client latin1 799collation_connection latin1_swedish_ci 800Database Collation latin1_swedish_ci 801Db db_storedproc 802Name fn_2 803Type FUNCTION 804Definer root@localhost 805Modified <modified> 806Created <created> 807Security_type DEFINER 808Comment FN changed to DEFINER 809character_set_client latin1 810collation_connection latin1_swedish_ci 811Database Collation latin1_swedish_ci 812SHOW PROCEDURE STATUS LIKE 'sp_%'; 813Db db_storedproc 814Name sp_1 815Type PROCEDURE 816Definer root@localhost 817Modified <modified> 818Created <created> 819Security_type INVOKER 820Comment new comment, SP changed to INVOKER 821character_set_client latin1 822collation_connection latin1_swedish_ci 823Database Collation latin1_swedish_ci 824Db db_storedproc 825Name sp_2 826Type PROCEDURE 827Definer root@localhost 828Modified <modified> 829Created <created> 830Security_type DEFINER 831Comment SP changed to DEFINER 832character_set_client latin1 833collation_connection latin1_swedish_ci 834Database Collation latin1_swedish_ci 835 836... cleanup 837----------- 838DROP FUNCTION fn_1; 839DROP FUNCTION fn_2; 840DROP PROCEDURE sp_1; 841 842--source suite/funcs_1/storedproc/cleanup_sp_tb.inc 843-------------------------------------------------------------------------------- 844DROP DATABASE IF EXISTS db_storedproc; 845DROP DATABASE IF EXISTS db_storedproc_1; 846 847. +++ END OF SCRIPT +++ 848-------------------------------------------------------------------------------- 849