diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/r')
16 files changed, 498 insertions, 320 deletions
diff --git a/mysql-test/suite/funcs_1/r/is_character_sets.result b/mysql-test/suite/funcs_1/r/is_character_sets.result index caff08ae1e4..da4e3b06503 100644 --- a/mysql-test/suite/funcs_1/r/is_character_sets.result +++ b/mysql-test/suite/funcs_1/r/is_character_sets.result @@ -57,7 +57,7 @@ INSERT INTO information_schema.character_sets SELECT * FROM information_schema.character_sets; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.character_sets SET description = 'just updated'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.character_sets WHERE table_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.character_sets; diff --git a/mysql-test/suite/funcs_1/r/is_coll_char_set_appl.result b/mysql-test/suite/funcs_1/r/is_coll_char_set_appl.result index 7eba964bdf6..a512a7458fb 100644 --- a/mysql-test/suite/funcs_1/r/is_coll_char_set_appl.result +++ b/mysql-test/suite/funcs_1/r/is_coll_char_set_appl.result @@ -52,10 +52,10 @@ SELECT * FROM information_schema.collation_character_set_applicability; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.collation_character_set_applicability SET collation_name = 'big6_chinese_ci' WHERE character_set_name = 'big6'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors UPDATE information_schema.collation_character_set_applicability SET character_set_name = 't_4711'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.collation_character_set_applicability; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.collation_character_set_applicability; diff --git a/mysql-test/suite/funcs_1/r/is_collations.result b/mysql-test/suite/funcs_1/r/is_collations.result index c017bbd4eaf..c74976f9fc2 100644 --- a/mysql-test/suite/funcs_1/r/is_collations.result +++ b/mysql-test/suite/funcs_1/r/is_collations.result @@ -67,7 +67,7 @@ INSERT INTO information_schema.collations VALUES ( 'cp1251_bin', 'cp1251',50, '', '',0); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.collations SET description = 'just updated'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.collations WHERE table_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.collations; diff --git a/mysql-test/suite/funcs_1/r/is_engines.result b/mysql-test/suite/funcs_1/r/is_engines.result index e76cbe7bdee..54372ae9f39 100644 --- a/mysql-test/suite/funcs_1/r/is_engines.result +++ b/mysql-test/suite/funcs_1/r/is_engines.result @@ -64,7 +64,7 @@ INSERT INTO information_schema.engines SELECT * FROM information_schema.engines; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.engines SET engine = '1234567'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.engines WHERE support IN ('DEFAULT','YES'); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.engines; diff --git a/mysql-test/suite/funcs_1/r/is_events.result b/mysql-test/suite/funcs_1/r/is_events.result index 3db6cc5e61d..ca9e7427513 100644 --- a/mysql-test/suite/funcs_1/r/is_events.result +++ b/mysql-test/suite/funcs_1/r/is_events.result @@ -128,7 +128,7 @@ SELECT * FROM information_schema.events; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.events SET event_name = '1234567' WHERE table_name = 't1'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.events WHERE event_catalog IS NULL; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.events; diff --git a/mysql-test/suite/funcs_1/r/is_key_column_usage.result b/mysql-test/suite/funcs_1/r/is_key_column_usage.result index 80dd2db5441..fa2a738788e 100644 --- a/mysql-test/suite/funcs_1/r/is_key_column_usage.result +++ b/mysql-test/suite/funcs_1/r/is_key_column_usage.result @@ -345,28 +345,6 @@ db_datadict my_idx db_datadict t1_my_tablex f4 1 db_datadict my_idx db_datadict t1_my_tablex first_col 2 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1 db_datadict PRIMARY db_datadict t1_my_tablex f2 2 -SELECT constraint_schema, constraint_name, table_schema, -table_name, column_name, ordinal_position -FROM information_schema.key_column_usage -WHERE table_name = 't1_my_tablex' -ORDER BY constraint_schema, constraint_name, table_schema, -table_name, ordinal_position; -constraint_schema constraint_name table_schema table_name column_name ordinal_position -db_datadict my_idx db_datadict t1_my_tablex f4 1 -db_datadict my_idx db_datadict t1_my_tablex first_col 2 -db_datadict PRIMARY db_datadict t1_my_tablex first_col 1 -db_datadict PRIMARY db_datadict t1_my_tablex f2 2 -ALTER TABLE db_datadict.t1_my_tablex -DROP COLUMN first_col; -SELECT constraint_schema, constraint_name, table_schema, -table_name, column_name, ordinal_position -FROM information_schema.key_column_usage -WHERE table_name = 't1_my_tablex' -ORDER BY constraint_schema, constraint_name, table_schema, -table_name, ordinal_position; -constraint_schema constraint_name table_schema table_name column_name ordinal_position -db_datadict my_idx db_datadict t1_my_tablex f4 1 -db_datadict PRIMARY db_datadict t1_my_tablex f2 1 SELECT table_name, column_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex' @@ -374,6 +352,8 @@ ORDER BY table_name, column_name; table_name column_name t1_my_tablex f2 t1_my_tablex f4 +t1_my_tablex first_col +t1_my_tablex first_col DROP TABLE db_datadict.t1_my_tablex; SELECT table_name, column_name FROM information_schema.key_column_usage @@ -419,7 +399,7 @@ SELECT * FROM information_schema.key_column_usage; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.key_column_usage SET table_name = 'db1' WHERE constraint_name = 'primary'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.key_column_usage WHERE table_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.key_column_usage; diff --git a/mysql-test/suite/funcs_1/r/is_key_column_usage_embedded.result b/mysql-test/suite/funcs_1/r/is_key_column_usage_embedded.result index a794bf8328b..4a6114f3da3 100644 --- a/mysql-test/suite/funcs_1/r/is_key_column_usage_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_key_column_usage_embedded.result @@ -28,10 +28,10 @@ DROP FUNCTION test.f1; ######################################################################### DESCRIBE information_schema.KEY_COLUMN_USAGE; Field Type Null Key Default Extra -CONSTRAINT_CATALOG varchar(512) YES NULL +CONSTRAINT_CATALOG varchar(512) NO CONSTRAINT_SCHEMA varchar(64) NO CONSTRAINT_NAME varchar(64) NO -TABLE_CATALOG varchar(512) YES NULL +TABLE_CATALOG varchar(512) NO TABLE_SCHEMA varchar(64) NO TABLE_NAME varchar(64) NO COLUMN_NAME varchar(64) NO @@ -43,14 +43,14 @@ REFERENCED_COLUMN_NAME varchar(64) YES NULL SHOW CREATE TABLE information_schema.KEY_COLUMN_USAGE; Table Create Table KEY_COLUMN_USAGE CREATE TEMPORARY TABLE `KEY_COLUMN_USAGE` ( - `CONSTRAINT_CATALOG` varchar(512) DEFAULT NULL, + `CONSTRAINT_CATALOG` varchar(512) NOT NULL DEFAULT '', `CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '', `CONSTRAINT_NAME` varchar(64) NOT NULL DEFAULT '', - `TABLE_CATALOG` varchar(512) DEFAULT NULL, + `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `TABLE_NAME` varchar(64) NOT NULL DEFAULT '', `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '', - `ORDINAL_POSITION` bigint(10) NOT NULL DEFAULT '0', + `ORDINAL_POSITION` bigint(10) NOT NULL DEFAULT 0, `POSITION_IN_UNIQUE_CONSTRAINT` bigint(10) DEFAULT NULL, `REFERENCED_TABLE_SCHEMA` varchar(64) DEFAULT NULL, `REFERENCED_TABLE_NAME` varchar(64) DEFAULT NULL, @@ -58,10 +58,10 @@ KEY_COLUMN_USAGE CREATE TEMPORARY TABLE `KEY_COLUMN_USAGE` ( ) ENGINE=MEMORY DEFAULT CHARSET=utf8 SHOW COLUMNS FROM information_schema.KEY_COLUMN_USAGE; Field Type Null Key Default Extra -CONSTRAINT_CATALOG varchar(512) YES NULL +CONSTRAINT_CATALOG varchar(512) NO CONSTRAINT_SCHEMA varchar(64) NO CONSTRAINT_NAME varchar(64) NO -TABLE_CATALOG varchar(512) YES NULL +TABLE_CATALOG varchar(512) NO TABLE_SCHEMA varchar(64) NO TABLE_NAME varchar(64) NO COLUMN_NAME varchar(64) NO @@ -75,6 +75,74 @@ table_schema, table_name, column_name FROM information_schema.key_column_usage WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL; constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name column_name +def mysql PRIMARY def mysql column_stats db_name +def mysql PRIMARY def mysql column_stats table_name +def mysql PRIMARY def mysql column_stats column_name +def mysql PRIMARY def mysql columns_priv Host +def mysql PRIMARY def mysql columns_priv Db +def mysql PRIMARY def mysql columns_priv User +def mysql PRIMARY def mysql columns_priv Table_name +def mysql PRIMARY def mysql columns_priv Column_name +def mysql PRIMARY def mysql db Host +def mysql PRIMARY def mysql db Db +def mysql PRIMARY def mysql db User +def mysql PRIMARY def mysql event db +def mysql PRIMARY def mysql event name +def mysql PRIMARY def mysql func name +def mysql PRIMARY def mysql gtid_slave_pos domain_id +def mysql PRIMARY def mysql gtid_slave_pos sub_id +def mysql PRIMARY def mysql help_category help_category_id +def mysql name def mysql help_category name +def mysql PRIMARY def mysql help_keyword help_keyword_id +def mysql name def mysql help_keyword name +def mysql PRIMARY def mysql help_relation help_keyword_id +def mysql PRIMARY def mysql help_relation help_topic_id +def mysql PRIMARY def mysql help_topic help_topic_id +def mysql name def mysql help_topic name +def mysql PRIMARY def mysql host Host +def mysql PRIMARY def mysql host Db +def mysql PRIMARY def mysql index_stats db_name +def mysql PRIMARY def mysql index_stats table_name +def mysql PRIMARY def mysql index_stats index_name +def mysql PRIMARY def mysql index_stats prefix_arity +def mysql PRIMARY def mysql innodb_index_stats database_name +def mysql PRIMARY def mysql innodb_index_stats table_name +def mysql PRIMARY def mysql innodb_index_stats index_name +def mysql PRIMARY def mysql innodb_index_stats stat_name +def mysql PRIMARY def mysql innodb_table_stats database_name +def mysql PRIMARY def mysql innodb_table_stats table_name +def mysql PRIMARY def mysql plugin name +def mysql PRIMARY def mysql proc db +def mysql PRIMARY def mysql proc name +def mysql PRIMARY def mysql proc type +def mysql PRIMARY def mysql procs_priv Host +def mysql PRIMARY def mysql procs_priv Db +def mysql PRIMARY def mysql procs_priv User +def mysql PRIMARY def mysql procs_priv Routine_name +def mysql PRIMARY def mysql procs_priv Routine_type +def mysql PRIMARY def mysql proxies_priv Host +def mysql PRIMARY def mysql proxies_priv User +def mysql PRIMARY def mysql proxies_priv Proxied_host +def mysql PRIMARY def mysql proxies_priv Proxied_user +def mysql Host def mysql roles_mapping Host +def mysql Host def mysql roles_mapping User +def mysql Host def mysql roles_mapping Role +def mysql PRIMARY def mysql servers Server_name +def mysql PRIMARY def mysql table_stats db_name +def mysql PRIMARY def mysql table_stats table_name +def mysql PRIMARY def mysql tables_priv Host +def mysql PRIMARY def mysql tables_priv Db +def mysql PRIMARY def mysql tables_priv User +def mysql PRIMARY def mysql tables_priv Table_name +def mysql PRIMARY def mysql time_zone Time_zone_id +def mysql PRIMARY def mysql time_zone_leap_second Transition_time +def mysql PRIMARY def mysql time_zone_name Name +def mysql PRIMARY def mysql time_zone_transition Time_zone_id +def mysql PRIMARY def mysql time_zone_transition Transition_time +def mysql PRIMARY def mysql time_zone_transition_type Time_zone_id +def mysql PRIMARY def mysql time_zone_transition_type Transition_type_id +def mysql PRIMARY def mysql user Host +def mysql PRIMARY def mysql user User ######################################################################################## # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.KEY_COLUMN_USAGE accessible information ######################################################################################## @@ -100,25 +168,27 @@ WHERE table_name LIKE 't1_%' ORDER BY constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, ordinal_position; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME -NULL db_datadict PRIMARY NULL db_datadict t1_1 f1 1 NULL NULL NULL NULL -NULL db_datadict PRIMARY NULL db_datadict t1_2 f1 1 NULL NULL NULL NULL -# Establish connection testuser1 (user=testuser1) +def db_datadict PRIMARY def db_datadict t1_1 f1 1 NULL NULL NULL NULL +def db_datadict PRIMARY def db_datadict t1_2 f1 1 NULL NULL NULL NULL +connect testuser1, localhost, testuser1, , db_datadict; SELECT * FROM information_schema.key_column_usage WHERE table_name LIKE 't1_%' ORDER BY constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, ordinal_position; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME -NULL db_datadict PRIMARY NULL db_datadict t1_1 f1 1 NULL NULL NULL NULL -NULL db_datadict PRIMARY NULL db_datadict t1_2 f1 1 NULL NULL NULL NULL -# Establish connection testuser2 (user=testuser2) +def db_datadict PRIMARY def db_datadict t1_1 f1 1 NULL NULL NULL NULL +def db_datadict PRIMARY def db_datadict t1_2 f1 1 NULL NULL NULL NULL +connect testuser2, localhost, testuser2, , db_datadict; SELECT * FROM information_schema.key_column_usage WHERE table_name LIKE 't1_%' ORDER BY constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, ordinal_position; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME -NULL db_datadict PRIMARY NULL db_datadict t1_1 f1 1 NULL NULL NULL NULL -NULL db_datadict PRIMARY NULL db_datadict t1_2 f1 1 NULL NULL NULL NULL -# Switch to connection default and close connections testuser1, testuser2 +def db_datadict PRIMARY def db_datadict t1_1 f1 1 NULL NULL NULL NULL +def db_datadict PRIMARY def db_datadict t1_2 f1 1 NULL NULL NULL NULL +connection default; +disconnect testuser1; +disconnect testuser2; DROP USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; DROP TABLE t1_1; @@ -139,10 +209,10 @@ DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ENGINE = <engine_type>; SELECT * FROM information_schema.key_column_usage WHERE table_name = 't1_my_table'; -CONSTRAINT_CATALOG NULL +CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME PRIMARY -TABLE_CATALOG NULL +TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1_my_table COLUMN_NAME f1 @@ -151,10 +221,10 @@ POSITION_IN_UNIQUE_CONSTRAINT NULL REFERENCED_TABLE_SCHEMA NULL REFERENCED_TABLE_NAME NULL REFERENCED_COLUMN_NAME NULL -CONSTRAINT_CATALOG NULL +CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME PRIMARY -TABLE_CATALOG NULL +TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1_my_table COLUMN_NAME f2 @@ -277,28 +347,6 @@ db_datadict my_idx db_datadict t1_my_tablex f4 1 db_datadict my_idx db_datadict t1_my_tablex first_col 2 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1 db_datadict PRIMARY db_datadict t1_my_tablex f2 2 -SELECT constraint_schema, constraint_name, table_schema, -table_name, column_name, ordinal_position -FROM information_schema.key_column_usage -WHERE table_name = 't1_my_tablex' -ORDER BY constraint_schema, constraint_name, table_schema, -table_name, ordinal_position; -constraint_schema constraint_name table_schema table_name column_name ordinal_position -db_datadict my_idx db_datadict t1_my_tablex f4 1 -db_datadict my_idx db_datadict t1_my_tablex first_col 2 -db_datadict PRIMARY db_datadict t1_my_tablex first_col 1 -db_datadict PRIMARY db_datadict t1_my_tablex f2 2 -ALTER TABLE db_datadict.t1_my_tablex -DROP COLUMN first_col; -SELECT constraint_schema, constraint_name, table_schema, -table_name, column_name, ordinal_position -FROM information_schema.key_column_usage -WHERE table_name = 't1_my_tablex' -ORDER BY constraint_schema, constraint_name, table_schema, -table_name, ordinal_position; -constraint_schema constraint_name table_schema table_name column_name ordinal_position -db_datadict my_idx db_datadict t1_my_tablex f4 1 -db_datadict PRIMARY db_datadict t1_my_tablex f2 1 SELECT table_name, column_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex' @@ -306,6 +354,8 @@ ORDER BY table_name, column_name; table_name column_name t1_my_tablex f2 t1_my_tablex f4 +t1_my_tablex first_col +t1_my_tablex first_col DROP TABLE db_datadict.t1_my_tablex; SELECT table_name, column_name FROM information_schema.key_column_usage @@ -351,7 +401,7 @@ SELECT * FROM information_schema.key_column_usage; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.key_column_usage SET table_name = 'db1' WHERE constraint_name = 'primary'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.key_column_usage WHERE table_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.key_column_usage; diff --git a/mysql-test/suite/funcs_1/r/is_routines_embedded.result b/mysql-test/suite/funcs_1/r/is_routines_embedded.result index e5f476367e2..1fc9f90ae49 100644 --- a/mysql-test/suite/funcs_1/r/is_routines_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_routines_embedded.result @@ -1,3 +1,4 @@ +set sql_mode=""; SHOW TABLES FROM information_schema LIKE 'ROUTINES'; Tables_in_information_schema (ROUTINES) ROUTINES @@ -29,11 +30,19 @@ DROP FUNCTION test.f1; DESCRIBE information_schema.ROUTINES; Field Type Null Key Default Extra SPECIFIC_NAME varchar(64) NO -ROUTINE_CATALOG varchar(512) YES NULL +ROUTINE_CATALOG varchar(512) NO ROUTINE_SCHEMA varchar(64) NO ROUTINE_NAME varchar(64) NO ROUTINE_TYPE varchar(9) NO -DTD_IDENTIFIER varchar(64) YES NULL +DATA_TYPE varchar(64) NO +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 ROUTINE_DEFINITION longtext YES NULL EXTERNAL_NAME varchar(64) YES NULL @@ -45,23 +54,31 @@ SQL_PATH varchar(64) YES NULL SECURITY_TYPE varchar(7) NO CREATED datetime NO 0000-00-00 00:00:00 LAST_ALTERED datetime NO 0000-00-00 00:00:00 -SQL_MODE longtext NO NULL -ROUTINE_COMMENT varchar(64) NO -DEFINER varchar(77) NO +SQL_MODE varchar(8192) NO +ROUTINE_COMMENT longtext NO +DEFINER varchar(189) NO CHARACTER_SET_CLIENT varchar(32) NO COLLATION_CONNECTION varchar(32) NO -DATABASE_COLLATION varchar(32) NO +DATABASE_COLLATION varchar(32) NO SHOW CREATE TABLE information_schema.ROUTINES; Table Create Table ROUTINES CREATE TEMPORARY TABLE `ROUTINES` ( `SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '', - `ROUTINE_CATALOG` varchar(512) DEFAULT NULL, + `ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '', `ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '', `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '', - `DTD_IDENTIFIER` varchar(64) DEFAULT NULL, + `DATA_TYPE` varchar(64) NOT NULL DEFAULT '', + `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL, + `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL, + `NUMERIC_PRECISION` int(21) DEFAULT NULL, + `NUMERIC_SCALE` int(21) DEFAULT NULL, + `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL, + `CHARACTER_SET_NAME` varchar(64) DEFAULT NULL, + `COLLATION_NAME` varchar(64) DEFAULT NULL, + `DTD_IDENTIFIER` longtext DEFAULT NULL, `ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '', - `ROUTINE_DEFINITION` longtext, + `ROUTINE_DEFINITION` longtext DEFAULT NULL, `EXTERNAL_NAME` varchar(64) DEFAULT NULL, `EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL, `PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '', @@ -71,21 +88,29 @@ ROUTINES CREATE TEMPORARY TABLE `ROUTINES` ( `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '', `CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `SQL_MODE` longtext NOT NULL, - `ROUTINE_COMMENT` varchar(64) NOT NULL DEFAULT '', - `DEFINER` varchar(77) NOT NULL DEFAULT '', + `SQL_MODE` varchar(8192) NOT NULL DEFAULT '', + `ROUTINE_COMMENT` longtext NOT NULL DEFAULT '', + `DEFINER` varchar(189) NOT NULL DEFAULT '', `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '', `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '', `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT '' -) ENGINE=MyISAM DEFAULT CHARSET=utf8 +) DEFAULT CHARSET=utf8 SHOW COLUMNS FROM information_schema.ROUTINES; Field Type Null Key Default Extra SPECIFIC_NAME varchar(64) NO -ROUTINE_CATALOG varchar(512) YES NULL +ROUTINE_CATALOG varchar(512) NO ROUTINE_SCHEMA varchar(64) NO ROUTINE_NAME varchar(64) NO ROUTINE_TYPE varchar(9) NO -DTD_IDENTIFIER varchar(64) YES NULL +DATA_TYPE varchar(64) NO +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 ROUTINE_DEFINITION longtext YES NULL EXTERNAL_NAME varchar(64) YES NULL @@ -97,12 +122,12 @@ SQL_PATH varchar(64) YES NULL SECURITY_TYPE varchar(7) NO CREATED datetime NO 0000-00-00 00:00:00 LAST_ALTERED datetime NO 0000-00-00 00:00:00 -SQL_MODE longtext NO NULL -ROUTINE_COMMENT varchar(64) NO -DEFINER varchar(77) NO +SQL_MODE varchar(8192) NO +ROUTINE_COMMENT longtext NO +DEFINER varchar(189) NO CHARACTER_SET_CLIENT varchar(32) NO COLLATION_CONNECTION varchar(32) NO -DATABASE_COLLATION varchar(32) NO +DATABASE_COLLATION varchar(32) NO USE test; DROP PROCEDURE IF EXISTS sp_for_routines; DROP FUNCTION IF EXISTS function_for_routines; @@ -111,11 +136,14 @@ 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_catalog IS NOT NULL OR external_name IS NOT NULL +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; + 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; ################################################################################ @@ -159,34 +187,46 @@ 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; -# Establish connection testuser1 (user=testuser1) +connect testuser1, localhost, testuser1, , db_datadict; SELECT * FROM information_schema.routines; -SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 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 NULL db_datadict sp_6_408002_1 PROCEDURE NULL SQL BEGIN +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 latin1_swedish_ci -sp_6_408002_2 NULL db_datadict_2 sp_6_408002_2 PROCEDURE NULL SQL BEGIN +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 latin1_swedish_ci -# Establish connection testuser2 (user=testuser2) +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 utf8 utf8_general_ci latin1_swedish_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', 'innodb_file_format_max') 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' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema'); 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'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; 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.host, mysql.plugin, mysql.proc, 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.user; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; 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 utf8 utf8_general_ci latin1_swedish_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; 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 utf8 utf8_general_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 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 NULL db_datadict sp_6_408002_1 PROCEDURE NULL SQL BEGIN +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 latin1_swedish_ci -sp_6_408002_2 NULL db_datadict_2 sp_6_408002_2 PROCEDURE NULL SQL BEGIN +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 latin1_swedish_ci -# Establish connection testuser3 (user=testuser3) +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 utf8 utf8_general_ci latin1_swedish_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', 'innodb_file_format_max') 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' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema'); 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'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; 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.host, mysql.plugin, mysql.proc, 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.user; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; 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 utf8 utf8_general_ci latin1_swedish_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; 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 utf8 utf8_general_ci latin1_swedish_ci +connect testuser3, localhost, testuser3, , test; SELECT * FROM information_schema.routines; -SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 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 NULL db_datadict sp_6_408002_1 PROCEDURE NULL SQL BEGIN +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 latin1_swedish_ci -sp_6_408002_2 NULL db_datadict_2 sp_6_408002_2 PROCEDURE NULL SQL BEGIN +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 latin1_swedish_ci -# Switch to connection default and close connections testuser1,testuser2,testuser3 +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 utf8 utf8_general_ci latin1_swedish_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', 'innodb_file_format_max') 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' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema'); 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'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; 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.host, mysql.plugin, mysql.proc, 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.user; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; 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 utf8 utf8_general_ci latin1_swedish_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; 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 utf8 utf8_general_ci latin1_swedish_ci +connection default; +disconnect testuser1; +disconnect testuser2; +disconnect testuser3; DROP USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; DROP USER 'testuser3'@'localhost'; @@ -199,17 +239,25 @@ DROP DATABASE db_datadict_2; 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 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 +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 NULL +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 @@ -229,10 +277,18 @@ CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME sp_for_routines -ROUTINE_CATALOG NULL +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' @@ -256,10 +312,18 @@ 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 NULL +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 @@ -279,10 +343,18 @@ CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME sp_for_routines -ROUTINE_CATALOG NULL +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' @@ -304,16 +376,24 @@ 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 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 +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 NULL +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 @@ -333,10 +413,18 @@ CHARACTER_SET_CLIENT latin1 COLLATION_CONNECTION latin1_swedish_ci DATABASE_COLLATION latin1_swedish_ci SPECIFIC_NAME sp_for_routines -ROUTINE_CATALOG NULL +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' @@ -358,7 +446,7 @@ 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 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 +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 @@ -498,10 +586,18 @@ 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 NULL +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 @@ -630,7 +726,7 @@ 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' +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 ; diff --git a/mysql-test/suite/funcs_1/r/is_schemata_embedded.result b/mysql-test/suite/funcs_1/r/is_schemata_embedded.result index db0583b4b72..5f48c030c16 100644 --- a/mysql-test/suite/funcs_1/r/is_schemata_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_schemata_embedded.result @@ -28,31 +28,36 @@ DROP FUNCTION test.f1; ######################################################################### DESCRIBE information_schema.SCHEMATA; Field Type Null Key Default Extra -CATALOG_NAME varchar(512) YES NULL +CATALOG_NAME varchar(512) NO SCHEMA_NAME varchar(64) NO -DEFAULT_CHARACTER_SET_NAME varchar(64) NO -DEFAULT_COLLATION_NAME varchar(64) NO +DEFAULT_CHARACTER_SET_NAME varchar(32) NO +DEFAULT_COLLATION_NAME varchar(32) NO SQL_PATH varchar(512) YES NULL SHOW CREATE TABLE information_schema.SCHEMATA; Table Create Table SCHEMATA CREATE TEMPORARY TABLE `SCHEMATA` ( - `CATALOG_NAME` varchar(512) DEFAULT NULL, + `CATALOG_NAME` varchar(512) NOT NULL DEFAULT '', `SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '', - `DEFAULT_CHARACTER_SET_NAME` varchar(64) NOT NULL DEFAULT '', - `DEFAULT_COLLATION_NAME` varchar(64) NOT NULL DEFAULT '', + `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', + `DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '', `SQL_PATH` varchar(512) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 SHOW COLUMNS FROM information_schema.SCHEMATA; Field Type Null Key Default Extra -CATALOG_NAME varchar(512) YES NULL +CATALOG_NAME varchar(512) NO SCHEMA_NAME varchar(64) NO -DEFAULT_CHARACTER_SET_NAME varchar(64) NO -DEFAULT_COLLATION_NAME varchar(64) NO +DEFAULT_CHARACTER_SET_NAME varchar(32) NO +DEFAULT_COLLATION_NAME varchar(32) NO SQL_PATH varchar(512) YES NULL SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL; catalog_name schema_name sql_path +def information_schema NULL +def mtr NULL +def mysql NULL +def performance_schema NULL +def test NULL ############################################################################### # Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA accessible information ############################################################################### @@ -72,43 +77,46 @@ GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost'; SELECT * FROM information_schema.schemata WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH -NULL db_datadict_1 latin1 latin1_swedish_ci NULL -NULL db_datadict_2 latin1 latin1_swedish_ci NULL +def db_datadict_1 latin1 latin1_swedish_ci NULL +def db_datadict_2 latin1 latin1_swedish_ci NULL SHOW DATABASES LIKE 'db_datadict_%'; Database (db_datadict_%) db_datadict_1 db_datadict_2 -# Establish connection testuser1 (user=testuser1) +connect testuser1, localhost, testuser1, , db_datadict_1; SELECT * FROM information_schema.schemata WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH -NULL db_datadict_1 latin1 latin1_swedish_ci NULL -NULL db_datadict_2 latin1 latin1_swedish_ci NULL +def db_datadict_1 latin1 latin1_swedish_ci NULL +def db_datadict_2 latin1 latin1_swedish_ci NULL SHOW DATABASES LIKE 'db_datadict_%'; Database (db_datadict_%) db_datadict_1 db_datadict_2 -# Establish connection testuser2 (user=testuser2) +connect testuser2, localhost, testuser2, , db_datadict_2; SELECT * FROM information_schema.schemata WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH -NULL db_datadict_1 latin1 latin1_swedish_ci NULL -NULL db_datadict_2 latin1 latin1_swedish_ci NULL +def db_datadict_1 latin1 latin1_swedish_ci NULL +def db_datadict_2 latin1 latin1_swedish_ci NULL SHOW DATABASES LIKE 'db_datadict_%'; Database (db_datadict_%) db_datadict_1 db_datadict_2 -# Establish connection testuser3 (user=testuser3) +connect testuser3, localhost, testuser3, , test; SELECT * FROM information_schema.schemata WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH -NULL db_datadict_1 latin1 latin1_swedish_ci NULL -NULL db_datadict_2 latin1 latin1_swedish_ci NULL +def db_datadict_1 latin1 latin1_swedish_ci NULL +def db_datadict_2 latin1 latin1_swedish_ci NULL SHOW DATABASES LIKE 'db_datadict_%'; Database (db_datadict_%) db_datadict_1 db_datadict_2 -# Switch to connection default and close connections testuser1,testuser2,testuser3 +connection default; +disconnect testuser1; +disconnect testuser2; +disconnect testuser3; DROP USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; DROP USER 'testuser3'@'localhost'; @@ -123,7 +131,7 @@ CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_P CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'; SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict'; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH -NULL db_datadict latin1 latin1_swedish_ci NULL +def db_datadict latin1 latin1_swedish_ci NULL SELECT schema_name, default_character_set_name FROM information_schema.schemata WHERE schema_name = 'db_datadict'; schema_name default_character_set_name @@ -167,9 +175,9 @@ ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_ UPDATE information_schema.schemata SET default_character_set_name = 'utf8' WHERE schema_name = 'db_datadict'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +ERROR HY000: The target table schemata of the UPDATE is not updatable UPDATE information_schema.schemata SET catalog_name = 't_4711'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +ERROR HY000: The target table schemata of the UPDATE is not updatable DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.schemata; diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints.result b/mysql-test/suite/funcs_1/r/is_table_constraints.result index 37fcce5ae42..0553b4344c8 100644 --- a/mysql-test/suite/funcs_1/r/is_table_constraints.result +++ b/mysql-test/suite/funcs_1/r/is_table_constraints.result @@ -162,7 +162,7 @@ SELECT table_name FROM information_schema.table_constraints WHERE table_name LIKE 't1_my_table%'; table_name CREATE TABLE test.t1_my_table -(f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2)) +(f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1)) DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ENGINE = <engine_type>; SELECT constraint_name, table_schema, table_name, constraint_type diff --git a/mysql-test/suite/funcs_1/r/is_tables.result b/mysql-test/suite/funcs_1/r/is_tables.result index 5ccbdd3d90c..75809a3b77a 100644 --- a/mysql-test/suite/funcs_1/r/is_tables.result +++ b/mysql-test/suite/funcs_1/r/is_tables.result @@ -402,7 +402,7 @@ SELECT * FROM information_schema.tables; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.tables SET table_schema = 'test' WHERE table_name = 't1'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.tables WHERE table_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.tables; diff --git a/mysql-test/suite/funcs_1/r/is_tables_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_embedded.result index 13ee6277bad..c42645f3536 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_embedded.result @@ -28,77 +28,77 @@ DROP FUNCTION test.f1; ######################################################################### DESCRIBE information_schema.TABLES; Field Type Null Key Default Extra -TABLE_CATALOG varchar(512) YES NULL +TABLE_CATALOG varchar(512) NO TABLE_SCHEMA varchar(64) NO TABLE_NAME varchar(64) NO TABLE_TYPE varchar(64) NO ENGINE varchar(64) YES NULL -VERSION bigint(21) YES NULL +VERSION bigint(21) unsigned YES NULL ROW_FORMAT varchar(10) YES NULL -TABLE_ROWS bigint(21) YES NULL -AVG_ROW_LENGTH bigint(21) YES NULL -DATA_LENGTH bigint(21) YES NULL -MAX_DATA_LENGTH bigint(21) YES NULL -INDEX_LENGTH bigint(21) YES NULL -DATA_FREE bigint(21) YES NULL -AUTO_INCREMENT bigint(21) YES NULL +TABLE_ROWS bigint(21) unsigned YES NULL +AVG_ROW_LENGTH bigint(21) unsigned YES NULL +DATA_LENGTH bigint(21) unsigned YES NULL +MAX_DATA_LENGTH bigint(21) unsigned YES NULL +INDEX_LENGTH bigint(21) unsigned YES NULL +DATA_FREE bigint(21) unsigned YES NULL +AUTO_INCREMENT bigint(21) unsigned YES NULL CREATE_TIME datetime YES NULL UPDATE_TIME datetime YES NULL CHECK_TIME datetime YES NULL -TABLE_COLLATION varchar(64) YES NULL -CHECKSUM bigint(21) YES NULL -CREATE_OPTIONS varchar(255) YES NULL -TABLE_COMMENT varchar(80) NO +TABLE_COLLATION varchar(32) YES NULL +CHECKSUM bigint(21) unsigned YES NULL +CREATE_OPTIONS varchar(2048) YES NULL +TABLE_COMMENT varchar(2048) NO SHOW CREATE TABLE information_schema.TABLES; Table Create Table TABLES CREATE TEMPORARY TABLE `TABLES` ( - `TABLE_CATALOG` varchar(512) default NULL, - `TABLE_SCHEMA` varchar(64) NOT NULL default '', - `TABLE_NAME` varchar(64) NOT NULL default '', - `TABLE_TYPE` varchar(64) NOT NULL default '', - `ENGINE` varchar(64) default NULL, - `VERSION` bigint(21) default NULL, - `ROW_FORMAT` varchar(10) default NULL, - `TABLE_ROWS` bigint(21) default NULL, - `AVG_ROW_LENGTH` bigint(21) default NULL, - `DATA_LENGTH` bigint(21) default NULL, - `MAX_DATA_LENGTH` bigint(21) default NULL, - `INDEX_LENGTH` bigint(21) default NULL, - `DATA_FREE` bigint(21) default NULL, - `AUTO_INCREMENT` bigint(21) default NULL, - `CREATE_TIME` datetime default NULL, - `UPDATE_TIME` datetime default NULL, - `CHECK_TIME` datetime default NULL, - `TABLE_COLLATION` varchar(64) default NULL, - `CHECKSUM` bigint(21) default NULL, - `CREATE_OPTIONS` varchar(255) default NULL, - `TABLE_COMMENT` varchar(80) NOT NULL default '' + `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', + `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', + `TABLE_NAME` varchar(64) NOT NULL DEFAULT '', + `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '', + `ENGINE` varchar(64) DEFAULT NULL, + `VERSION` bigint(21) unsigned DEFAULT NULL, + `ROW_FORMAT` varchar(10) DEFAULT NULL, + `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL, + `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL, + `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, + `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, + `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL, + `DATA_FREE` bigint(21) unsigned DEFAULT NULL, + `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL, + `CREATE_TIME` datetime DEFAULT NULL, + `UPDATE_TIME` datetime DEFAULT NULL, + `CHECK_TIME` datetime DEFAULT NULL, + `TABLE_COLLATION` varchar(32) DEFAULT NULL, + `CHECKSUM` bigint(21) unsigned DEFAULT NULL, + `CREATE_OPTIONS` varchar(2048) DEFAULT NULL, + `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT '' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 SHOW COLUMNS FROM information_schema.TABLES; Field Type Null Key Default Extra -TABLE_CATALOG varchar(512) YES NULL +TABLE_CATALOG varchar(512) NO TABLE_SCHEMA varchar(64) NO TABLE_NAME varchar(64) NO TABLE_TYPE varchar(64) NO ENGINE varchar(64) YES NULL -VERSION bigint(21) YES NULL +VERSION bigint(21) unsigned YES NULL ROW_FORMAT varchar(10) YES NULL -TABLE_ROWS bigint(21) YES NULL -AVG_ROW_LENGTH bigint(21) YES NULL -DATA_LENGTH bigint(21) YES NULL -MAX_DATA_LENGTH bigint(21) YES NULL -INDEX_LENGTH bigint(21) YES NULL -DATA_FREE bigint(21) YES NULL -AUTO_INCREMENT bigint(21) YES NULL +TABLE_ROWS bigint(21) unsigned YES NULL +AVG_ROW_LENGTH bigint(21) unsigned YES NULL +DATA_LENGTH bigint(21) unsigned YES NULL +MAX_DATA_LENGTH bigint(21) unsigned YES NULL +INDEX_LENGTH bigint(21) unsigned YES NULL +DATA_FREE bigint(21) unsigned YES NULL +AUTO_INCREMENT bigint(21) unsigned YES NULL CREATE_TIME datetime YES NULL UPDATE_TIME datetime YES NULL CHECK_TIME datetime YES NULL -TABLE_COLLATION varchar(64) YES NULL -CHECKSUM bigint(21) YES NULL -CREATE_OPTIONS varchar(255) YES NULL -TABLE_COMMENT varchar(80) NO +TABLE_COLLATION varchar(32) YES NULL +CHECKSUM bigint(21) unsigned YES NULL +CREATE_OPTIONS varchar(2048) YES NULL +TABLE_COMMENT varchar(2048) NO SELECT table_catalog, table_schema, table_name -FROM information_schema.tables WHERE table_catalog IS NOT NULL; +FROM information_schema.tables WHERE table_catalog IS NULL OR table_catalog <> 'def'; table_catalog table_schema table_name ################################################################################ # Testcase 3.2.12.2 + 3.2.12.3: INFORMATION_SCHEMA.TABLES accessible information @@ -117,7 +117,7 @@ CREATE TABLE db_datadict.tb1 (f1 INT, f2 INT, f3 INT) ENGINE = <engine_type>; GRANT SELECT ON db_datadict.tb1 TO 'testuser1'@'localhost'; GRANT ALL ON db_datadict.tb1 TO 'testuser2'@'localhost' WITH GRANT OPTION; -# Establish connection testuser1 (user=testuser1) +connect testuser1, localhost, testuser1, , db_datadict; CREATE TABLE tb2 (f1 DECIMAL) ENGINE = <engine_type>; CREATE TABLE tb3 (f1 VARCHAR(200)) @@ -129,59 +129,61 @@ GRANT SELECT ON db_datadict.v3 to 'testuser3'@'localhost'; SELECT * FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT -NULL db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW +def db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW SHOW TABLES FROM db_datadict; Tables_in_db_datadict tb1 tb2 tb3 v3 -# Establish connection testuser2 (user=testuser2) +connect testuser2, localhost, testuser2, , db_datadict; SELECT * FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT -NULL db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW +def db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW SHOW TABLES FROM db_datadict; Tables_in_db_datadict tb1 tb2 tb3 v3 -# Establish connection testuser3 (user=testuser3) +connect testuser3, localhost, testuser3, , db_datadict; SELECT * FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT -NULL db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW +def db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW SHOW TABLES FROM db_datadict; Tables_in_db_datadict tb1 tb2 tb3 v3 -# Switch to connection default (user=root) +connection default; SELECT * FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_name; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT -NULL db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# -NULL db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW +def db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS# +def db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW SHOW TABLES FROM db_datadict; Tables_in_db_datadict tb1 tb2 tb3 v3 -# Close connection testuser1, testuser2, testuser3 +disconnect testuser1; +disconnect testuser2; +disconnect testuser3; DROP USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; DROP USER 'testuser3'@'localhost'; @@ -200,7 +202,7 @@ DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT = 'Initial Comment' ENGINE = <engine_type>; SELECT * FROM information_schema.tables WHERE table_name = 't1_my_table'; -TABLE_CATALOG NULL +TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1_my_table TABLE_TYPE BASE TABLE @@ -318,6 +320,7 @@ SELECT UPDATE_TIME, checksum INTO @UPDATE_TIME, @checksum FROM information_schema.tables WHERE table_name = 't1_my_tablex'; INSERT INTO db_datadict.t1_my_tablex SET f1 = 3; +FLUSH TABLES; SELECT UPDATE_TIME > @UPDATE_TIME AS "Is current UPDATE_TIME bigger than before last INSERT?" FROM information_schema.tables @@ -352,7 +355,7 @@ DROP TABLE test.t1_my_tablex; CREATE VIEW test.t1_my_tablex AS SELECT 1; SELECT * FROM information_schema.tables WHERE table_name = 't1_my_tablex'; -TABLE_CATALOG NULL +TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1_my_tablex TABLE_TYPE VIEW @@ -407,7 +410,7 @@ SELECT * FROM information_schema.tables; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.tables SET table_schema = 'test' WHERE table_name = 't1'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.tables WHERE table_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.tables; diff --git a/mysql-test/suite/funcs_1/r/is_triggers.result b/mysql-test/suite/funcs_1/r/is_triggers.result index d20eeb9a319..8e5842742cb 100644 --- a/mysql-test/suite/funcs_1/r/is_triggers.result +++ b/mysql-test/suite/funcs_1/r/is_triggers.result @@ -215,7 +215,7 @@ SELECT * FROM information_schema.triggers; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.triggers SET trigger_schema = 'test' WHERE table_name = 't1'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.triggers WHERE trigger_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.triggers; diff --git a/mysql-test/suite/funcs_1/r/is_triggers_embedded.result b/mysql-test/suite/funcs_1/r/is_triggers_embedded.result index 5e0c7601da7..55dc79e50d2 100644 --- a/mysql-test/suite/funcs_1/r/is_triggers_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_triggers_embedded.result @@ -1,3 +1,4 @@ +set global sql_mode=""; SHOW TABLES FROM information_schema LIKE 'TRIGGERS'; Tables_in_information_schema (TRIGGERS) TRIGGERS @@ -28,74 +29,85 @@ DROP FUNCTION test.f1; ######################################################################### DESCRIBE information_schema.TRIGGERS; Field Type Null Key Default Extra -TRIGGER_CATALOG varchar(512) YES NULL +TRIGGER_CATALOG varchar(512) NO TRIGGER_SCHEMA varchar(64) NO TRIGGER_NAME varchar(64) NO EVENT_MANIPULATION varchar(6) NO -EVENT_OBJECT_CATALOG varchar(512) YES NULL +EVENT_OBJECT_CATALOG varchar(512) NO EVENT_OBJECT_SCHEMA varchar(64) NO EVENT_OBJECT_TABLE varchar(64) NO ACTION_ORDER bigint(4) NO 0 ACTION_CONDITION longtext YES NULL -ACTION_STATEMENT longtext NO NULL +ACTION_STATEMENT longtext NO ACTION_ORIENTATION varchar(9) NO ACTION_TIMING varchar(6) NO ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL ACTION_REFERENCE_OLD_ROW varchar(3) NO ACTION_REFERENCE_NEW_ROW varchar(3) NO -CREATED datetime YES NULL -SQL_MODE longtext NO NULL -DEFINER longtext NO NULL +CREATED datetime(2) YES NULL +SQL_MODE varchar(8192) NO +DEFINER varchar(189) NO +CHARACTER_SET_CLIENT varchar(32) NO +COLLATION_CONNECTION varchar(32) NO +DATABASE_COLLATION varchar(32) NO SHOW CREATE TABLE information_schema.TRIGGERS; Table Create Table TRIGGERS CREATE TEMPORARY TABLE `TRIGGERS` ( - `TRIGGER_CATALOG` varchar(512) default NULL, - `TRIGGER_SCHEMA` varchar(64) NOT NULL default '', - `TRIGGER_NAME` varchar(64) NOT NULL default '', - `EVENT_MANIPULATION` varchar(6) NOT NULL default '', - `EVENT_OBJECT_CATALOG` varchar(512) default NULL, - `EVENT_OBJECT_SCHEMA` varchar(64) NOT NULL default '', - `EVENT_OBJECT_TABLE` varchar(64) NOT NULL default '', - `ACTION_ORDER` bigint(4) NOT NULL default '0', - `ACTION_CONDITION` longtext, - `ACTION_STATEMENT` longtext NOT NULL, - `ACTION_ORIENTATION` varchar(9) NOT NULL default '', - `ACTION_TIMING` varchar(6) NOT NULL default '', - `ACTION_REFERENCE_OLD_TABLE` varchar(64) default NULL, - `ACTION_REFERENCE_NEW_TABLE` varchar(64) default NULL, - `ACTION_REFERENCE_OLD_ROW` varchar(3) NOT NULL default '', - `ACTION_REFERENCE_NEW_ROW` varchar(3) NOT NULL default '', - `CREATED` datetime default NULL, - `SQL_MODE` longtext NOT NULL, - `DEFINER` longtext NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=utf8 + `TRIGGER_CATALOG` varchar(512) NOT NULL DEFAULT '', + `TRIGGER_SCHEMA` varchar(64) NOT NULL DEFAULT '', + `TRIGGER_NAME` varchar(64) NOT NULL DEFAULT '', + `EVENT_MANIPULATION` varchar(6) NOT NULL DEFAULT '', + `EVENT_OBJECT_CATALOG` varchar(512) NOT NULL DEFAULT '', + `EVENT_OBJECT_SCHEMA` varchar(64) NOT NULL DEFAULT '', + `EVENT_OBJECT_TABLE` varchar(64) NOT NULL DEFAULT '', + `ACTION_ORDER` bigint(4) NOT NULL DEFAULT 0, + `ACTION_CONDITION` longtext DEFAULT NULL, + `ACTION_STATEMENT` longtext NOT NULL DEFAULT '', + `ACTION_ORIENTATION` varchar(9) NOT NULL DEFAULT '', + `ACTION_TIMING` varchar(6) NOT NULL DEFAULT '', + `ACTION_REFERENCE_OLD_TABLE` varchar(64) DEFAULT NULL, + `ACTION_REFERENCE_NEW_TABLE` varchar(64) DEFAULT NULL, + `ACTION_REFERENCE_OLD_ROW` varchar(3) NOT NULL DEFAULT '', + `ACTION_REFERENCE_NEW_ROW` varchar(3) NOT NULL DEFAULT '', + `CREATED` datetime(2) DEFAULT NULL, + `SQL_MODE` varchar(8192) NOT NULL DEFAULT '', + `DEFINER` varchar(189) NOT NULL DEFAULT '', + `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '', + `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '', + `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT '' +) DEFAULT CHARSET=utf8 SHOW COLUMNS FROM information_schema.TRIGGERS; Field Type Null Key Default Extra -TRIGGER_CATALOG varchar(512) YES NULL +TRIGGER_CATALOG varchar(512) NO TRIGGER_SCHEMA varchar(64) NO TRIGGER_NAME varchar(64) NO EVENT_MANIPULATION varchar(6) NO -EVENT_OBJECT_CATALOG varchar(512) YES NULL +EVENT_OBJECT_CATALOG varchar(512) NO EVENT_OBJECT_SCHEMA varchar(64) NO EVENT_OBJECT_TABLE varchar(64) NO ACTION_ORDER bigint(4) NO 0 ACTION_CONDITION longtext YES NULL -ACTION_STATEMENT longtext NO NULL +ACTION_STATEMENT longtext NO ACTION_ORIENTATION varchar(9) NO ACTION_TIMING varchar(6) NO ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL ACTION_REFERENCE_OLD_ROW varchar(3) NO ACTION_REFERENCE_NEW_ROW varchar(3) NO -CREATED datetime YES NULL -SQL_MODE longtext NO NULL -DEFINER longtext NO NULL +CREATED datetime(2) YES NULL +SQL_MODE varchar(8192) NO +DEFINER varchar(189) NO +CHARACTER_SET_CLIENT varchar(32) NO +COLLATION_CONNECTION varchar(32) NO +DATABASE_COLLATION varchar(32) NO SELECT * FROM information_schema.triggers WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL OR action_reference_new_table IS NOT NULL; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def mtr gs_insert INSERT def mtr global_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def mtr ts_insert INSERT def mtr test_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci ################################################################################## # Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information ################################################################################## @@ -109,62 +121,74 @@ DROP USER 'testuser3'@'localhost'; CREATE USER 'testuser3'@'localhost'; DROP USER 'testuser4'@'localhost'; CREATE USER 'testuser4'@'localhost'; -GRANT SUPER ON *.* TO 'testuser1'@'localhost'; -GRANT SUPER ON *.* TO 'testuser3'@'localhost'; -GRANT SUPER ON *.* TO 'testuser4'@'localhost'; +GRANT TRIGGER ON *.* TO 'testuser1'@'localhost'; +GRANT TRIGGER ON *.* TO 'testuser3'@'localhost'; +GRANT TRIGGER ON *.* TO 'testuser4'@'localhost'; GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION; -# Establish connection testuser1 (user=testuser1) +connect testuser1, localhost, testuser1, , db_datadict; CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT) ENGINE = <engine_type>; CREATE TRIGGER trg1 BEFORE INSERT ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before; GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost'; +REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost'; GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost'; SELECT * FROM information_schema.triggers WHERE trigger_name = 'trg1'; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER -NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci SHOW TRIGGERS FROM db_datadict; -Trigger Event Table Statement Timing Created sql_mode Definer -trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost -# Establish connection testuser2 (user=testuser2) +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci +connect testuser2, localhost, testuser2, , db_datadict; SHOW GRANTS FOR 'testuser2'@'localhost'; -# No SUPER Privilege --> no result for query +# No TRIGGER Privilege --> no result for query SELECT * FROM information_schema.triggers WHERE trigger_name = 'trg1'; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER -NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci SHOW TRIGGERS FROM db_datadict; -Trigger Event Table Statement Timing Created sql_mode Definer -trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost -# Establish connection testuser3 (user=testuser3) +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci +connect testuser3, localhost, testuser3, , test; SHOW GRANTS FOR 'testuser3'@'localhost'; -# SUPER Privilege + SELECT Privilege on t1 --> result for query +# TRIGGER Privilege + SELECT Privilege on t1 --> result for query SELECT * FROM information_schema.triggers WHERE trigger_name = 'trg1'; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER -NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci SHOW TRIGGERS FROM db_datadict; -Trigger Event Table Statement Timing Created sql_mode Definer -trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost -# Establish connection testuser4 (user=testuser4) +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci +connect testuser4, localhost, testuser4, , test; SHOW GRANTS FOR 'testuser4'@'localhost'; -# SUPER Privilege + no SELECT Privilege on t1 --> no result for query +# TRIGGER Privilege + no SELECT Privilege on t1 --> result for query +SELECT * FROM db_datadict.t1; +f1 f2 f3 +DESC db_datadict.t1; +Field Type Null Key Default Extra +f1 int(11) YES NULL +f2 int(11) YES NULL +f3 int(11) YES NULL SELECT * FROM information_schema.triggers WHERE trigger_name = 'trg1'; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER -NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci SHOW TRIGGERS FROM db_datadict; -Trigger Event Table Statement Timing Created sql_mode Definer -trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost -# Switch to connection default and close connections testuser1 - testuser4 +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci +connection default; +disconnect testuser1; +disconnect testuser2; +disconnect testuser3; +disconnect testuser4; SELECT * FROM information_schema.triggers WHERE trigger_name = 'trg1'; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER -NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci SHOW TRIGGERS FROM db_datadict; -Trigger Event Table Statement Timing Created sql_mode Definer -trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci latin1_swedish_ci DROP USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; DROP USER 'testuser3'@'localhost'; @@ -188,7 +212,7 @@ SELECT * FROM information_schema.triggers; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.triggers SET trigger_schema = 'test' WHERE table_name = 't1'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.triggers WHERE trigger_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.triggers; @@ -206,3 +230,4 @@ ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_ ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP DATABASE db_datadict; +set global sql_mode=default; diff --git a/mysql-test/suite/funcs_1/r/is_views.result b/mysql-test/suite/funcs_1/r/is_views.result index fb3daef3317..cc50b8033f0 100644 --- a/mysql-test/suite/funcs_1/r/is_views.result +++ b/mysql-test/suite/funcs_1/r/is_views.result @@ -234,7 +234,7 @@ VALUES ('db2', 'v2'); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.views SET table_schema = 'test' WHERE table_name = 't1'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.views WHERE table_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.views; diff --git a/mysql-test/suite/funcs_1/r/is_views_embedded.result b/mysql-test/suite/funcs_1/r/is_views_embedded.result index 21f799fca79..1f2e23c3610 100644 --- a/mysql-test/suite/funcs_1/r/is_views_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_views_embedded.result @@ -28,36 +28,45 @@ DROP FUNCTION test.f1; ######################################################################### DESCRIBE information_schema.VIEWS; Field Type Null Key Default Extra -TABLE_CATALOG varchar(512) YES NULL +TABLE_CATALOG varchar(512) NO TABLE_SCHEMA varchar(64) NO TABLE_NAME varchar(64) NO -VIEW_DEFINITION longtext NO NULL +VIEW_DEFINITION longtext NO CHECK_OPTION varchar(8) NO IS_UPDATABLE varchar(3) NO -DEFINER varchar(77) NO +DEFINER varchar(189) NO SECURITY_TYPE varchar(7) NO +CHARACTER_SET_CLIENT varchar(32) NO +COLLATION_CONNECTION varchar(32) NO +ALGORITHM varchar(10) NO SHOW CREATE TABLE information_schema.VIEWS; Table Create Table VIEWS CREATE TEMPORARY TABLE `VIEWS` ( - `TABLE_CATALOG` varchar(512) default NULL, - `TABLE_SCHEMA` varchar(64) NOT NULL default '', - `TABLE_NAME` varchar(64) NOT NULL default '', - `VIEW_DEFINITION` longtext NOT NULL, - `CHECK_OPTION` varchar(8) NOT NULL default '', - `IS_UPDATABLE` varchar(3) NOT NULL default '', - `DEFINER` varchar(77) NOT NULL default '', - `SECURITY_TYPE` varchar(7) NOT NULL default '' -) ENGINE=MyISAM DEFAULT CHARSET=utf8 + `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', + `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', + `TABLE_NAME` varchar(64) NOT NULL DEFAULT '', + `VIEW_DEFINITION` longtext NOT NULL DEFAULT '', + `CHECK_OPTION` varchar(8) NOT NULL DEFAULT '', + `IS_UPDATABLE` varchar(3) NOT NULL DEFAULT '', + `DEFINER` varchar(189) NOT NULL DEFAULT '', + `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '', + `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '', + `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '', + `ALGORITHM` varchar(10) NOT NULL DEFAULT '' +) DEFAULT CHARSET=utf8 SHOW COLUMNS FROM information_schema.VIEWS; Field Type Null Key Default Extra -TABLE_CATALOG varchar(512) YES NULL +TABLE_CATALOG varchar(512) NO TABLE_SCHEMA varchar(64) NO TABLE_NAME varchar(64) NO -VIEW_DEFINITION longtext NO NULL +VIEW_DEFINITION longtext NO CHECK_OPTION varchar(8) NO IS_UPDATABLE varchar(3) NO -DEFINER varchar(77) NO +DEFINER varchar(189) NO SECURITY_TYPE varchar(7) NO +CHARACTER_SET_CLIENT varchar(32) NO +COLLATION_CONNECTION varchar(32) NO +ALGORITHM varchar(10) NO SELECT table_catalog, table_schema, table_name FROM information_schema.views WHERE table_catalog IS NOT NULL; table_catalog table_schema table_name @@ -81,28 +90,31 @@ GRANT SELECT ON db_datadict.v_granted_to_1 TO 'testuser1'@'localhost'; GRANT SHOW VIEW, CREATE VIEW ON db_datadict.* TO 'testuser2'@'localhost'; SELECT * FROM information_schema.views WHERE table_schema = 'db_datadict' ORDER BY table_name; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE -NULL db_datadict v_granted_glob /* ALGORITHM=UNDEFINED */ select `db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER -NULL db_datadict v_granted_to_1 /* ALGORITHM=UNDEFINED */ select `db_datadict`.`t1`.`f1` AS `f1`,`db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER -# Establish connection testuser1 (user=testuser1) +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +def db_datadict v_granted_glob select `db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def db_datadict v_granted_to_1 select `db_datadict`.`t1`.`f1` AS `f1`,`db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +connect testuser1, localhost, testuser1, , test; SELECT * FROM information_schema.views WHERE table_schema = 'db_datadict' ORDER BY table_name; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE -NULL db_datadict v_granted_glob NONE YES root@localhost DEFINER -NULL db_datadict v_granted_to_1 NONE YES root@localhost DEFINER -# Establish connection testuser2 (user=testuser2) +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +def db_datadict v_granted_glob NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def db_datadict v_granted_to_1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +connect testuser2, localhost, testuser2, , test; SELECT * FROM information_schema.views WHERE table_schema = 'db_datadict' ORDER BY table_name; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE -NULL db_datadict v_granted_glob NONE YES root@localhost DEFINER -NULL db_datadict v_granted_to_1 NONE YES root@localhost DEFINER -# Establish connection test_no_views (user=test_no_views) +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +def db_datadict v_granted_glob NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def db_datadict v_granted_to_1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +connect test_no_views, localhost, test_no_views, , test; SELECT * FROM information_schema.views WHERE table_schema = 'db_datadict' ORDER BY table_name; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE -NULL db_datadict v_granted_glob NONE YES root@localhost DEFINER -NULL db_datadict v_granted_to_1 NONE YES root@localhost DEFINER -# Switch to connection default and close all other connections +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +def db_datadict v_granted_glob NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def db_datadict v_granted_to_1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +connection default; +disconnect testuser1; +disconnect testuser2; +disconnect test_no_views; DROP USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; DROP USER 'test_no_views'@'localhost'; @@ -120,12 +132,18 @@ DROP USER 'testuser1'@'localhost'; CREATE USER 'testuser1'@'localhost'; SELECT * FROM information_schema.views WHERE table_name LIKE 't1_%'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table; +CREATE ALGORITHM=MERGE VIEW test.t1_view1 AS SELECT f1 FROM test.t1_table; +CREATE ALGORITHM=TEMPTABLE VIEW test.t1_view2 AS SELECT f1 FROM test.t1_table; SELECT * FROM information_schema.views WHERE table_name LIKE 't1_%'; -TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE -NULL test t1_view /* ALGORITHM=UNDEFINED */ select distinct `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE NO root@localhost DEFINER +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM +def test t1_view select distinct `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED +def test t1_view1 select `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE YES root@localhost DEFINER latin1 latin1_swedish_ci MERGE +def test t1_view2 select `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci TEMPTABLE +DROP VIEW test.t1_view1; +DROP VIEW test.t1_view2; SELECT table_name,definer FROM information_schema.views WHERE table_name = 't1_view'; table_name definer @@ -148,7 +166,7 @@ ORDER BY table_schema,table_name; table_schema table_name test t1_view RENAME TABLE test.t1_view TO db_datadict.t1_view; -ERROR HY000: Changing schema from 'test' to 'db_datadict' is not allowed. +ERROR HY000: Changing schema from 'test' to 'db_datadict' is not allowed DROP VIEW test.t1_view; CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table; SELECT table_schema,table_name FROM information_schema.views @@ -189,8 +207,6 @@ WHERE table_name LIKE 't1_%' ORDER BY table_name; table_name t1_view -Warnings: -Warning 1356 View 'db_datadict.t1_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10)) DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT = 'Initial Comment' ENGINE = <engine_type>; @@ -221,7 +237,7 @@ VALUES ('db2', 'v2'); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' UPDATE information_schema.views SET table_schema = 'test' WHERE table_name = 't1'; -ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' +Got one of the listed errors DELETE FROM information_schema.views WHERE table_name = 't1'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' TRUNCATE information_schema.views; |