WL#9814: Implement INFORMATION_SCHEMA system views for FILES/PARTITIONS

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

This WL should implement new system view definition for following
I_S tables, reading metadata from data dictionary tables and
avoid creation of temporary table for these I_S table as we do
today for I_S.TABLES/COLUMNS/etc:

INFORMATION_SCHEMA.PARTITIONS
INFORMATION_SCHEMA.FILES


INFORMATION_SCHEMA.TABLESPACES:-

This I_S tables does not provide any information for now. 



Abbreviation:

I_S  - INFORMATION_SCHEMA.
DD   - Data dictionary.
SE   - Storage engine.


Functional Requirements:

FR1:
Implement I_S.FILES system view over DD tables.

FR2:
Implement I_S.PARTITIONS system view over DD tables.

FR3:
Implement handlerton API for InnoDB to retrieve tablespace
metadata required by I_S.FILES.

FR4:
Implement UDF GET_TABLESPACE_SE_METADATA() to be used by FR1,
which invokes handlerton API designed in FR4.

FR5:
Add new columns to representing utf8 string version of
PARTITION_EXPRESSION
SUBPARTITION_EXPRESSION
PARTITION_DESCRIPTION.

FR6:
Implement UDF INTERNAL_GET_PARTITION_NODEGROUP() to be used by FR2. To read
nodegroup id from DD tables options.

FR7:
Change to partition code to introduce
mysql.partitions.parent_partition_id, instead of 'level' column.


Non-Functional Requirements:

NFR1: User interface to I_S.FILES/PARTITIONS
should not change.

NFR2: Related old I_S code should be removed.
Introduction:

As discussed above, this WL aims to implement I_S tables as a
view over data dictionary tables.  The following text describes
the mapping of DD columns to I_S columns. There are I_S columns
that do not map to DD columns, but the get their values from SE.


A) I_S.FILES system view design (FR1):

  1) Columns that map to DD tables:

  - FILE_NAME maps to mysql.tablespaces.file_name.
  - TABLESPACE_NAME maps to mysql.tablespaces.name.
  - TABLE_CATALOG maps to mysql.catalog.name.
  - TABLE_SCHEMA maps to mysql.schemata.name.
  - TABLE_NAME maps to mysql.tabes.name.  
  - ENGINE maps to mysql.tablespaces.engine.


  2) Columns that requests SE for their value (FR4):

  typedef bool (*get_tablespace_se_metadata_t)(
                   const char *se_private_data,
                   ha_tablespace_se_metadata *tsm /* OUT PARAM */);

  /*
    The following members will be fill by respective SE as
    mentioned below.
  */
  class ha_tablespace_se_metadata
  {
    ulonglong m_file_id;               // InnoDB & NDB
    char *m_file_type;                 // InnoDB & NDB
    char *m_logfile_group_name;        // NDB
    ulonglong m_logfile_group_number;  // NDB
    ulonglong m_version;               // NDB
    char *m_row_format;                // NDB
    ulonglong m_FREE_EXTENTS;          // InnoDB & NDB
    ulonglong m_TOTAL_EXTENTS;         // InnoDB & NDB
    ulonglong m_EXTENT_SIZE;           // InnoDB & NDB
    ulonglong m_INITIAL_SIZE;          // InnoDB & NDB
    ulonglong m_MAXIMUM_SIZE;          // InnoDB & NDB
    ulonglong m_AUTOEXTEND_SIZE;       // InnoDB 
    ulonglong m_DATA_FREE;             // InnoDB
    char *m_status;                    // InnoDB & NDB
    char *m_extra;                     // NDB
  };

  3) (FR5) Implement UDF GET_TABLESPACE_SE_METADATA() which invokes
    get_tablespace_se_metadata_t() handlerton API. This UDF would
    be used in system view definition for I_S.FILES.

  Q1) Can some of these information be stored in DD ?
      may not be as part of this WL, but in future.

  Q2) With above proposal of handlerton API, one drawback is that
      SE would calculate and fill all the applicable metadata into
      ha_tablespace_se_metadata, irrespective of user requesting just
      few or single metadata value. E.g., User can request
      I_S.FILES.DATA_FREE, but all SE would work to return all
      metadata. This might affect performance. Should we really
      bother about it now ?

      AFAIU, the performance of I_S.FILES is not critical and hence the
      above design is fine. If there is a need to improve performance
      of such queries in future, we should consider introducing more
      handlerton API's one per I_S column.


  3) Unused columns in I_S.FILES by all SE:

    FULLTEXT_KEYS
    DELETED_ROWS
    UPDATE_COUNT
    CREATION_TIME
    LAST_UPDATE_TIME
    LAST_ACCESS_TIME
    RECOVER_TIME
    TRANSACTION_COUNTER
    TABLE_ROWS
    AVG_ROW_LENGTH
    DATA_LENGTH
    MAX_DATA_LENGTH
    INDEX_LENGTH
    CREATE_TIME
    UPDATE_TIME
    CHECK_TIME
    CHECKSUM


