Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 34.6Mb
PDF (A4) - 34.6Mb
PDF (RPM) - 32.3Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 147.5Kb
Man Pages (Zip) - 208.8Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.2Mb


MySQL 8.0 Reference Manual  /  INFORMATION_SCHEMA Tables  /  The INFORMATION_SCHEMA FILES Table

Pre-General Availability Draft: 2017-12-16

25.9 The INFORMATION_SCHEMA FILES Table

The FILES table provides information about the files in which MySQL tablespace data is stored.

INFORMATION_SCHEMA.FILES reports data about NDB and InnoDB data files.

INFORMATION_SCHEMA NameSHOW NameRemarks
FILE_ID MySQL extension
FILE_NAME MySQL extension
FILE_TYPE MySQL extension
TABLESPACE_NAME MySQL extension
TABLE_CATALOG MySQL extension
TABLE_SCHEMA MySQL extension
TABLE_NAME MySQL extension
LOGFILE_GROUP_NAME MySQL extension
LOGFILE_GROUP_NUMBER MySQL extension
ENGINE MySQL extension
FULLTEXT_KEYS MySQL extension
DELETED_ROWS MySQL extension
UPDATE_COUNT MySQL extension
FREE_EXTENTS MySQL extension
TOTAL_EXTENTS MySQL extension
EXTENT_SIZE MySQL extension
INITIAL_SIZE MySQL extension
MAXIMUM_SIZE MySQL extension
AUTOEXTEND_SIZE MySQL extension
CREATION_TIME MySQL extension
LAST_UPDATE_TIME MySQL extension
LAST_ACCESS_TIME MySQL extension
RECOVER_TIME MySQL extension
TRANSACTION_COUNTER MySQL extension
VERSION MySQL extension
ROW_FORMAT MySQL extension
TABLE_ROWS MySQL extension
AVG_ROW_LENGTH MySQL extension
DATA_LENGTH MySQL extension
MAX_DATA_LENGTH MySQL extension
INDEX_LENGTH MySQL extension
DATA_FREE MySQL extension
CREATE_TIME MySQL extension
UPDATE_TIME MySQL extension
CHECK_TIME MySQL extension
CHECKSUM MySQL extension
STATUS MySQL extension
EXTRA MySQL extension

InnoDB Notes:

The following notes apply to InnoDB data files. INFORMATION_SCHEMA.FILES fields that are not described below are not applicable to InnoDB and report a NULL value.

  • Data reported by INFORMATION_SCHEMA.FILES is reported from the InnoDB in-memory cache for open files. By comparison, INFORMATION_SCHEMA.INNODB_DATAFILES reports data from the InnoDB SYS_DATAFILES internal data dictionary table.

  • The data reported by INFORMATION_SCHEMA.FILES includes temporary tablespace data. This data is not available in the internal SYS_DATAFILES data dictionary table, and is therefore not reported by INNODB_DATAFILES.

  • Undo tablespace data is reported by INFORMATION_SCHEMA.FILES when separate undo tablespaces are present, which they are by default in MySQL 8.0

  • FILE_ID is the tablespace ID, also referred to as the space_id or fil_space_t::id.

  • FILE_NAME is the name of the data file. File-per-table and general tablespaces have a .ibd file name extension. Undo tablespaces are prefixed by undo. The system tablespace is prefixed by ibdata. Temporary tablespaces are prefixed by ibtmp. The file name includes the file path, which may be relative to the MySQL data directory (datadir).

  • FILE_TYPE is the tablespace file type. There are three possible file types for InnoDB files. TABLESPACE is the file type for any system, general, or file-per-table tablespace file that holds tables, indexes, or other forms of user data. TEMPORARY is the file type for temporary tablespaces. UNDO LOG is the file type for undo tablespaces, which hold undo records.

  • TABLESPACE_NAME is the SQL name for the tablespace. A general tablespace name is the SYS_TABLESPACES.NAME value. For other tablespace files, names start with innodb_, such as innodb_system, innodb_undo, and innodb_file_per_table. The file-per-table tablespace name format is innodb_file_per_table_##, where ## is the tablespace ID.

  • ENGINE is the storage engine. For InnoDB files, the value is always InnoDB.

  • FREE_EXTENTS is the number of fully free extents in the current data file.

  • TOTAL_EXTENTS is the number of full extents used in the current data file. Any partial extent at the end of the file is not counted.

  • EXTENT_SIZE is 1048576 (1MB) for files with a 4k, 8k, or 16k page size. Extent size is 2097152 bytes (2MB) for files with a 32k page size, and 4194304 (4MB) for files with a 64k page size. INFORMATION_SCHEMA.FILES does not report InnoDB page size. Page size is defined by the innodb_page_size option. Extent size information can also be retrieved from INNODB_TABLESPACES where FILES.FILE_ID = INNODB_TABLESPACES.SPACE_ID.

  • INITIAL_SIZE is the initial size of the file, in bytes.

  • MAXIMUM_SIZE is the maximum number of bytes allowed in the file. The value is NULL for all data files except for predefined system tablespace data files. Maximum system tablespace file size is defined by innodb_data_file_path. Maximum temporary tablespace file size is defined by innodb_temp_data_file_path. A NULL value for a predefined system tablespace data file indicates that a file size limit was not defined explicitly.

  • AUTOEXTEND_SIZE is the auto-extend size defined by innodb_data_file_path for the system tablespace, or defined by innodb_temp_data_file_path for temporary tablespaces.

  • DATA_FREE is the total amount of free space (in bytes) for the entire tablespace. Predefined system tablespaces, which include the system tablespace and temporary table tablespaces, may have one or more data files.

  • STATUS is NORMAL by default. InnoDB file-per-table tablespaces may report IMPORTING, which indicates that the tablespace is not yet available.

  • The following query returns all data pertinent to InnoDB tablespaces.

    mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS, TOTAL_EXTENTS,
    EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE
    FROM INFORMATION_SCHEMA.FILES \G

User Comments
Sign Up Login You must be logged in to post a comment.