WL#6599: New Data Dictionary and I_S integration

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

Introduction:
-------------

The INFORMATION_SCHEMA tables on 5.7 are temporary tables that
are created and filled during execution of the query. The
meta-data is read from .FRM files, storage engines and server in
memory data structures. Reading data from .FRM files involves
preparation of TABLE_SHARE object for each and every table and
this is time consuming. 

With the introduction of data dictionary in 8.0, most of
INFORMATION_SCHEMA tables meta-data can be read from data
dictionary tables. The goal of this WL is,

1.  To re-implement the possible INFORMATION_SCHEMA tables to pick
  meta-data from data-dictionary tables and avoid creation of
  temporary tables and preparation of TABLE_SHARE object upon every
  query execution that contains INFORMATION_SCHEMA table.

2.  The implementation would also provide following modes,

  2.1 Support retrieval of dynamic table or index statistics from a
  new DD table where the statistics are already stored. The DD table
  would be updated using ANALYZE TABLE command. This allows a
  INFORMATION_SCHEMA system view to present the dynamic statistics to
  the users faster. It is understood that the statistics presented
  this way would be from the time when user ran ANALYZE TABLE and are
  not latest statistics.

  2.2 Support retrieval of dynamic table or index statistics from
  storage engine. This allows a INFORMATION_SCHEMA system view to
  present latest dynamic statistics to the users. The design would
  also involve developing customer storage engine API's that would
  help get the latest table statistics without running open_table()
  on a given table.

  2.3 Provide support for user to switch between behaviour 2.1 and
      2.2 based on a session variable.

3.  With the re-implementation of several INFORMATION_SCHEMA
  tables, there will be compatibility issues. We must try to
  support the current behaviour as much of possible. However few
  specific cases are,

  - The current behavior is that the order of row's returned from
    I_S query is fixed as of now. After this WL implementation the
    rows returned from every execution of INFORMATION_SCHEMA system
    view might depend on optimizer decisions and hence the order might
    change.

  - On 5.7, I_S tables are temporary tables and then the 'CREATE
    TABLE LIKE' and 'HANDLER COLUMNS OPEN' command would work on I_S
    tables.  On 8.0, few I_S tables are system views and these two
    command expect the input to be a BASE table and not a view. This
    brings in a limitation.

  - The --ignore-db-dir option was mostly added to avoid SQL server
    to read physical directories like lost+found. Now that 8.0 server
    do not depend of file-system folder to find databases, this option
    is removed.

  - Upon execution of I_S system views, the resulting column names
    are show in capital. This seems to be result of how views are
    merged.

  There could be other compatibility issues, that should be
  investigated by the WL.

4.  Discuss possible solution and outline a plan for implementation.


1) Definitions and Abbreviation:
--------------------------------

  DD          : Data Dictionary

  I_S         : INFORMATION_SCHEMA

  System View : A MySQL view that represent a I_S table.

  TS          : Dynamic table statistics, like,

                  TABLE_ROWS
                  AVG_ROW_LENGTH
                  DATA_LENGTH
                  MAX_DATA_LENGTH
                  INDEX_LENGTH
                  DATA_FREE
                  AUTO_INCREMENT
                  CREATE_TIME
                  UPDATE_TIME
                  CHECK_TIME

  IS          : Dynamic Index statistics i.e., index CARDINALITY.

  TS_DD       : DD table mysql.table_stat to store table statistics

  IS_DD       : DD table mysql.index_stat to store index statistics

  FDS         : Fast Dynamic Table/Index Statistics, but not
                latest.  Statistics will be based on last
                execution of ANALYZE TABLE command.

  LDS         : Latest Dynamic Table/Index Statistics read from
                storage engine. This would be slower compare to
                read LDS compared to FDS.

  INTERNAL_UDF: User defined functions implemented to enable
                reading SE statistics dynamically in LDS
                configuration.


2) Functional requirements:
---------------------------

FR-01  Implement possible I_S tables as a System View over the DD
       tables.

      a) I_S tables that mostly fetch static meta-data.
         I.e., it does not read TS/IS details.

      CHARACTER_SETS
      COLLATIONS
      SCHEMATA
      COLLATION_CHARACTER_SET_APPLICABILITY
      COLUMNS
      COLUMN_PRIVILEGES
      KEY_COLUMN_USAGE
      PARAMETERS
      PARTITIONS
      REFERENTIAL_CONSTRAINTS
      EVENTS
      ROUTINES
      TABLESPACES
      TABLE_CONSTRAINTS
      TRIGGERS
      VIEWS
      KEY_COLUMN_USAGE

      b) I_S tables that mostly read both static meta-data
         and dynamic table statistics.

      TABLES
      STATISTICS
      FILES


      Note that the SHOW CREATE command on system view would now show
      the system view definition.


FR-02  Implement possible SHOW commands to use new I_S system view
       introduced by FR-01.

        SHOW CHARSET
        SHOW COLLATION
        SHOW DATABASES
        SHOW TABLES
        SHOW TABLE STATUS
        SHOW COLUMNS
        SHOW KEYS/INDEXES
        SHOW STATISTICS
        DESCRIBE

FR-03  Introduce new TS_DD table to store dynamic table
       statistics, to enable FDS retrieval from I_S system view.

FR-04  Introduce new IS_DD table to store dynamic index
       statistics, to enable FDS retrieval from I_S system view.

FR-05  It must be possible for I_S system view to read dynamic
       table statistics from storage engine enabling to read LDS.

FR-06  It must be possible for I_S system views to pick dynamic
       table statistics from TS_DD and IS_DD tables providing
       FDS.

FR-07  Enable MySQL users to pick either FDS or LDS based on a
       session variable.

FR-08  Implement new INTERNAL_UDF's which would retrieve LDS
       from storage engine. This would be used in new I_S system
       view definition.

FR-09  Implement INTERNAL_UDF's to do quick retrieval of
       TS/IS from storage engine by giving the table name. The mean
       of 'quick' here is to read statistics quicker compared to
       opening the underlying table using open_table() and then
       reading the statistics. Designing a new UDF will help avoid
       building complete TABLE_SHARE for a table.

       Any new SE API's required to read quick TS/IS values would
       be implemented as separate work.

FR-10  Store meta-data of I_S tables into DD during bootstrapping
       itself. As this information never changes, we can stored
       them once. This include I_S tables that are not system views too.

FR-11  Create I_S system views as part of bootstrapping
       procedure.

FR-12  Introduce new DD columns enhancing DD table definitions
       to store required information by I_S, Like 
       COLUMN_TYPE, DEFAULT_VALUE_UTF8, INDEX_ORDER, ROW_FORMAT.

