MySQL Bugs: #102009: INNODB_SYS_TABLESPACES doesn't show InnoDB system tablespace data (for SPACE 0)

Bug #102009	INNODB_SYS_TABLESPACES doesn't show InnoDB system tablespace data (for SPACE 0)
Submitted:	16 Dec 2020 11:16	Modified:	16 Dec 2020 14:39
Reporter:	Pranay Motupalli	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
INNODB_SYS_TABLESPACES doesn't show InnoDB system tablespace data (for SPACE 0) 

Quoting the document:

"With the introduction of general tablespaces, InnoDB system tablespace data (for SPACE 0) is exposed in INNODB_SYS_TABLESPACES."

https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-sys-tablespaces-table.ht...

How to repeat:
mysql [localhost:5732] {msandbox} ((none)) > select space,name from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES;
+-------+---------------------------------+
| space | name                            |
+-------+---------------------------------+
|     2 | mysql/plugin                    |
|     3 | mysql/servers                   |
|     4 | mysql/help_topic                |
|     5 | mysql/help_category             |
|     6 | mysql/help_relation             |
|     7 | mysql/help_keyword              |
|     8 | mysql/time_zone_name            |
|     9 | mysql/time_zone                 |
|    10 | mysql/time_zone_transition      |
|    11 | mysql/time_zone_transition_type |
|    12 | mysql/time_zone_leap_second     |
|    13 | mysql/innodb_table_stats        |
|    14 | mysql/innodb_index_stats        |
|    15 | mysql/slave_relay_log_info      |
|    16 | mysql/slave_master_info         |
|    17 | mysql/slave_worker_info         |
|    18 | mysql/gtid_executed             |
|    19 | mysql/server_cost               |
|    20 | mysql/engine_cost               |
|    21 | sys/sys_config                  |
+-------+---------------------------------+
20 rows in set (0.00 sec)

mysql [localhost:5732] {msandbox} ((none)) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.32    |
+-----------+
1 row in set (0.00 sec)

There is no detail related to system tablespace here.

Suggested fix:
Please fix this or change the documentation accordingly.

Hi Mr. Motupalli,

Thank you for your bug report.

Can you make sure that you have general tablespaces at all ????

Please, create one as described in the chapter 14.6.3.3 and run the command again. If you can't get the output, filter your query by looking for space with a value of 0.

We are waiting on your feedback.

Followed your recommendation. Still not able to see SPACE 0 in the list.
 

mysql [localhost:5732] {msandbox} (test) > CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:5732] {msandbox} (test) > CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:5732] {msandbox} (test) > select space,name from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES;
+-------+---------------------------------+
| space | name                            |
+-------+---------------------------------+
|     2 | mysql/plugin                    |
|     3 | mysql/servers                   |
|     4 | mysql/help_topic                |
|     5 | mysql/help_category             |
|     6 | mysql/help_relation             |
|     7 | mysql/help_keyword              |
|     8 | mysql/time_zone_name            |
|     9 | mysql/time_zone                 |
|    10 | mysql/time_zone_transition      |
|    11 | mysql/time_zone_transition_type |
|    12 | mysql/time_zone_leap_second     |
|    13 | mysql/innodb_table_stats        |
|    14 | mysql/innodb_index_stats        |
|    15 | mysql/slave_relay_log_info      |
|    16 | mysql/slave_master_info         |
|    17 | mysql/slave_worker_info         |
|    18 | mysql/gtid_executed             |
|    19 | mysql/server_cost               |
|    20 | mysql/engine_cost               |
|    21 | sys/sys_config                  |
|    23 | ts1                             |
+-------+---------------------------------+
21 rows in set (0.00 sec)

mysql [localhost:5732] {msandbox} (test) >

Hi Mr. Motupalli,

You are correct. I myself have tried so many variants of creating many general tablespaces, but I_S will not show none of them.

This is either a code bug or a documentation bug.

Verified as reported.

Thanks for the acknowledgement.

INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES does show details about general tablespaces.

When general tablespace is created, a new space id will be allocated and this is shown in INNODB_SYS_TABLESPACES.

In my previous output, space id 23 is created for new general tablespace.

However, the concern is about SYSTEM TABLESPACE which has space id 0 is not listed.