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