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.
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)
It’s like selinux, life is too short for systemd 😉 thank you for your comment, I will update it as soon as I can.
Thank you Daniël, I updated the post 😉
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?
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.
Hi Giuseppe & Simon,
Thank you for your comments and opinion on this topic. I’ll forward your view to our development team.
Regards !
[…] Read More (Community content) […]
[…] Source link […]
Thanks for the article, it’s very helpful.
To me, I need to update the CREATE_TIME and UPDATE_TIME of some tables, these two metadata(s) are important to me.