FR-13  Implement SHOW command on temporary table to skip using
       I_S system views and use the current approach of creating
       temporary table and filling it during execution of query
       itself. There are temporary tables created by CREATE
       TEMPORARY TABLE command, whose metadata is not stored in DD
       tables.

FR-14  Introduce new MySQL extension I_S.TABLES.LAST_ALTERED
       column projecting the new information that is now
       available in DD i.e, mysql.tables.last_altered. Possibly
       we can enhance other I_S.* tables where data dictionary
       tables now contain the last_altered information.

FR-15  Allow I_S system views to be executed without waiting for
       concurrent writes to DD tables.

FR-16  Store mandatory plugin schema tables meta-data into DD
       tables upon bootstrap.

FR-17  Store dynamic plugin schema tables meta-data into DD table
       upon plugin install. And remove the same during plugin
       uninstall.

FR-18  Enable use of I_S system view under locked table modes.

FR-19  Make SHOW commands to read view column meta-data.

FR-20  Enable users to provide information schema and system view
       names in mix case letters and do not treat them as case
       sensitive, this is required to match the old behavior.

       The reason we need to handle this case is that the
       TABLES.TABLE_NAME maps to DD table column mysql.tables.name with
       the new implementation. Whose collation would make it
       depend on l_c_t_n. However, in 5.7 this was fixed to utf8_general_ci
       which enabled users to automatically do case insensitive
       comparison. Requirement is to try and match the old behavior.

       When user provide a I_S schema and table name, convert them
       to lowercase when possible. For e.g., we could do this when table
       name is added to open_table list by add_table_to_list(). This
       enables use to support part of old behavior but not complete.

       The restriction would be that I_S schema and table name
       provide in WHERE clauses of SHOW and I_S query would be treated
       with same collation requirements as that of mysql.tables.name.

FR-21  The 'System View' here is mostly treated by the MySQL server in
       the same way as that of user tables, except that we handle them
       in lock table mode a bit differently. See 2.1.11 for more info.

1. Architecture of I_S without DD:
==================================

I_S tables are implemented in MySQL as temporary tables which are created on
the fly during query execution. The existing architecture is that, this
temporary table is filled with required details read from,
  1) FRM files.
  2) Retrieved from SE, OR
  3) SQL server global data structures.


1.1 The low level details of current implementation is as below:
----------------------------------------------------------------

  ST_SCHEMA_TABLE structure represents I_S table and ST_FIELD_INFO
  represents fields of I_S table. These two data structures are use
  to create the temporary table for I_S. A array of ST_SCHEMA_TABLE
  object is used to represent 31 information schema tables related
  to SQL engine. 

  struct ST_SCHEMA_TABLE
  {
    const char* table_name;
    ST_FIELD_INFO *fields_info;

    // 1st level callback function
    int (*fill_table) (THD *thd, TABLE_LIST *tables, Item *cond);
    
    // 2nd level callback function
    int (*process_table) (THD *thd, TABLE_LIST *tables,
                          TABLE *table,
                          bool res, LEX_STRING *db_name,
                          LEX_STRING *table_name);
    
    ...
  }

  struct ST_FIELD_INFO
  {
    const char* field_name;
    uint field_length;
    enum_field_types field_type;
    int value;
    uint field_flags;
    ...
  }


  1.1.1 )

  'ST_SCHEMA_TABLE.fill_table' function pointer points to
  callback function which fills records into the I_S temporary
  table.

  Below are some of the I_S tables and its corresponding callback
  functions. There are mostly I_S tables that read the required
  information from MySQL data structures.
  -----------------------------------------------------
  I_S TABLE               | (*fill_table)()
  ------------------------------------------------------
  CHARACTER_SETS          | fill_schema_charsets
  COLLATIONS              | fill_schema_collation
  ENGINES                 | fill_schema_engines
  GLOBAL_STATUS           | fill_status
  GLOBAL_VARIABLES        | fill_variables 
  OPEN_TABLES             | fill_open_tables
  OPTIMIZER_TRACE         | fill_optimizer_trace_info
  FILES                   | hton_fill_schema_table
  COLUMN_PRIVILEGES       | fill_schema_column_privileges
  EVENTS                  | Events::fill_schema_events
  -------------------------------------------------------


  1.1.2 )

  I_S tables which reads meta-data of database objects like
  TABLES, TRIGGER, COLUMNS, VIEW uses callback function pointed to
  by 'ST_SCHEMA_TABLE.process_table' to read meta-data from given
  table.. As these I_S tables require list of all databases and
  their tables list from which the details are extracted, the
  function get_all_tables() is invoked at first.

  Below are list of callback function which are responsible to
  fill data into respective I_S tables.
  ------------------------------------------------------------------------
  I_S TABLE              |(*fill_table)()|(*process_table)()
  ------------------------------------------------------------------------
  COLUMNS                |get_all_tables |get_schema_column_record
  KEY_COLUMN_USAGE       |get_all_tables |get_schema_key_column_usage_record
  PARTITIONS             |get_all_tables |get_schema_partitions_record
  REFERENTIAL_CONSTRAINTS|get_all_tables |get_referential_constraints_record
  STATISTICS             |get_all_tables |get_schema_stat_record
  TABLES                 |get_all_tables |get_schema_tables_record
  TABLE_CONSTRAINTS      |get_all_tables |get_schema_constraints_record
  TRIGGERS               |get_all_tables |get_schema_triggers_record
  VIEWS                  |get_all_tables |get_schema_views_record
  ------------------------------------------------------------------------


  1.1.3 )

  Pseudo code for get_all_tables() function
  -----------------------------------------
  // 2nd argument TABLE_LIST *tables represents temporary table 'T' created to
  store resultset

  for ('database' in databases_list) (directory scan on DATA_DIR + I_S)
  {
    for ('table' in tables_list of 'database') (directory scan on DATA_DIR/<DB>)
    {
      if ('table' is a I_S table)
        - create temporary table 'TT' (creates .frm file for I_S)
        - open table 'TT'
        - Access meta-data using TABLE_SHARE,handler,handlerton,etc.
        - call (*process_table)() to store meta-data into 'T'
        - Present resulting rows to mysql client.
        - close table 'TT'
        - remove table 'TT' (removes .frm file)
      else
        - open table (open .frm file first time and then read from TABLE_CACHE) 
        - access meta info using TABLE_SHARE, handler, handlerton,etc of table
        - call (*process_table)() to store meta-data into 'T'
        - close table
     endif
    }
  }

2. Architecture of I_S with DD:
===============================

