--source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; --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 charset utf8mb4; CREATE DATABASE db_storedproc_1 charset utf8mb4; USE db_storedproc; create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = ; load data infile '/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 = ; load data infile '/std_data/funcs_1/t4.txt' into table t2; create table t3(f1 char(20),f2 char(20),f3 integer) engine = ; load data infile '/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 = ; load data infile '/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 = ; load data infile '/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 = ; load data infile '/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 = ; load data infile '/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 = ; load data infile '/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 = ; load data infile '/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 = ; load data infile '/std_data/funcs_1/t4.txt' into table t11; SET sql_mode = default; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Section 3.1.8 - SHOW statement checks: -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Testcase 3.1.8.9: ----------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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. -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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' ORDER BY routine_type, routine_name; 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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE DEFINER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT created with INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE DEFINER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT created with INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW CREATE FUNCTION fn_2; Function fn_2 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW CREATE PROCEDURE sp_1; Procedure sp_1 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int) BEGIN set @x=i1; END character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW CREATE PROCEDURE sp_2; Procedure sp_2 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW FUNCTION STATUS LIKE 'fn_%'; Db db_storedproc Name fn_1 Type FUNCTION Definer root@localhost Modified Created Security_type DEFINER Comment character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci Db db_storedproc Name fn_2 Type FUNCTION Definer root@localhost Modified Created Security_type INVOKER Comment created with INVOKER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW PROCEDURE STATUS LIKE 'sp_%'; Db db_storedproc Name sp_1 Type PROCEDURE Definer root@localhost Modified Created Security_type DEFINER Comment character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci Db db_storedproc Name sp_2 Type PROCEDURE Definer root@localhost Modified Created Security_type INVOKER Comment created with INVOKER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. ... now change some stuff: -------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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' ORDER BY routine_type, routine_name; 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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS NO SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT new comment, FN changed to INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS MODIFIES SQL DATA SQL_PATH NULL SECURITY_TYPE DEFINER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT FN changed to DEFINER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT new comment, SP changed to INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS READS SQL DATA SQL_PATH NULL SECURITY_TYPE DEFINER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT SP changed to DEFINER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW CREATE FUNCTION fn_2; Function fn_2 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW CREATE PROCEDURE sp_1; Procedure sp_1 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW CREATE PROCEDURE sp_2; Procedure sp_2 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW FUNCTION STATUS LIKE 'fn_%'; Db db_storedproc Name fn_1 Type FUNCTION Definer root@localhost Modified Created Security_type INVOKER Comment new comment, FN changed to INVOKER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci Db db_storedproc Name fn_2 Type FUNCTION Definer root@localhost Modified Created Security_type DEFINER Comment FN changed to DEFINER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW PROCEDURE STATUS LIKE 'sp_%'; Db db_storedproc Name sp_1 Type PROCEDURE Definer root@localhost Modified Created Security_type INVOKER Comment new comment, SP changed to INVOKER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci Db db_storedproc Name sp_2 Type PROCEDURE Definer root@localhost Modified Created Security_type DEFINER Comment SP changed to DEFINER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. ... change back to default and check result: -------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. ALTER FUNCTION fn_2 CONTAINS SQL; ... now check what is stored: ----------------------------- SELECT * FROM information_schema.routines WHERE routine_schema = 'db_storedproc' ORDER BY routine_type, routine_name; 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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS NO SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT new comment, FN changed to INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE DEFINER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT FN changed to DEFINER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS CONTAINS SQL SQL_PATH NULL SECURITY_TYPE INVOKER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT new comment, SP changed to INVOKER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_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 SQL PARAMETER_STYLE SQL IS_DETERMINISTIC NO SQL_DATA_ACCESS READS SQL DATA SQL_PATH NULL SECURITY_TYPE DEFINER CREATED LAST_ALTERED SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ROUTINE_COMMENT SP changed to DEFINER DEFINER root@localhost CHARACTER_SET_CLIENT utf8mb4 COLLATION_CONNECTION utf8mb4_0900_ai_ci DATABASE_COLLATION utf8mb4_0900_ai_ci SHOW CREATE FUNCTION fn_1; Function fn_1 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW CREATE FUNCTION fn_2; Function fn_2 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW CREATE PROCEDURE sp_1; Procedure sp_1 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW CREATE PROCEDURE sp_2; Procedure sp_2 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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 utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW FUNCTION STATUS LIKE 'fn_%'; Db db_storedproc Name fn_1 Type FUNCTION Definer root@localhost Modified Created Security_type INVOKER Comment new comment, FN changed to INVOKER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci Db db_storedproc Name fn_2 Type FUNCTION Definer root@localhost Modified Created Security_type DEFINER Comment FN changed to DEFINER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci SHOW PROCEDURE STATUS LIKE 'sp_%'; Db db_storedproc Name sp_1 Type PROCEDURE Definer root@localhost Modified Created Security_type INVOKER Comment new comment, SP changed to INVOKER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci Db db_storedproc Name sp_2 Type PROCEDURE Definer root@localhost Modified Created Security_type DEFINER Comment SP changed to DEFINER character_set_client utf8mb4 collation_connection utf8mb4_0900_ai_ci Database Collation utf8mb4_0900_ai_ci Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. ... cleanup ----------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. . +++ END OF SCRIPT +++ -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.