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.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

8 Comments

  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?

  3. I agree with Giuseppe.

    Should you _need_ to see inside these tables restarting mysqld may not be an option. For normal production usage when you detect some sort of strange behaviour direct read-access to these tables may become invaluable to determine the cause of the problem. This hasn’t or doesn’t happen? It doesn’t happen much, but it does happen and at these times flipping some switch to see the insides of the server may be useful.

    So as a person who has seen problems in the binary, corrupt data (caused maybe by events outside of mysqld) and so on I’d really like it that these tables are potentially visible and on the _normal production_ binaries. I never use debug binaries except when testing and when testing my data isn’t important so access to the DD tables is less/unimportant too.

    8.0 provides roles so why not add a can_see_dd_tables role and for that I have to do something like:

    SET ROLE = ‘can_see_dd_tables’; stuff.. This acts as the safety net that you want.
    Ideally I’d also like a SET ROLE ‘can_write_to_dd_tables’ which might give a warning like:

    WARNING: You’re about to enter a mysql mode where you can destroy your system and leave it and any data contained within broken and unusable. This is not recommended except under guidance of appropriate support personnel.

    Since you’ve _said_ you want complete freedom to change structures inside here it’s fine to make the tables invisible normally and it’s also fine if the structures change from one minor release to another. I think we’ll all accept that compromise without problems. A pain for us? Sure but I accept that potential pain for the day I really need to look inside.

    As for write access: Yes, this is dangerous. No, it is unlikely to be used much. I think the paranoia here hides the fact that one of your support staff troubleshooting a production problem is going to really wish he could do this to help a customer. Maybe he won’t be able to find out what’s going on, but maybe he can just confirm that the DD is consistent and working properly, or if it’s not (due to a bug or data corruption) he can see what’s happened and not be speculating etc. Then he can figure out whatever action is needed next and discuss. Until now this magic has been inaccessible because the code couldn’t make it visible. This has changed. So don’t completely brick up the doorway that you may need to go through.

    I guess Giuseppe and I are fighting a losing battle. I hope not but we’ll see.

Leave a Reply to lefredCancel Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.