2.1 Design Overview
-------------------

  The approach of implementing new I_S table is to define I_S
  system view over DD tables, representing a I_S table. This will
  completely eliminate the need of preparing a temporary table for
  each I_S table during execution and other costs that are
  described in section 1) above.  I_S query execution would work
  same as executing a query on a view.

  Following are the changes involved:


  2.1.1 ) I_S tables that can be implemented as a System View (FR-01).

  The names of new system views that are implemented are listed in FR-01.
  Definition of each system view is listed in LLD.


  2.1.2 ) I_S tables whose implementation remains the same.

    ENGINES
    GLOBAL_STATUS
    GLOBAL_VARIABLES
    OPTIMIZER_TRACE
    PLUGINS
    PROCESSLIST
    PROFILING
    OPEN_TABLES
    SESSION_STATUS
    SESSION_VARIABLES
    SCHEMA_PRIVILEGES
    TABLE_PRIVILEGES
    COLUMN_PRIVILEGES
    USER_PRIVILEGES
    STATUS

  2.1.3 ) List of commands that would be re-implemented to use new
          I_S system views defined in 2.1.1. FR-02.

    - We prepare a SELECT_LEX representing a SELECT query over I_S system view
      by 'build_show_*()' function in sql/dd/show.cc. The approach is to prepare 
a
      Parse Tree node and let the executor execute it.


  2.1.4 ) Implementation of FDS and LDS. (FR-03..09)

    Following are the details of implementation to support
    FDS and LDS configuration over I_S system views.


    a) SQL server bootstrap will creates TS_DD and IS_DD
       tables during DD initialization.

    b) ANALYZE TABLE command updates TS_DD and IS_DD tables with
       TS/IS meta-data read from SE. This will be picked by I_S tables
       in FDS configuration.

    c) There are two version of each I_S system view which read
       TDS/IDS. This applies to I_S tables listed under 2.1.1 b)

       c1) I_S system view definition that joins with TS_DD and
           IS_DD to read FDS.

       c2) I_S system view definition that invokes new
           INTERNAL_UDF's to read LDS. These system view
           definitions are suffixed with text '_dynamic' in there
           view name.


    d) SQL server would switch between c1) and c2) configuration
       based on session variable 'information_schema_stats'. 
       This is implemented such that the 'add_table_to_list()' function
       would rename the I_S system view name picking the one
       required based on 'information_schema_stats' setting.

       SQL server will use c1) behavior by default which
       chooses the FDS configuration, that means that the
       'information_schema_stats' is set to 'cached' by default.

       E.g.,
       We have I_S.TABLES system view representing c1) and
       I_S.TABLES_DYNAMIC system view that represents c2) version.
       Where a user executes a statement with I_S table name
       e.g., 'SELECT * FROM INFORMATION_SCHEMA.TABLES', the parser
       invokes 'add_table_to_list()' function. This function intern
       checks if the 'information_schema_stats=latest', then replace
       the I_S table name from INFORMATION_SCHEMA.TABLES to
       INFORMATION_SCHEMA.TABLES_DYNAMIC. This same concept would be
       applied to other I_S tables in 2.1.1 b)

       Note that this behavior applies for SHOW commands implementation
       which uses I_S tables.


  2.1.5) I_S and concurrency (FR-15)

  2.1.6) Allow view creation under bootstrapping (FR-11)

    The new I_S system views should be created during
    bootstrapping similar to the way DD tables are created. This
    avoid possibility of SQL server picking wrong I_S system view
    definition, accidentally modified by user OR due to user
    using a old DB patch.

    TODO: The upgrade needs to handle drop and re-create
          the system views, mainly when upgrading from 8.0 to higher
          versions.


  2.1.7) Store meta-data of plugin schema tables. (FR-10,FR-16,FR-17)

    FR-10
    As described by Praba in 1.1.3 above, the 5.7 server would
    end-up creating a temporary table to retrieve I_S table metad-data
    and present that in output of I_S.TABLES. With FR-01
    implemented we cannot do the same.

    As the I_S tables schema does not change, we will store the
    meta-data of I_S tables during server bootstrapping procedure.


    FR-16:
    The 5.7 server would request meta-data of I_S system tables
    owned by storage engines upon execution of every I_S table listed
    in 2.1.1 a) & b). With FR-01 implemented the I_S system views
    cannot retrieve this meta-data from SE. I.e., 2.1.4 c1 and c2 would
    be modeled to read rows from SE during execution, which is
    not possible.

    As the SE schema tables meta-data is pretty much fixed and
    does not change, we would store such mandatory plugins schema
    table meta-data into DD tables during bootstrapping. This would
    save lot of time spent to read data from SE during execution. 


    FR-17 - TBD
    Dynamic plugins can own schema tables. On 5.7 these tables
    are requested by SQL server through SE API and the values are
    filled in by SE engine itself.

    With FR-01 implemented, a system view cannot read this
    information from SE. Hence the plan is to store the Plugin's
    schema table meta-data into DD table when the plugin is
    installed. And we remove the same when plugin is un-installed.


  2.1.8) Introducing new DD columns (FR-12)

    On 5.7 server we read meta-data of table from TABLE_SHARE
    prepared after opening the table. This TABLE_SHARE contains
    information like below, which are mostly not persistent as is in
    .FRM by are computed when the TABLE_SHARE is prepared. This
    information was not available in DD tables readily.

    So we added following new DD columns which are required by
    I_S tables.

      COLUMNS.COLUMN_KEY
      COLUMNS.COLUMN_TYPE
      COLUMNS.DEFAULT_VALUE_UTF8
      TABLES.ROW_FORMAT


  2.1.9) SHOW COLUMNS for temporary tables (FR-13)

    SHOW COLUMNS command would pick data from I_S system view
    after FR-01. This bring data from DD tables. However, the
    meta-data of temporary tables is not stored/persistent in the DD
    tables. So the meta-data of temporary table would not be seen by
    I_S system view.

    SHOW COLUMNS command on a temporary tables would skip using
    the I_S system view and fall-back to the old model of creating a
    temporary table and filling it with the data temporary table
    meta-data from the SQL server memory data structures.


  2.1.10) Introducing new I_S.TABLES.LAST_ALTERED field (FR-14)

    There is a plan to introduce this new I_S column as a MySQL
    extension.


  2.1.11) I_S query execution under locked table mode: (FR-18)

    I_S tables are temporary tables on 5.7. This make them
    possible to access under LOCK TABLE command with explicitly
    locking I_S tables. Now with FR-01 is implemented, the query
    execution procedure fails to process I_S system view as the under
    laying DD table are not locked.

    The SQL server is made to open the DD tables used by a I_S
    system view without requiring them to be locked explicitly.

  2.1.12) Quick retrieval [Avoid open_table()] of latest statistics
          using INTERNAL_UDF's (FR-08, FR-09)

    When 'information_schema_stats=latest' the TS/IS
    statistics are read from SE. The way we read these values on 5.7
    is by opening the underlying table and reading handler->info()
    values. If the UDF's in 8.0 does the same, the performance of I_S
    query on 8.0 will not improvement and would remain same.

    In effort to improve performance of I_S query with TS/IS
    data, new SE API's are designed (see LLD section 2) to request
    the TS/IS statistics based on the table name, without doing a
    full blown table open.

    These new SE API's are implemented by 'InnoDB' SE alone for
    now. So, the INTERNAL_UDF's would use these new SE API's only for
    'InnoDB' tables. For non-InnoDB tables, the INTERNAL_UDF's would
    following the same procedure as that of 5.7 to open the table and
    invoke handler->info() to read the TS/IS values.


