mirror of
https://github.com/MariaDB/server.git
synced 2025-02-07 06:12:18 +01:00
758 lines
41 KiB
Text
758 lines
41 KiB
Text
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 BEGIN
|
|
SELECT * FROM db_datadict.res_6_408002_1;
|
|
END 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 BEGIN
|
|
SELECT * FROM db_datadict_2.res_6_408002_2;
|
|
END 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
|
|
add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
|
|
check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' AND variable_name != 'THREAD_POOL_SIZE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert') AND TRIGGER_SCHEMA != 'sys'; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA != 'sys'; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.plugin, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.global_priv; SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_STATUS != 'INACTIVE'; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
|
|
check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
|
|
AddGeometryColumn def mysql AddGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
|
|
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
DropGeometryColumn def mysql DropGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
|
|
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@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_1 def db_datadict sp_6_408002_1 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN
|
|
SELECT * FROM db_datadict.res_6_408002_1;
|
|
END 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 BEGIN
|
|
SELECT * FROM db_datadict_2.res_6_408002_2;
|
|
END 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
|
|
add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
|
|
check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' AND variable_name != 'THREAD_POOL_SIZE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert') AND TRIGGER_SCHEMA != 'sys'; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA != 'sys'; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.plugin, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.global_priv; SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_STATUS != 'INACTIVE'; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
|
|
check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
|
|
AddGeometryColumn def mysql AddGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
|
|
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
DropGeometryColumn def mysql DropGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
|
|
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@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
|
|
sp_6_408002_1 def db_datadict sp_6_408002_1 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN
|
|
SELECT * FROM db_datadict.res_6_408002_1;
|
|
END 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 BEGIN
|
|
SELECT * FROM db_datadict_2.res_6_408002_2;
|
|
END 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
|
|
add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
|
|
check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' AND variable_name != 'THREAD_POOL_SIZE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert') AND TRIGGER_SCHEMA != 'sys'; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA != 'sys'; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.plugin, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.global_priv; SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_STATUS != 'INACTIVE'; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
|
|
check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
|
|
AddGeometryColumn def mysql AddGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
|
|
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
DropGeometryColumn def mysql DropGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
|
|
set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
|
|
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 HY000: The target table routines of the UPDATE is not updatable
|
|
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;
|