B) I_S.PARTITIONS system view design:

  1) Columns that map to DD tables:

  - TABLE_CATALOG maps to mysql.catalogs.name.
  - TABLE_SCHEMA maps to mysql.schemata.name.
  - TABLE_NAME maps to mysql.tables.name.
  - PARTITION_NAME maps to mysql.partitions.name.
  - SUBPARTITION_NAME maps to mysql.partitions.name.
  - PARTITION_ORDINAL_POSITION maps to mysql.partitions.ordinal_position.
  - SUBPARTITION_ORDINAL_POSITION maps to mysql.partitions.ordinal_position.
  - PARTITION_METHOD maps to mysql.partitions.partition_type.
  - SUBPARTITION_METHOD maps to mysql.partitions.subpartition_type 

  - PARTITION_EXPRESSION maps to mysql.tables.partition_expression 
  - SUBPARTITION_EXPRESSION mysql.tables.subpartition_expression 
    (FR6) Add new columns to representing utf8 string version of
    partition_expression and subpartition_expression. Following new
    columns are added.

    mysql.tables.partition_expression_utf8 VARCHAR(2048)
    mysql.tables.subpartition_expression_utf8 VARCHAR(2048)

  - PARTITION_COMMENT maps to mysql.partitions.comment.
  - TABLESPACE_NAME maps to mysql.tablespaces.name.

  - (FR6) PARTITION_DESCRIPTION maps to more than one rows in DD tables.
    It is difficult to calculate this value in the system view definition
    by using SELECT grammer. Hence the value is now stored in new column
    mysql.table_partitions.description_utf8 field.

  - (FR7) NODEGROUP maps to a value stored for the key 'nodegroup_id' in
    mysql.partitions.options. The following UDF is user to retrieve
    the value.
  
    INTERNAL_GET_PARTITION_NODEGROUP(mysql.partitions.options) AS NODEGROUP

  2) Columns that map to respective columns in I_S.TABLES.

  - TABLE_ROWS
  - AVG_ROW_LENGTH
  - DATA_LENGTH
  - MAX_DATA_LENGTH
  - INDEX_LENGTH
  - DATA_FREE
  - CREATE_TIME
  - UPDATE_TIME
  - CHECK_TIME
  - CHECKSUM

  Q1) Value of these columns in I_S.TABLES work based on server
  setting information_schema_stats=latest/cached. I presume the
  same behavior should be applicable to I_S.PARTITIONS too ?

  3) Changes to DD schema (FR8) :

  In order to develop a system view to map each subpartition to
  its parent partition, we need following task to be completed.
  Without this implementation of B) is not possible.

C) Performance:

C.1)
Performance of I_S.FILES is expected to be better than 5.7.

C.2)
Performance of I_S.PARTITIONS with current implementation would
be slower than 5.7 if the query uses on of column listed in B.2).
This is because we end-up opening tables for each row. We should
ideally develop SE API to fetch required statistics per partition.
This needs effort from InnoDB and some server code changes too.
This work will be handled as part of WL#11076.

However, if the I_S.PARTITIONS query does not use columns listed
in B.2), then the execution of I_S.PARTITIONS is expected to be
faster (at-least 10times) than 5.7.


D) Upgrade:

5.7 to 8.0 Upgrade might not be affected. Database created before
this WL, may not work with server that has this WL implemented.
IMO, as we do not support DD to DD upgrade as of now, no action
is required.


E) Documentation:

Sections that might apply for documentation are,
FR1, FR2 and D). And also mention about C.2) which
expects I_S.PARTITIONS to be slower in certain cases
and it is going to be improved.


[DRAFT content - To be updated]