3 Compatibility issues:
-----------------------
  Please refer main.dd_is_compatibility for test cases for each of
  below change in behavior.

  a) Requires execution of ANALYZE TABLE in FDS mode. I.e.,
     when 'information_schema_stats=cached'.

     Steps:
     1) start server.
     2) create a table.
     3) insert few rows.
     4) read I_S.TABLES dynamic statistics.

     If we carry on following steps on 5.7 server then we do get
     TS/IS read from SE.

     However on 8.0 server, step 4) would get NULL for all the
     dynamic statistics. The reason is that the default configuration
     of I_S system view is to pick the TS/IS values from TS_DD and
     IS_DD tables, which is based on
     'information_schema_stats=cached'. TS_DD/IS_DD tables
     needs to be updated by a explicit ANALYZE TABLE command. Without
     which the step 4) would result in NULL due to LEFT JOIN done
     in system view definition with TS_DD/IS_DD.

     The correct steps to get TS/IS on 8.0 would be:
     Steps:
     1) start server.
     2) create a table.
     3) insert few rows.
     4) run ANALYZE TABLE of the table.
     5) read I_S.TABLES dynamic statistics.


  b) The DD tables are listed in I_S query output.

     We will hide the DD table meta-data to be exposed by I_S queries.

  c) Capital cased I_S table column names.

     Upon execution of a I_S query, the resulting column names
     were in lower case on 5.7. However, on 8.0 with I_S system views,
     SQL server present the I_S table column name in capital letters.
     This is because optimizer does view merging instead of temp table
     algorithm, due to which the column names are changed to uppercase.

     One workaround to get column name with desired (lower/upper) case,
     is to use a alias for the projections from the query, e.g.,

     SELECT table_name as 'table_name' FROM INFORMATION_SCHEMA.TABLES;
     table_name
     ----------
     t1

     Whereas without the alias we get,

     SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
     TABLE_NAME
     ----------
     t1


  d) The order of row returned I_S query.

     On 5.7, the order of row returned by I_S query was predictable
     upon multiple execution of same query. With FR-01 being
     implemented, the problem is that the optimizer do not guaranty
     the order of rows returned from the SELECT.

     On 8.0, it is recommended to use ORDER BY clause on I_S query
     to order the rows based on users desire.


  e) CREATE_TIME stored in DD table.

     On 5.7, the CREATE_TIME of a table is not preserved by SQL
     server and it is requested to SE. Moreover, not all SE's do
     return CREATE_TIME.

     On 8.0, SQL server store the CREATE_TIME of table in
     mysql.tables.create_time.


  f) Removal of --ignore-db-dir option from server.

     This option was mostly added to avoid SQL server to read
     physical directories like lost+found. Now that 8.0 server do not
     depend of file-system folder to find databases, this option is
     removed.


  g) 'CREATE TABLE LIKE <I_S.system_view>' and
     'HANDLER COLUMNS OPEN on <I_S.system_view>'

     On 5.7, I_S tables are temporary tables and then the 'CREATE
     TABLE LIKE' and 'HANDLER COLUMNS OPEN' command would work on I_S
     tables.

     On 8.0, few I_S tables are system views and these two
     command expect the input to be a BASE table and not a view. This
     brings in a limitation.


  h) I_S schema name and the table name are treated as
     case-sensitive based on l_c_t_n setting, especially when these
     names are used in WHERE clause of SHOW commands. This is because
     the TABLES.TABLE_NAME maps to mysql.tables.name, which works based
     on l_c_t_n.


  i) Refering the behavior in FR-20, the restriction would
     be that I_S schema and table name provide in WHERE clauses
     of SHOW and I_S query would be treated with same collation
     requirements as that of mysql.tables.name.


  j) Prepared statements of SHOW commands fail differently for e.g.
     ER_BAD_DB_ERROR unknown schema, instead of ER_TOO_BIG_SELECT for
     big SELECT's.  This is consequence of we checking for existing
     database first and then JOIN::optimize(). In 5.7 this was done in
     the other way.


  k) ANALYZE TABLE under innodb read only mode fails with
     error/warning. This would be a restrictions with wl6599. It is
     recommended to use 'information_schema_stats=latest' to get latest
     statistics from IS queries in read only mode.


  l) Most of the INFORMATION_SCHEMA table are re-implemented as view.
     Current method of dumping INFORMATION_SCHEMA does not work
     for views.
     OTOH, INFORMATION_SCHEMA DB content dump is only used to reload the
     data into another tables for analysis purpose. This feature is not the 
     core responsibility of mysqlpump tool. INFORMATION_SCHEMA DB
     content can be dumped using other methods like SELECT INTO OUTFILE ...
     for such purpose. Hence ignoring INFORMATION_SCHEMA DB dump from
     mysqldump and mysqlpump tool.


  m) MySQL 'DISABLE KEYS' functionality is only applicable to MyISAM
     tables, but looks like its marked for InnoDB table too in trunk.
     This problem is fixed now. It has been discussed with Martin Hansson
     who worked on WL8697 which introduced invisible index. He agreed that
     the behavior on trunk is bad and needed to be fixed. We have fixed
     that now.

  n) In the 5.7 code, value for IS_UPDATABLE column of view is evaluated based
     on view is mergable or not and view has at least one fields which
     can be updated. This value is stored in the schema table.
     But there are many other factors to decide view is updatable or not.
     In 5.7 code too, these factors are checked while creating or 
     altering view but this value is not used while filling schema table.
     Due to which we were getting wrong IS_UDPATABLE for some views.
     Now we get correct is_updatable value for views as we
     read the value stored in dicationary tables while creating or altering
     view.

  o) Most of the INFORMATION_SCHEMA table are re-implemented as view as
     this WL. Current method of dumping INFORMATION_SCHEMA does not work
     for views.
     OTOH, INFORMATION_SCHEMA DB content dump is only used to reload the data
     into another tables for analysis purpose. This feature is not the 
     core responsibility of mysqlpump tool. INFORMATION_SCHEMA DB
     content can be dumped using other methods like SELECT INTO OUTFILE ...
     for such purpose. Hence ignoring INFORMATION_SCHEMA DB dump from
     mysqldump and mysqlpump tool.

  p) ANLYZE TABLE operation on non-transactional table was incompatible
     with FLUSH TABLES WITH READ LOCK. ANLYZE TABLE operation 
     used to wait for GLOBAL READ LOCK before.
     Now ANLYZE TABLE operations for transactional and non-transactional
     tables, ignores GLOBAL READ LOCK. So analyze table operation
     is compatible with FLUSH TABLES WITH READ LOCK now.
   
  q) In the 5.7 code, operation on INFORMATION_SCHEMA tables
     acquires MDL lock on the tables to open definitions from the
     data dictionary while filling schema table. With system view 
     implementation, as the rows are fetched from the new data dictionary   
     tables, MDL lock on the database objects are not acquired.
     This is true in most of the cases except when  
      "information_schema_stats=latest" and operation on INFORMATION_SCHMEA 
     table requires latest statstics values.
     Only in this case MDL lock of type MDL_SHARED_HIGH_PRIO is acquired on
     the database object for the explicit(operation) duration.
