WL#9461: Upgrade for Innodb Internal Dictionary Migration

With the implementation of Data Dictionary for Server, InnoDB will remove its
internal Data Dictionary. InnoDB will store its metadata in Server Data
dictionary. Dictionary provides se_private_id and se_private_data columns to be
used by Storage Engines.

For the user to upgrade from older mysql versions, a way is needed to migrate
the contents of InnoDB internal dictionary to the Data Dictionary. This includes
all the innodb internal metadata related to :
- tablespaces
- tables
- indexes
- Foreign keys, currently stored by InnoDB dictionary only.

The scope of the worklog:
- This worklog will deal with the in place (live) server version upgrade from 
  mysql-5.7(all GAed versions), to mysql-8.0 with InnoDB internal dictionary 
  removed.
- http://wl.no.oracle.com/worklog/Server-Sprint/?tid=6392#Upgrade_Prerequisites 
  mentions all the upgrade pre requisites. 
- WL#6392 has implemented upgrade from mysql-5.7 to mysql-8.0. In the scope of 
  WL#6392, workarounds were implemented for the tasks which were needed to be
  implemented by Storage Engines. For example : In mysql-5.7, Storage engines 
  stores Foreign key information. In implemenation of WL#6392, Foreign key 
  information was fetched from storage engine to store in server data dictionary.
  This worklog will remove above implementation and provide handler API to be 
  called for each table. Storage engine will take the responsibility to populate
  Foreign key information.
- Major or minor version upgrade of DD will be dealt with as separate work.
- Downgrade from mysql-8.0 to a lower version which do not support DD is not in
  the scope of this worklog.

User Documentation
==================

No user-visible effects. No user documentation required.

Functional Requirements
=======================

i>  Server should automatically detect if started on an old data directory 
    (created with mysql 5.7 or earlier and do not contain DD tables) which is 
    not upgraded. If DD tables are not present in data directory, it is 
    treated as a case of upgrade from 5.7 server. If DD tables are present in 
    data directory, it is treated as case of mysql-8.0 server restart.

ii> Upgrade from only mysql-5.7 GAed version is supported.
    In case of trying to do in place upgrade of data directory created with  
    - mysql-5.6 or earlier versions
    - DMR version of mysql-8.0
    server should refuse to start/upgrade with an error.

iii> Storage engine specific metadata for all database objects from mysql-5.7 
     should be upgraded to new DD.
     Database object refers to
     - Tablespaces
     - Tablespace files
     - System tables
     - User tables
     - Indexes

     This worklog will provides handlerton and handler API's to be used by 
     Storage engines while populating data dictionary. Storage engines need to 
     implement these APIs to get the engine specific data to the server data  
     dictionary. 

     InnoDB will implement these API's to migrate internal dictionary
information to server data dictionary. 

iv> Meta data should not be lost or corrupted during upgrade process.

v>  Upgrade process should handle partial upgrade.
    Partial upgrade here refers to a failure before the upgrade process is 
    complete. This can happen due to a corrupted .frm file, error in parsing 
    of Triggers, error in reading  mysql.proc/event table, server kill, etc.
    If there is an error while upgrading, upgrade should either revert all its 
    changes to the data directory OR resume migration from point of failure 
    to completion.

vi> It is not necessary to have mysql.innodb_table_stats and 
    mysql.innodb_index_stats tables in mysql-5.7. Upgrade should tolerate this 
    condition. Upgrade should not error out if stats tables are not found in 
    data directory.

Get Rid of InnoDB internal Dictionary Information
==================================================

Some storage engines (Innodb, NDB) store their own dictionary information in
addition to the  meta data stored in server. In mysql-5.7 InnoDB storage engine
has its own dictionary for meta data. Redundant dictionary information has led
to complexity in keeping it in sync with FRM file. 

In mysql-8.0, it is planned to remove InnoDB internal dictionary. InnoDB will
store and retrieve metadata from the global DD. For the tables created in
mysql-5.7, new DD tables will not have this information. This requires to gather
InnoDB related dictionary information from 5.7 to be stored in DD while
upgrading to 8.0 server. 

While upgrading from 5.7 to 8.0, storage engine related information to be fetched is

i> Tablespace related information 

ii> Table information: Information needed from SE at table level includes row 
                       type, foreign key information, partition information, 
                       and storage engine specific information.

iii> Index information: Storage engine specific information.

Upgrade Preconditions
======================
This worklog will not introduce any  new pre requisites for upgrade at server
level.

Metadata Source
================

Storage engines's internal dictionary will be source for the se_private_id and
se_private_data. For InnoDB tables, INNODB_SYS_* data should be preferred over
FRM file data. It is because FRM file is created before InnoDB creates its
internal files. In that case, InnoDB might change or ignore some data. For
example, if we add KEY_BLOCK_SIZE=8 option to InnoDB table in Antelope file
format, InnoDB will internally ignore this option. InnoDB page size will be 16K
but, FRM file stores 8K for this.