A) System view definition for FILES

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.FILES AS
SELECT
  'UDF" as FILE_ID,
  tsf.file_name AS FILE_NAME,
  'UDF' AS FILE_TYPE, /* cluster/innodb */
  ts.name AS TABLESPACE_NAME,
  cat.name AS TABLE_CATALOG,
  sch.name AS TABLE_SCHEMA,
  tab.name AS TABLE_NAME,
  'UDF' AS LOGFILE_GROUP_NAME, /* cluster */
  'UDF' AS LOGFILE_GROUP_NUMBER, /* cluster */
  ts.engine AS ENGINE,
  '' AS FULLTEXT_KEYS,
  '' AS DELETED_ROWS,
  '' AS UPDATE_COUNT,
  'UDF' AS FREE_EXTENTS, /* cluster/innodb */
  'UDF' AS TOTAL_EXTENTS, /* cluster/innodb */
  'UDF' AS EXTENT_SIZE, /* cluster/innodb */
  'UDF' AS INITIAL_SIZE, /* cluster/innodb */
  'UDF' AS MAXIMUM_SIZE, /* cluster/innodb */
  'UDF' AS AUTOEXTEND_SIZE,
  '' AS CREATION_TIME,
  '' AS LAST_UPDATE_TIME,
  '' AS LAST_ACCESS_TIME,
  '' AS RECOVER_TIME,
  '' AS TRANSACTION_COUNTER,
  'UDF' AS VERSION, /* cluster */
  'UDF' AS ROW_FORMAT, /* cluster */
  '' AS TABLE_ROWS,
  '' AS AVG_ROW_LENGTH,
  '' AS DATA_LENGTH,
  '' AS MAX_DATA_LENGTH,
  '' AS INDEX_LENGTH,
  'UDF' AS DATA_FREE, /* innodb */
  '' AS CREATE_TIME,
  '' AS UPDATE_TIME,
  '' AS CHECK_TIME,
  '' AS CHECKSUM,
  'UDF' AS STATUS, /* innodb/cluster always NORMAL */
  '' AS EXTRA  /* no-innodb/cluster*/
 FROM mysql.tablespaces ts
     JOIN mysql.tablespace_files tsf ON ts.id=tsf.tablespace_id
     LEFT JOIN mysql.tables tab ON tab.tablespace_id=ts.id
     JOIN mysql.schemata sch ON tab.schema_id=sch.id
     JOIN mysql.catalogs cat ON sch.catalog_id=cat.id");


B) System view definition of PARTITIONS.

   CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 
VIEW `information_schema`.`PARTITIONS`
  AS SELECT
  `cat`.`name` AS `TABLE_CATALOG`,
  `sch`.`name` AS `TABLE_SCHEMA`,,
  `tbl`.`name` AS `TABLE_NAME`,
  `part`.`name` AS `PARTITION_NAME`,
  `sub_part`.`name` AS `SUBPARTITION_NAME`,
  `part`.`number` + 1 AS `PARTITION_ORDINAL_POSITION`,
  `sub_part`.`number` + 1 AS `SUBPARTITION_ORDINAL_POSITION`,
  (case `tbl`.`partition_type`

   when 'HASH' then 'HASH'
     when 'RANGE' then 'RANGE'
     when 'LIST' then 'LIST'
     when 'AUTO' then 'AUTO'
     when 'KEY_51' then 'KEY'
     when 'KEY_55' then 'KEY'
     when 'LINEAR_KEY_51' then 'LINEAR KEY'
     when 'LINEAR_KEY_55' then 'LINEAR KEY'
     when 'LINEAR_HASH' then 'LINEAR HASH'
     when 'RANGE_COLUMNS' then 'RANGE COLUMNS'
     when 'LIST_COLUMNS' then 'LIST COLUMNS' else NULL end) AS 
`PARTITION_METHOD`,
  (case `tbl`.`subpartition_type`
     when 'HASH' then 'HASH'
     when 'RANGE' then 'RANGE'                                                 
      
     when 'LIST' then 'LIST'
     when 'AUTO' then 'AUTO'
     when 'KEY_51' then 'KEY'
     when 'KEY_55' then 'KEY'
     when 'LINEAR_KEY_51' then 'LINEAR KEY'
     when 'LINEAR_KEY_55' then 'LINEAR KEY'
     when 'LINEAR_HASH' then 'LINEAR HASH'                                     
      
     when 'RANGE_COLUMNS' then 'RANGE COLUMNS'                                 
      
     when 'LIST_COLUMNS' then 'LIST COLUMNS' else NULL end) AS 
`SUBPARTITION_METHOD`, 
  `tbl`.`partition_expression_utf8` AS `PARTITION_EXPRESSION`,                 
      
  `tbl`.`subpartition_expression_utf8` AS `SUBPARTITION_EXPRESSION`,           
      
  `part`.`description_utf8` AS `PARTITION_DESCRIPTION`,                        
      