1 System view definitions implementing FR-01: (Mar 10 2016)
-----------------------------------------------------------

--
-- INFORMATION_SCHEMA.COLLATIONS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.collations 
AS
  SELECT col.name AS COLLATION_NAME,
         cs.name AS CHARACTER_SET_NAME,
         col.id AS ID,
         IF(EXISTS(SELECT * FROM mysql.character_sets
                            WHERE mysql.character_sets.default_collation_id= 
col.id),
            'Yes','') AS IS_DEFAULT,
         IF(col.is_compiled,'Yes','') AS IS_COMPILED,
         col.sort_length AS SORTLEN
  FROM mysql.collations col JOIN mysql.character_sets cs ON 
col.character_set_id=cs.id;

--
-- INFORMATION_SCHEMA.CHARACTER_SETS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW 
information_schema.character_sets as
  SELECT cs.name AS CHARACTER_SET_NAME,
         col.name AS DEFAULT_COLLATE_NAME,
         cs.comment AS DESCRIPTION,
         cs.mb_max_length AS MAXLEN
  FROM mysql.character_sets cs JOIN mysql.collations col ON 
cs.default_collation_id = col.id;

--
-- INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
  information_schema.collation_character_set_applicability AS
  SELECT col.name AS COLLATION_NAME,
         cs.name AS CHARACTER_SET_NAME
  FROM mysql.character_sets cs JOIN mysql.collations col ON cs.id = 
col.character_set_id;

--
-- INFORMATION_SCHEMA.SCHEMATA
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.schemata AS
  SELECT cat.name AS CATALOG_NAME,
    sch.name AS SCHEMA_NAME,
    cs.name AS DEFAULT_CHARACTER_SET_NAME,
    col.name AS DEFAULT_COLLATION_NAME,
    NULL AS SQL_PATH
  FROM mysql.schemata sch JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
       JOIN mysql.collations col ON sch.default_collation_id = col.id
       JOIN mysql.character_sets cs ON col.character_set_id= cs.id
  WHERE CAN_ACCESS_DATABASE(sch.name);

--
-- INFORMATION_SCHEMA.TABLES
--
-- There are two definitions of information_schema.tables.
-- 1. INFORMATION_SCHEMA.TABLES view which picks dynamic column
--    statistics from mysql.table_stats which gets populated when
--    we execute 'anaylze table' command.
--
-- 2. INFORMATION_SCHEMA.TABLES_DYNAMIC view which retrieves dynamic
--    column statistics using a internal UDF which opens the user
--    table and reads dynamic table statistics.
--
-- MySQL server uses definition 1) by default. The session variable
-- dynamic_information_schema_stats=true would enable use of definition 2).
--

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.tables AS
  SELECT cat.name AS TABLE_CATALOG,
    sch.name AS TABLE_SCHEMA,
    tbl.name AS TABLE_NAME,
    tbl.type AS TABLE_TYPE,
    IF(tbl.type  = 'VIEW', NULL, tbl.engine) AS ENGINE,
    IF(tbl.type = 'VIEW', NULL, 10 /* FRM_VER_TRUE_VARCHAR */) AS VERSION,
    tbl.row_format AS ROW_FORMAT,
    stat.table_rows AS TABLE_ROWS,
    stat.avg_row_length AS AVG_ROW_LENGTH,
    stat.data_length AS DATA_LENGTH,
    stat.max_data_length AS MAX_DATA_LENGTH,
    stat.index_length AS INDEX_LENGTH,
    stat.data_free AS DATA_FREE,
    stat.auto_increment AS AUTO_INCREMENT,
    tbl.created AS CREATE_TIME,
    stat.update_time AS UPDATE_TIME,
    stat.check_time AS CHECK_TIME,
    col.name AS TABLE_COLLATION,
    stat.checksum AS CHECKSUM,
    IF (tbl.type = 'VIEW', NULL, 
        GET_DD_CREATE_OPTIONS(tbl.options,
          IF(IFNULL(tbl.partition_expression,'NOT_PART_TBL')='NOT_PART_TBL', 0, 
1)))
        AS CREATE_OPTIONS,
    IF (tbl.type = 'VIEW', 'VIEW', tbl.comment) AS TABLE_COMMENT
  FROM mysql.tables tbl JOIN mysql.schemata sch ON tbl.schema_id=sch.id
       JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
       LEFT JOIN mysql.collations col ON tbl.collation_id=col.id
       LEFT JOIN mysql.table_stats stat ON tbl.name=stat.table_name
       AND sch.name=stat.schema_name
  WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden;

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW 
information_schema.tables_dynamic AS
  SELECT cat.name AS TABLE_CATALOG,
    sch.name AS TABLE_SCHEMA,
    tbl.name AS TABLE_NAME,
    tbl.type AS TABLE_TYPE,
    IF(tbl.type  = 'VIEW', NULL, tbl.engine) AS ENGINE,
    IF(tbl.type = 'VIEW', NULL, 10 /* FRM_VER_TRUE_VARCHAR */) AS VERSION,
    tbl.row_format AS ROW_FORMAT,
    INTERNAL_TABLE_ROWS(sch.name, tbl.name,
                        IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                        tbl.se_private_id) AS TABLE_ROWS,
    INTERNAL_AVG_ROW_LENGTH(sch.name, tbl.name,
                            IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                            tbl.se_private_id) AS AVG_ROW_LENGTH,
    INTERNAL_DATA_LENGTH(sch.name, tbl.name,
                         IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                         tbl.se_private_id) AS DATA_LENGTH,
    INTERNAL_MAX_DATA_LENGTH(sch.name, tbl.name,
                             IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                             tbl.se_private_id) AS MAX_DATA_LENGTH,
    INTERNAL_INDEX_LENGTH(sch.name, tbl.name,
                          IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                          tbl.se_private_id) AS INDEX_LENGTH,
    INTERNAL_DATA_FREE(sch.name, tbl.name,
                       IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                       tbl.se_private_id) AS DATA_FREE,
    INTERNAL_AUTO_INCREMENT(sch.name, tbl.name,
                            IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                            tbl.se_private_id) AS AUTO_INCREMENT,
    tbl.created AS CREATE_TIME,
    INTERNAL_UPDATE_TIME(sch.name, tbl.name,
                         IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                         tbl.se_private_id) AS UPDATE_TIME,
    INTERNAL_CHECK_TIME(sch.name, tbl.name,
                        IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                        tbl.se_private_id) AS CHECK_TIME,
    col.name AS TABLE_COLLATION,
    INTERNAL_CHECKSUM(sch.name, tbl.name,
                      IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
                      tbl.se_private_id) AS CHECKSUM,
    IF (tbl.type = 'VIEW', NULL, 
        GET_DD_CREATE_OPTIONS(tbl.options,
          IF(IFNULL(tbl.partition_expression,'NOT_PART_TBL')='NOT_PART_TBL', 0, 
1)))
        AS CREATE_OPTIONS,
    IF (tbl.type = 'VIEW', 'VIEW',
        INTERNAL_GET_COMMENT_OR_ERROR(tbl.comment)) AS TABLE_COMMENT
  FROM mysql.tables tbl JOIN mysql.schemata sch ON tbl.schema_id=sch.id
       JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
       LEFT JOIN mysql.collations col ON tbl.collation_id=col.id
  WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden;