Upgrade Flowchart:
==================
Flowchart of Upgrade from mysql-5.7 to a version with global data dictionary
after InnoDB changes will be as follows :

1.  Slow shutdown 5.7 server.
2.  Start 8.0 server on same data directory.
3.  Check if we are upgrading or restarting.
4.  Create a temporary file to record stages of upgrade to handle server crash 
    in case server is upgrading.
5.  Check that InnoDB undo log is empty, else empty undo log. 
6.  Create Dictionary tables in old data directory.
7.  Update the temporary file maintaining stages of upgrade to mark that data 
    dictionary tables are created. This is important as InnoDB does not create 
    entry in undo logs for dictionary table creation.
8.  Initialize all stages of dictionary.
9.  Upgrade schema, i.e., create dictionary entry for schema.
10  Upgrade tablespaces, i.e., create dictionary entry for tablespace.
11. In upgrading table, get se_private_data and foreign key data from storage 
    engine.
12. Upgrade views without fixing view dependency.
13. Upgrade Events.
14. Upgrade SP/SF.
15. Fix view dependency.
16. Create a marking that we have migrated all meta data from other storage 
    engines to dictionary. It is done in temporary file created to record 
    progress of upgrade.

    InnoDB will change .ibd files after this stage. Even if server is killed 
    after this step, upgrade will only roll forward and complete only steps 
    after this ( step 18 onwards). Upgrade can't roll back its changes after 
    this step is complete.

17. Iterate over all tablespaces and tables to create SDI information.
18. Cleanup after upgrade, delete all .frm, .TRG, .TRN, .opt and other files 
    used by mysql-5.7. Drop mysql.proc and mysql.event system tables used by 
    mysql-5.7. Delete the temporary file created to record progress of upgrade.

Upgrade and Restart
====================
Server and InnoDB needs to know if it is upgrade or normal server restart before
innodb initializes. As any check based on dictionary tables is not possible, it
is proposed to rely on presence of mysql.ibd (dictionary tablespace) and
mysql/plugin.frm file to decide if the server is upgrading or restarting.

If mysql.ibd is present, it will be treated as normal server restart.
If mysql.ibd is not present and mysql/plugin.frm is present, it will be treated
as upgrade on mysql-5.7 data directory.
If neither mysql.ibd or mysql/plugin.frm is present, server will error out.

Tablespace Upgrade
==================
In mysql-5.7, server does  not store tablespace specific metadata. It is used by
only SE (InnoDB and NDB). Server will provide a handlerton layer API to enable
SE to add tablespace entry in dictionary. Tablespace metadata migration to
dictionary should be done before starting with table metadata migration.

Handlerton API signature:

int (*upgrade_tablespace_t)(THD *thd);

InnoDB will implement this API to read tablespace metadata from internal system
tables and store in mysql.tablespaces dictionary table. Tablespace files should
be handled with tablespaces in this API.

Other Handlerton APIs
=====================
Server will provide following  handlerton API's to be called during/after
upgrade process. Server will not implement these API's. Storage engines should
implement these API's according to the requirement. InnoDB will implement these
APIs separately.

i> int (*upgrade_logs_t)(THD *thd);

   Upgrade innodb undo logs after upgrade. Also increment the table_id
   offset by DICT_MAX_DD_TABLES. This offset increment is because the
   first 256 table_ids are reserved for dictionary. 

   This API will be called before Step 14 in the above flowchart. Storage 
   engines can use this API to mark the checkpoint and any changes after this 
   API call will be persisted by the server. 

ii> int (*finish_upgrade_t)(THD *thd, bool failed_upgrade);
    This API is used to flush InnoDB dirty pages to disk after upgrading but 
    before marking upgrade complete. Different Storage engines can implement it 
    for the cleanup after upgrade but before server restart continues.

    In case of server kill during upgrade, this API is used to tell storage 
    engines in next restart to perform a cleanup or set appropriate flags to do 
    so.

Table Upgrade
=============
Table upgrade is done based on FRM files. Only the tables for which .frm file is
present will be upgraded to work with dictionary. To fill se_private_id and
se_private_data for tables, a new handler API will be added.
While upgrading, the handler API will provide
- se_private_id for dd::Table (mysql.tables)
- se_private_data for dd::Table(mysql.tables)
- se_private_data for dd::Index (mysql.indexes)
- Foreign key information (mysql.foreign_keys and mysql.foreign_key_columns)

Handler API Signature:  

bool handler::ha_upgrade_table(THD *thd,
                                       const char* dbname,
                                       const char* table_name,
                                       dd::Table *dd_table,
                                       TABLE *table_arg)

For InnoDB, internal dictionary will be used to get se_private_id,
se_private_data and Foreign key information. InnoDB will implement the handler
function to read internal system tables in case of upgrade. It will create and
fill dict_table_t structure. dict_table_t structure has all the internal InnoDB
metadata information. It is used to fill se_private_data, Foreign key
information, full text index information and partition information during
upgrade. This work be handled separately. This worklog will only
handle defining the API to be implemented by Storage engines.

