Gecko [ www.jdcpatala.org ]

Name	Size	Permission
charset_collation.result	1.31 KB	-rw-r--r--
innodb_bitdata.result	117 B	-rw-r--r--
innodb_cursors.result	116 B	-rw-r--r--
innodb_func_view.result	309.26 KB	-rw-r--r--
innodb_storedproc_02.result	36.35 KB	-rw-r--r--
innodb_storedproc_03.result	15.48 KB	-rw-r--r--
innodb_storedproc_06.result	14.62 KB	-rw-r--r--
innodb_storedproc_07.result	8.36 KB	-rw-r--r--
innodb_storedproc_08.result	25.53 KB	-rw-r--r--
innodb_storedproc_10.result	13.39 KB	-rw-r--r--
innodb_trig_0102.result	15.08 KB	-rw-r--r--
innodb_trig_03.result	24.65 KB	-rw-r--r--
innodb_trig_03e.result	48.78 KB	-rw-r--r--
innodb_trig_0407.result	10.07 KB	-rw-r--r--
innodb_trig_08.result	18.46 KB	-rw-r--r--
innodb_trig_09.result	9.98 KB	-rw-r--r--
innodb_trig_1011ext.result	12.04 KB	-rw-r--r--
innodb_trig_frkey.result	2.23 KB	-rw-r--r--
innodb_views.result	588.04 KB	-rw-r--r--
is_basics_mixed.result	30.21 KB	-rw-r--r--
is_character_sets.result	3.96 KB	-rw-r--r--
is_cml_innodb.result	4.97 KB	-rw-r--r--
is_cml_memory.result	3.94 KB	-rw-r--r--
is_cml_myisam.result	4.97 KB	-rw-r--r--
is_coll_char_set_appl.result	4.34 KB	-rw-r--r--
is_collations.result	4.43 KB	-rw-r--r--
is_column_privileges.result	19.15 KB	-rw-r--r--
is_column_privileges_is_mysql_...	1.44 KB	-rw-r--r--
is_columns.result	20.71 KB	-rw-r--r--
is_columns_innodb.result	78.28 KB	-rw-r--r--
is_columns_is.result	77.55 KB	-rw-r--r--
is_columns_is_embedded.result	75.57 KB	-rw-r--r--
is_columns_memory.result	75.05 KB	-rw-r--r--
is_columns_myisam.result	83.51 KB	-rw-r--r--
is_columns_myisam_embedded.res...	72.89 KB	-rw-r--r--
is_columns_mysql.result	67.25 KB	-rw-r--r--
is_columns_mysql_embedded.resu...	58.59 KB	-rw-r--r--
is_engines.result	3.88 KB	-rw-r--r--
is_engines_archive.result	161 B	-rw-r--r--
is_engines_blackhole.result	205 B	-rw-r--r--
is_engines_csv.result	149 B	-rw-r--r--
is_engines_federated.result	173 B	-rw-r--r--
is_engines_innodb.result	202 B	-rw-r--r--
is_engines_memory.result	194 B	-rw-r--r--
is_engines_merge.result	182 B	-rw-r--r--
is_engines_myisam.result	158 B	-rw-r--r--
is_events.result	6.38 KB	-rw-r--r--
is_key_column_usage.result	20.01 KB	-rw-r--r--
is_key_column_usage_embedded.r...	16.94 KB	-rw-r--r--
is_routines.result	27.73 KB	-rw-r--r--
is_routines_embedded.result	23.94 KB	-rw-r--r--
is_schema_privileges.result	15.56 KB	-rw-r--r--
is_schema_privileges_is_mysql_...	1.84 KB	-rw-r--r--
is_schemata.result	8.67 KB	-rw-r--r--
is_schemata_embedded.result	8.75 KB	-rw-r--r--
is_schemata_is_mysql_test.resu...	1.59 KB	-rw-r--r--
is_statistics.result	20.12 KB	-rw-r--r--
is_statistics_is.result	1.01 KB	-rw-r--r--
is_statistics_mysql.result	6.23 KB	-rw-r--r--
is_statistics_mysql_embedded.r...	11.47 KB	-rw-r--r--
is_table_constraints.result	15.57 KB	-rw-r--r--
is_table_constraints_is.result	828 B	-rw-r--r--
is_table_constraints_mysql.res...	2.3 KB	-rw-r--r--
is_table_constraints_mysql_emb...	3.81 KB	-rw-r--r--
is_table_privileges.result	15.08 KB	-rw-r--r--
is_tables.result	17.27 KB	-rw-r--r--
is_tables_embedded.result	17.48 KB	-rw-r--r--
is_tables_innodb.result	4.07 KB	-rw-r--r--
is_tables_is.result	30.01 KB	-rw-r--r--
is_tables_memory.result	4.17 KB	-rw-r--r--
is_tables_myisam.result	4.23 KB	-rw-r--r--
is_tables_myisam_embedded.resu...	4.63 KB	-rw-r--r--
is_tables_mysql.result	16.11 KB	-rw-r--r--
is_tables_mysql_embedded.resul...	31.05 KB	-rw-r--r--
is_triggers.result	15.87 KB	-rw-r--r--
is_triggers_embedded.result	11.97 KB	-rw-r--r--
is_user_privileges.result	41.08 KB	-rw-r--r--
is_views.result	14.67 KB	-rw-r--r--
is_views_embedded.result	10.98 KB	-rw-r--r--
memory_bitdata.result	286 B	-rw-r--r--
memory_cursors.result	285 B	-rw-r--r--
memory_func_view.result	309.31 KB	-rw-r--r--
memory_storedproc_02.result	36.4 KB	-rw-r--r--
memory_storedproc_03.result	15.53 KB	-rw-r--r--
memory_storedproc_06.result	14.67 KB	-rw-r--r--
memory_storedproc_07.result	8.41 KB	-rw-r--r--
memory_storedproc_08.result	25.58 KB	-rw-r--r--
memory_storedproc_10.result	13.44 KB	-rw-r--r--
memory_trig_0102.result	15.25 KB	-rw-r--r--
memory_trig_03.result	24.47 KB	-rw-r--r--
memory_trig_03e.result	47.41 KB	-rw-r--r--
memory_trig_0407.result	10.23 KB	-rw-r--r--
memory_trig_08.result	18.51 KB	-rw-r--r--
memory_trig_09.result	10.14 KB	-rw-r--r--
memory_trig_1011ext.result	12.11 KB	-rw-r--r--
memory_views.result	588.33 KB	-rw-r--r--
myisam_bitdata.result	286 B	-rw-r--r--
myisam_cursors.result	285 B	-rw-r--r--
myisam_func_view.result	309.31 KB	-rw-r--r--
myisam_storedproc_02.result	36.4 KB	-rw-r--r--
myisam_storedproc_03.result	15.53 KB	-rw-r--r--
myisam_storedproc_06.result	14.67 KB	-rw-r--r--
myisam_storedproc_07.result	8.41 KB	-rw-r--r--
myisam_storedproc_08.result	25.58 KB	-rw-r--r--
myisam_storedproc_10.result	13.44 KB	-rw-r--r--
myisam_trig_0102.result	15.25 KB	-rw-r--r--
myisam_trig_03.result	24.47 KB	-rw-r--r--
myisam_trig_03e.result	47.41 KB	-rw-r--r--
myisam_trig_0407.result	10.23 KB	-rw-r--r--
myisam_trig_08.result	18.51 KB	-rw-r--r--
myisam_trig_09.result	10.14 KB	-rw-r--r--
myisam_trig_1011ext.result	12.11 KB	-rw-r--r--
myisam_views-big.result	603.32 KB	-rw-r--r--
processlist_priv_no_prot.resul...	28.91 KB	-rw-r--r--
processlist_priv_ps.result	28.94 KB	-rw-r--r--
processlist_val_no_prot.result	91.97 KB	-rw-r--r--
processlist_val_ps.result	91.98 KB	-rw-r--r--
row_count_func.result	1.4 KB	-rw-r--r--
storedproc.result	893.84 KB	-rw-r--r--

Code Editor : memory_storedproc_08.result

SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.

--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;
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;
SET sql_mode = default;

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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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	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
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;

.                               +++ END OF SCRIPT +++
--------------------------------------------------------------------------------

${this.title}

Code Editor : memory_storedproc_08.result