Revisiting the hidden MySQL 8.0 data dictionary tables

A few months ago I wrote about showing the hidden tables in MySQL 8 data dictionary. What I presented there was a naïve solution, which I am now able to improve with a few (bold) moves. In the solution given in my previous article, I was able to guess the table names somehow, but they were still hidden from the catalog. I did not think clearly then. I should have used the data dictionary itself to see the tables. Here, in addition to getting the real list of tables, I offer a feature to unhide them permanently.

MySQL-Sandbox 3.2.08 has now the ability of un-hide the data dictionary tables, and keep them available for inspection. This feature came to my mind after a chat with the MySQL team during PerconaLive 2017. They stressed the reason for hiding the tables, which is they want the freedom to change the tables if needed, without being constrained by what is published. They also say that if there is something missing in the information_schema views we could ask for an enhancement. I thought immediately that asking for I_S views enhancements would be easier if we could see the original tables. In the interest of science, then, I added an option --expose_dd_tables to MySQL-Sandbox, which will start the server with the changes needed to see and use the data dictionary tables.

$ make_sandbox 8.0.1 -- --expose_dd_tables

[...]

$ ~/sandboxes/msb_8_0_1/use mysql
mysql [localhost] {msandbox} (mysql) > select tables.name from mysql.tables inner join sys.dd_hidden_tables using (id);
+------------------------------+
| name                         |
+------------------------------+
| version                      |
| collations                   |
| tablespaces                  |
| tablespace_files             |
| catalogs                     |
| schemata                     |
| st_spatial_reference_systems |
| tables                       |
| view_table_usage             |
| view_routine_usage           |
| columns                      |
| indexes                      |
| index_column_usage           |
| column_type_elements         |
| foreign_keys                 |
| foreign_key_column_usage     |
| table_partitions             |
| table_partition_values       |
| index_partitions             |
| table_stats                  |
| index_stats                  |
| events                       |
| routines                     |
| parameters                   |
| parameter_type_elements      |
| triggers                     |
| character_sets               |
+------------------------------+
27 rows in set (0.00 sec)

This is, without a doubt, the complete list of hidden tables.

As you can infer from the query above, MySQL-Sandbox adds a table to the sys schema with the list of hidden tables.
If you want to hide the tables again, you can run this:

mysql [localhost] {msandbox} (mysql) > update mysql.tables set hidden=1 
    where id in (select id from sys.dd_hidden_tables);
Query OK, 27 rows affected (0.04 sec)
Rows matched: 27  Changed: 27  Warnings: 0

But of course we need the tables visible, so now we can peruse the data dictionary tables at will, and find out if there is valuable information missing from information_schema views.

How does this work?

I used the same hack defined in my previous post, combined with a new feature of MySQL 8.0 (SET PERSIST) that allows me to keep the special option enabled across restarts. Unlike the solution in my previous post, though, users only need to use the starting option (--expose_dd_tables) without remembering obscure debug sequences.

Happy hacking!