The freshly released MySQL 8.0 includes a data dictionary, which makes MySQL much more reliable. Thanks to this features, we don't have any '.frm' files, and querying the information_schema is 30x to 100x faster than previous versions.
One drawback of the implementation is that the data dictionary tables are hidden by design.
While the reason is fully understandable (they don't want to commit on an interface that may change in the future) many curious users are disappointed, because openness is the basis of good understanding and feedback.
The problem to access the dictionary tables can be split in three parts:
- Finding the list of tables;
- Finding the description of the tables;
- Getting the contents of each table.
The first part is easily solved. We know that the data dictionary tables are accessed from some information_schema views (the views are defined during the initialization, at which point the DD tables are readable.)
For example:
show create view information_schema.tables\G
*************************** 1. row ***************************
View: TABLES
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
SQL SECURITY DEFINER VIEW `TABLES` AS select `cat`.`name` AS
`TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS
`TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE
TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`,if((`tbl`.`type` = 'VIEW'),NULL,10)
AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,`stat`.`table_rows` AS
`TABLE_ROWS`,`stat`.`avg_row_length` AS `AVG_ROW_LENGTH`,`stat`.`data_length`
AS `DATA_LENGTH`,`stat`.`max_data_length` AS
`MAX_DATA_LENGTH`,`stat`.`index_length` AS `INDEX_LENGTH`,`stat`.`data_free`
AS `DATA_FREE`,`stat`.`auto_increment` AS `AUTO_INCREMENT`,`tbl`.`created` AS
`CREATE_TIME`,`stat`.`update_time` AS `UPDATE_TIME`,`stat`.`check_time` AS
`CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,`stat`.`checksum` AS
`CHECKSUM`,if((`tbl`.`type` =
'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL')
= 'NOT_PART_TBL'),0,1))) AS
`CREATE_OPTIONS`,internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`)
AS `TABLE_COMMENT` from ((((`mysql`.`tables` `tbl` join `mysql`.`schemata`
`sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat`
on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col`
on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`table_stats`
`stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` =
`stat`.`schema_name`)))) where (can_access_table(`sch`.`name`,`tbl`.`name`)
and (not(`tbl`.`hidden`)))
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)
Here we see many tables (such as mysql.schemata or mysql.collations) that don't show up when we run SHOW TABLES in the mysql database.
We can use a script like this to get the list of all tables:
MYSQL=~/sandboxes/msb_full_8_0_0/use
TABLES=$($MYSQL information_schema -BN -e 'show tables')
function show_tables
{
for T in $TABLES
do
is_view=$($MYSQL information_schema -BN -e "show create table $T\G" | grep ALGORITHM)
if [ -n "$is_view" ]
then
$MYSQL information_schema -e "show create table $T\G" \
| perl -lne 'print $1 while /mysql.\..(\w+)/g'
fi
done
}
show_tables | sort | uniq
This script searches all information_schema tables, skips the ones that are not views, and then searches in the view definition every table from the mysql database. The result is this:
catalogs
character_sets
collations
columns
foreign_key_column_usage
foreign_keys
index_column_usage
indexes
index_stats
schemata
tables
table_stats
Good. Now we have the list of tables that we can't see. The second operation is getting the description.
So, I looked at the source code, and I found out where the prohibition originated. From there, I saw that the table is accessible when the variable skip_dd_table_access_check is set. Looking at the variables inside the server, I did not find any skip_dd_table_access_check, as I was expecting, since it would not make sense to provide this information in the open after going through the pains of making all DD tables unreachable.
I searched the code for the string skip_dd_table_access_check and I found out how it is used in the test suite. The key is using the debug build of the MySQL server.
Using MySQL Sandbox, with a sandbox made from the full tarball of MySQL 8.0, I run:
~/sandboxes/msb_full_8_0_0/restart --mysqld=mysqld-debug
Now I have loaded the debug-enabled server. Let's try:
$ ~/sandboxes/msb_full_8_0_0/use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 91
Server version: 8.0.0-dmr-debug MySQL Community Server - Debug (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [localhost] {msandbox} (mysql) > show create table schemata\G
ERROR 3554 (HY000): Access to system table 'mysql.schemata' is rejected.
Having the debug build is not enough. We need to use the magic spell.
mysql [localhost] {msandbox} (mysql) > SET SESSION debug='+d,skip_dd_table_access_check';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (mysql) > show create table schemata\G
*************************** 1. row ***************************
Table: schemata
Create Table: CREATE TABLE `schemata` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`catalog_id` bigint(20) unsigned NOT NULL,
`name` varchar(64) COLLATE utf8_bin NOT NULL,
`default_collation_id` bigint(20) unsigned NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_altered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `catalog_id` (`catalog_id`,`name`),
KEY `default_collation_id` (`default_collation_id`),
CONSTRAINT `schemata_ibfk_1` FOREIGN KEY (`catalog_id`) REFERENCES `catalogs` (`id`),
CONSTRAINT `schemata_ibfk_2` FOREIGN KEY (`default_collation_id`) REFERENCES `collations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
Yay! The table is now visible! We can modify the above script as follows:
mysql_tables=$(show_tables | sort | uniq)
for T in $mysql_tables
do
echo "-- $T "
$MYSQL -e "SET SESSION debug= '+d,skip_dd_table_access_check'; show create table mysql.$T\G"
done
And we get the description of every table in the data dictionary. Here it goes:
-- catalogs
*************************** 1. row ***************************
Table: catalogs
Create Table: CREATE TABLE `catalogs` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) COLLATE utf8_bin NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_altered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- character_sets
*************************** 1. row ***************************
Table: character_sets
Create Table: CREATE TABLE `character_sets` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) CHARACTER SET utf8 NOT NULL,
`default_collation_id` bigint(20) unsigned NOT NULL,
`comment` varchar(2048) COLLATE utf8_bin NOT NULL,
`mb_max_length` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `default_collation_id` (`default_collation_id`),
CONSTRAINT `character_sets_ibfk_1` FOREIGN KEY (`default_collation_id`) REFERENCES `collations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=249 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- collations
*************************** 1. row ***************************
Table: collations
Create Table: CREATE TABLE `collations` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) CHARACTER SET utf8 NOT NULL,
`character_set_id` bigint(20) unsigned NOT NULL,
`is_compiled` tinyint(1) NOT NULL,
`sort_length` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `character_set_id` (`character_set_id`),
CONSTRAINT `collations_ibfk_1` FOREIGN KEY (`character_set_id`) REFERENCES `character_sets` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=278 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- columns
*************************** 1. row ***************************
Table: columns
Create Table: CREATE TABLE `columns` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`table_id` bigint(20) unsigned NOT NULL,
`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci NOT NULL,
`ordinal_position` int(10) unsigned NOT NULL,
`type` enum(/* removed */) COLLATE utf8_bin NOT NULL,
`is_nullable` tinyint(1) NOT NULL,
`is_zerofill` tinyint(1) DEFAULT NULL,
`is_unsigned` tinyint(1) DEFAULT NULL,
`char_length` int(10) unsigned DEFAULT NULL,
`numeric_precision` int(10) unsigned DEFAULT NULL,
`numeric_scale` int(10) unsigned DEFAULT NULL,
`datetime_precision` int(10) unsigned DEFAULT NULL,
`collation_id` bigint(20) unsigned DEFAULT NULL,
`has_no_default` tinyint(1) DEFAULT NULL,
`default_value` blob,
`default_value_utf8` text COLLATE utf8_bin,
`default_option` blob,
`update_option` varchar(32) COLLATE utf8_bin DEFAULT NULL,
`is_auto_increment` tinyint(1) DEFAULT NULL,
`is_virtual` tinyint(1) DEFAULT NULL,
`generation_expression` longblob,
`generation_expression_utf8` longtext COLLATE utf8_bin,
`comment` varchar(2048) COLLATE utf8_bin NOT NULL,
`hidden` tinyint(1) NOT NULL,
`options` mediumtext COLLATE utf8_bin,
`se_private_data` mediumtext COLLATE utf8_bin,
`column_key` enum('','PRI','UNI','MUL') COLLATE utf8_bin NOT NULL,
`column_type_utf8` mediumtext COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `table_id` (`table_id`,`name`),
UNIQUE KEY `table_id_2` (`table_id`,`ordinal_position`),
KEY `collation_id` (`collation_id`),
CONSTRAINT `columns_ibfk_1` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`),
CONSTRAINT `columns_ibfk_2` FOREIGN KEY (`collation_id`) REFERENCES `collations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3450 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- foreign_key_column_usage
*************************** 1. row ***************************
Table: foreign_key_column_usage
Create Table: CREATE TABLE `foreign_key_column_usage` (
`foreign_key_id` bigint(20) unsigned NOT NULL,
`ordinal_position` int(10) unsigned NOT NULL,
`column_id` bigint(20) unsigned NOT NULL,
`referenced_column_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci NOT NULL,
PRIMARY KEY (`foreign_key_id`,`ordinal_position`),
UNIQUE KEY `foreign_key_id` (`foreign_key_id`,`column_id`,`referenced_column_name`),
KEY `column_id` (`column_id`),
CONSTRAINT `foreign_key_column_usage_ibfk_1` FOREIGN KEY (`foreign_key_id`) REFERENCES `foreign_keys` (`id`),
CONSTRAINT `foreign_key_column_usage_ibfk_2` FOREIGN KEY (`column_id`) REFERENCES `columns` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- foreign_keys
*************************** 1. row ***************************
Table: foreign_keys
Create Table: CREATE TABLE `foreign_keys` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`schema_id` bigint(20) unsigned NOT NULL,
`table_id` bigint(20) unsigned NOT NULL,
`name` varchar(64) CHARACTER SET utf8 NOT NULL,
`unique_constraint_id` bigint(20) unsigned NOT NULL,
`match_option` enum('NONE','PARTIAL','FULL') COLLATE utf8_bin NOT NULL,
`update_rule` enum('NO ACTION','RESTRICT','CASCADE','SET NULL','SET DEFAULT') COLLATE utf8_bin NOT NULL,
`delete_rule` enum('NO ACTION','RESTRICT','CASCADE','SET NULL','SET DEFAULT') COLLATE utf8_bin NOT NULL,
`referenced_table_catalog` varchar(64) COLLATE utf8_bin NOT NULL,
`referenced_table_schema` varchar(64) COLLATE utf8_bin NOT NULL,
`referenced_table_name` varchar(64) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `schema_id` (`schema_id`,`name`),
UNIQUE KEY `table_id` (`table_id`,`name`),
KEY `unique_constraint_id` (`unique_constraint_id`),
CONSTRAINT `foreign_keys_ibfk_1` FOREIGN KEY (`schema_id`) REFERENCES `schemata` (`id`),
CONSTRAINT `foreign_keys_ibfk_2` FOREIGN KEY (`unique_constraint_id`) REFERENCES `indexes` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- index_column_usage
*************************** 1. row ***************************
Table: index_column_usage
Create Table: CREATE TABLE `index_column_usage` (
`index_id` bigint(20) unsigned NOT NULL,
`ordinal_position` int(10) unsigned NOT NULL,
`column_id` bigint(20) unsigned NOT NULL,
`length` int(10) unsigned DEFAULT NULL,
`order` enum('UNDEF','ASC','DESC') COLLATE utf8_bin NOT NULL,
`hidden` tinyint(1) NOT NULL,
UNIQUE KEY `index_id` (`index_id`,`ordinal_position`),
UNIQUE KEY `index_id_2` (`index_id`,`column_id`,`hidden`),
KEY `f2` (`column_id`),
CONSTRAINT `index_column_usage_ibfk_1` FOREIGN KEY (`index_id`) REFERENCES `indexes` (`id`),
CONSTRAINT `index_column_usage_ibfk_2` FOREIGN KEY (`column_id`) REFERENCES `columns` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- indexes
*************************** 1. row ***************************
Table: indexes
Create Table: CREATE TABLE `indexes` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`table_id` bigint(20) unsigned NOT NULL,
`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci NOT NULL,
`type` enum('PRIMARY','UNIQUE','MULTIPLE','FULLTEXT','SPATIAL') COLLATE utf8_bin NOT NULL,
`algorithm` enum('SE_SPECIFIC','BTREE','RTREE','HASH','FULLTEXT') COLLATE utf8_bin NOT NULL,
`is_algorithm_explicit` tinyint(1) NOT NULL,
`is_visible` tinyint(1) NOT NULL,
`is_generated` tinyint(1) NOT NULL,
`hidden` tinyint(1) NOT NULL,
`ordinal_position` int(10) unsigned NOT NULL,
`comment` varchar(2048) COLLATE utf8_bin NOT NULL,
`options` mediumtext COLLATE utf8_bin,
`se_private_data` mediumtext COLLATE utf8_bin,
`tablespace_id` bigint(20) unsigned DEFAULT NULL,
`engine` varchar(64) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `table_id` (`table_id`,`name`),
KEY `tablespace_id` (`tablespace_id`),
CONSTRAINT `indexes_ibfk_1` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`),
CONSTRAINT `indexes_ibfk_2` FOREIGN KEY (`tablespace_id`) REFERENCES `tablespaces` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=235 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- index_stats
*************************** 1. row ***************************
Table: index_stats
Create Table: CREATE TABLE `index_stats` (
`schema_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`column_name` varchar(64) COLLATE utf8_bin NOT NULL,
`cardinality` bigint(20) unsigned DEFAULT NULL,
UNIQUE KEY `schema_name` (`schema_name`,`table_name`,`index_name`,`column_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- schemata
*************************** 1. row ***************************
Table: schemata
Create Table: CREATE TABLE `schemata` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`catalog_id` bigint(20) unsigned NOT NULL,
`name` varchar(64) COLLATE utf8_bin NOT NULL,
`default_collation_id` bigint(20) unsigned NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_altered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `catalog_id` (`catalog_id`,`name`),
KEY `default_collation_id` (`default_collation_id`),
CONSTRAINT `schemata_ibfk_1` FOREIGN KEY (`catalog_id`) REFERENCES `catalogs` (`id`),
CONSTRAINT `schemata_ibfk_2` FOREIGN KEY (`default_collation_id`) REFERENCES `collations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- tables
*************************** 1. row ***************************
Table: tables
Create Table: CREATE TABLE `tables` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`schema_id` bigint(20) unsigned NOT NULL,
`name` varchar(64) COLLATE utf8_bin NOT NULL,
`type` enum('BASE TABLE','VIEW','SYSTEM VIEW') COLLATE utf8_bin NOT NULL,
`engine` varchar(64) CHARACTER SET utf8 NOT NULL,
`mysql_version_id` int(10) unsigned NOT NULL,
`row_format` enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') COLLATE utf8_bin DEFAULT NULL,
`collation_id` bigint(20) unsigned DEFAULT NULL,
`comment` varchar(2048) COLLATE utf8_bin NOT NULL,
`hidden` tinyint(1) NOT NULL,
`options` mediumblob,
`se_private_data` mediumtext COLLATE utf8_bin,
`se_private_id` bigint(20) unsigned DEFAULT NULL,
`tablespace_id` bigint(20) unsigned DEFAULT NULL,
`partition_type` enum(/*removed*/) COLLATE utf8_bin DEFAULT NULL,
`partition_expression` varchar(2048) COLLATE utf8_bin DEFAULT NULL,
`default_partitioning` enum('NO','YES','NUMBER') COLLATE utf8_bin DEFAULT NULL,
`subpartition_type` enum('HASH','KEY_51','KEY_55','LINEAR_HASH','LINEAR_KEY_51','LINEAR_KEY_55') COLLATE utf8_bin DEFAULT NULL,
`subpartition_expression` varchar(2048) COLLATE utf8_bin DEFAULT NULL,
`default_subpartitioning` enum('NO','YES','NUMBER') COLLATE utf8_bin DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_altered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`view_definition` longblob,
`view_definition_utf8` longtext COLLATE utf8_bin,
`view_check_option` enum('NONE','LOCAL','CASCADED') COLLATE utf8_bin DEFAULT NULL,
`view_is_updatable` enum('NO','YES') COLLATE utf8_bin DEFAULT NULL,
`view_algorithm` enum('UNDEFINED','TEMPTABLE','MERGE') COLLATE utf8_bin DEFAULT NULL,
`view_security_type` enum('DEFAULT','INVOKER','DEFINER') COLLATE utf8_bin DEFAULT NULL,
`view_definer` varchar(93) COLLATE utf8_bin DEFAULT NULL,
`view_client_collation_id` bigint(20) unsigned DEFAULT NULL,
`view_connection_collation_id` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `schema_id` (`schema_id`,`name`),
UNIQUE KEY `engine` (`engine`,`se_private_id`),
KEY `engine_2` (`engine`),
KEY `collation_id` (`collation_id`),
KEY `tablespace_id` (`tablespace_id`),
CONSTRAINT `tables_ibfk_1` FOREIGN KEY (`schema_id`) REFERENCES `schemata` (`id`),
CONSTRAINT `tables_ibfk_2` FOREIGN KEY (`collation_id`) REFERENCES `collations` (`id`),
CONSTRAINT `tables_ibfk_3` FOREIGN KEY (`tablespace_id`) REFERENCES `tablespaces` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=322 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
-- table_stats
*************************** 1. row ***************************
Table: table_stats
Create Table: CREATE TABLE `table_stats` (
`schema_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_rows` bigint(20) unsigned DEFAULT NULL,
`avg_row_length` bigint(20) unsigned DEFAULT NULL,
`data_length` bigint(20) unsigned DEFAULT NULL,
`max_data_length` bigint(20) unsigned DEFAULT NULL,
`index_length` bigint(20) unsigned DEFAULT NULL,
`data_free` bigint(20) unsigned DEFAULT NULL,
`auto_increment` bigint(20) unsigned DEFAULT NULL,
`checksum` bigint(20) unsigned DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL,
`check_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`schema_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
In addition to the tables referred in views, there are three that are mentioned in the documentation but not implemented as a view in information_schema: triggers, events, and routines.
show create table triggers\G
*************************** 1. row ***************************
Table: triggers
Create Table: CREATE TABLE `triggers` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`schema_id` bigint(20) unsigned NOT NULL,
`name` varchar(64) CHARACTER SET utf8 NOT NULL,
`event_type` enum('INSERT','UPDATE','DELETE') COLLATE utf8_bin NOT NULL,
`table_id` bigint(20) unsigned NOT NULL,
`action_timing` enum('BEFORE','AFTER') COLLATE utf8_bin NOT NULL,
`action_order` int(10) unsigned NOT NULL,
`action_statement` longblob NOT NULL,
`action_statement_utf8` longtext COLLATE utf8_bin NOT NULL,
`created` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2),
`last_altered` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2),
`sql_mode` set(/*removed*/) COLLATE utf8_bin NOT NULL,
`definer` varchar(93) COLLATE utf8_bin NOT NULL,
`client_collation_id` bigint(20) unsigned NOT NULL,
`connection_collation_id` bigint(20) unsigned NOT NULL,
`schema_collation_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `schema_id` (`schema_id`,`name`),
UNIQUE KEY `table_id` (`table_id`,`event_type`,`action_timing`,`action_order`),
KEY `client_collation_id` (`client_collation_id`),
KEY `connection_collation_id` (`connection_collation_id`),
KEY `schema_collation_id` (`schema_collation_id`),
CONSTRAINT `triggers_ibfk_1` FOREIGN KEY (`schema_id`) REFERENCES `schemata` (`id`),
CONSTRAINT `triggers_ibfk_2` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`),
CONSTRAINT `triggers_ibfk_3` FOREIGN KEY (`client_collation_id`) REFERENCES `collations` (`id`),
CONSTRAINT `triggers_ibfk_4` FOREIGN KEY (`connection_collation_id`) REFERENCES `collations` (`id`),
CONSTRAINT `triggers_ibfk_5` FOREIGN KEY (`schema_collation_id`) REFERENCES `collations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
show create table events\G
*************************** 1. row ***************************
Table: events
Create Table: CREATE TABLE `events` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`schema_id` bigint(20) unsigned NOT NULL,
`name` varchar(64) CHARACTER SET utf8 NOT NULL,
`definer` varchar(93) COLLATE utf8_bin NOT NULL,
`time_zone` varchar(64) COLLATE utf8_bin NOT NULL,
`definition` longblob NOT NULL,
`definition_utf8` longtext COLLATE utf8_bin NOT NULL,
`execute_at` datetime DEFAULT NULL,
`interval_value` int(11) DEFAULT NULL,
`interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') COLLATE utf8_bin DEFAULT NULL,
`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') COLLATE utf8_bin NOT NULL,
`starts` datetime DEFAULT NULL,
`ends` datetime DEFAULT NULL,
`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') COLLATE utf8_bin NOT NULL,
`on_completion` enum('DROP','PRESERVE') COLLATE utf8_bin NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_altered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_executed` datetime DEFAULT NULL,
`comment` varchar(2048) COLLATE utf8_bin NOT NULL,
`originator` int(10) unsigned NOT NULL,
`client_collation_id` bigint(20) unsigned NOT NULL,
`connection_collation_id` bigint(20) unsigned NOT NULL,
`schema_collation_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `schema_id` (`schema_id`,`name`),
KEY `client_collation_id` (`client_collation_id`),
KEY `connection_collation_id` (`connection_collation_id`),
KEY `schema_collation_id` (`schema_collation_id`),
CONSTRAINT `events_ibfk_1` FOREIGN KEY (`schema_id`) REFERENCES `schemata` (`id`),
CONSTRAINT `events_ibfk_2` FOREIGN KEY (`client_collation_id`) REFERENCES `collations` (`id`),
CONSTRAINT `events_ibfk_3` FOREIGN KEY (`connection_collation_id`) REFERENCES `collations` (`id`),
CONSTRAINT `events_ibfk_4` FOREIGN KEY (`schema_collation_id`) REFERENCES `collations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
show create table routines\G
*************************** 1. row ***************************
Table: ROUTINES
Create Table: CREATE TEMPORARY TABLE `ROUTINES` (
`SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '',
`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 '',
`DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned 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,
`ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '',
`ROUTINE_DEFINITION` longtext,
`EXTERNAL_NAME` varchar(64) DEFAULT NULL,
`EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL,
`PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '',
`IS_DETERMINISTIC` varchar(3) NOT NULL DEFAULT '',
`SQL_DATA_ACCESS` varchar(64) NOT NULL DEFAULT '',
`SQL_PATH` varchar(64) DEFAULT NULL,
`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` varchar(8192) NOT NULL DEFAULT '',
`ROUTINE_COMMENT` longtext NOT NULL,
`DEFINER` varchar(93) 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=InnoDB DEFAULT CHARSET=utf8
Looking at the code again, I see that there are also tables tablespaces and version:
show create table tablespaces\G
*************************** 1. row ***************************
Table: tablespaces
Create Table: CREATE TABLE `tablespaces` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_bin NOT NULL,
`options` mediumtext COLLATE utf8_bin,
`se_private_data` mediumtext COLLATE utf8_bin,
`comment` varchar(2048) COLLATE utf8_bin NOT NULL,
`engine` varchar(64) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
show create table version\G
*************************** 1. row ***************************
Table: version
Create Table: CREATE TABLE `version` (
`version` int(10) unsigned NOT NULL,
PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
Now we can try the last part of our task, i.e., querying the data directory for some specific info.
mysql [localhost] {msandbox} (mysql) > SET SESSION debug='+d,skip_dd_table_access_check';
mysql [localhost] {msandbox} (mysql) > select * from version;
+---------+
| version |
+---------+
| 1 |
+---------+
mysql [localhost] {msandbox} (mysql) > select id, name from schemata;
+----+--------------------+
| id | name |
+----+--------------------+
| 2 | information_schema |
| 1 | mysql |
| 3 | performance_schema |
| 4 | sys |
| 5 | test |
+----+--------------------+
mysql [localhost] {msandbox} (mysql) > select id, name, type, engine, mysql_version_id, comment from tables where name = 'user' and schema_id=1;
+----+------+------------+--------+------------------+-----------------------------+
| id | name | type | engine | mysql_version_id | comment |
+----+------+------------+--------+------------------+-----------------------------+
| 84 | user | BASE TABLE | InnoDB | 80000 | Users and global privileges |
+----+------+------------+--------+------------------+-----------------------------+
Now the data dictionary is much more readable!
DISCLAIMER: there may be a simpler or more elegant solution to this problem. The method shown here is what I got by researching. But in fact, if there is a better method, short of recompiling the server, I'd like to know.
WARNING: Don't do what I do in the following paragraphs!
To complete the experiment, I am going to do what the MySQL team does not want me to do at all.
First, I create a directory inside the data directory. As shown in data dictionary limitations, this is not supported. But since we can access the data dictionary ...
$ mkdir ~/sandboxes/msb_full_8_0_0/data/db1
Now for the felony part:
mysql [localhost] {msandbox} (mysql) > SET SESSION debug= '+d,skip_dd_table_access_check';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (mysql) > select * from schemata;
+----+------------+--------------------+----------------------+---------------------+---------------------+
| id | catalog_id | name | default_collation_id | created | last_altered |
+----+------------+--------------------+----------------------+---------------------+---------------------+
| 1 | 1 | mysql | 8 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 2 | 1 | information_schema | 33 | 2016-09-25 18:06:00 | 2016-09-25 18:06:00 |
| 3 | 1 | performance_schema | 33 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 4 | 1 | sys | 33 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 5 | 1 | test | 8 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+------------+--------------------+----------------------+---------------------+---------------------+
5 rows in set (0.00 sec)
mysql [localhost] {msandbox} (mysql) > insert into schemata values (6, 1, 'db1', 8, now(), now());
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (mysql) > show schemas;
+--------------------+
| Database |
+--------------------+
| db1 | ## TA-DA!
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
Now, pay attention! This why the MySQL team don't want anyone to mess up with the data dictionary tables.
DOUBLE WARNING! Don't do the following!
mysql [localhost] {msandbox} (mysql) > insert into schemata values (7, 1, 'db2', 8, now(), now());
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (mysql) > show schemas;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql [localhost] {msandbox} (mysql) > use db2
ERROR 1049 (42000): Unknown database 'db2'
There! I broke the system. Lesson learned: read, don't write data dictionary tables.
