SHOW TABLES FROM information_schema LIKE 'KEY_COLUMN_USAGE'; Tables_in_information_schema (KEY_COLUMN_USAGE) KEY_COLUMN_USAGE ####################################################################### # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT ####################################################################### DROP VIEW IF EXISTS test.v1; DROP PROCEDURE IF EXISTS test.p1; DROP FUNCTION IF EXISTS test.f1; CREATE VIEW test.v1 AS SELECT * FROM information_schema.KEY_COLUMN_USAGE; CREATE PROCEDURE test.p1() SELECT * FROM information_schema.KEY_COLUMN_USAGE; CREATE FUNCTION test.f1() returns BIGINT BEGIN DECLARE counter BIGINT DEFAULT NULL; SELECT COUNT(*) INTO counter FROM information_schema.KEY_COLUMN_USAGE; RETURN counter; END// # Attention: The printing of the next result sets is disabled. SELECT * FROM information_schema.KEY_COLUMN_USAGE; SELECT * FROM test.v1; CALL test.p1; SELECT test.f1(); DROP VIEW test.v1; DROP PROCEDURE test.p1; DROP FUNCTION test.f1; ######################################################################### # Testcase 3.2.7.1: INFORMATION_SCHEMA.KEY_COLUMN_USAGE layout ######################################################################### DESCRIBE information_schema.KEY_COLUMN_USAGE; Field Type Null Key Default Extra CONSTRAINT_CATALOG varchar(64) YES NULL CONSTRAINT_SCHEMA varchar(64) YES NULL CONSTRAINT_NAME varchar(64) YES NULL TABLE_CATALOG varchar(64) YES NULL TABLE_SCHEMA varchar(64) YES NULL TABLE_NAME varchar(64) YES NULL COLUMN_NAME varchar(64) YES NULL ORDINAL_POSITION int unsigned NO 0 POSITION_IN_UNIQUE_CONSTRAINT int unsigned YES NULL REFERENCED_TABLE_SCHEMA varchar(64) YES NULL REFERENCED_TABLE_NAME varchar(64) YES NULL REFERENCED_COLUMN_NAME varchar(64) YES NULL SHOW CREATE TABLE information_schema.KEY_COLUMN_USAGE; View Create View character_set_client collation_connection KEY_COLUMN_USAGE CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`KEY_COLUMN_USAGE` AS select (`cat`.`name` collate utf8mb3_tolower_ci) AS `CONSTRAINT_CATALOG`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `CONSTRAINT_SCHEMA`,`constraints`.`CONSTRAINT_NAME` AS `CONSTRAINT_NAME`,(`cat`.`name` collate utf8mb3_tolower_ci) AS `TABLE_CATALOG`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8mb3_tolower_ci) AS `TABLE_NAME`,(`col`.`name` collate utf8mb3_tolower_ci) AS `COLUMN_NAME`,`constraints`.`ORDINAL_POSITION` AS `ORDINAL_POSITION`,`constraints`.`POSITION_IN_UNIQUE_CONSTRAINT` AS `POSITION_IN_UNIQUE_CONSTRAINT`,`constraints`.`REFERENCED_TABLE_SCHEMA` AS `REFERENCED_TABLE_SCHEMA`,`constraints`.`REFERENCED_TABLE_NAME` AS `REFERENCED_TABLE_NAME`,`constraints`.`REFERENCED_COLUMN_NAME` AS `REFERENCED_COLUMN_NAME` from (((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join (lateral (select `idx`.`name` AS `CONSTRAINT_NAME`,`icu`.`ordinal_position` AS `ORDINAL_POSITION`,NULL AS `POSITION_IN_UNIQUE_CONSTRAINT`,NULL AS `REFERENCED_TABLE_SCHEMA`,NULL AS `REFERENCED_TABLE_NAME`,NULL AS `REFERENCED_COLUMN_NAME`,`icu`.`column_id` AS `column_id`,((0 <> `idx`.`hidden`) or (0 <> `icu`.`hidden`)) AS `HIDDEN` from (`mysql`.`indexes` `idx` join `mysql`.`index_column_usage` `icu` on((`icu`.`index_id` = `idx`.`id`))) where ((`idx`.`table_id` = `tbl`.`id`) and (`idx`.`type` in ('PRIMARY','UNIQUE'))) union all select (`fk`.`name` collate utf8mb3_tolower_ci) AS `CONSTRAINT_NAME`,`fkcu`.`ordinal_position` AS `ORDINAL_POSITION`,`fkcu`.`ordinal_position` AS `POSITION_IN_UNIQUE_CONSTRAINT`,`fk`.`referenced_table_schema` AS `REFERENCED_TABLE_SCHEMA`,`fk`.`referenced_table_name` AS `REFERENCED_TABLE_NAME`,`fkcu`.`referenced_column_name` AS `REFERENCED_COLUMN_NAME`,`fkcu`.`column_id` AS `column_id`,false AS `HIDDEN` from (`mysql`.`foreign_keys` `fk` join `mysql`.`foreign_key_column_usage` `fkcu` on((`fkcu`.`foreign_key_id` = `fk`.`id`))) where (`fk`.`table_id` = `tbl`.`id`)) `constraints` join `mysql`.`columns` `col` on((`constraints`.`column_id` = `col`.`id`)))) where ((0 <> can_access_column(`sch`.`name`,`tbl`.`name`,`col`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`,((`col`.`hidden` not in ('Visible','User')) or (0 <> `constraints`.`HIDDEN`)),`col`.`options`))) utf8mb3 utf8mb3_general_ci SHOW COLUMNS FROM information_schema.KEY_COLUMN_USAGE; Field Type Null Key Default Extra CONSTRAINT_CATALOG varchar(64) YES NULL CONSTRAINT_SCHEMA varchar(64) YES NULL CONSTRAINT_NAME varchar(64) YES NULL TABLE_CATALOG varchar(64) YES NULL TABLE_SCHEMA varchar(64) YES NULL TABLE_NAME varchar(64) YES NULL COLUMN_NAME varchar(64) YES NULL ORDINAL_POSITION int unsigned NO 0 POSITION_IN_UNIQUE_CONSTRAINT int unsigned YES NULL REFERENCED_TABLE_SCHEMA varchar(64) YES NULL REFERENCED_TABLE_NAME varchar(64) YES NULL REFERENCED_COLUMN_NAME varchar(64) YES NULL SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog, table_schema, table_name, column_name FROM information_schema.key_column_usage WHERE (constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL) AND table_name != 'ndb_binlog_index' ORDER BY constraint_schema, constraint_name, table_schema, table_name, column_name; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME def mtr PRIMARY def mtr asserted_test_suppressions message def mtr PRIMARY def mtr test_ignored_global_suppressions pattern def mysql name def mysql help_category name def mysql name def mysql help_keyword name def mysql name def mysql help_topic name def mysql PRIMARY def mysql columns_priv Column_name def mysql PRIMARY def mysql columns_priv Db def mysql PRIMARY def mysql columns_priv Host def mysql PRIMARY def mysql columns_priv Table_name def mysql PRIMARY def mysql columns_priv User def mysql PRIMARY def mysql component component_id def mysql PRIMARY def mysql db Db def mysql PRIMARY def mysql db Host def mysql PRIMARY def mysql db User def mysql PRIMARY def mysql default_roles DEFAULT_ROLE_HOST def mysql PRIMARY def mysql default_roles DEFAULT_ROLE_USER def mysql PRIMARY def mysql default_roles HOST def mysql PRIMARY def mysql default_roles USER def mysql PRIMARY def mysql engine_cost cost_name def mysql PRIMARY def mysql engine_cost device_type def mysql PRIMARY def mysql engine_cost engine_name def mysql PRIMARY def mysql func name def mysql PRIMARY def mysql global_grants HOST def mysql PRIMARY def mysql global_grants PRIV def mysql PRIMARY def mysql global_grants USER def mysql PRIMARY def mysql gtid_executed interval_start def mysql PRIMARY def mysql gtid_executed source_uuid def mysql PRIMARY def mysql help_category help_category_id def mysql PRIMARY def mysql help_keyword help_keyword_id 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 PRIMARY def mysql innodb_index_stats database_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_index_stats table_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 password_history Host def mysql PRIMARY def mysql password_history Password_timestamp def mysql PRIMARY def mysql password_history User def mysql PRIMARY def mysql plugin name def mysql PRIMARY def mysql procs_priv Db def mysql PRIMARY def mysql procs_priv Host def mysql PRIMARY def mysql procs_priv Routine_name def mysql PRIMARY def mysql procs_priv Routine_type def mysql PRIMARY def mysql procs_priv User def mysql PRIMARY def mysql proxies_priv Host def mysql PRIMARY def mysql proxies_priv Proxied_host def mysql PRIMARY def mysql proxies_priv Proxied_user def mysql PRIMARY def mysql proxies_priv User def mysql PRIMARY def mysql replication_asynchronous_connection_failover Channel_name def mysql PRIMARY def mysql replication_asynchronous_connection_failover Host def mysql PRIMARY def mysql replication_asynchronous_connection_failover Managed_name def mysql PRIMARY def mysql replication_asynchronous_connection_failover Network_namespace def mysql PRIMARY def mysql replication_asynchronous_connection_failover Port def mysql PRIMARY def mysql replication_asynchronous_connection_failover_managed Channel_name def mysql PRIMARY def mysql replication_asynchronous_connection_failover_managed Managed_name def mysql PRIMARY def mysql replication_group_configuration_version name def mysql PRIMARY def mysql replication_group_member_actions event def mysql PRIMARY def mysql replication_group_member_actions name def mysql PRIMARY def mysql role_edges FROM_HOST def mysql PRIMARY def mysql role_edges FROM_USER def mysql PRIMARY def mysql role_edges TO_HOST def mysql PRIMARY def mysql role_edges TO_USER def mysql PRIMARY def mysql server_cost cost_name def mysql PRIMARY def mysql servers Server_name def mysql PRIMARY def mysql slave_master_info Channel_name def mysql PRIMARY def mysql slave_relay_log_info Channel_name def mysql PRIMARY def mysql slave_worker_info Channel_name def mysql PRIMARY def mysql slave_worker_info Id def mysql PRIMARY def mysql tables_priv Db def mysql PRIMARY def mysql tables_priv Host def mysql PRIMARY def mysql tables_priv Table_name def mysql PRIMARY def mysql tables_priv User 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 def performance_schema ACCOUNT def performance_schema accounts HOST def performance_schema ACCOUNT def performance_schema accounts USER def performance_schema ACCOUNT def performance_schema events_errors_summary_by_account_by_error ERROR_NUMBER def performance_schema ACCOUNT def performance_schema events_errors_summary_by_account_by_error HOST def performance_schema ACCOUNT def performance_schema events_errors_summary_by_account_by_error USER def performance_schema ACCOUNT def performance_schema events_stages_summary_by_account_by_event_name EVENT_NAME def performance_schema ACCOUNT def performance_schema events_stages_summary_by_account_by_event_name HOST def performance_schema ACCOUNT def performance_schema events_stages_summary_by_account_by_event_name USER def performance_schema ACCOUNT def performance_schema events_statements_summary_by_account_by_event_name EVENT_NAME def performance_schema ACCOUNT def performance_schema events_statements_summary_by_account_by_event_name HOST def performance_schema ACCOUNT def performance_schema events_statements_summary_by_account_by_event_name USER def performance_schema ACCOUNT def performance_schema events_transactions_summary_by_account_by_event_name EVENT_NAME def performance_schema ACCOUNT def performance_schema events_transactions_summary_by_account_by_event_name HOST def performance_schema ACCOUNT def performance_schema events_transactions_summary_by_account_by_event_name USER def performance_schema ACCOUNT def performance_schema events_waits_summary_by_account_by_event_name EVENT_NAME def performance_schema ACCOUNT def performance_schema events_waits_summary_by_account_by_event_name HOST def performance_schema ACCOUNT def performance_schema events_waits_summary_by_account_by_event_name USER def performance_schema ACCOUNT def performance_schema memory_summary_by_account_by_event_name EVENT_NAME def performance_schema ACCOUNT def performance_schema memory_summary_by_account_by_event_name HOST def performance_schema ACCOUNT def performance_schema memory_summary_by_account_by_event_name USER def performance_schema ACCOUNT def performance_schema status_by_account HOST def performance_schema ACCOUNT def performance_schema status_by_account USER def performance_schema ACCOUNT def performance_schema status_by_account VARIABLE_NAME def performance_schema ERROR_NUMBER def performance_schema events_errors_summary_global_by_error ERROR_NUMBER def performance_schema HOST def performance_schema events_errors_summary_by_host_by_error ERROR_NUMBER def performance_schema HOST def performance_schema events_errors_summary_by_host_by_error HOST def performance_schema HOST def performance_schema events_stages_summary_by_host_by_event_name EVENT_NAME def performance_schema HOST def performance_schema events_stages_summary_by_host_by_event_name HOST def performance_schema HOST def performance_schema events_statements_summary_by_host_by_event_name EVENT_NAME def performance_schema HOST def performance_schema events_statements_summary_by_host_by_event_name HOST def performance_schema HOST def performance_schema events_transactions_summary_by_host_by_event_name EVENT_NAME def performance_schema HOST def performance_schema events_transactions_summary_by_host_by_event_name HOST def performance_schema HOST def performance_schema events_waits_summary_by_host_by_event_name EVENT_NAME def performance_schema HOST def performance_schema events_waits_summary_by_host_by_event_name HOST def performance_schema HOST def performance_schema hosts HOST def performance_schema HOST def performance_schema memory_summary_by_host_by_event_name EVENT_NAME def performance_schema HOST def performance_schema memory_summary_by_host_by_event_name HOST def performance_schema HOST def performance_schema status_by_host HOST def performance_schema HOST def performance_schema status_by_host VARIABLE_NAME def performance_schema OBJECT def performance_schema objects_summary_global_by_type OBJECT_NAME def performance_schema OBJECT def performance_schema objects_summary_global_by_type OBJECT_SCHEMA def performance_schema OBJECT def performance_schema objects_summary_global_by_type OBJECT_TYPE def performance_schema OBJECT def performance_schema setup_objects OBJECT_NAME def performance_schema OBJECT def performance_schema setup_objects OBJECT_SCHEMA def performance_schema OBJECT def performance_schema setup_objects OBJECT_TYPE def performance_schema OBJECT def performance_schema table_io_waits_summary_by_index_usage INDEX_NAME def performance_schema OBJECT def performance_schema table_io_waits_summary_by_index_usage OBJECT_NAME def performance_schema OBJECT def performance_schema table_io_waits_summary_by_index_usage OBJECT_SCHEMA def performance_schema OBJECT def performance_schema table_io_waits_summary_by_index_usage OBJECT_TYPE def performance_schema OBJECT def performance_schema table_io_waits_summary_by_table OBJECT_NAME def performance_schema OBJECT def performance_schema table_io_waits_summary_by_table OBJECT_SCHEMA def performance_schema OBJECT def performance_schema table_io_waits_summary_by_table OBJECT_TYPE def performance_schema OBJECT def performance_schema table_lock_waits_summary_by_table OBJECT_NAME def performance_schema OBJECT def performance_schema table_lock_waits_summary_by_table OBJECT_SCHEMA def performance_schema OBJECT def performance_schema table_lock_waits_summary_by_table OBJECT_TYPE def performance_schema OWNER_THREAD_ID def performance_schema prepared_statements_instances OWNER_EVENT_ID def performance_schema OWNER_THREAD_ID def performance_schema prepared_statements_instances OWNER_THREAD_ID def performance_schema PRIMARY def performance_schema cond_instances OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema data_lock_waits BLOCKING_ENGINE_LOCK_ID def performance_schema PRIMARY def performance_schema data_lock_waits ENGINE def performance_schema PRIMARY def performance_schema data_lock_waits REQUESTING_ENGINE_LOCK_ID def performance_schema PRIMARY def performance_schema data_locks ENGINE def performance_schema PRIMARY def performance_schema data_locks ENGINE_LOCK_ID def performance_schema PRIMARY def performance_schema error_log LOGGED def performance_schema PRIMARY def performance_schema events_stages_current EVENT_ID def performance_schema PRIMARY def performance_schema events_stages_current THREAD_ID def performance_schema PRIMARY def performance_schema events_stages_history EVENT_ID def performance_schema PRIMARY def performance_schema events_stages_history THREAD_ID def performance_schema PRIMARY def performance_schema events_stages_summary_by_thread_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema events_stages_summary_by_thread_by_event_name THREAD_ID def performance_schema PRIMARY def performance_schema events_stages_summary_global_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema events_statements_current EVENT_ID def performance_schema PRIMARY def performance_schema events_statements_current THREAD_ID def performance_schema PRIMARY def performance_schema events_statements_histogram_global BUCKET_NUMBER def performance_schema PRIMARY def performance_schema events_statements_history EVENT_ID def performance_schema PRIMARY def performance_schema events_statements_history THREAD_ID def performance_schema PRIMARY def performance_schema events_statements_summary_by_program OBJECT_NAME def performance_schema PRIMARY def performance_schema events_statements_summary_by_program OBJECT_SCHEMA def performance_schema PRIMARY def performance_schema events_statements_summary_by_program OBJECT_TYPE def performance_schema PRIMARY def performance_schema events_statements_summary_by_thread_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema events_statements_summary_by_thread_by_event_name THREAD_ID def performance_schema PRIMARY def performance_schema events_statements_summary_global_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema events_transactions_current EVENT_ID def performance_schema PRIMARY def performance_schema events_transactions_current THREAD_ID def performance_schema PRIMARY def performance_schema events_transactions_history EVENT_ID def performance_schema PRIMARY def performance_schema events_transactions_history THREAD_ID def performance_schema PRIMARY def performance_schema events_transactions_summary_by_thread_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema events_transactions_summary_by_thread_by_event_name THREAD_ID def performance_schema PRIMARY def performance_schema events_transactions_summary_global_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema events_waits_current EVENT_ID def performance_schema PRIMARY def performance_schema events_waits_current THREAD_ID def performance_schema PRIMARY def performance_schema events_waits_history EVENT_ID def performance_schema PRIMARY def performance_schema events_waits_history THREAD_ID def performance_schema PRIMARY def performance_schema events_waits_summary_by_instance OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema events_waits_summary_by_thread_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema events_waits_summary_by_thread_by_event_name THREAD_ID def performance_schema PRIMARY def performance_schema events_waits_summary_global_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema file_instances FILE_NAME def performance_schema PRIMARY def performance_schema file_summary_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema file_summary_by_instance OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema global_status VARIABLE_NAME def performance_schema PRIMARY def performance_schema global_variables VARIABLE_NAME def performance_schema PRIMARY def performance_schema host_cache IP def performance_schema PRIMARY def performance_schema memory_summary_by_thread_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema memory_summary_by_thread_by_event_name THREAD_ID def performance_schema PRIMARY def performance_schema memory_summary_global_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema metadata_locks OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema mutex_instances OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema persisted_variables VARIABLE_NAME def performance_schema PRIMARY def performance_schema prepared_statements_instances OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema processlist ID def performance_schema PRIMARY def performance_schema replication_applier_configuration CHANNEL_NAME def performance_schema PRIMARY def performance_schema replication_applier_status CHANNEL_NAME def performance_schema PRIMARY def performance_schema replication_applier_status_by_coordinator CHANNEL_NAME def performance_schema PRIMARY def performance_schema replication_applier_status_by_worker CHANNEL_NAME def performance_schema PRIMARY def performance_schema replication_applier_status_by_worker WORKER_ID def performance_schema PRIMARY def performance_schema replication_connection_configuration CHANNEL_NAME def performance_schema PRIMARY def performance_schema replication_connection_status CHANNEL_NAME def performance_schema PRIMARY def performance_schema rwlock_instances OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema session_account_connect_attrs ATTR_NAME def performance_schema PRIMARY def performance_schema session_account_connect_attrs PROCESSLIST_ID def performance_schema PRIMARY def performance_schema session_connect_attrs ATTR_NAME def performance_schema PRIMARY def performance_schema session_connect_attrs PROCESSLIST_ID def performance_schema PRIMARY def performance_schema session_status VARIABLE_NAME def performance_schema PRIMARY def performance_schema session_variables VARIABLE_NAME def performance_schema PRIMARY def performance_schema setup_actors HOST def performance_schema PRIMARY def performance_schema setup_actors ROLE def performance_schema PRIMARY def performance_schema setup_actors USER def performance_schema PRIMARY def performance_schema setup_consumers NAME def performance_schema PRIMARY def performance_schema setup_instruments NAME def performance_schema PRIMARY def performance_schema setup_threads NAME def performance_schema PRIMARY def performance_schema socket_instances OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema socket_summary_by_event_name EVENT_NAME def performance_schema PRIMARY def performance_schema socket_summary_by_instance OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema status_by_thread THREAD_ID def performance_schema PRIMARY def performance_schema status_by_thread VARIABLE_NAME def performance_schema PRIMARY def performance_schema table_handles OBJECT_INSTANCE_BEGIN def performance_schema PRIMARY def performance_schema threads THREAD_ID def performance_schema PRIMARY def performance_schema user_defined_functions UDF_NAME def performance_schema PRIMARY def performance_schema user_variables_by_thread THREAD_ID def performance_schema PRIMARY def performance_schema user_variables_by_thread VARIABLE_NAME def performance_schema PRIMARY def performance_schema variables_by_thread THREAD_ID def performance_schema PRIMARY def performance_schema variables_by_thread VARIABLE_NAME def performance_schema SCHEMA_NAME def performance_schema events_statements_histogram_by_digest BUCKET_NUMBER def performance_schema SCHEMA_NAME def performance_schema events_statements_histogram_by_digest DIGEST def performance_schema SCHEMA_NAME def performance_schema events_statements_histogram_by_digest SCHEMA_NAME def performance_schema SCHEMA_NAME def performance_schema events_statements_summary_by_digest DIGEST def performance_schema SCHEMA_NAME def performance_schema events_statements_summary_by_digest SCHEMA_NAME def performance_schema THREAD_ID def performance_schema events_errors_summary_by_thread_by_error ERROR_NUMBER def performance_schema THREAD_ID def performance_schema events_errors_summary_by_thread_by_error THREAD_ID def performance_schema USER def performance_schema events_errors_summary_by_user_by_error ERROR_NUMBER def performance_schema USER def performance_schema events_errors_summary_by_user_by_error USER def performance_schema USER def performance_schema events_stages_summary_by_user_by_event_name EVENT_NAME def performance_schema USER def performance_schema events_stages_summary_by_user_by_event_name USER def performance_schema USER def performance_schema events_statements_summary_by_user_by_event_name EVENT_NAME def performance_schema USER def performance_schema events_statements_summary_by_user_by_event_name USER def performance_schema USER def performance_schema events_transactions_summary_by_user_by_event_name EVENT_NAME def performance_schema USER def performance_schema events_transactions_summary_by_user_by_event_name USER def performance_schema USER def performance_schema events_waits_summary_by_user_by_event_name EVENT_NAME def performance_schema USER def performance_schema events_waits_summary_by_user_by_event_name USER def performance_schema USER def performance_schema memory_summary_by_user_by_event_name EVENT_NAME def performance_schema USER def performance_schema memory_summary_by_user_by_event_name USER def performance_schema USER def performance_schema status_by_user USER def performance_schema USER def performance_schema status_by_user VARIABLE_NAME def performance_schema USER def performance_schema users USER def sys PRIMARY def sys sys_config variable ######################################################################################## # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.KEY_COLUMN_USAGE accessible information ######################################################################################## DROP DATABASE IF EXISTS db_datadict; CREATE DATABASE db_datadict; DROP USER 'testuser1'@'localhost'; CREATE USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; CREATE USER 'testuser2'@'localhost'; USE db_datadict; CREATE TABLE t1_1 (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2)) ENGINE = ; GRANT SELECT ON t1_1 to 'testuser1'@'localhost'; CREATE TABLE t1_2 (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2)) ENGINE = ; GRANT SELECT ON t1_2 to 'testuser2'@'localhost'; 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 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 # Establish connection testuser1 (user=testuser1) 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 def db_datadict PRIMARY def db_datadict t1_1 f1 1 NULL NULL NULL NULL # Establish connection testuser2 (user=testuser2) 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 def db_datadict PRIMARY def db_datadict t1_2 f1 1 NULL NULL NULL NULL # Switch to connection default and close connections testuser1, testuser2 DROP USER 'testuser1'@'localhost'; DROP USER 'testuser2'@'localhost'; DROP TABLE t1_1; DROP TABLE t1_2; DROP DATABASE IF EXISTS db_datadict; ######################################################################################## # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.KEY_COLUMN_USAGE modifications ######################################################################################## DROP DATABASE IF EXISTS db_datadict; DROP TABLE IF EXISTS test.t1_my_table; CREATE DATABASE db_datadict; SELECT table_name FROM information_schema.key_column_usage 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)) DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ENGINE = ; SELECT * FROM information_schema.key_column_usage WHERE table_name = 't1_my_table' ORDER BY column_name; CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME PRIMARY TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1_my_table COLUMN_NAME f1 ORDINAL_POSITION # POSITION_IN_UNIQUE_CONSTRAINT NULL REFERENCED_TABLE_SCHEMA NULL REFERENCED_TABLE_NAME NULL REFERENCED_COLUMN_NAME NULL CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME PRIMARY TABLE_CATALOG def TABLE_SCHEMA test TABLE_NAME t1_my_table COLUMN_NAME f2 ORDINAL_POSITION # POSITION_IN_UNIQUE_CONSTRAINT NULL REFERENCED_TABLE_SCHEMA NULL REFERENCED_TABLE_NAME NULL REFERENCED_COLUMN_NAME NULL SELECT DISTINCT table_name FROM information_schema.key_column_usage WHERE table_name LIKE 't1_my_table%'; TABLE_NAME t1_my_table RENAME TABLE test.t1_my_table TO test.t1_my_tablex; SELECT DISTINCT table_name FROM information_schema.key_column_usage WHERE table_name LIKE 't1_my_table%'; TABLE_NAME t1_my_tablex SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex'; TABLE_SCHEMA TABLE_NAME test t1_my_tablex RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex; SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex'; TABLE_SCHEMA TABLE_NAME db_datadict t1_my_tablex SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex' ORDER BY table_name, column_name; TABLE_NAME COLUMN_NAME t1_my_tablex f1 t1_my_tablex f2 ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12); SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex' ORDER BY table_name, column_name; TABLE_NAME COLUMN_NAME t1_my_tablex f2 t1_my_tablex 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 PRIMARY db_datadict t1_my_tablex first_col 1 db_datadict PRIMARY db_datadict t1_my_tablex f2 2 CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2); 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 PRIMARY db_datadict t1_my_tablex first_col 1 db_datadict PRIMARY db_datadict t1_my_tablex f2 2 DROP INDEX f2 ON db_datadict.t1_my_tablex; 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 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 ADD UNIQUE (f2); 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 f2 db_datadict t1_my_tablex f2 1 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1 db_datadict PRIMARY db_datadict t1_my_tablex f2 2 DROP INDEX f2 ON db_datadict.t1_my_tablex; 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 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 ADD UNIQUE my_idx (f2); 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 f2 1 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1 db_datadict PRIMARY db_datadict t1_my_tablex f2 2 DROP INDEX my_idx ON db_datadict.t1_my_tablex; 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 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 ADD UNIQUE my_idx (f4,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 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' ORDER BY table_name, column_name; TABLE_NAME COLUMN_NAME t1_my_tablex f2 t1_my_tablex f4 DROP TABLE db_datadict.t1_my_tablex; SELECT table_name, column_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex'; TABLE_NAME COLUMN_NAME SELECT table_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex'; TABLE_NAME CREATE TABLE db_datadict.t1_my_tablex ENGINE = AS SELECT 1 AS f1; SELECT table_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex'; TABLE_NAME ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1); SELECT table_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex'; TABLE_NAME t1_my_tablex SELECT table_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex'; TABLE_NAME t1_my_tablex DROP DATABASE db_datadict; SELECT table_name FROM information_schema.key_column_usage WHERE table_name = 't1_my_tablex'; TABLE_NAME ######################################################################## # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and # DDL on INFORMATION_SCHEMA table are not supported ######################################################################## DROP DATABASE IF EXISTS db_datadict; DROP TABLE IF EXISTS db_datadict.t1; CREATE DATABASE db_datadict; CREATE TABLE db_datadict.t1 (f1 BIGINT) ENGINE = ; INSERT INTO information_schema.key_column_usage (constraint_schema, constraint_name, table_name) VALUES ( 'mysql', 'primary', 'db'); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' INSERT INTO information_schema.key_column_usage 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' 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; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' CREATE INDEX i3 ON information_schema.key_column_usage(table_name); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE information_schema.key_column_usage ADD f1 INT; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP TABLE information_schema.key_column_usage; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE information_schema.key_column_usage RENAME db_datadict.key_column_usage; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' ALTER TABLE information_schema.key_column_usage RENAME information_schema.xkey_column_usage; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' DROP TABLE db_datadict.t1; DROP DATABASE db_datadict;