SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';

--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 MariaDB 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 +++
--------------------------------------------------------------------------------
