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