Bug #83889 Please unhide the internal innodb data dictionary tables
Submitted: 19 Nov 2016 9:13 Modified: 24 Nov 2016 8:08
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S4 (Feature request)
Version:8.0.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: data dictionary, Hidden, tables

[19 Nov 2016 9:13] Simon Mudd
Description:
MySQL 8.0.0 comes with a new data dictionary implemented in InnoDB with several special internal tables which manage this.

One surprise to me is that these tables are not visible to the user.
While the general user may not care about them, nor probably should he have permission to see their contents, the DBAs may at certain moments in time need to consult their content if there issues on the server due to bugs, corruption, or other failures. Hiding this information is not good as it may (under supervision) be necessary to fix some of the content and if you can't see the tables then you can not do this.

The reasons I understand for not wanting to make this visible are:

Reason: The public interface to data dictionary information should be visible via INFORMATION_SCHEMA which ends up being a set of views on top of these hidden tables.  In theory I_S views should provide all information necessary.

my response:
(a) yes but this is only a read_only view, so is no use for _fixing_ any servers issues which may arise.
(b) If the VIEW _I_ need is not available then in a major release it's unlikely to be added later, so it does not solve the problem.

Reason: Oracle may not want to expose the structure of these tables as this is a potential maintenance burden for the future.

my response:
(a) I think it is absolutely fine to state that the information provided in these "internal system tables" is liable to change WITHOUT notice,
and users SHOULD NOT rely IN ANY WAY on the content being consistent between different versions (including minor versions) of the product.  It's easy to add a table comment. So add it to each one:   COMMENT 'INTERNAL DATA STRUCTURES - USE AT YOUR OWN RISK - FORMAT AND CONTENT LIABLE TO CHANGE WITHOUT NOTICE - See: ....'

Reason: "use the debug binaries where you can see the tables"

my response:
(a) If the server I'm running has a problem I don't want to shut it down. I'll want to "inspect" it while it's running. Having to find and/or swap binaries means taking the service down and that in many cases may not be acceptable (e.g. on primary masters). In some cases I may have seen a warning about something, in others I may want to simply inspect some setting as the result of some "investigation".   The swap binaries argument is great if you're in a development environment but not if you're in a production one especially if you're chasing down a bug on a live server.

I also want to be clear: I would expect the number of times users might want or need to access (or possibly modify) the internal tables may be very limited. Doing so would need to be done by someone who really understands the consequences. I also wonder how much special code needs to be written to hide these tables and whether it would be simpler to just be able to use existing grant management, especially now as they are more flexible than in 5.7.

I believe there is time to re-evaluate the current implementation before 8.0 goes GA so would welcome comments on both sides of this debate.
If I don't make this public bug report / feature request then changing this later will be next to impossible and the day I get some corruption and need to fix something (especially on a live server) this may not be possible.

How to repeat:
See: http://datacharmer.blogspot.nl/2016/09/mysql-80-first-impressions.html where this is mentioned.

Suggested fix:
So my feeling is:
(1) the hidden tables should not be hidden
(2) add all the warnings you like about these being internal structures, liable to change at any time, and not intended to be used except by the system, and also not intended to be modified by "humans".
(3) these tables should be (for a DBA, so SUPER) read_only by default. I agree that we really don't want to change these values and most circumstances. Probably any access to these tables should require a new grant permission: ACCESS_TO_SYSTEM_TABLES
(4) there should a way to be able to write to the tables, via first changing some permission

Sybase has a similar concept with its master database and has a command:

'allow updates to system tables' with a nice shiny message which warns you about doing this:

"Warning!   Incorrect alteration of a system table can result in database corruption and loss of data. To protect against errors that might corrupt your databases, always use begin transaction when modifying a system table. Immediately after finishing your modifications, disable allow updates to system tables."

I'm sure the reasons for having such a command are for the same reasons I'm requesting the  "hidden Innodb tables" are made visiable and could be writeable.

So please despite your valid concerns about exposing these tables do make them visible to DBAs with appropriate privileges and make them writeable with more elevated privileges.
[24 Nov 2016 8:08] MySQL Verification Team
Hello Simon,

Thank you for the feature request and feedback!

Thanks,
Umesh