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

[13 May 2015 22:34] Kevin Lewis
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
[4 Jun 2015 18:36] Daniel Price
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.
[14 Jul 2015 6:26] Erlend Dahl
bug#77055 was marked as a duplicate