internal_table_rows(`sch`.`name`,`tbl`.`name`,
                    if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
                    `tbl`.`se_private_id`,
                    if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`), 
                       `tbl`.`se_private_data`, `part_ts`.`se_private_data`),
                       `sub_part_ts`.`se_private_data`),
                    ifnull(`sub_part`.`name`,`part`.`name`)) AS `TABLE_ROWS`,
  
internal_avg_row_length(`sch`.`name`,`tbl`.`name`,
                    if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
                    `tbl`.`se_private_id`,
                    if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`), 
                       `tbl`.`se_private_data`, `part_ts`.`se_private_data`),
                       `sub_part_ts`.`se_private_data`),
                    ifnull(`sub_part`.`name`,`part`.`name`)) AS `AVG_ROW_LENGTH`,
  
internal_data_length(`sch`.`name`,`tbl`.`name`,
                    if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
                    `tbl`.`se_private_id`,
                    if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`), 
                       `tbl`.`se_private_data`, `part_ts`.`se_private_data`),
                       `sub_part_ts`.`se_private_data`),
                    ifnull(`sub_part`.`name`,`part`.`name`)) AS `DATA_LENGTH`,
  
internal_max_data_length(`sch`.`name`,`tbl`.`name`,
                    if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
                    `tbl`.`se_private_id`,
                    if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`), 
                       `tbl`.`se_private_data`, `part_ts`.`se_private_data`),
                       `sub_part_ts`.`se_private_data`),
                    ifnull(`sub_part`.`name`,`part`.`name`)) AS `MAX_DATA_LENGTH`,
  
internal_index_length(`sch`.`name`,`tbl`.`name`,
                    if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
                    `tbl`.`se_private_id`,
                    if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`), 
                       `tbl`.`se_private_data`, `part_ts`.`se_private_data`),
                       `sub_part_ts`.`se_private_data`),
                    ifnull(`sub_part`.`name`,`part`.`name`)) AS `INDEX_LENGTH`,
  
internal_data_free(`sch`.`name`,`tbl`.`name`,
                    if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
                    `tbl`.`se_private_id`,
                    if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`), 
                       `tbl`.`se_private_data`, `part_ts`.`se_private_data`),
                       `sub_part_ts`.`se_private_data`),
                    ifnull(`sub_part`.`name`,`part`.`name`)) AS `DATA_FREE`,

  `tbl`.`created` AS `CREATE_TIME`,
  
internal_update_time(`sch`.`name`,`tbl`.`name`,
                    if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
                    `tbl`.`se_private_id`,
                    if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`), 
                       `tbl`.`se_private_data`, `part_ts`.`se_private_data`),
                       `sub_part_ts`.`se_private_data`),
                    ifnull(`sub_part`.`name`,`part`.`name`)) AS `UPDATE_TIME`,
  
internal_check_time(`sch`.`name`,`tbl`.`name`,
                    if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
                    `tbl`.`se_private_id`,
                    if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`), 
                       `tbl`.`se_private_data`, `part_ts`.`se_private_data`),
                       `sub_part_ts`.`se_private_data`),
                    ifnull(`sub_part`.`name`,`part`.`name`)) AS `CHECK_TIME`,
  
internal_checksum(`sch`.`name`,`tbl`.`name`,
                    if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
                    `tbl`.`se_private_id`,
                    if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`), 
                       `tbl`.`se_private_data`, `part_ts`.`se_private_data`),
                       `sub_part_ts`.`se_private_data`),
                    ifnull(`sub_part`.`name`,`part`.`name`)) AS `CHECKSUM`,
  
   if(isnull(`sub_part`.`name`),
      ifnull(`part`.`comment`,''),
      ifnull(`sub_part`.`comment`,'')) AS `PARTITION_COMMENT`,
  
   if(isnull(`part`.`name`),'',
     
internal_get_partition_nodegroup(if(isnull(`sub_part`.`name`),`part`.`options`,`sub_part`.`options`)))
     AS `NODEGROUP`,

  ifnull(`sub_part_ts`.`name`,`part_ts`.`name`) AS `TABLESPACE_NAME`

  FROM mysql.tables tbl");
       JOIN mysql.schemata sch ON sch.id=tbl.schema_id
       JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
       LEFT JOIN mysql.table_partitions part ON part.table_id=tbl.id
       LEFT JOIN mysql.table_partitions sub_part ON 
sub_part.parent_partition_id=part.id
       LEFT JOIN mysql.tablespaces part_ts ON part_ts.id=part.tablespace_id
       LEFT JOIN mysql.tablespaces sub_part_ts ON sub_part.tablespace_id IS NOT 
NULL
       AND sub_part_ts.id=sub_part.tablespace_id
  WHERE CAN_ACCESS_TABLE(sch.name, tbl.name)
        AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
        AND part.parent_partition_id IS NULL;