Impressum is_routines.result   Sprache: Lisp

set sql_mode="";
SHOW TABLES FROM information_schema LIKE 'ROUTINES';
Tables_in_information_schema (ROUTINES)
ROUTINES
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW      IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION  IF EXISTS test.f1;
CREATE VIEW test.v1 AS     SELECT * FROM information_schema.ROUTINES;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.ROUTINES;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.ROUTINES;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.ROUTINES;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.8.1: INFORMATION_SCHEMA.ROUTINES layout
#########################################################################
DESCRIBE          information_schema.ROUTINES;
Field Type Null Key Default Extra
SPECIFIC_NAME varchar(64) NO  NULL 
ROUTINE_CATALOG varchar(512) NO  NULL 
ROUTINE_SCHEMA varchar(64) NO  NULL 
ROUTINE_NAME varchar(64) NO  NULL 
ROUTINE_TYPE varchar(13) NO  NULL 
DATA_TYPE varchar(64) NO  NULL 
CHARACTER_MAXIMUM_LENGTH int(21) YES  NULL 
CHARACTER_OCTET_LENGTH int(21) YES  NULL 
NUMERIC_PRECISION int(21) YES  NULL 
NUMERIC_SCALE int(21) YES  NULL 
DATETIME_PRECISION bigint(21) unsigned YES  NULL 
CHARACTER_SET_NAME varchar(64) YES  NULL 
COLLATION_NAME varchar(64) YES  NULL 
DTD_IDENTIFIER longtext YES  NULL 
ROUTINE_BODY varchar(8) NO  NULL 
ROUTINE_DEFINITION longtext YES  NULL 
EXTERNAL_NAME varchar(64) YES  NULL 
EXTERNAL_LANGUAGE varchar(64) YES  NULL 
PARAMETER_STYLE varchar(8) NO  NULL 
IS_DETERMINISTIC varchar(3) NO  NULL 
SQL_DATA_ACCESS varchar(64) NO  NULL 
SQL_PATH varchar(64) YES  NULL 
SECURITY_TYPE varchar(7) NO  NULL 
CREATED datetime NO  NULL 
LAST_ALTERED datetime NO  NULL 
SQL_MODE varchar(8192) NO  NULL 
ROUTINE_COMMENT longtext NO  NULL 
DEFINER varchar(384) NO  NULL 
CHARACTER_SET_CLIENT varchar(32) NO  NULL 
COLLATION_CONNECTION varchar(64) NO  NULL 
DATABASE_COLLATION varchar(64) NO  NULL 
SHOW CREATE TABLE information_schema.ROUTINES;
Table Create Table
ROUTINES CREATE TEMPORARY TABLE `ROUTINES` (
  `SPECIFIC_NAME` varchar(64) NOT NULL,
  `ROUTINE_CATALOG` varchar(512) NOT NULL,
  `ROUTINE_SCHEMA` varchar(64) NOT NULL,
  `ROUTINE_NAME` varchar(64) NOT NULL,
  `ROUTINE_TYPE` varchar(13) NOT NULL,
  `DATA_TYPE` varchar(64) NOT NULL,
  `CHARACTER_MAXIMUM_LENGTH` int(21),
  `CHARACTER_OCTET_LENGTH` int(21),
  `NUMERIC_PRECISION` int(21),
  `NUMERIC_SCALE` int(21),
  `DATETIME_PRECISION` bigint(21) unsigned,
  `CHARACTER_SET_NAME` varchar(64),
  `COLLATION_NAME` varchar(64),
  `DTD_IDENTIFIER` longtext,
  `ROUTINE_BODY` varchar(8) NOT NULL,
  `ROUTINE_DEFINITION` longtext,
  `EXTERNAL_NAME` varchar(64),
  `EXTERNAL_LANGUAGE` varchar(64),
  `PARAMETER_STYLE` varchar(8) NOT NULL,
  `IS_DETERMINISTIC` varchar(3) NOT NULL,
  `SQL_DATA_ACCESS` varchar(64) NOT NULL,
  `SQL_PATH` varchar(64),
  `SECURITY_TYPE` varchar(7) NOT NULL,
  `CREATED` datetime NOT NULL,
  `LAST_ALTERED` datetime NOT NULL,
  `SQL_MODE` varchar(8192) NOT NULL,
  `ROUTINE_COMMENT` longtext NOT NULL,
  `DEFINER` varchar(384) NOT NULL,
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULL,
  `COLLATION_CONNECTION` varchar(64) NOT NULL,
  `DATABASE_COLLATION` varchar(64) NOT NULL
)  DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
SHOW COLUMNS FROM information_schema.ROUTINES;
Field Type Null Key Default Extra
SPECIFIC_NAME varchar(64) NO  NULL 
ROUTINE_CATALOG varchar(512) NO  NULL 
ROUTINE_SCHEMA varchar(64) NO  NULL 
ROUTINE_NAME varchar(64) NO  NULL 
ROUTINE_TYPE varchar(13) NO  NULL 
DATA_TYPE varchar(64) NO  NULL 
CHARACTER_MAXIMUM_LENGTH int(21) YES  NULL 
CHARACTER_OCTET_LENGTH int(21) YES  NULL 
NUMERIC_PRECISION int(21) YES  NULL 
NUMERIC_SCALE int(21) YES  NULL 
DATETIME_PRECISION bigint(21) unsigned YES  NULL 
CHARACTER_SET_NAME varchar(64) YES  NULL 
COLLATION_NAME varchar(64) YES  NULL 
DTD_IDENTIFIER longtext YES  NULL 
ROUTINE_BODY varchar(8) NO  NULL 
ROUTINE_DEFINITION longtext YES  NULL 
EXTERNAL_NAME varchar(64) YES  NULL 
EXTERNAL_LANGUAGE varchar(64) YES  NULL 
PARAMETER_STYLE varchar(8) NO  NULL 
IS_DETERMINISTIC varchar(3) NO  NULL 
SQL_DATA_ACCESS varchar(64) NO  NULL 
SQL_PATH varchar(64) YES  NULL 
SECURITY_TYPE varchar(7) NO  NULL 
CREATED datetime NO  NULL 
LAST_ALTERED datetime NO  NULL 
SQL_MODE varchar(8192) NO  NULL 
ROUTINE_COMMENT longtext NO  NULL 
DEFINER varchar(384) NO  NULL 
CHARACTER_SET_CLIENT varchar(32) NO  NULL 
COLLATION_CONNECTION varchar(64) NO  NULL 
DATABASE_COLLATION varchar(64) NO  NULL 
USE test;
DROP PROCEDURE IF EXISTS sp_for_routines;
DROP FUNCTION  IF EXISTS function_for_routines;
CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type,
routine_body,external_name,external_language,parameter_style,sql_path
FROM information_schema.routines
WHERE routine_schema = 'test' AND
(routine_catalog   IS NOT NULL OR external_name   IS NOT NULL
OR external_language IS NOT NULL OR sql_path        IS NOT NULL
OR routine_body      <> 'SQL'    OR parameter_style <> 'SQL'
   OR specific_name     <> routine_name);
