Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.2Mb
PDF (A4) - 38.3Mb
PDF (RPM) - 33.1Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 133.2Kb
Man Pages (Zip) - 189.3Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

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

24.10 The INFORMATION_SCHEMA FILES Table

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

The FILES table provides information about InnoDB data files.

The FILES table has these columns:

  • FILE_ID

    For InnoDB: The tablespace ID, also referred to as the space_id or fil_space_t::id.

  • FILE_NAME

    For InnoDB: The name of the data file. File-per-table and general tablespaces have an .ibd file name extension. Undo tablespaces are prefixed by undo. The system tablespace is prefixed by ibdata. The global temporary tablespace is prefixed by ibtmp. The file name includes the file path, which may be relative to the MySQL data directory (the value of the datadir system variable).

  • FILE_TYPE

    For InnoDB: 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

    For InnoDB: 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.

  • TABLE_CATALOG

    This value is always empty.

  • TABLE_SCHEMA

    This value is always NULL.

  • TABLE_NAME

    For InnoDB: This value is always NULL.

  • LOGFILE_GROUP_NAME

    For InnoDB: This value is always NULL.

  • LOGFILE_GROUP_NUMBER

    For InnoDB: This value is always NULL.

  • ENGINE

    For InnoDB: This value is always InnoDB.

  • FULLTEXT_KEYS

    For InnoDB: This value is always NULL.

  • DELETED_ROWS

    For InnoDB: This value is always NULL.

  • UPDATE_COUNT

    For InnoDB: This value is always NULL.

  • FREE_EXTENTS

    For InnoDB: The number of fully free extents in the current data file.

  • TOTAL_EXTENTS

    For InnoDB: 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

    For InnoDB: Extent size is 1048576 (1MB) for files with a 4KB, 8KB, or 16KB page size. Extent size is 2097152 bytes (2MB) for files with a 32KB page size, and 4194304 (4MB) for files with a 64KB page size. FILES does not report InnoDB page size. Page size is defined by the innodb_page_size system variable. Extent size information can also be retrieved from the INNODB_TABLESPACES table where FILES.FILE_ID = INNODB_TABLESPACES.SPACE.

  • INITIAL_SIZE

    For InnoDB: The initial size of the file in bytes.

  • MAXIMUM_SIZE

    For InnoDB: The maximum number of bytes permitted 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 global 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

    For InnoDB: AUTOEXTEND_SIZE is the auto-extend size defined by innodb_data_file_path for the system tablespace, or by innodb_temp_data_file_path for the global temporary tablespace.

  • CREATION_TIME

    For InnoDB: This value is always NULL.

  • LAST_UPDATE_TIME

    For InnoDB: This value is always NULL.

  • LAST_ACCESS_TIME

    For InnoDB: This value is always NULL.

  • RECOVER_TIME

    For InnoDB: This value is always NULL.

  • TRANSACTION_COUNTER

    For InnoDB: This value is always NULL.

  • VERSION

    For InnoDB: This value is always NULL.

  • ROW_FORMAT

    For InnoDB: This value is always NULL.

  • TABLE_ROWS

    For InnoDB: This value is always NULL.

  • AVG_ROW_LENGTH

    For InnoDB: This value is always NULL.

  • DATA_LENGTH

    For InnoDB: This value is always NULL.

  • MAX_DATA_LENGTH

    For InnoDB: This value is always NULL.

  • INDEX_LENGTH

    For InnoDB: This value is always NULL.

  • DATA_FREE

    For InnoDB: 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.

  • CREATE_TIME

    For InnoDB: This value is always NULL.

  • UPDATE_TIME

    For InnoDB: This value is always NULL.

  • CHECK_TIME

    For InnoDB: This value is always NULL.

  • CHECKSUM

    For InnoDB: This value is always NULL.

  • STATUS

    For InnoDB: This value is NORMAL by default. InnoDB file-per-table tablespaces may report IMPORTING, which indicates that the tablespace is not yet available.

  • EXTRA

    For InnoDB: This value is always NULL.

Notes

  • The FILES table is a nonstandard INFORMATION_SCHEMA table.

InnoDB Notes

The following notes apply to InnoDB data files.

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

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

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

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

    SELECT
      FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,
      TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE,
      AUTOEXTEND_SIZE, DATA_FREE, STATUS
    FROM INFORMATION_SCHEMA.FILES WHERE ENGINE='InnoDB'\G

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.