--
-- INFORMATION_SCHEMA.COLUMNS
--

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.columns AS
  SELECT
    cat.name AS TABLE_CATALOG,
    sch.name AS TABLE_SCHEMA,
    tbl.name AS TABLE_NAME,
    col.name AS COLUMN_NAME,
    col.ordinal_position AS ORDINAL_POSITION,
    IFNULL (col.default_value_utf8, '') AS COLUMN_DEFAULT,
    IF (col.is_nullable = 1, 'YES','NO') AS IS_NULLABLE,
    SUBSTRING_INDEX(SUBSTRING_INDEX(col.column_type_utf8, '(', 1), ' ', 1) AS 
DATA_TYPE,
    INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 0) AS 
CHARACTER_MAXIMUM_LENGTH,
    INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 1) AS 
CHARACTER_OCTET_LENGTH,
    IF (col.numeric_precision = 0, NULL, col.numeric_precision) AS 
NUMERIC_PRECISION,
    IF (col.numeric_scale = 0 && col.numeric_precision = 0, NULL, 
col.numeric_scale) AS NUMERIC_SCALE,
    col.datetime_precision AS DATETIME_PRECISION,
    CASE col.type
      WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, cs.name))
      WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, cs.name))
      WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, cs.name))
      WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
      WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
      WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
      WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
      WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, cs.name))
      WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, cs.name))
      ELSE NULL
    END AS CHARACTER_SET_NAME,
    CASE col.type
      WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, coll.name))
      WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, coll.name))
      WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, coll.name))
      WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, coll.name))
      WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, coll.name))
      WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, coll.name))
      WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, coll.name))
      WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, coll.name))
      WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, coll.name))
      ELSE NULL
    END AS COLLATION_NAME,
    col.column_type_utf8 AS COLUMN_TYPE,
    col.column_key AS COLUMN_KEY,
    IF(IFNULL(col.generation_expression_utf8,'IS_NOT_GC')='IS_NOT_GC',
       IF (col.is_auto_increment=TRUE,
            CONCAT(IFNULL(CONCAT("on update ", col.update_option, " "),''),
                    "auto_increment"),
           IFNULL(CONCAT("on update ", col.update_option),'')),
      IF(col.is_virtual, "VIRTUAL GENERATED", "STORED GENERATED")) AS EXTRA,
    GET_DD_COLUMN_PRIVILEGES(sch.name, tbl.name, col.name) AS `PRIVILEGES`,
    IFNULL(col.comment, "") AS COLUMN_COMMENT,
    IFNULL(col.generation_expression_utf8, "") AS GENERATION_EXPRESSION
  FROM mysql.columns col JOIN mysql.tables tbl ON col.table_id=tbl.id
       JOIN mysql.schemata sch ON tbl.schema_id=sch.id
       JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
       JOIN mysql.collations coll ON col.collation_id=coll.id
       JOIN mysql.character_sets cs ON coll.character_set_id= cs.id
  WHERE CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name) AND NOT tbl.hidden;

--
-- INFORMATION_SCHEMA.STATISTICS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW 
information_schema.statistics_base AS
  (SELECT cat.name AS TABLE_CATALOG,
    sch.name AS TABLE_SCHEMA,
    tbl.name AS TABLE_NAME,
    IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE','0','1') AS NON_UNIQUE,
    sch.name AS INDEX_SCHEMA,
    idx.name AS INDEX_NAME,
    icu.ordinal_position AS SEQ_IN_INDEX,
    col.name AS COLUMN_NAME,
    CASE WHEN icu.order = 'DESC' THEN 'D'
         WHEN icu.order = 'ASC'  THEN 'A'
         ELSE NULL END AS COLLATION,
    GET_DD_INDEX_SUB_PART_LENGTH(icu.length, col.type, col.char_length,
                                 col.collation_id, idx.options) AS SUB_PART,
    NULL AS PACKED,
    if (col.is_nullable = 1, 'YES','') AS NULLABLE,
    CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL'
         WHEN idx.algorithm = 'SE_PRIVATE' THEN ''
         ELSE idx.algorithm END AS INDEX_TYPE,
    IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',
        '',IF(INTERNAL_KEYS_DISABLED(sch.name, tbl.name, 
tbl.options),'disabled', ''))
      AS COMMENT,
    idx.comment AS INDEX_COMMENT,
    IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
    idx.ordinal_position AS INDEX_ORDINAL_POSITION,
    icu.ordinal_position AS COLUMN_ORDINAL_POSITION,
    tbl.engine AS ENGINE,
    tbl.se_private_id AS SE_PRIVATE_ID
  FROM mysql.index_column_usage icu JOIN mysql.indexes idx ON 
