MySQL Bugs: #77032: InnoDB: Only one datafile per tablespace is dispayed in I_S.INNODB_SYS

Bug #77032	InnoDB: Only one datafile per tablespace is dispayed in I_S.INNODB_SYS_DATAFILES
Submitted:	13 May 2015 22:34	Modified:	4 Jun 2015 18:36
Reporter:	Kevin Lewis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Starting with v5.7.6, predefined system and temporary tablespaces are displayed in information_schema.innodb_sys_datafiles. 
But only the first datafile belonging to a system tablespace can be displayed since the primary key is the space_id.

  "CREATE TABLE SYS_DATAFILES(\n"
  " SPACE INT, PATH CHAR);\n"
  "CREATE UNIQUE CLUSTERED INDEX SYS_DATAFILES_SPACE"
  " ON SYS_DATAFILES (SPACE);\n"

SYS_DATAFILES has been created with a unique index on the space_id since it was introduced in v5.6. But it has only been used on single file tablespaces.

How to repeat:
Start mysqld with 
loose-innodb_data_file_path=ibdata1:12M;ibdata2:12M:autoextend
loose-innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:12M:autoextend

mysql> select * from information_schema.innodb_sys_datafiles where path like '%ibdata%';
+-------+-----------+
| SPACE | PATH      |
+-------+-----------+
|     0 | .\ibdata1 |
+-------+-----------+
1 row in set (0.00 sec)

Suggested fix:
1. Add an ORDER column so that the datafiles can be stored in the correct order.
     Problem: Backward compatibility to existing SYS_DATAFILES
2. Sort the clustered index on (SPACE, PATH)
     Problem: The path may be very long, long enough for off-page storage, or too long for the index.
2. Stop storing system tablespaces in SYS_DATAFILES and SYS_TABLESPACES
3. Do nothing in v5.7 and fix this in GlobalDD

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

The INFORMATION_SCHEMA.FILES table now reports metadata for all InnoDB
tablespace types including file-per-table tablespaces, general
tablespaces, the system tablespace, temporary table tablespaces, and undo
tablespaces (if present). System tablespace and temporary table tablespace
metadata is no longer reported by the
INFORMATION_SCHEMA.INNODB__SYS_TABLESPACES and
INFORMATION_SCHEMA.INNODB_SYS_DATAFILES tables. However, these tables
continue to provide metadata for file-per-table and general tablespaces.

Note: The changelog entry also covers WL7943 and Bug #20660744.

bug#77055 was marked as a duplicate