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(189) NO NULL CHARACTER_SET_CLIENT varchar(32) NO NULL COLLATION_CONNECTION varchar(32) NO NULL DATABASE_COLLATION varchar(32) 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(189) NOT NULL, `CHARACTER_SET_CLIENT` varchar(32) NOT NULL, `COLLATION_CONNECTION` varchar(32) NOT NULL, `DATABASE_COLLATION` varchar(32) NOT NULL ) DEFAULT CHARSET=utf8 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(189) NO NULL CHARACTER_SET_CLIENT varchar(32) NO NULL COLLATION_CONNECTION varchar(32) NO NULL DATABASE_COLLATION varchar(32) 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 = ; 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 = ; 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; 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 latin1_swedish_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 latin1_swedish_ci connect testuser2, localhost, testuser2, , db_datadict; SELECT * FROM information_schema.routines; 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 latin1_swedish_ci connect testuser3, localhost, testuser3, , test; SELECT * FROM information_schema.routines; 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 LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_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 LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_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 LAST_ALTERED SQL_MODE ROUTINE_COMMENT updated comments DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_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 LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_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 LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_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 LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER root@localhost CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_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 = ; 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 = ; 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 latin1_swedish_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;