idx.id=icu.index_id
    JOIN mysql.tables tbl ON idx.table_id=tbl.id
    JOIN mysql.columns col ON icu.column_id=col.id
    JOIN mysql.schemata sch ON tbl.schema_id=sch.id
    JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
    JOIN mysql.collations coll ON tbl.collation_id=coll.id
  WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden);

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.statistics 
AS
 (SELECT TABLE_CATALOG,
    TABLE_SCHEMA,
    sb.TABLE_NAME AS `TABLE_NAME`,
    NON_UNIQUE,
    INDEX_SCHEMA,
    sb.INDEX_NAME AS `INDEX_NAME`,
    SEQ_IN_INDEX,
    sb.COLUMN_NAME AS `COLUMN_NAME`,
    COLLATION,
    stat.cardinality AS CARDINALITY,
    SUB_PART,
    PACKED,
    NULLABLE,
    INDEX_TYPE,
    COMMENT,
    INDEX_COMMENT,
    IS_VISIBLE
  FROM information_schema.statistics_base sb
    LEFT JOIN mysql.index_stats stat
                 ON sb.table_name=stat.table_name
                and sb.table_schema=stat.schema_name
                and sb.index_name=stat.index_name
                and sb.column_name=stat.column_name);

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW 
information_schema.statistics_dynamic AS
 (SELECT TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    NON_UNIQUE,
    INDEX_SCHEMA,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    COLLATION,
    INTERNAL_INDEX_COLUMN_CARDINALITY(TABLE_SCHEMA,TABLE_NAME, INDEX_NAME,
                                      INDEX_ORDINAL_POSITION,
                                      COLUMN_ORDINAL_POSITION,
                                      ENGINE,
                                      SE_PRIVATE_ID)
      AS CARDINALITY,
    SUB_PART,
    PACKED,
    NULLABLE,
    INDEX_TYPE,
    COMMENT,
    INDEX_COMMENT,
    IS_VISIBLE
  FROM information_schema.statistics_base);

--
-- INFORMATION_SCHEMA.TABLE_CONSTRAINTS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW 
information_schema.table_constraints AS
  (SELECT cat.name AS CONSTRAINT_CATALOG,
          sch.name AS CONSTRAINT_SCHEMA,
          CONVERT(idx.name USING utf8) AS CONSTRAINT_NAME,
          sch.name AS TABLE_SCHEMA,
          tbl.name AS TABLE_NAME,
          IF (idx.type='PRIMARY', 'PRIMARY KEY', idx.type) AS CONSTRAINT_TYPE
    FROM mysql.indexes idx JOIN mysql.tables tbl ON idx.table_id = tbl.id
         JOIN mysql.schemata sch ON tbl.schema_id= sch.id
         JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
         AND idx.type IN ('PRIMARY', 'UNIQUE')
    WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden)
  UNION
  (SELECT cat.name AS CONSTRAINT_CATALOG,
          sch.name AS CONSTRAINT_SCHEMA,
          CONVERT(fk.name USING utf8)  AS CONSTRAINT_NAME,
          sch.name AS TABLE_SCHEMA,
          tbl.name AS TABLE_NAME,
          'Foreign Key' AS CONSTRAINT_TYPE
    FROM mysql.foreign_keys fk JOIN mysql.tables tbl ON fk.table_id = tbl.id
         JOIN mysql.schemata sch ON fk.schema_id= sch.id
         JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
    WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden);


--
-- INFORMATION_SCHEMA.KEY_COLUMN_USAGE
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW 
information_schema.key_column_usage AS
  (SELECT cat.name AS CONSTRAINT_CATALOG,
     sch.name AS CONSTRAINT_SCHEMA,
     CONVERT(idx.name USING utf8) AS CONSTRAINT_NAME,
     cat.name AS TABLE_CATALOG,
     sch.name AS TABLE_SCHEMA,
     tbl.name AS TABLE_NAME,
     col.name AS COLUMN_NAME,
     icu.ordinal_position AS ORDINAL_POSITION,
     NULL AS POSITION_IN_UNIQUE_CONSTRAINT,
     NULL AS REFERENCED_TABLE_SCHEMA,
     NULL AS REFERENCED_TABLE_NAME,
     NULL AS REFERENCED_COLUMN_NAME
   FROM mysql.indexes idx JOIN mysql.tables tbl ON idx.table_id = tbl.id
     JOIN mysql.schemata sch ON tbl.schema_id= sch.id
     JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
     JOIN mysql.index_column_usage icu ON icu.index_id=idx.id
     JOIN mysql.columns col ON icu.column_id=col.id
   WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden)
  UNION
  (SELECT cat.name AS CONSTRAINT_CATALOG,
     sch.name AS CONSTRAINT_SCHEMA,
     CONVERT(fk.name USING utf8) AS CONSTRAINT_NAME,
     cat.name AS TABLE_CATALOG,
     sch.name AS TABLE_SCHEMA,
     tbl.name AS TABLE_NAME,
     col.name AS COLUMN_NAME,
     fkcu.ordinal_position AS ORDINAL_POSITION,
     icu.ordinal_position AS POSITION_IN_UNIQUE_CONSTRAINT,
     fk.referenced_table_schema AS REFERENCED_TABLE_SCHEMA,
     fk.referenced_table_name AS REFERENCED_TABLE_NAME,
     fkcu.referenced_column_name AS REFERENCED_COLUMN_NAME
   FROM mysql.foreign_keys fk JOIN mysql.tables tbl ON fk.table_id = tbl.id
     JOIN mysql.foreign_key_column_usage fkcu ON fkcu.foreign_key_id=fk.id
     JOIN mysql.schemata sch ON fk.schema_id= sch.id
     JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
     JOIN mysql.columns col ON fkcu.column_id=col.id
     JOIN mysql.indexes idx ON fk.unique_constraint_id=idx.id
     JOIN mysql.index_column_usage icu ON idx.id=icu.index_id
   WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden);

