1SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; 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' 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' 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 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 152CHARACTER_SET_NAME NULL 153COLLATION_NAME NULL 154DTD_IDENTIFIER year(4) 155ROUTINE_BODY SQL 156ROUTINE_DEFINITION BEGIN 157set @x=i1; 158set @y=@x; 159return i4; 160END 161EXTERNAL_NAME NULL 162EXTERNAL_LANGUAGE NULL 163PARAMETER_STYLE SQL 164IS_DETERMINISTIC NO 165SQL_DATA_ACCESS CONTAINS SQL 166SQL_PATH NULL 167SECURITY_TYPE INVOKER 168CREATED <modified> 169LAST_ALTERED <created> 170SQL_MODE NO_ENGINE_SUBSTITUTION 171ROUTINE_COMMENT created with INVOKER 172DEFINER root@localhost 173CHARACTER_SET_CLIENT latin1 174COLLATION_CONNECTION latin1_swedish_ci 175DATABASE_COLLATION latin1_swedish_ci 176SPECIFIC_NAME sp_1 177ROUTINE_CATALOG def 178ROUTINE_SCHEMA db_storedproc 179ROUTINE_NAME sp_1 180ROUTINE_TYPE PROCEDURE 181DATA_TYPE 182CHARACTER_MAXIMUM_LENGTH NULL 183CHARACTER_OCTET_LENGTH NULL 184NUMERIC_PRECISION NULL 185NUMERIC_SCALE NULL 186CHARACTER_SET_NAME NULL 187COLLATION_NAME NULL 188DTD_IDENTIFIER NULL 189ROUTINE_BODY SQL 190ROUTINE_DEFINITION BEGIN 191set @x=i1; 192END 193EXTERNAL_NAME NULL 194EXTERNAL_LANGUAGE NULL 195PARAMETER_STYLE SQL 196IS_DETERMINISTIC NO 197SQL_DATA_ACCESS CONTAINS SQL 198SQL_PATH NULL 199SECURITY_TYPE DEFINER 200CREATED <modified> 201LAST_ALTERED <created> 202SQL_MODE NO_ENGINE_SUBSTITUTION 203ROUTINE_COMMENT 204DEFINER root@localhost 205CHARACTER_SET_CLIENT latin1 206COLLATION_CONNECTION latin1_swedish_ci 207DATABASE_COLLATION latin1_swedish_ci 208SPECIFIC_NAME sp_2 209ROUTINE_CATALOG def 210ROUTINE_SCHEMA db_storedproc 211ROUTINE_NAME sp_2 212ROUTINE_TYPE PROCEDURE 213DATA_TYPE 214CHARACTER_MAXIMUM_LENGTH NULL 215CHARACTER_OCTET_LENGTH NULL 216NUMERIC_PRECISION NULL 217NUMERIC_SCALE NULL 218CHARACTER_SET_NAME NULL 219COLLATION_NAME NULL 220DTD_IDENTIFIER NULL 221ROUTINE_BODY SQL 222ROUTINE_DEFINITION BEGIN 223set @x=i1; 224END 225EXTERNAL_NAME NULL 226EXTERNAL_LANGUAGE NULL 227PARAMETER_STYLE SQL 228IS_DETERMINISTIC NO 229SQL_DATA_ACCESS CONTAINS SQL 230SQL_PATH NULL 231SECURITY_TYPE INVOKER 232CREATED <modified> 233LAST_ALTERED <created> 234SQL_MODE NO_ENGINE_SUBSTITUTION 235ROUTINE_COMMENT created with INVOKER 236DEFINER root@localhost 237CHARACTER_SET_CLIENT latin1 238COLLATION_CONNECTION latin1_swedish_ci 239DATABASE_COLLATION latin1_swedish_ci 240SHOW CREATE FUNCTION fn_1; 241Function fn_1 242sql_mode NO_ENGINE_SUBSTITUTION 243Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 244BEGIN 245set @x=i1; 246set @y=@x; 247return i4; 248END 249character_set_client latin1 250collation_connection latin1_swedish_ci 251Database Collation latin1_swedish_ci 252SHOW CREATE FUNCTION fn_2; 253Function fn_2 254sql_mode NO_ENGINE_SUBSTITUTION 255Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 256 SQL SECURITY INVOKER 257 COMMENT 'created with INVOKER' 258BEGIN 259set @x=i1; 260set @y=@x; 261return i4; 262END 263character_set_client latin1 264collation_connection latin1_swedish_ci 265Database Collation latin1_swedish_ci 266SHOW CREATE PROCEDURE sp_1; 267Procedure sp_1 268sql_mode NO_ENGINE_SUBSTITUTION 269Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) 270BEGIN 271set @x=i1; 272END 273character_set_client latin1 274collation_connection latin1_swedish_ci 275Database Collation latin1_swedish_ci 276SHOW CREATE PROCEDURE sp_2; 277Procedure sp_2 278sql_mode NO_ENGINE_SUBSTITUTION 279Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) 280 SQL SECURITY INVOKER 281 COMMENT 'created with INVOKER' 282BEGIN 283set @x=i1; 284END 285character_set_client latin1 286collation_connection latin1_swedish_ci 287Database Collation latin1_swedish_ci 288SHOW FUNCTION STATUS LIKE 'fn_%'; 289Db db_storedproc 290Name fn_1 291Type FUNCTION 292Definer root@localhost 293Modified <modified> 294Created <created> 295Security_type DEFINER 296Comment 297character_set_client latin1 298collation_connection latin1_swedish_ci 299Database Collation latin1_swedish_ci 300Db db_storedproc 301Name fn_2 302Type FUNCTION 303Definer root@localhost 304Modified <modified> 305Created <created> 306Security_type INVOKER 307Comment created with INVOKER 308character_set_client latin1 309collation_connection latin1_swedish_ci 310Database Collation latin1_swedish_ci 311SHOW PROCEDURE STATUS LIKE 'sp_%'; 312Db db_storedproc 313Name sp_1 314Type PROCEDURE 315Definer root@localhost 316Modified <modified> 317Created <created> 318Security_type DEFINER 319Comment 320character_set_client latin1 321collation_connection latin1_swedish_ci 322Database Collation latin1_swedish_ci 323Db db_storedproc 324Name sp_2 325Type PROCEDURE 326Definer root@localhost 327Modified <modified> 328Created <created> 329Security_type INVOKER 330Comment created with INVOKER 331character_set_client latin1 332collation_connection latin1_swedish_ci 333Database Collation latin1_swedish_ci 334 335... now change some stuff: 336-------------------------- 337ALTER PROCEDURE sp_1 SQL SECURITY INVOKER; 338ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER'; 339ALTER PROCEDURE sp_2 SQL SECURITY DEFINER; 340ALTER PROCEDURE sp_2 DROP COMMENT; 341ERROR 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 342ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER'; 343ALTER PROCEDURE sp_2 READS SQL DATA; 344ALTER FUNCTION fn_1 SQL SECURITY INVOKER; 345ALTER FUNCTION fn_1 COMMENT 'new comment, FN changed to INVOKER'; 346ALTER FUNCTION fn_1 NO SQL; 347ALTER FUNCTION fn_2 SQL SECURITY DEFINER; 348ALTER FUNCTION fn_2 COMMENT 'FN changed to DEFINER'; 349ALTER FUNCTION fn_2 MODIFIES SQL DATA; 350 351... now check what is stored: 352----------------------------- 353SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; 354SPECIFIC_NAME fn_1 355ROUTINE_CATALOG def 356ROUTINE_SCHEMA db_storedproc 357ROUTINE_NAME fn_1 358ROUTINE_TYPE FUNCTION 359DATA_TYPE year 360CHARACTER_MAXIMUM_LENGTH NULL 361CHARACTER_OCTET_LENGTH NULL 362NUMERIC_PRECISION NULL 363NUMERIC_SCALE NULL 364CHARACTER_SET_NAME NULL 365COLLATION_NAME NULL 366DTD_IDENTIFIER year(4) 367ROUTINE_BODY SQL 368ROUTINE_DEFINITION BEGIN 369set @x=i1; 370set @y=@x; 371return i4; 372END 373EXTERNAL_NAME NULL 374EXTERNAL_LANGUAGE NULL 375PARAMETER_STYLE SQL 376IS_DETERMINISTIC NO 377SQL_DATA_ACCESS NO SQL 378SQL_PATH NULL 379SECURITY_TYPE INVOKER 380CREATED <modified> 381LAST_ALTERED <created> 382SQL_MODE NO_ENGINE_SUBSTITUTION 383ROUTINE_COMMENT new comment, FN changed to INVOKER 384DEFINER root@localhost 385CHARACTER_SET_CLIENT latin1 386COLLATION_CONNECTION latin1_swedish_ci 387DATABASE_COLLATION latin1_swedish_ci 388SPECIFIC_NAME fn_2 389ROUTINE_CATALOG def 390ROUTINE_SCHEMA db_storedproc 391ROUTINE_NAME fn_2 392ROUTINE_TYPE FUNCTION 393DATA_TYPE year 394CHARACTER_MAXIMUM_LENGTH NULL 395CHARACTER_OCTET_LENGTH NULL 396NUMERIC_PRECISION NULL 397NUMERIC_SCALE NULL 398CHARACTER_SET_NAME NULL 399COLLATION_NAME NULL 400DTD_IDENTIFIER year(4) 401ROUTINE_BODY SQL 402ROUTINE_DEFINITION BEGIN 403set @x=i1; 404set @y=@x; 405return i4; 406END 407EXTERNAL_NAME NULL 408EXTERNAL_LANGUAGE NULL 409PARAMETER_STYLE SQL 410IS_DETERMINISTIC NO 411SQL_DATA_ACCESS MODIFIES SQL DATA 412SQL_PATH NULL 413SECURITY_TYPE DEFINER 414CREATED <modified> 415LAST_ALTERED <created> 416SQL_MODE NO_ENGINE_SUBSTITUTION 417ROUTINE_COMMENT FN changed to DEFINER 418DEFINER root@localhost 419CHARACTER_SET_CLIENT latin1 420COLLATION_CONNECTION latin1_swedish_ci 421DATABASE_COLLATION latin1_swedish_ci 422SPECIFIC_NAME sp_1 423ROUTINE_CATALOG def 424ROUTINE_SCHEMA db_storedproc 425ROUTINE_NAME sp_1 426ROUTINE_TYPE PROCEDURE 427DATA_TYPE 428CHARACTER_MAXIMUM_LENGTH NULL 429CHARACTER_OCTET_LENGTH NULL 430NUMERIC_PRECISION NULL 431NUMERIC_SCALE NULL 432CHARACTER_SET_NAME NULL 433COLLATION_NAME NULL 434DTD_IDENTIFIER NULL 435ROUTINE_BODY SQL 436ROUTINE_DEFINITION BEGIN 437set @x=i1; 438END 439EXTERNAL_NAME NULL 440EXTERNAL_LANGUAGE NULL 441PARAMETER_STYLE SQL 442IS_DETERMINISTIC NO 443SQL_DATA_ACCESS CONTAINS SQL 444SQL_PATH NULL 445SECURITY_TYPE INVOKER 446CREATED <modified> 447LAST_ALTERED <created> 448SQL_MODE NO_ENGINE_SUBSTITUTION 449ROUTINE_COMMENT new comment, SP changed to INVOKER 450DEFINER root@localhost 451CHARACTER_SET_CLIENT latin1 452COLLATION_CONNECTION latin1_swedish_ci 453DATABASE_COLLATION latin1_swedish_ci 454SPECIFIC_NAME sp_2 455ROUTINE_CATALOG def 456ROUTINE_SCHEMA db_storedproc 457ROUTINE_NAME sp_2 458ROUTINE_TYPE PROCEDURE 459DATA_TYPE 460CHARACTER_MAXIMUM_LENGTH NULL 461CHARACTER_OCTET_LENGTH NULL 462NUMERIC_PRECISION NULL 463NUMERIC_SCALE NULL 464CHARACTER_SET_NAME NULL 465COLLATION_NAME NULL 466DTD_IDENTIFIER NULL 467ROUTINE_BODY SQL 468ROUTINE_DEFINITION BEGIN 469set @x=i1; 470END 471EXTERNAL_NAME NULL 472EXTERNAL_LANGUAGE NULL 473PARAMETER_STYLE SQL 474IS_DETERMINISTIC NO 475SQL_DATA_ACCESS READS SQL DATA 476SQL_PATH NULL 477SECURITY_TYPE DEFINER 478CREATED <modified> 479LAST_ALTERED <created> 480SQL_MODE NO_ENGINE_SUBSTITUTION 481ROUTINE_COMMENT SP changed to DEFINER 482DEFINER root@localhost 483CHARACTER_SET_CLIENT latin1 484COLLATION_CONNECTION latin1_swedish_ci 485DATABASE_COLLATION latin1_swedish_ci 486SHOW CREATE FUNCTION fn_1; 487Function fn_1 488sql_mode NO_ENGINE_SUBSTITUTION 489Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 490 NO SQL 491 SQL SECURITY INVOKER 492 COMMENT 'new comment, FN changed to INVOKER' 493BEGIN 494set @x=i1; 495set @y=@x; 496return i4; 497END 498character_set_client latin1 499collation_connection latin1_swedish_ci 500Database Collation latin1_swedish_ci 501SHOW CREATE FUNCTION fn_2; 502Function fn_2 503sql_mode NO_ENGINE_SUBSTITUTION 504Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 505 MODIFIES SQL DATA 506 COMMENT 'FN changed to DEFINER' 507BEGIN 508set @x=i1; 509set @y=@x; 510return i4; 511END 512character_set_client latin1 513collation_connection latin1_swedish_ci 514Database Collation latin1_swedish_ci 515SHOW CREATE PROCEDURE sp_1; 516Procedure sp_1 517sql_mode NO_ENGINE_SUBSTITUTION 518Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) 519 SQL SECURITY INVOKER 520 COMMENT 'new comment, SP changed to INVOKER' 521BEGIN 522set @x=i1; 523END 524character_set_client latin1 525collation_connection latin1_swedish_ci 526Database Collation latin1_swedish_ci 527SHOW CREATE PROCEDURE sp_2; 528Procedure sp_2 529sql_mode NO_ENGINE_SUBSTITUTION 530Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) 531 READS SQL DATA 532 COMMENT 'SP changed to DEFINER' 533BEGIN 534set @x=i1; 535END 536character_set_client latin1 537collation_connection latin1_swedish_ci 538Database Collation latin1_swedish_ci 539SHOW FUNCTION STATUS LIKE 'fn_%'; 540Db db_storedproc 541Name fn_1 542Type FUNCTION 543Definer root@localhost 544Modified <modified> 545Created <created> 546Security_type INVOKER 547Comment new comment, FN changed to INVOKER 548character_set_client latin1 549collation_connection latin1_swedish_ci 550Database Collation latin1_swedish_ci 551Db db_storedproc 552Name fn_2 553Type FUNCTION 554Definer root@localhost 555Modified <modified> 556Created <created> 557Security_type DEFINER 558Comment FN changed to DEFINER 559character_set_client latin1 560collation_connection latin1_swedish_ci 561Database Collation latin1_swedish_ci 562SHOW PROCEDURE STATUS LIKE 'sp_%'; 563Db db_storedproc 564Name sp_1 565Type PROCEDURE 566Definer root@localhost 567Modified <modified> 568Created <created> 569Security_type INVOKER 570Comment new comment, SP changed to INVOKER 571character_set_client latin1 572collation_connection latin1_swedish_ci 573Database Collation latin1_swedish_ci 574Db db_storedproc 575Name sp_2 576Type PROCEDURE 577Definer root@localhost 578Modified <modified> 579Created <created> 580Security_type DEFINER 581Comment SP changed to DEFINER 582character_set_client latin1 583collation_connection latin1_swedish_ci 584Database Collation latin1_swedish_ci 585 586... change back to default and check result: 587-------------------------------------------- 588ALTER FUNCTION fn_2 CONTAINS SQL; 589 590... now check what is stored: 591----------------------------- 592SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; 593SPECIFIC_NAME fn_1 594ROUTINE_CATALOG def 595ROUTINE_SCHEMA db_storedproc 596ROUTINE_NAME fn_1 597ROUTINE_TYPE FUNCTION 598DATA_TYPE year 599CHARACTER_MAXIMUM_LENGTH NULL 600CHARACTER_OCTET_LENGTH NULL 601NUMERIC_PRECISION NULL 602NUMERIC_SCALE NULL 603CHARACTER_SET_NAME NULL 604COLLATION_NAME NULL 605DTD_IDENTIFIER year(4) 606ROUTINE_BODY SQL 607ROUTINE_DEFINITION BEGIN 608set @x=i1; 609set @y=@x; 610return i4; 611END 612EXTERNAL_NAME NULL 613EXTERNAL_LANGUAGE NULL 614PARAMETER_STYLE SQL 615IS_DETERMINISTIC NO 616SQL_DATA_ACCESS NO SQL 617SQL_PATH NULL 618SECURITY_TYPE INVOKER 619CREATED <modified> 620LAST_ALTERED <created> 621SQL_MODE NO_ENGINE_SUBSTITUTION 622ROUTINE_COMMENT new comment, FN changed to INVOKER 623DEFINER root@localhost 624CHARACTER_SET_CLIENT latin1 625COLLATION_CONNECTION latin1_swedish_ci 626DATABASE_COLLATION latin1_swedish_ci 627SPECIFIC_NAME fn_2 628ROUTINE_CATALOG def 629ROUTINE_SCHEMA db_storedproc 630ROUTINE_NAME fn_2 631ROUTINE_TYPE FUNCTION 632DATA_TYPE year 633CHARACTER_MAXIMUM_LENGTH NULL 634CHARACTER_OCTET_LENGTH NULL 635NUMERIC_PRECISION NULL 636NUMERIC_SCALE NULL 637CHARACTER_SET_NAME NULL 638COLLATION_NAME NULL 639DTD_IDENTIFIER year(4) 640ROUTINE_BODY SQL 641ROUTINE_DEFINITION BEGIN 642set @x=i1; 643set @y=@x; 644return i4; 645END 646EXTERNAL_NAME NULL 647EXTERNAL_LANGUAGE NULL 648PARAMETER_STYLE SQL 649IS_DETERMINISTIC NO 650SQL_DATA_ACCESS CONTAINS SQL 651SQL_PATH NULL 652SECURITY_TYPE DEFINER 653CREATED <modified> 654LAST_ALTERED <created> 655SQL_MODE NO_ENGINE_SUBSTITUTION 656ROUTINE_COMMENT FN changed to DEFINER 657DEFINER root@localhost 658CHARACTER_SET_CLIENT latin1 659COLLATION_CONNECTION latin1_swedish_ci 660DATABASE_COLLATION latin1_swedish_ci 661SPECIFIC_NAME sp_1 662ROUTINE_CATALOG def 663ROUTINE_SCHEMA db_storedproc 664ROUTINE_NAME sp_1 665ROUTINE_TYPE PROCEDURE 666DATA_TYPE 667CHARACTER_MAXIMUM_LENGTH NULL 668CHARACTER_OCTET_LENGTH NULL 669NUMERIC_PRECISION NULL 670NUMERIC_SCALE NULL 671CHARACTER_SET_NAME NULL 672COLLATION_NAME NULL 673DTD_IDENTIFIER NULL 674ROUTINE_BODY SQL 675ROUTINE_DEFINITION BEGIN 676set @x=i1; 677END 678EXTERNAL_NAME NULL 679EXTERNAL_LANGUAGE NULL 680PARAMETER_STYLE SQL 681IS_DETERMINISTIC NO 682SQL_DATA_ACCESS CONTAINS SQL 683SQL_PATH NULL 684SECURITY_TYPE INVOKER 685CREATED <modified> 686LAST_ALTERED <created> 687SQL_MODE NO_ENGINE_SUBSTITUTION 688ROUTINE_COMMENT new comment, SP changed to INVOKER 689DEFINER root@localhost 690CHARACTER_SET_CLIENT latin1 691COLLATION_CONNECTION latin1_swedish_ci 692DATABASE_COLLATION latin1_swedish_ci 693SPECIFIC_NAME sp_2 694ROUTINE_CATALOG def 695ROUTINE_SCHEMA db_storedproc 696ROUTINE_NAME sp_2 697ROUTINE_TYPE PROCEDURE 698DATA_TYPE 699CHARACTER_MAXIMUM_LENGTH NULL 700CHARACTER_OCTET_LENGTH NULL 701NUMERIC_PRECISION NULL 702NUMERIC_SCALE NULL 703CHARACTER_SET_NAME NULL 704COLLATION_NAME NULL 705DTD_IDENTIFIER NULL 706ROUTINE_BODY SQL 707ROUTINE_DEFINITION BEGIN 708set @x=i1; 709END 710EXTERNAL_NAME NULL 711EXTERNAL_LANGUAGE NULL 712PARAMETER_STYLE SQL 713IS_DETERMINISTIC NO 714SQL_DATA_ACCESS READS SQL DATA 715SQL_PATH NULL 716SECURITY_TYPE DEFINER 717CREATED <modified> 718LAST_ALTERED <created> 719SQL_MODE NO_ENGINE_SUBSTITUTION 720ROUTINE_COMMENT SP changed to DEFINER 721DEFINER root@localhost 722CHARACTER_SET_CLIENT latin1 723COLLATION_CONNECTION latin1_swedish_ci 724DATABASE_COLLATION latin1_swedish_ci 725SHOW CREATE FUNCTION fn_1; 726Function fn_1 727sql_mode NO_ENGINE_SUBSTITUTION 728Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 729 NO SQL 730 SQL SECURITY INVOKER 731 COMMENT 'new comment, FN changed to INVOKER' 732BEGIN 733set @x=i1; 734set @y=@x; 735return i4; 736END 737character_set_client latin1 738collation_connection latin1_swedish_ci 739Database Collation latin1_swedish_ci 740SHOW CREATE FUNCTION fn_2; 741Function fn_2 742sql_mode NO_ENGINE_SUBSTITUTION 743Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) 744 COMMENT 'FN changed to DEFINER' 745BEGIN 746set @x=i1; 747set @y=@x; 748return i4; 749END 750character_set_client latin1 751collation_connection latin1_swedish_ci 752Database Collation latin1_swedish_ci 753SHOW CREATE PROCEDURE sp_1; 754Procedure sp_1 755sql_mode NO_ENGINE_SUBSTITUTION 756Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) 757 SQL SECURITY INVOKER 758 COMMENT 'new comment, SP changed to INVOKER' 759BEGIN 760set @x=i1; 761END 762character_set_client latin1 763collation_connection latin1_swedish_ci 764Database Collation latin1_swedish_ci 765SHOW CREATE PROCEDURE sp_2; 766Procedure sp_2 767sql_mode NO_ENGINE_SUBSTITUTION 768Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) 769 READS SQL DATA 770 COMMENT 'SP changed to DEFINER' 771BEGIN 772set @x=i1; 773END 774character_set_client latin1 775collation_connection latin1_swedish_ci 776Database Collation latin1_swedish_ci 777SHOW FUNCTION STATUS LIKE 'fn_%'; 778Db db_storedproc 779Name fn_1 780Type FUNCTION 781Definer root@localhost 782Modified <modified> 783Created <created> 784Security_type INVOKER 785Comment new comment, FN changed to INVOKER 786character_set_client latin1 787collation_connection latin1_swedish_ci 788Database Collation latin1_swedish_ci 789Db db_storedproc 790Name fn_2 791Type FUNCTION 792Definer root@localhost 793Modified <modified> 794Created <created> 795Security_type DEFINER 796Comment FN changed to DEFINER 797character_set_client latin1 798collation_connection latin1_swedish_ci 799Database Collation latin1_swedish_ci 800SHOW PROCEDURE STATUS LIKE 'sp_%'; 801Db db_storedproc 802Name sp_1 803Type PROCEDURE 804Definer root@localhost 805Modified <modified> 806Created <created> 807Security_type INVOKER 808Comment new comment, SP changed to INVOKER 809character_set_client latin1 810collation_connection latin1_swedish_ci 811Database Collation latin1_swedish_ci 812Db db_storedproc 813Name sp_2 814Type PROCEDURE 815Definer root@localhost 816Modified <modified> 817Created <created> 818Security_type DEFINER 819Comment SP changed to DEFINER 820character_set_client latin1 821collation_connection latin1_swedish_ci 822Database Collation latin1_swedish_ci 823 824... cleanup 825----------- 826DROP FUNCTION fn_1; 827DROP FUNCTION fn_2; 828DROP PROCEDURE sp_1; 829 830--source suite/funcs_1/storedproc/cleanup_sp_tb.inc 831-------------------------------------------------------------------------------- 832DROP DATABASE IF EXISTS db_storedproc; 833DROP DATABASE IF EXISTS db_storedproc_1; 834 835. +++ END OF SCRIPT +++ 836-------------------------------------------------------------------------------- 837