Handling of innodb_index_stats and innodb_table_stats tables
=============================================================
mysql.innodb_table_stats and mysql.innodb_index_stats are present in mysql-5.7.
These tables are system tables in mysql-5.7 but dictionary tables in mysql-8.0.
During upgrade, handling of these tables will be as follows:
- Rename mysql\innodb_index_stats.ibd and and mysql\innodb_table_stats.ibd
  to mysql\innodb_index_stats_backup57.ibd and 
  mysql\innodb_table_stats_backup57.ibd. 
- Create mysql.innodb_table_stats and mysql.innodb_index_stats along with other
  dictionary tables.
- Perform upgrade (migration of metadata) for stats tables as normal 
  user table. If stats tables are encountered, rename table_name (by adding 
  '_backup_57' suffix) before storing in them in dictionary tables.
- After all tables have been upgraded, execute following query to populate 
  statistics for the tables:

  INSERT IGNORE INTO mysql.innodb_table_stats SELECT * FROM 
  mysql.innodb_table_stats_backup57;

Note: 

- It is not necessary to have mysql.innodb_table_stats and 
  mysql.innodb_index_stats tables in mysql-5.7. Upgrade should tolerate this 
  condition. Upgrade should not error out if stats tables are not found in data
  directory.

Partial Upgrade and Error handling
==================================

Checking for Partial Upgrade : A temporary file 'mysql_dd_ugrade_info' will be
created in data directory to track the progress of upgrade.

The stages recorded in mysql_dd_ugrade_info file are:
1> Stage O : mysql_dd_ugrade_info file is created. It indicates server is 
   staring the dictionary upgrade process on mysl-5.7 data directory.
   This indicates completion of stage 3 from upgrade flowchart(above).
2> Stage I : InnoDB is booted in upgrade mode, redo and undo logs are upgraded,
   mysql tablespace is created.
3> Stage II: Dictionary tables are created.
4> Stage III: Dictionary initialization is complete and upgrade will process 
   user table now.
5> Stage IV: Upgrade of user tables will complete.
6> Stage V: SDI information for tables is created and stored.
'mysql_dd_ugrade_info' file is deleted as part of cleanup after upgrade is
successful.

mysql.ibd and mysql_dd_ugrade_info will be used for handling partial upgrade.

- If mysql.ibd file is not present and mysql_dd_ugrade_info file is not 
  present, we are operating on an old data directory and upgrade process has 
  not started. 
- If mysql.ibd is present and mysql_dd_ugrade_info file is present, and 
  mysql_dd_ugrade_info has recorded completion till stage III or lower of 
  upgrade, it is the case of partial upgrade. Upgrade will revert all changes 
  in data directory (delete dictionary tables and all .SDI files) and exit.
- If mysql.ibd is present and mysql_dd_ugrade_info file is present, and 
  mysql_dd_ugrade_info has recorded completion of Stage IV or V, it is the 
  case of partial upgrade. Upgrade will roll forward and finish all upgrade 
  process, do a clean up after upgrade and start the server.
- If mysql.ibd is present and mysql_dd_ugrade_info is not present, it will be 
  treated as normal server restart case.

Rollback of Partial Upgrade:
- If the case of partial upgrade is found all changes to the data directory  
  will be reverted. This involves:
    - Deleting all SDI files created.
    - Deleting all DD tables created.
      Note : Individual dictionary tables will not be deleted as part of 
             cleanup. Instead, mysql.ibd will be deleted.
- User will get error message that upgrade process is incomplete and changes 
  to the Data directory would be reverted. User should retry the upgrade 
  process.

Error handling:
If upgrade can not finish successfully due to an error, upgrade will error out.
It will perform the cleanup to allow users to start 5.7 server again on same
data directory. 


Handling SDI information
========================

SDI files will not be created for tables while creating dictionary entry.
Upgrade process can not be rolled back once it reached to step 14 mentioned in
above flowchart. To create SDI information for all tables:
- Traverse in loop for all tablespace entries in mysql.tablespaces and call 
  handlerton API to modify tablespaces to add SDI flags.
- Traverse in loop for all table entries in mysql.tables and call API to add 
  SDI information to tablespaces. SDI for non-innodb tables will also be 
  created and written to file in this step. 
 
Cleanup
========
In mysql-8.0 InnoDB internal system tables will be discarded and hence cannot be
accessed. However, the information still exists within data directory (iddata1).
Innodb sys tables are not exposed to SQL layer. In mysql-8.0, InnoDB will treat
them as orphaned tables. These tables will be deleted as part of cleanup after
upgrade is successful. Server will delete following files as part of cleanup:

- FRM files
- TRG and TRN files
- OPT files
- PAR files

mysql.event and mysql.proc tables from mysql-5.7 will be deleted.
handlerton API finish_se_upgrade() will be called after successful upgrade to
let storage engines do the cleanup on their part

Downgrade Scenarios:
=====================
We don't support inplace downgrade. mysqldump tool should be used to
downgrade from mysql-8.0 to mysql-5.7.