Edit File: innodb_storedproc_08.result
--source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- --source suite/funcs_1/storedproc/cleanup_sp_tb.inc -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; CREATE DATABASE db_storedproc; CREATE DATABASE db_storedproc_1; USE db_storedproc; create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1; create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2; create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3; create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4; USE db_storedproc_1; create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 Warning 1265 Data truncated for column 'f3' at row 3 Warning 1265 Data truncated for column 'f3' at row 4 Warning 1265 Data truncated for column 'f3' at row 5 Warning 1265 Data truncated for column 'f3' at row 6 Warning 1265 Data truncated for column 'f3' at row 7 Warning 1265 Data truncated for column 'f3' at row 8 Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 Warning 1265 Data truncated for column 'f3' at row 3 Warning 1265 Data truncated for column 'f3' at row 4 Warning 1265 Data truncated for column 'f3' at row 5 Warning 1265 Data truncated for column 'f3' at row 6 Warning 1265 Data truncated for column 'f3' at row 7 Warning 1265 Data truncated for column 'f3' at row 8 Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9; create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10; create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = <engine_to_be_tested>; load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11; Section 3.1.8 - SHOW statement checks: -------------------------------------------------------------------------------- Testcase 3.1.8.9: ----------------- Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS statement (respectively) is executed. -------------------------------------------------------------------------------- DROP FUNCTION IF EXISTS fn_1; DROP FUNCTION IF EXISTS fn_2; DROP PROCEDURE IF EXISTS sp_1; DROP PROCEDURE IF EXISTS sp_2; CREATE PROCEDURE sp_1 (i1 int) BEGIN set @x=i1; END// CREATE PROCEDURE sp_2 (i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER' BEGIN set @x=i1; END// CREATE FUNCTION fn_1 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) returns year BEGIN set @x=i1; set @y=@x; return i4; END// CREATE FUNCTION fn_2 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS YEAR SQL SECURITY INVOKER COMMENT 'created with INVOKER' BEGIN set @x=i1; set @y=@x; return i4; END// ... now check what is stored: ----------------------------- SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME fn_1 ROUTINE_TYPE FUNCTION DATA_TYPE year CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER year(4) ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; set @y=@x; return i4; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE DEFINER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME fn_2 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME fn_2 ROUTINE_TYPE FUNCTION DATA_TYPE year CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER year(4) ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; set @y=@x; return i4; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT created with INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME sp_1 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME sp_1 ROUTINE_TYPE PROCEDURE DATA_TYPE CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER NULL ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE DEFINER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME sp_2 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME sp_2 ROUTINE_TYPE PROCEDURE DATA_TYPE CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER NULL ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT created with INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode NO_ENGINE_SUBSTITUTION Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) BEGIN set @x=i1; set @y=@x; return i4; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW CREATE FUNCTION fn_2; Function fn_2 sql_mode NO_ENGINE_SUBSTITUTION Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) SQL SECURITY INVOKER COMMENT 'created with INVOKER' BEGIN set @x=i1; set @y=@x; return i4; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW CREATE PROCEDURE sp_1; Procedure sp_1 sql_mode NO_ENGINE_SUBSTITUTION Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) BEGIN set @x=i1; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW CREATE PROCEDURE sp_2; Procedure sp_2 sql_mode NO_ENGINE_SUBSTITUTION Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER' BEGIN set @x=i1; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW FUNCTION STATUS LIKE 'fn_%'; Db db_storedproc Name fn_1 Type FUNCTION Definer root@localhost Modified <modified> Created <created> Security_type DEFINER Comment character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci Db db_storedproc Name fn_2 Type FUNCTION Definer root@localhost Modified <modified> Created <created> Security_type INVOKER Comment created with INVOKER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW PROCEDURE STATUS LIKE 'sp_%'; Db db_storedproc Name sp_1 Type PROCEDURE Definer root@localhost Modified <modified> Created <created> Security_type DEFINER Comment character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci Db db_storedproc Name sp_2 Type PROCEDURE Definer root@localhost Modified <modified> Created <created> Security_type INVOKER Comment created with INVOKER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci ... now change some stuff: -------------------------- ALTER PROCEDURE sp_1 SQL SECURITY INVOKER; ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER'; ALTER PROCEDURE sp_2 SQL SECURITY DEFINER; ALTER PROCEDURE sp_2 DROP COMMENT; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP COMMENT' at line 1 ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER'; ALTER PROCEDURE sp_2 READS SQL DATA; ALTER FUNCTION fn_1 SQL SECURITY INVOKER; ALTER FUNCTION fn_1 COMMENT 'new comment, FN changed to INVOKER'; ALTER FUNCTION fn_1 NO SQL; ALTER FUNCTION fn_2 SQL SECURITY DEFINER; ALTER FUNCTION fn_2 COMMENT 'FN changed to DEFINER'; ALTER FUNCTION fn_2 MODIFIES SQL DATA; ... now check what is stored: ----------------------------- SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME fn_1 ROUTINE_TYPE FUNCTION DATA_TYPE year CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER year(4) ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; set @y=@x; return i4; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS NO SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT new comment, FN changed to INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME fn_2 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME fn_2 ROUTINE_TYPE FUNCTION DATA_TYPE year CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER year(4) ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; set @y=@x; return i4; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS MODIFIES SQL DATA SQL_PATH NULL SECURITY_TYPE DEFINER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT FN changed to DEFINER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME sp_1 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME sp_1 ROUTINE_TYPE PROCEDURE DATA_TYPE CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER NULL ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT new comment, SP changed to INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME sp_2 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME sp_2 ROUTINE_TYPE PROCEDURE DATA_TYPE CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER NULL ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS READS SQL DATA SQL_PATH NULL SECURITY_TYPE DEFINER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT SP changed to DEFINER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode NO_ENGINE_SUBSTITUTION Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) NO SQL SQL SECURITY INVOKER COMMENT 'new comment, FN changed to INVOKER' BEGIN set @x=i1; set @y=@x; return i4; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW CREATE FUNCTION fn_2; Function fn_2 sql_mode NO_ENGINE_SUBSTITUTION Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) MODIFIES SQL DATA COMMENT 'FN changed to DEFINER' BEGIN set @x=i1; set @y=@x; return i4; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW CREATE PROCEDURE sp_1; Procedure sp_1 sql_mode NO_ENGINE_SUBSTITUTION Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) SQL SECURITY INVOKER COMMENT 'new comment, SP changed to INVOKER' BEGIN set @x=i1; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW CREATE PROCEDURE sp_2; Procedure sp_2 sql_mode NO_ENGINE_SUBSTITUTION Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) READS SQL DATA COMMENT 'SP changed to DEFINER' BEGIN set @x=i1; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW FUNCTION STATUS LIKE 'fn_%'; Db db_storedproc Name fn_1 Type FUNCTION Definer root@localhost Modified <modified> Created <created> Security_type INVOKER Comment new comment, FN changed to INVOKER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci Db db_storedproc Name fn_2 Type FUNCTION Definer root@localhost Modified <modified> Created <created> Security_type DEFINER Comment FN changed to DEFINER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW PROCEDURE STATUS LIKE 'sp_%'; Db db_storedproc Name sp_1 Type PROCEDURE Definer root@localhost Modified <modified> Created <created> Security_type INVOKER Comment new comment, SP changed to INVOKER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci Db db_storedproc Name sp_2 Type PROCEDURE Definer root@localhost Modified <modified> Created <created> Security_type DEFINER Comment SP changed to DEFINER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci ... change back to default and check result: -------------------------------------------- ALTER FUNCTION fn_2 CONTAINS SQL; ... now check what is stored: ----------------------------- SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc'; SPECIFIC_NAME fn_1 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME fn_1 ROUTINE_TYPE FUNCTION DATA_TYPE year CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER year(4) ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; set @y=@x; return i4; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS NO SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT new comment, FN changed to INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME fn_2 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME fn_2 ROUTINE_TYPE FUNCTION DATA_TYPE year CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER year(4) ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; set @y=@x; return i4; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE DEFINER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT FN changed to DEFINER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME sp_1 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME sp_1 ROUTINE_TYPE PROCEDURE DATA_TYPE CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER NULL ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT new comment, SP changed to INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME sp_2 ROUTINE_CATALOG def ROUTINE_SCHEMA db_storedproc ROUTINE_NAME sp_2 ROUTINE_TYPE PROCEDURE DATA_TYPE CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL DTD_IDENTIFIER NULL ROUTINE_BODY SQL ROUTINE_DEFINITION BEGIN set @x=i1; END EXTERNAL_NAME NULL EXTERNAL_LANGUAGE NULL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS READS SQL DATA SQL_PATH NULL SECURITY_TYPE DEFINER CREATED <modified> LAST_ALTERED <created> SQL_MODE NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT SP changed to DEFINER DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode NO_ENGINE_SUBSTITUTION Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) NO SQL SQL SECURITY INVOKER COMMENT 'new comment, FN changed to INVOKER' BEGIN set @x=i1; set @y=@x; return i4; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW CREATE FUNCTION fn_2; Function fn_2 sql_mode NO_ENGINE_SUBSTITUTION Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4) COMMENT 'FN changed to DEFINER' BEGIN set @x=i1; set @y=@x; return i4; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW CREATE PROCEDURE sp_1; Procedure sp_1 sql_mode NO_ENGINE_SUBSTITUTION Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) SQL SECURITY INVOKER COMMENT 'new comment, SP changed to INVOKER' BEGIN set @x=i1; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW CREATE PROCEDURE sp_2; Procedure sp_2 sql_mode NO_ENGINE_SUBSTITUTION Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int) READS SQL DATA COMMENT 'SP changed to DEFINER' BEGIN set @x=i1; END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW FUNCTION STATUS LIKE 'fn_%'; Db db_storedproc Name fn_1 Type FUNCTION Definer root@localhost Modified <modified> Created <created> Security_type INVOKER Comment new comment, FN changed to INVOKER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci Db db_storedproc Name fn_2 Type FUNCTION Definer root@localhost Modified <modified> Created <created> Security_type DEFINER Comment FN changed to DEFINER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci SHOW PROCEDURE STATUS LIKE 'sp_%'; Db db_storedproc Name sp_1 Type PROCEDURE Definer root@localhost Modified <modified> Created <created> Security_type INVOKER Comment new comment, SP changed to INVOKER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci Db db_storedproc Name sp_2 Type PROCEDURE Definer root@localhost Modified <modified> Created <created> Security_type DEFINER Comment SP changed to DEFINER character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci ... cleanup ----------- DROP FUNCTION fn_1; DROP FUNCTION fn_2; DROP PROCEDURE sp_1; --source suite/funcs_1/storedproc/cleanup_sp_tb.inc -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; . +++ END OF SCRIPT +++ --------------------------------------------------------------------------------