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

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

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.

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

4 thoughts on “MySQL 8.0 Data Dictionary Tables… and why they should stay protected

  1. Please update your post with the correct way to change systemd settings. The manual has the right info. Basically: don’t edit system files, but use the edit command to generate an override file. Also some of the bugs I reported about mysqld-debug will be fixed in 8.0.4. For 8.0.3 people might have to change the plugin dir location manually if using plugins (semisync, mysqlx, etc)

  2. Thanks for advertising my posts, Fred.
    Please, allow me to retort.
    There are several reasons for having the DD tables visible:
    * You can’t put everything from the DD in the information_schema views. Therefore, we need to know what is available in the DD to either bypass the limitations manually or ask for a feature request;
    * If the DD is invisible to users, you won’t get any sensible feedback on how it could be improved. You will get only bug reports about side effects of using the DD.
    * Since the manual acknowledges that the DD can be made visible, I don’t see why the tables could not be visible and read-only by default.
    * Regarding the fear that a ill advised operation can brick the system, you don’t need the DD for it. In fact, you can make your system useless by messing around with server privileges or global variables. Users can shoot themselves in the foot in many ways, but hiding the server resources is never a good solution against this risk. The right way to address the problem is using clear documentation and easy to use operations.
    * About the freedom of DD developers to change things at will, i’d say that this is a non issue. MySQL has changed established behaviours before, using deprecations. When this happens, users look at the manual, realise that some feature has changed, and adjust their tools and operations accordingly. Why we can’t do the same with the DD?

Leave a Reply

Your email address will not be published. Required fields are marked *

recent

Last Tweets Last Tweets

Locations of visitors to this page
categories