I was aware of this problem already in 2015 or maybe even earlier, while still working at Oracle. The tables were introduced without my approval.

In my opinion, it was a mistake to add the SYS_TABLESPACES and SYS_DATAFILES in the first place in MySQL 5.6. The table SYS_TABLESPACES is completely redundant; files are identified by SYS_TABLES anyway. Likewise, the only additional value that the table SYS_DATAFILES might bring is to register the location of data files when the DATA DIRECTORY attribute is being used. But, we store that information also in .isl files!

In a support issue a couple of months ago, I noticed that orphan entries can accumulate in both these tables under some circumstances.

In summary, adding file paths to the system tablespace is only adding problems, not solving any.

Unfortunately, we missed the opportunity to remove these tables in 10.5, due to higher-priority tasks. I think that we should do it in 10.6, and we should try to make the entries in the tables optional in older versions.

Note: In MySQL 5.7, SYS_TABLESPACES became necessary for the multi-table tablespaces that can be created by CREATE TABLESPACE. MariaDB does not support CREATE TABLESPACE for InnoDB, in my opinion for good reasons.

What should the fix be? Make INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES reflect the InnoDB fil_system cache contents? But, then we would lose a way to query the contents of the SYS_TABLESPACES table?

Tablespace metadata will always be present in memory for all tablespaces, so it is technically doable. Perhaps we should instead introduce a new view, such as INFORMATION_SCHEMA.INNODB_TABLESPACES?

Hi marko,

Unfortunately, we missed the opportunity to remove these tables in 10.5, due to higher-priority tasks. I think that we should do it in 10.6, and we should try to make the entries in the tables optional in older versions.

OK, that makes sense. It sounds like we should tell users to only query the information_schema.INNODB_SYS_TABLES table for table and tablespace-related metadata, since the information_schema.INNODB_SYS_TABLESPACES table has known bugs, and since you plan to remove that table anyway.

Tablespace metadata will always be present in memory for all tablespaces, so it is technically doable. Perhaps we should instead introduce a new view, such as INFORMATION_SCHEMA.INNODB_TABLESPACES?

In the long term, it might be useful to have ways to view the contents of important data structures like that.

julien.fritsch, I do not really think that ‘upstream’ is a meaningful concept any more. We have diverged rather far from MySQL and definitely do not depend on them for bug fixes.

GeoffMontee, would it suffice to fix this bug in 10.6 as part of MDEV-22343? By possibly providing something like INFORMATION_SCHEMA.INNODB_TABLESPACES?

Hi marko,

That sounds reasonable to me. Users can still use information_schema.INNODB_SYS_TABLES in existing GA versions to avoid the bugs in information_schema.INNODB_SYS_TABLESPACES.

MDEV-22343 in MariaDB Server 10.6.0 removes the system tables SYS_TABLESPACES and SYS_DATAFILES altogether and makes INNODB_SYS_TABLESPACES reflect the fil_system data structure.