MySQL 8.0 Data Dictionary Tables… and why they should stay protected

With MySQL 8.0, one key feature is the new Data Dictionary.

The system tables that were previously in MyISAM are now replaced by new protected ones in the DD.

My friend Giuseppe already explained how you could see those tables using sandbox  and he also warned you that you should not mess up with them in this post too.

I’ll explain you how you can see those tables and their actual content. But will also explain why we decided to protect them and why it should stay like that.

DD protected internal tables list

The easiest way to get the list of the DD protected tables is to use the debug binary. Since Giuseppe’s blog, DD has evolved to use a dedicated tablespace, therefor the comment about using the filesystem files won’t’ work with newer MySQL 8.0.

The easiest way to use the debug binary of mysqld is to change the systemd service file for mysqld:

# systemctl edit --full mysqld

Just replace mysqld by mysqld-debug in the [Service] section:

ExecStart=/usr/sbin/mysqld-debug --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS

Then restart mysqld:

# systemctl restart mysqld

Now in a MySQL client session, we can see the DD hidden protected tables when we add a debug attribute:

mysql> SET SESSION debug='+d,skip_dd_table_access_check';

If you don’t do it, you will have the following error:

ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.

Now you can list the tables:

SELECT tables1.name, mysql.tablespaces.name 
FROM
(  SELECT * FROM mysql.tables WHERE schema_id 
   IN (SELECT id FROM mysql.schemata WHERE name='mysql')
) AS tables1 
LEFT JOIN
  mysql.tablespaces ON tables1.tablespace_id = tablespaces.id
WHERE tables1.name NOT IN ('ndb_binlog_index') 
ORDER BY tables1.name;
+------------------------------+-------+
| name                         | name  |
+------------------------------+-------+
| catalogs                     | mysql |
| character_sets               | mysql |
| collations                   | mysql |
| column_statistics            | mysql |
...
| tablespaces                  | mysql |
| triggers                     | mysql |
| view_routine_usage           | mysql |
| view_table_usage             | mysql |
+------------------------------+-------+

Accessing the DD protected tables

In the same session where the debug variable has been set, we can now access those tables:

mysql> select * from mysql.tablespaces;
+----+------------------+---------+-----------------------------------------------------------------+---------+--------+
| id | name             | options | se_private_data                                                 | comment | engine |
+----+------------------+---------+-----------------------------------------------------------------+---------+--------+
|  1 | mysql            | NULL    | flags=18432;id=4294967294;server_version=80004;space_version=1; |         | InnoDB |
|  2 | innodb_system    | NULL    | flags=18432;id=0;server_version=80004;space_version=1;          |         | InnoDB |
|  3 | innodb_temporary | NULL    | flags=4096;id=4294967293;server_version=80004;space_version=1;  |         | InnoDB |
|  4 | innodb_undo_001  | NULL    | flags=0;id=4294967279;server_version=80004;space_version=1;     |         | InnoDB |
|  5 | innodb_undo_002  | NULL    | flags=0;id=4294967278;server_version=80004;space_version=1;     |         | InnoDB |
|  6 | fred/fred        | NULL    | flags=16417;id=1;server_version=80004;space_version=1;          |         | InnoDB |
|  8 | fred/t1          | NULL    | flags=16417;id=3;server_version=80004;space_version=1;          |         | InnoDB |
|  9 | fred/test_json   | NULL    | flags=16417;id=4;server_version=80004;space_version=1;          |         | InnoDB |
+----+------------------+---------+-----------------------------------------------------------------+---------+--------+
8 rows in set (0.00 sec)

Why DD protected tables should stay hidden

At MySQL we decided that we won’t expose those tables because nobody should write in those tables as they are very sensitive and could break the full system.

These tables must also be protected against DDL as someone with sufficient privileges could change their definition, which would be disastrous, since the server code accesses the tables and expects a certain definition.

We also don’t want that people start writing tools around those tables that would reduce our freedom to modify those tables (structure, names) when we need it. If we expose those tables and allow queries directly against them, the DD tables will  become a de facto interface that the users will expect to be stable and limit our changes. We provide all needed commands and interfaces for the DD, there is no need for hacks and we are always happy to receive feature requests.

Data Dictionary is an amazing addition that opens the door for many future improvements on how MySQL deals with DDLs. We don’t want to slow down that innovation because we have constraints in changing our internal structure of the DD.