specific_name routine_catalog routine_schema routine_name routine_type routine_body external_name external_language parameter_style sql_path
function_for_routines def test function_for_routines FUNCTION SQL NULL NULL SQL NULL
sp_for_routines def test sp_for_routines PROCEDURE SQL NULL NULL SQL NULL
DROP PROCEDURE sp_for_routines;
DROP FUNCTION  function_for_routines;
################################################################################
# Testcase 3.2.8.2 + 3.2.8.3: INFORMATION_SCHEMA.ROUTINES accessible information
################################################################################
DROP DATABASE IF EXISTS db_datadict;
DROP DATABASE IF EXISTS db_datadict_2;
CREATE DATABASE db_datadict;
USE db_datadict;
CREATE TABLE res_6_408002_1(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
ENGINE = <other_engine_type>;
INSERT INTO res_6_408002_1(f1, f2, f3, f4)
VALUES('abc', 'xyz', '1989-11-09', 0815);
DROP PROCEDURE IF EXISTS sp_6_408002_1;
CREATE PROCEDURE sp_6_408002_1()
BEGIN
SELECT * FROM db_datadict.res_6_408002_1;
END//
CREATE DATABASE db_datadict_2;
USE db_datadict_2;
CREATE TABLE res_6_408002_2(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
ENGINE = <other_engine_type>;
INSERT INTO res_6_408002_2(f1, f2, f3, f4)
VALUES('abc', 'xyz', '1990-10-03', 4711);
DROP PROCEDURE IF EXISTS sp_6_408002_2;
CREATE PROCEDURE sp_6_408002_2()
BEGIN
SELECT * FROM db_datadict_2.res_6_408002_2;
END//
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP   USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
DROP   USER 'testuser3'@'localhost';
CREATE USER 'testuser3'@'localhost';
GRANT SELECT  ON db_datadict_2.* TO 'testuser1'@'localhost';
GRANT EXECUTE ON db_datadict_2.* TO 'testuser1'@'localhost';
GRANT EXECUTE ON db_datadict.*   TO 'testuser1'@'localhost';
GRANT SELECT  ON db_datadict.*   TO 'testuser2'@'localhost';
GRANT EXECUTE ON PROCEDURE db_datadict_2.sp_6_408002_2
TO 'testuser2'@'localhost';
GRANT EXECUTE ON db_datadict_2.* TO 'testuser2'@'localhost';
FLUSH PRIVILEGES;
connect  testuser1, localhost, testuser1, , db_datadict;
SELECT * FROM information_schema.routines where routine_schema <> 'sys';
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
sp_6_408002_1 def db_datadict sp_6_408002_1 PROCEDURE  NULL NULL NULL NULL NULL NULL NULL NULL SQL NULL NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss   root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE  NULL NULL NULL NULL NULL NULL NULL NULL SQL NULL NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss   root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
connect  testuser2, localhost, testuser2, , db_datadict;
SELECT * FROM information_schema.routines where routine_schema <> 'sys';
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE  NULL NULL NULL NULL NULL NULL NULL NULL SQL NULL NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss   root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
connect  testuser3, localhost, testuser3, , "*NO-ONE*";
SELECT * FROM information_schema.routines where routine_schema <> 'sys';
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
connection default;
disconnect testuser1;
disconnect testuser2;
disconnect testuser3;
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP USER 'testuser3'@'localhost';
USE test;
DROP DATABASE db_datadict;
DROP DATABASE db_datadict_2;
#########################################################################
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.ROUTINES modifications
#########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
USE db_datadict;
CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
ORDER BY routine_name;
SPECIFIC_NAME function_for_routines
ROUTINE_CATALOG def
ROUTINE_SCHEMA db_datadict
ROUTINE_NAME function_for_routines
ROUTINE_TYPE FUNCTION
DATA_TYPE int
CHARACTER_MAXIMUM_LENGTH NULL
CHARACTER_OCTET_LENGTH NULL
NUMERIC_PRECISION 10
NUMERIC_SCALE 0
DATETIME_PRECISION NULL
CHARACTER_SET_NAME NULL
COLLATION_NAME NULL
DTD_IDENTIFIER int(11)
ROUTINE_BODY SQL
ROUTINE_DEFINITION RETURN 0
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGE NULL
PARAMETER_STYLE SQL
IS_DETERMINISTIC NO
SQL_DATA_ACCESS CONTAINS SQL
SQL_PATH NULL
SECURITY_TYPE DEFINER
CREATED <created>
LAST_ALTERED <modified>
SQL_MODE 
ROUTINE_COMMENT 
DEFINER root@localhost
CHARACTER_SET_CLIENT latin1
COLLATION_CONNECTION latin1_swedish_ci
DATABASE_COLLATION utf8mb4_uca1400_ai_ci
SPECIFIC_NAME sp_for_routines
ROUTINE_CATALOG def
ROUTINE_SCHEMA db_datadict
ROUTINE_NAME sp_for_routines
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 SELECT 'db_datadict'
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGE NULL
PARAMETER_STYLE SQL
IS_DETERMINISTIC NO
SQL_DATA_ACCESS CONTAINS SQL
SQL_PATH NULL
SECURITY_TYPE DEFINER
CREATED <created>
LAST_ALTERED <modified>
SQL_MODE 
ROUTINE_COMMENT 
DEFINER root@localhost
CHARACTER_SET_CLIENT latin1
COLLATION_CONNECTION latin1_swedish_ci
DATABASE_COLLATION utf8mb4_uca1400_ai_ci
ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER;
ALTER FUNCTION function_for_routines COMMENT 'updated comments';
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
ORDER BY routine_name;
SPECIFIC_NAME function_for_routines
ROUTINE_CATALOG def
ROUTINE_SCHEMA db_datadict
ROUTINE_NAME function_for_routines
ROUTINE_TYPE FUNCTION
DATA_TYPE int
CHARACTER_MAXIMUM_LENGTH NULL
CHARACTER_OCTET_LENGTH NULL
NUMERIC_PRECISION 10
NUMERIC_SCALE 0
DATETIME_PRECISION NULL
CHARACTER_SET_NAME NULL
COLLATION_NAME NULL
DTD_IDENTIFIER int(11)
ROUTINE_BODY SQL
ROUTINE_DEFINITION RETURN 0
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGE NULL
PARAMETER_STYLE SQL
IS_DETERMINISTIC NO
SQL_DATA_ACCESS CONTAINS SQL
SQL_PATH NULL
SECURITY_TYPE DEFINER
CREATED <created>
LAST_ALTERED <modified>
SQL_MODE 
ROUTINE_COMMENT updated comments
DEFINER root@localhost
CHARACTER_SET_CLIENT latin1
COLLATION_CONNECTION latin1_swedish_ci
DATABASE_COLLATION utf8mb4_uca1400_ai_ci
SPECIFIC_NAME sp_for_routines
ROUTINE_CATALOG def
ROUTINE_SCHEMA db_datadict
ROUTINE_NAME sp_for_routines
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 SELECT 'db_datadict'
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGE NULL
PARAMETER_STYLE SQL
IS_DETERMINISTIC NO
SQL_DATA_ACCESS CONTAINS SQL
SQL_PATH NULL
SECURITY_TYPE INVOKER
CREATED <created>
LAST_ALTERED <modified>
SQL_MODE 
ROUTINE_COMMENT 
DEFINER root@localhost
CHARACTER_SET_CLIENT latin1
COLLATION_CONNECTION latin1_swedish_ci
DATABASE_COLLATION utf8mb4_uca1400_ai_ci
DROP PROCEDURE sp_for_routines;
DROP FUNCTION function_for_routines;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
ORDER BY routine_name;
SPECIFIC_NAME function_for_routines
ROUTINE_CATALOG def
ROUTINE_SCHEMA db_datadict
ROUTINE_NAME function_for_routines
ROUTINE_TYPE FUNCTION
DATA_TYPE int
CHARACTER_MAXIMUM_LENGTH NULL
CHARACTER_OCTET_LENGTH NULL
NUMERIC_PRECISION 10
NUMERIC_SCALE 0
DATETIME_PRECISION NULL
CHARACTER_SET_NAME NULL
COLLATION_NAME NULL
DTD_IDENTIFIER int(11)
ROUTINE_BODY SQL
ROUTINE_DEFINITION RETURN 0
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGE NULL
PARAMETER_STYLE SQL
IS_DETERMINISTIC NO
SQL_DATA_ACCESS CONTAINS SQL
SQL_PATH NULL
SECURITY_TYPE DEFINER
CREATED <created>
LAST_ALTERED <modified>
SQL_MODE 
ROUTINE_COMMENT 
DEFINER root@localhost
CHARACTER_SET_CLIENT latin1
COLLATION_CONNECTION latin1_swedish_ci
DATABASE_COLLATION utf8mb4_uca1400_ai_ci
SPECIFIC_NAME sp_for_routines
ROUTINE_CATALOG def
ROUTINE_SCHEMA db_datadict
ROUTINE_NAME sp_for_routines
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 SELECT 'db_datadict'
EXTERNAL_NAME NULL
EXTERNAL_LANGUAGE NULL
PARAMETER_STYLE SQL
IS_DETERMINISTIC NO
SQL_DATA_ACCESS CONTAINS SQL
SQL_PATH NULL
SECURITY_TYPE DEFINER
CREATED <created>
LAST_ALTERED <modified>
SQL_MODE 
ROUTINE_COMMENT 
DEFINER root@localhost
CHARACTER_SET_CLIENT latin1
COLLATION_CONNECTION latin1_swedish_ci
DATABASE_COLLATION utf8mb4_uca1400_ai_ci
use test;
DROP DATABASE db_datadict;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
#########################################################################
# 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for
#          ROUTINE_DEFINITION column
#########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
USE db_datadict;
CREATE TABLE db_datadict.res_6_408004_1
(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
ENGINE = <other_engine_type>;
INSERT INTO db_datadict.res_6_408004_1
VALUES ('abc', 98765 , 99999999 , 98765, 10);
CREATE TABLE db_datadict.res_6_408004_2
(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
ENGINE = <other_engine_type>;
INSERT INTO db_datadict.res_6_408004_2
VALUES ('abc', 98765 , 99999999 , 98765, 10);
# Checking the max. possible length of (currently) 4 GByte is not
# in this environment here.
CREATE PROCEDURE sp_6_408004 ()
BEGIN
DECLARE done INTEGER DEFAULt 0;
DECLARE variable_number_1 LONGTEXT;
DECLARE variable_number_2 MEDIUMINT;
DECLARE variable_number_3 LONGBLOB;
DECLARE variable_number_4 REAL;
DECLARE variable_number_5 YEAR;
DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
BEGIN
OPEN cursor_number_1;
WHILE done <> 1 DO
FETCH cursor_number_1
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
BEGIN
BEGIN
SET done = 0;
OPEN cursor_number_2;
WHILE done <> 1 DO
FETCH cursor_number_2
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES(variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
SET done = 0;
OPEN cursor_number_3;
WHILE done <> 1 DO
FETCH cursor_number_3
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES(variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
END;
BEGIN
SET done = 0;
OPEN cursor_number_4;
WHILE done <> 1 DO
FETCH cursor_number_4
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
BEGIN
SET @a='test row';
SELECT @a;
SELECT @a;
SELECT @a;
END;
BEGIN
SET done = 0;
OPEN cursor_number_5;
WHILE done <> 1 DO
FETCH cursor_number_5
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
BEGIN
SET @a='test row';
SELECT @a;
SELECT @a;
SELECT @a;
END;
END//
CALL db_datadict.sp_6_408004 ();
@a
test row
@a
test row
@a
test row
@a
test row
@a
test row
@a
test row
SELECT * FROM db_datadict.res_6_408004_2;
f1 f2 f3 f4 f5
abc 98765 99999999 98765 2010
abc 98765 99999999 98765 2010
abc 98765 99999999 98765 2010
abc 98765 99999999 98765 2010
abc 98765 99999999 98765 2010
abc 98765 99999999 98765 2010
SELECT *, LENGTH(routine_definition) FROM information_schema.routines
WHERE routine_schema = 'db_datadict';
SPECIFIC_NAME sp_6_408004
ROUTINE_CATALOG def
ROUTINE_SCHEMA db_datadict
ROUTINE_NAME sp_6_408004
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
DECLARE done INTEGER DEFAULt 0;
DECLARE variable_number_1 LONGTEXT;
DECLARE variable_number_2 MEDIUMINT;
DECLARE variable_number_3 LONGBLOB;
DECLARE variable_number_4 REAL;
DECLARE variable_number_5 YEAR;
DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
BEGIN
OPEN cursor_number_1;
WHILE done <> 1 DO
FETCH cursor_number_1
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
BEGIN
BEGIN
SET done = 0;
OPEN cursor_number_2;
WHILE done <> 1 DO
FETCH cursor_number_2
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES(variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
SET done = 0;
OPEN cursor_number_3;
WHILE done <> 1 DO
FETCH cursor_number_3
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES(variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
END;
BEGIN
SET done = 0;
OPEN cursor_number_4;
WHILE done <> 1 DO
FETCH cursor_number_4
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
BEGIN
SET @a='test row';
SELECT @a;
SELECT @a;
SELECT @a;
END;
BEGIN
SET done = 0;
OPEN cursor_number_5;
WHILE done <> 1 DO
FETCH cursor_number_5
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
BEGIN
SET @a='test row';
SELECT @a;
SELECT @a;
SELECT @a;
END;
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 YYYY-MM-DD hh:mm:ss
LAST_ALTERED YYYY-MM-DD hh:mm:ss
SQL_MODE 
ROUTINE_COMMENT 
DEFINER root@localhost
CHARACTER_SET_CLIENT latin1
COLLATION_CONNECTION latin1_swedish_ci
DATABASE_COLLATION utf8mb4_uca1400_ai_ci
LENGTH(routine_definition) 2549
DROP DATABASE db_datadict;
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
#           DDL on INFORMATION_SCHEMA table are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
USE db_datadict;
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
USE test;
INSERT INTO information_schema.routines (routine_name, routine_type )
VALUES ('p2', 'procedure');
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.routines SET routine_name = 'p2'
WHERE routine_body = 'sql';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.routines ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.routines ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX i7 ON information_schema.routines (routine_name);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.routines  ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.routines  DISCARD TABLESPACE;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.routines ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.routines RENAME db_datadict.routines;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.routines RENAME information_schema.xroutines;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;