MySQL Bugs: #69323: Tablespace 0 is missing from INNODB_SYS

Bug #69323	Tablespace 0 is missing from INNODB_SYS_TABLESPACES
Submitted:	26 May 2013 9:46	Modified:	16 May 2015 6:45
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.6.10, 5.7.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The system tablespace (space 0) and the undo tablespaces are missing from the INNODB_SYS_DATAFILES and INNODB_SYS_TABLESPACES tables.

How to repeat:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES;
+-------+----------------------------+------+-------------+----------------------+-----------+---------------+
| SPACE | NAME                       | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE |
+-------+----------------------------+------+-------------+----------------------+-----------+---------------+
|     2 | mysql/innodb_table_stats   |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     3 | mysql/innodb_index_stats   |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     4 | mysql/slave_relay_log_info |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     5 | mysql/slave_master_info    |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     6 | mysql/slave_worker_info    |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    11 | test/foo                   |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
+-------+----------------------------+------+-------------+----------------------+-----------+---------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES;
+-------+----------------------------------+
| SPACE | PATH                             |
+-------+----------------------------------+
|     2 | ./mysql/innodb_table_stats.ibd   |
|     3 | ./mysql/innodb_index_stats.ibd   |
|     4 | ./mysql/slave_relay_log_info.ibd |
|     5 | ./mysql/slave_master_info.ibd    |
|     6 | ./mysql/slave_worker_info.ibd    |
|    11 | ./test/foo.ibd                   |
+-------+----------------------------------+
6 rows in set (0.00 sec)

Suggested fix:
Add system and undo tablespaces to I_S tables.

innodb_undo_tablespaces must be set to a non-zero value to have undo tablespaces outside of the system tablespace, this is what I used.

Hello Daniel,

Thank you for the report.
Verified as described.

Thanks,
Umesh

These I_S tables are for tablespaces and datafiles, tablespace and datafiles exist, but are NOT represented in the table.
if NOT explicitly documented in the manual, is a bug of some category.

master [localhost] {root} ((none)) > select version();
+---------------+
| version()     |
+---------------+
| 5.7.1-m11-log |
+---------------+
1 row in set (0.00 sec)

master [localhost] {root} ((none)) > SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES;
+-------+----------------------------+------+-------------+----------------------+-----------+---------------+
| SPACE | NAME                       | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE |
+-------+----------------------------+------+-------------+----------------------+-----------+---------------+
|     2 | mysql/innodb_table_stats   |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     3 | mysql/innodb_index_stats   |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     4 | mysql/slave_relay_log_info |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     5 | mysql/slave_master_info    |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|     6 | mysql/slave_worker_info    |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
+-------+----------------------------+------+-------------+----------------------+-----------+---------------+
5 rows in set (0.00 sec)

master [localhost] {root} ((none)) > SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES;
+-------+----------------------------------+
| SPACE | PATH                             |
+-------+----------------------------------+
|     2 | ./mysql/innodb_table_stats.ibd   |
|     3 | ./mysql/innodb_index_stats.ibd   |
|     4 | ./mysql/slave_relay_log_info.ibd |
|     5 | ./mysql/slave_master_info.ibd    |
|     6 | ./mysql/slave_worker_info.ibd    |
+-------+----------------------------------+
5 rows in set (0.00 sec)

This is fixed in 5.7.7-rc

mysql> select * from information_schema.innodb_sys_tablespaces where space=0;
+-------+---------------+------+-------------+----------------------+-----------+---------------+------------+
| SPACE | NAME          | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE |
+-------+---------------+------+-------------+----------------------+-----------+---------------+------------+
|     0 | innodb_system |    0 | Antelope    | Compact or Redundant |     16384 |             0 | System     |
+-------+---------------+------+-------------+----------------------+-----------+---------------+------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_sys_datafiles where space=0;
+-------+-----------+
| SPACE | PATH      |
+-------+-----------+
|     0 | ./ibdata1 |
+-------+-----------+
1 row in set (0.00 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.7.7-rc-debug |
+----------------+
1 row in set (0.00 sec)

Set to Closed as it is fixed

Posted by developer:
 
Fixed as of the upcoming 5.7.7 release, and here's the changelog entry:

The system tablespace and undo tablepaces were missing from
INNODB_SYS_DATAFILES and INNODB_SYS_TABLESPACES output. 

Note: As of MySQL 5.7.8, these tables once again only report metadata for file-per-table and general tablespaces. For metadata for other tablespace types, refer to INFORMATION_SCHEMA.FILES.

Thank you for the bug report.