--
-- INFORMATION_SCHEMA.VIEWS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.views AS
  SELECT cat.name AS TABLE_CATALOG,
    sch.name AS TABLE_SCHEMA,
    vw.name AS TABLE_NAME,
    vw.view_definition_utf8 AS VIEW_DEFINITION,
    vw.view_check_option AS CHECK_OPTION,
    vw.view_is_updatable AS IS_UPDATABLE,
    vw.view_definer AS DEFINER,
    IF (vw.view_security_type = 'DEFAULT', 'DEFINER', vw.view_security_type)
      AS SECURITY_TYPE,
    (SELECT cs.name
       FROM mysql.collations coll JOIN mysql.character_sets cs
                                  ON coll.character_set_id= cs.id
       WHERE coll.id=vw.view_client_collation_id) AS CHARACTER_SET_CLIENT,
    (SELECT coll.name
       FROM mysql.collations coll
       WHERE coll.id=vw.view_connection_collation_id) AS COLLATION_CONNECTION
  FROM mysql.tables vw JOIN mysql.schemata sch ON vw.schema_id=sch.id
       JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
  WHERE vw.type = 'VIEW' AND CAN_ACCESS_TABLE(sch.name, vw.name);


--
-- INFORMATION SCHEMA VIEWS implementing SHOW statements
--

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW 
information_schema.show_statistics AS
  (SELECT
    TABLE_SCHEMA as `Database`,
    sb.TABLE_NAME AS `Table`,
    NON_UNIQUE AS `Non_unique`,
    sb.INDEX_NAME AS `Key_name`,
    SEQ_IN_INDEX AS `Seq_in_index`,
    sb.COLUMN_NAME AS `Column_name`,
    COLLATION AS `Collation`,
    stat.cardinality AS `Cardinality`,
    SUB_PART AS `Sub_part`,
    PACKED AS `Packed`,
    NULLABLE AS `Null`,
    INDEX_TYPE AS `Index_type`,
    COMMENT AS `Comment`,
    INDEX_COMMENT AS `Index_comment`,
    IS_VISIBLE AS `Visible`,
    INDEX_ORDINAL_POSITION,
    COLUMN_ORDINAL_POSITION
  FROM information_schema.statistics_base sb
    LEFT JOIN mysql.index_stats stat
                 ON sb.table_name=stat.table_name
                and sb.table_schema=stat.schema_name
                and sb.index_name=stat.index_name
                and sb.column_name=stat.column_name);


CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW 
information_schema.show_statistics_dynamic AS
  (SELECT
    TABLE_SCHEMA as `Database`,
    TABLE_NAME AS `Table`,
    NON_UNIQUE AS `Non_unique`,
    INDEX_NAME AS `Key_name`,
    SEQ_IN_INDEX AS `Seq_in_index`,
    COLUMN_NAME AS `Column_name`,
    COLLATION AS `Collation`,
    INTERNAL_INDEX_COLUMN_CARDINALITY(TABLE_SCHEMA, TABLE_NAME, INDEX_NAME,
                                      INDEX_ORDINAL_POSITION,
                                      COLUMN_ORDINAL_POSITION,
                                      ENGINE,
                                      SE_PRIVATE_ID)
      AS `Cardinality`,
    SUB_PART AS `Sub_part`,
    PACKED AS `Packed`,
    NULLABLE AS `Null`,
    INDEX_TYPE AS `Index_type`,
    COMMENT AS `Comment`,
    INDEX_COMMENT AS `Index_comment`,
    IS_VISIBLE AS `Visible`,
    INDEX_ORDINAL_POSITION,
    COLUMN_ORDINAL_POSITION
  FROM information_schema.statistics_base);



2. Definitions of new data dictionary tables added by this WL :
---------------------------------------------------------------

  CREATE TABLE `table_stats` (
    `schema_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `table_rows` bigint(20) unsigned DEFAULT NULL,
    `avg_row_length` bigint(20) unsigned DEFAULT NULL,
    `data_length` bigint(20) unsigned DEFAULT NULL,
    `max_data_length` bigint(20) unsigned DEFAULT NULL,
    `index_length` bigint(20) unsigned DEFAULT NULL,
    `data_free` bigint(20) unsigned DEFAULT NULL,
    `auto_increment` bigint(20) unsigned DEFAULT NULL,
    `checksum` bigint(20) unsigned DEFAULT NULL,
    `update_time` timestamp NULL DEFAULT NULL,
    `check_time` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`schema_name`,`table_name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

  CREATE TABLE `index_stats` (
    `schema_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `column_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `cardinality` bigint(20) unsigned DEFAULT NULL,
    UNIQUE KEY `schema_name` 
(`schema_name`,`table_name`,`index_name`,`column_name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;



3. New SE API's introduced:
--------------------------

  The following new handlerton API's are introduced to read the
  table statistics from storage engine. This is currently implemented
  only for 'InnoDB' engine.


  /**
    @brief
    Retrieve ha_statistics from SE.

    @param1 db_name                  Name of schema
    @param2 table_name               Name of table
    @param3 se_private_id            SE private id of the table.
    @param4 flags                    Type of statistics to retrieve.
    @param5 stats                    (OUT) Contains statistics read from SE.

    @returns false on success,
             true on failure
  */
  bool (*get_table_statistics)(const char *db_name,
                               const char *table_name,
                               dd::Object_id se_private_id,
                               uint flags,
                               ha_statistics &stats);

  /**
    @brief
    Retrieve index column cardinality from SE.

    @param1 db_name                  Name of schema
    @param2 table_name               Name of table
    @param3 index_name               Name of index
    @param4 index_ordinal_position   Position of index.
    @param5 column_ordinal_position  Position of column in index.
    @param6 se_private_id            SE private id of the table.
    @param7 cardinality              (OUT) cardinality being returned by SE.

    @returns false on success,
             true on failure
  */
  bool (*get_index_column_cardinality)(const char *db_name,
                                       const char *table_name,
                                       const char *index_name,
                                       uint index_ordinal_position,
                                       uint column_ordinal_position,
                                       dd::Object_id se_private_id,
                                       ulonglong *cardinality);

4. Change made in mysqlpump tool:

    With the introduction of new information schema views on top
    of new data dictionary, the way the SHOW command works is
    changed. We now have two ways of SHOW command picking table
    statistics.

    One is to read it from DD table mysql.table_stats and
    mysql.index_stats. For this to happen, we need to execute
    ANALYZE TABLE prior to execution of mysqlpump tool.  As the
    tool can run on whole database, we would end-up running
    ANALYZE TABLE for all tables in the database irrespective of
    whether statistics are already present in the statictics
    tables. This could be a time consuming additional step to
    carry.

    Second option is to read statistics from SE itself. This
    options looks safe and execution of mysqlpump tool need not
    care if ANALYZE TABLE command was run on every table. We
    always get the statistics, which match the behavior without
    data dictionary.

    The first option would be faster as we do not opening the
    underlying tables during execution of SHOW command. However
    the first option might read old statistics, so we feel second
    option is preferred here to get statistics dynamically from
    SE by setting information_schema_stats=latest for this
    session, which would match the old behavior in 5.7.