WL#6392: Upgrade to Transactional Data Dictionary

Affects: Server-8.0 — Status: Complete

Description
Requirements
Dependent Tasks
High Level Architecture

Introduction

With the implementation of the new data dictionary, the metadata will be stored
in the InnoDB tables. For the users to upgrade from the older MySQL
version(5.7), a provision needs to be made which will help in

- migrating the metadata from mysql-5.7 to DD tables in 8.0.
- Remove .frm, .par, .TRG, .TRN, other files which are currently used for 
  metadata storage.

Definitions

DD             : New Data Dictionary.
dd::<tables>   : Data dictionary tables like dd::tables, dd::columns.

"upgrade" and "migrate" have been used alternately in the worklog page.

Worklog goals and scope

- This worklog will deal with the in place (live) server version upgrade from 
  mysql-5.7, which stores metadata in files (.frm, .TRG, ...) and some in MyISAM
  tables (mysql.proc, mysql.events) to mysql-8.0, which implements New DD.
- Major or minor version upgrade of DD is not in scope of this worklog.
- Downgrade from mysql-8.0 to a lower version which do not support DD is not in
  the scope of this worklog. See detailed discussion below.
- Migration of InnoDB internal dictionary to DD is not in scope of this worklog.

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 Process started on a data directory containing obsolete data types 
    and tables using non-native partitioning will will be aborted with an 
    error. 

iii> metadata for all database objects from mysql5.7 should be upgraded to new 
     DD.
     Database object refers to
    - Databases
    - Tablespaces
    - System tables
    - User tables
    - Views
    - Triggers
    - Stored procedures 
    - Stored Functions 
    - Events

iv>  Tables used for storing below objects are not considered DD tables
    - UDFs
    - plugins
    - users
    - privileges
    - Performance Schema
    - Sys Schema ...
    These are stored in the same table in mysql-5.7 and mysql-8.0. Any 
    change in the structure and storage engine of these tables will be taken 
    care by mysql_upgrade tool in mysql-8.0. There should not be any error 
    regarding structure of system tables in the error log when server is 
    restarted after executing mysql_upgrade tool.

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

vi>  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 .

vii> Upon successful upgrade, the metadata files should be moved to a new 
     directory within the data directory as backup.

viii> mysql.proc and mysql.event tables will not be automatically deleted by 
      upgrade process. They will be renamed as part of cleanup.
      User should delete them manually if needed.

Non Functional Requirements

-  The process should be extensible to take care of any change in the design 
   of the DD.

WL#6380: Formulate framework for API for DD
WL#7069: Provide data dictionary information in serialized form

SE-specific Dictionary Information and the scope of this WL


Some storage engines like Innodb store their own dictionary information in 
addition to the  meta data stored in server. This redundant dictionary 
information has led to complexity in keeping it in sync with FRM file. 

In mysql-8.0, the dictionary information for MySQL server and InnoDB will be 
collated and held with InnoDB tables as global DD.  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. 

Hence, InnoDB related dictionary information has to be migrated from 5.7 to DD 
while upgrading to 8.0 server. The migration of metadata from InnoDB Internal Dictionary to DD has been moved to a separate task . However when choosing approach for upgrade process we need to keep upgrade of SE-specific metadata  in mind, so it can integrate easily into the upgrade process implemented by this WL.

Possible Approaches for the Upgrade Process

Metadata Dump from 5.7

This approach deals with taking a dump of meta data from mysql-5.7 in an
intermediate file. mysql-8.0 will use this to populate DD tables while 
upgrading.

Using .SQL as Intermediate file
-------------------------------
One way to implement upgrade process is to take dump of SHOW CREATE statement 
output from mysql-5.7 to be used in mysql-8.0 to recreate metadata of different 
database objects. This approach has following disadvantages:

- It assumes that CREATE TABLE statement will generate EXACTLY the same 
  table definitions in both 5.7 and 8.0 (e.g. with the same options, binary 
  format and so on). Our experience with 5.6/5.7 shows that this is not 
  always the case (new temporals in 5.6, InnoDB POINT type in 5.7). So this 
  approach is going to be really fragile against any changes in CREATE TABLE 
  semantics in 8.0.
- We need to instrument implementation of DDL execution to keep the tables from 
  being created in the SE. This may be messy because we have no mechanism for 
  deciding this in the same way as we have for DD tables. For example, while 
  upgrading we dont want SE to process the  CREATE TABLE statements for the 
  user tables but when mysql_upgrade tool is executed, it uses CREATE TABLE  
  statement to create P_S and other system tables. We will need to 
  differentiate these scenario.
- Adding an additional mechanism for reading and parsing SE-specific information
  from dumps based on data from INNODB_SYS_* tables. This is a mechanism used 
  only for intermediate format with the usual probability of bugs we have in 
  new code.It would  be better to use one of the other import mechanisms and 
  formats that we will also use for other purposes.
- DDL changes should be restricted after dump is taken.


Using .SDI as Intermediate format
---------------------------------
SDI stands for Serialized Dictionary Information. It will be introduced with 
new DD. SDI file can be imported by 8.0 server to fill DD tables.

For upgrade, SDI files should be prepared using 5.7 server.The mysql-8.0 server 
can import these SDI files to populate the DD tables.
 
This approach has following disadvantages.
- It creates a dependency of 5.7 server on 8.0 server for DD related code.
- 5.7 server does not have DD related classes and code
- DDL changes should be restricted after SDI is created in mysql-5.7.

Using an embedded Tool

An embedded tool based on mysql-8.0 can be used for upgrade. Along with the 
server initialization, DD tables will be created. Following which the DD API's 
are used to populate DD tables with meta data read from the old data directory.
 
This approach has following disadvantages.
- Embedded tool is large in size.
- Running the tool is an overhead during the usual upgrade process.

Direct upgrade

Direct upgrade refers to starting mysql-8.0 on a data directory used by 
mysql-5.7 directly.
Server will create DD tables in old data directory and populate data in DD 
tables from various metadata sources described below. After that server will 
continue to start.

It has been decided to go with direct method of upgrade because it provides a 
better user experience.

Direct upgrade details

Upgrade Prerequisites

Pre requisites for upgrade from 5.7 to 8.0 are:

- Before upgrade, the 5.7 server must be shut down with
innodb_fast_shutdown=0. This will make sure that InnoDB undo logs are empty.
This is not explicitly checked in the upgrade process.

- DD table existence check. There should not be any tables with the same
name as DD tables to be created. Upgrade process checks for existence
for these tables and report appropriate errors.

- There are several data types that are obsolete and deprecated. There should
not be any old data types present in the data directory while upgrading.
Upgrade process checks for obsolete and deprecated data types and report
appropriate errors.

- There should not be any partitioned tables without native engine partition
support.
Upgrade process checks for use of partition engine without native partition
support and report appropriate errors.

- Upgrade from 5.7.11 which contains encrypted data files requires an
additional step. ALTER INSTANCE ROTATE INNODB MASTER KEY should be executed.

- definer_user and definer_host should simultaneously contain valid strings
(SUID) for triggers. In other words, definer_user and definer_host should
not contain simultaneously NULL-strings (non-SUID). Upgrade process will
enforce this check.

- F-8 from "InnoDB currently supports FK name larger than 64 characters, so this is a change of behaviour in that respect in order to get better identifier length consistency." Upgrade process will enforce this check. Checks has been added in the foreign key migration to check that foreign key name is less than 65 characters.

The user is highly recommended to check that the above pre-requisites are
met, and correct situation if not, before shutting down his MySQL 5.7
instance. "See Upgrade Steps for user section for details."

The checks which are supported by upgrade process will be performed on all
tables and all the issues will be reported to the user at once. If one or more
upgrade checks fail, upgrade will revert back the changes done on the data directory.

The checks are :

File existence check in mysql schema

DD creates table in mysql schema. Before creating DD tables, mysql schema will 
be scanned to check if any tables with the same name as DD tables to be created.

DD table names are :

version
character_sets                     
collations                  
tablespaces                 
tablespace_files            
catalogs                    
schemata                    
st_spatial_reference_systems
tables                      
view_table_usage   
view_routine_usage         
columns                     
indexes                     
index_column_usage          
column_type_elements        
foreign_keys                
foreign_key_column_usage    
table_partitions            
table_partition_values      
index_partitions            
events                      
routines                    
parameters                  
parameter_type_elements     
innodb_table_stats          
innodb_index_stats                        
triggers
table_stats
index_stats 

Out of these, innodb_table_stats.ibd and innodb_index_stats.ibd are allowed as 
they do exist as in mysql-5.7. Upgrade will fail if any other .frm file is 
found with the name as the above tables.

No Obsoleted data types

There are several data types that are obsolete which will be removed from trunk.
This means that server will not recognize these obsolete data types. 

Before upgrading to mysql-8.0, the old data types should be upgraded to new 
supported data types. This can be done by using REPAIR TABLE / ALTER TABLE 
statement.

Old temporal types, TIME/DATETIME and TIMESTAMP types - were obsoleted by
the introduction of temporals with microsecond support in 5.6. There should not 
be any old temporal data types in the tables when upgrading to mysql-8.0.

Native Partition Support

Partition storage engine is deprecated in mysql-5.7 and removed in mysql-8.0. 
Now storage engines will implement native partitioning. There should not be any 
partitioned tables of engines without native partition support in data 
directory.

Other Checks

Presence of Trigger's definers will be checked in .TRG file. 
Limit on FK name length will be checked while doing table upgrade.

Metadata source

a> Tables ( non InnoDB)
   FRM files will be main source of metadata for tables. FRM file contains 
   information about table, columns, indexes, partitions.
    
b> Tables (InnoDB)
   FRM file and INNODB_SYS_* data stored in InnoDB Dictionary.
   
c> Views
   FRM file will be source to read view metadata.
   View definition is stored in FRM file in text format.
   We will rely on FRM file directly for view related metadata.

d> Triggers
   We have .TRN and .TRG files for Triggers.
   .TRN is <trigger_name>.TRN file
   .TRG is <table_name>.TRG where table_name is the table on which trigger is 
    created.
    
    We will parse .TRG file to get the trigger metadata.

e> SP and SF
    Stored in "proc" myisam table in mysql database.

f> Events
    Stored in "event" myisam table in mysql database.

start-up of server

WL#6394 takes care of bootstrapping on a fresh installation of data directory.
For initializing the server on old data directory, bootstrapping needs to be 
extended.

Starting 8.0 server on mysql-5.7 data directory requires:

Creating the DD tables
Reading meta data from the old data directory.
Populating DD tables using that meta data

To handle the above upgrade process this worklog would implement auto detection of DD tables. 
If DD tables are not present, DD tables are automatically created and populated.
If DD tables are present, normal server restart will happen.

- If server is started on a data directory, but detects traces of
  previous failed upgrade attempt (i.e. we have partial upgrade case)
  then the traces of previous attempt are cleaned-up and error is
  reported.

  This was done to simplify the implementation part. When all DD tables are 
  deleted, we don't clear them from DD or DD cache. Only ha_delete() is called.
  If we try to create DD tables again, without clearing DD cache, it will 
  result in error in creating DD tables.

Upgrade Process Implementation

Flowchart

The general algorithm for upgrade will be:

Start on existing data directory
Run upgrade checks which does not require opening of tables.
CREATE DD tables.
Upgrade mysql.plugin table.
Initialize all plugins and all Storage Engines.
Loop through all files and folders in data directory.
- If it is a folder, create entry in schemata table.
Loop inside all folders found in data directory.
- If it is .frm file, create TABLE_SHARE from it.
- Do table-specific checks (e.g. for obsolete data types).
- create a table/view entry in mysql.tables table.
- If table uses general tablespace, create an entry in mysql.tablespaces table.
- For .TRG and .TRN files, create corresponding trigger entry.
Upgrade Events
- Open mysql.event table
- Read one record and create entry in mysql.events DD table.
Upgrade Stored Procedures and Stored Functions
- Open mysql.proc table
- Read one record at a time from mysql.proc and create entry in mysql.routines.
If upgrade fails at any point, delete all DD tables and exit with error.

Server initialization process in case of upgrade

In upgrade, bootstrapping will be changed compared to normal server startup.
When mysql-8.0 server instance is started on mysql-5.7 data directory, DD 
tables does not exist and hence created in existing data directory. Three new 
functions

bool upgrade_do_pre_checks_and_initialize_dd(THD *thd);
bool upgrade_fill_dd_and_finalize(THD *thd);
bool delete_dictionary_and_cleanup(THD *thd);

has been introduced in dd::bootstrap namespace. 

- upgrade_do_pre_checks_and_initialize_dd() should be called before server 
  initialize plugins.
- upgrade_fill_dd_and_finalize() should be called after all plugins have been 
  initialized.
- delete_dictionary_and_cleanup() is called in case upgrade aborts. Upgrade can 
  abort in case of partial upgrade OR if one or more checks for upgrade fails. 

The purpose of the function upgrade_do_pre_checks_and_initialize_dd() is as:
- Check for existence of DD table, mysql.version and version number written in 
  it to check for repeated upgrade process and partial upgrade.
- If version table is found with correct version, continue with server startup,
  else:
- Run pre upgrade checks which does not require opening of tables.
- Create DD tables in existing data directory using dd::bootstrap::install()
- Put "0" in mysql.version table to indicate that DD tables have been created 
  but upgrade is not complete. This will be used to check for partial upgrade.
- Upgrade mysql.plugin table, to be used to initialize all other plugins and 
  SEs. 

The purpose of the function upgrade_fill_dd_and_finalize() is as:
- Run pre upgrade checks on tables.
- Create entry in dd tables for all database objects.
- Mark the upgrade process as complete by updating the version number in 
  mysql.version table.
- Continue with normal server start up.

The function of delete_dictionary_and_cleanup() is as:
- Drop all DD tables.
- Drop all .SDI files created by the server.
This process will not delete mysql.innodb_table_stats and mysql.innodb_index_stats tables.

Schema Upgrade

When mysql-8.0 is started on old data directory, it does not contain entry in 
DD schemata table for existing schemas. After creating DD tables, scan through 
all folders in data directory and create schema entry in dd::schemata table. 
The database charset is fetched from db.opt file. If db.opt file is not 
present, the default charset is used and a warning is issued.

migrate_schema_to_dd() function is introduced to handle schema upgrade and dd::create_schema() is utilized to create schema entries in DD.
dd::create_schema() also creates .SDI file for the databases.

SDI stands for Serialized Dictionary Information. They are created for user 
databases and all non InnoDB tables.

Tablespace Upgrade

DD needs to be updated with general tablespaces information. In mysql-5.7, all 
user created tablespace either creates .ibd or isl file in data directory and a 
corresponding entry in Innodb system tables. 

In mysql-8.0, with InnoDB internal dictionary present, mysql.tablespaces acts 
as redundant storage for tablespace names. mysql.tables refers to it via 
foreign key relation. This table does not contain any data relevant to InnoDB.

Hence while upgrading tables, if it is found that table uses a general 
tablespace, an entry is created for it in mysql.tablespaces.
.frm file stores the name of tablespace. Only one entry  is created in the 
mysql.tablespaces  per general tablespace. 


dd::create_tablespace(THD *thd, st_alter_tablespace *ts_info,
                      handlerton *hton);

is called to create entry for corresponding tablespace.

Tables Upgrade

Meta data for tables is mostly stored in .frm files. WL#6390 removes code and 
related structures to read and parse .frm files. This worklog adds back 
open_binary_frm() to read and parse frm files. The variables needed by 
open_binary_frm() are added into a new class 

DD_TABLE_SHARE

Function added to handle table migration which also takes care of views and 
triggers.
bool migrate_table_to_dd(THD *thd, std::string schema_name, std::string 
                          table_name, std::string file_ext)


Steps for table upgrade are:

Create TABLE_SHARE from frm files
- Done by new function added create_table_share_for_upgrade()
Create HA_CREATE_INFO and Create_field List from TABLE_SHARE
- Done by new function added : fill_create_info_for_upgrade()
call function dd::create_dd_user_table() to create entry in DD.
- This will also create .SDI file for the non InnoDB tables.


Additional steps are required to handle Foreign Keys:
-----------------------------------------------------
WL#6599 creates I_S views which are dependent on DD for Foreign key information.
In mysql-5.7, server does not store foreign key data. This data should be 
fetched from Storage Engines which support foreign keys.
To populate foreign key data:
- open the table in storage engine
- call handler::get_foreign_key_list() to get list of foreign keys.
- Do conversion from FOREIGN_KEY_INFO to FOREIGN_KEY.
Foreign key metadata handling is part of table upgrade.

Functions restored for frm file handling:
- int read_string(File file, uchar**to, size_t length)

- void open_table_error(THD *thd, TABLE_SHARE *share, DD_TABLE_SHARE *dd_share,
                        int error, int db_errno, int errarg)
- static uint find_field(Field **fields, uchar *record, uint start, uint length)

- void fix_type_pointers(const char ***array, TYPELIB *point_to_type,
                         uint types, char **names)
- static ulong get_form_pos(File file, uchar *head)

- uint add_pk_parts_to_sk_upgrade(KEY *sk, uint sk_n, KEY *pk, uint pk_n,
                                  TABLE_SHARE *share, handler *handler_file,
                                  uint *usable_parts)
- static int make_field_from_frm(THD *thd,
                                 TABLE_SHARE *share,
                                 DD_TABLE_SHARE *dd_share,
                                 uint new_frm_ver,
                                 bool use_hash,
                                 uint field_idx,
                                 uchar *strpos,
                                 uchar *format_section_fields,
                                 char **comment_pos,
                                 char **gcol_screen_pos,
                                 uchar **null_pos,
                                 uint *null_bit_pos,
                                 int *errarg)

int open_binary_frm(THD *thd, TABLE_SHARE *share,
                    DD_TABLE_SHARE *dd_share,
                    uchar *head, File file)

SE-specific information handling:
---------------------------------

Later, InnoDB will get rid of its internal dictionary, then SE related information should be populated in DD too. If 8.0 server is started on 5.7 data directory, InnoDB will not have system tables to refer to the dictionary information. But dictionary information will be present in innodb system tablespace. To read this data, it is needed first to create meta data for these tables inside Data dictionary. After that, data from these tables can be retrieved to create se_private_data for system and user Innodb tables.


This will require some additional steps before upgrading any user or system 
tables. These steps are required to get se_private_data for tables during 
upgrade.

These steps are:
- Create hard coded meta data for InnoDB Core sys tables in Data Dictionary.
  InnoDB core sys tables are :
  INNODB_SYS_TABLES , INNODB_SYS_COLUMNS, INNODB_SYS_INDEXES, INNODB_SYS_FIELDS.
  These tables store se_private_data for other innodb system tables, server 
  system tables and user tables.

  This step should be added after DD table creation but before populating 
  metadata for tables. These tables should be used to 
  create se_private_data for tables and indexes.

- Create handcoded meta data for InnoDB Non Core system tables.
  InnoDB Non Core system tables are :
  INNODB_SYS_TABLESPACES, INNODB_SYS_DATAFILES, INNODB_SYS_FOREIGN, 
  INNODB_SYS_FOREIGN_COLS, INNODB_SY_VIRTUAL.

  Metadata for these tables should be created inside Data Dictionary using 
  se_private_data from innodb core system tables.
  These tables will be used to get Foreign Key Information and Tablespace 
  Information which is present only with InnoDB.

- A new functionality should be added which will fetch the required data from 
  Storage engine and update it in dd::Table object.

Views Upgrade

View definition is stored in frm files in 5.7.

Steps to view upgrade are

Read view meta data in a File_parser.
File_option View_parameters structure is added back to parse views.
Parse File_parser to populate TABLE_LIST object with view meta data.
Call dd::create_view()

Triggers Upgrade

In mysql-5.7, triggers are stored in .TRG files. When upgrading a table, existence of corresponding .TRG file is checked. If .TRG file exists:

Read .TRG file using File_parser
Structures added back to read and parse .TRG file
- struct Trg_file_data
- File_option trg_file_parameters
- File_option sql_modes_parameters
- class Handle_old_incorrect_sql_modes_hook: public Unknown_key_hook
- bool Trigger_loader::trg_file_exists()
- bool Trigger_loader::load_triggers()
Assign ORDER to the Triggers.
Call dd::create_trigger() to create Trigger entry in DD objects.

Events Upgrade

In mysql-8.0, events will be stored in events DD table. mysql-5.7 event table 
is treated as regular user table in mysql-8.0 and act as source for the events 
metadata to be migrated, created in mysql-5.7.
Steps for upgrading Events from mysql-5.7 to mysql-8.0 will be as

- Two new functions

   migrate_events_to_dd() 
   update_event_timing_fields()

has been introduced for events metadata migration.

Steps for upgrading Event from mysql-5.7 to mysql-8.0 to be implemented in migrate_events_to_dd() function will be:

- Open mysql.event table. - Read all records in mysql.event table. - Each record indicates one event - For each record,

 - Create Event_parse_data structure and fill it.
 - Call dd::create_event() to create entry in DD mysql.events table.
 - call update_event_timing_fields() for updating events timing.

update_event_timing_fields() sets last_created and last_altered fields for each event.

Any error encountered while upgrading will lead to partial upgrade scenario. This refers to the case when a few Events are upgraded but not all. This will be dealt along with handling of partial upgrade of data directory.

See dump upgrade section for the behavior change in upgrade of events using mysqldump and --all-tables option.

SP /SF Upgrade

Handling of SP/SF upgrade / downgrade will be similar to Events.
In mysql-8.0, SP/SF will be stored in routines DD table. mysql.proc table from 
5.7 data directory will be treated as regular user table in mysql-8.0 and act 
as source for the SP/SF metadata to be migrated.


- A new function migrate_routines_to_dd () is added to perform the migration of 
  metadata for SP/SF from mysql.proc to mysql.routines. 

Steps for upgrading SP/SF from mysql-5.7 to mysql-8.0 to be implemented in this 
function will be:

- Open mysql.proc table.
- Read all records in mysql.proc table.
  Each record indicates one SP/SF
- For each record,
  - Create sp_head * using function db_load_routine()
  - Call sp_create_routine() to create entry in DD routines table.


This will upgrade all SP/SF in 5.7 to DD tables.

Any error encountered while upgrading will lead to partial upgrade scenario.
This refers to the case when a few SP/SF are upgraded but not all.
This will be dealt along with handling of partial upgrade of data directory.

See dump upgrade section for the behavior change in upgrade of Stored Procedures and Store Function using mysqldump and --all-tables option.

Partial Upgrade


Checking for Partial Upgrade : mysql.version table will be used to check for 
partial upgrade as follows:
- If mysql.version table is not present, we are operating on an old data 
  directory and upgrade process has not started. 
- If mysql.version table is present but has 0 (Zero) as version number, it is 
  the case of partial upgrade.
- If mysql.version table is present and has correct version number in the 
  table, upgrade is complete. 

Handling 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.
  - 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.

Clean Up

After upgrade is successful, upgrade will do a cleanup. It will create a folder 
named 'backup_metadata_57'. All the 
- .frm files
- .TRG and .TRN files
- .par files
- .isl files
- .opt files
will be moved to this folder, retaining the original hierarchy. For example if 
t1.frm was in <datadir>/my_schema1/, it will be moved to
<datadir>/backup_metadata_57/my_schema1/.

mysql.proc and mysql.event tables will be renamed to mysql.proc_backup_57 and
mysql.event_backup_57.

Upgrade Steps for Users of libmysqld.a

An attempt to start up the embedded library on old data files should fail in a predictable way that indicates that upgrade is needed.

Upgrade Steps for user

a) Do a pre requisite check on mysql-5.7 data directory before shutdown.
This is very important before starting the process of upgrade. mysql-8.0
will refuse to upgrade if any of the upgrade requirements fail.

User can do these checks by execute by:

- executing mysqlcheck tool with --all-databases and --check-upgrade
This will check for obsolete data types and innodb tables with non native
partitioning. This will also report error if any trigger is missing
or has invalid creation context (character_set_client,
collation_connection, Database Collation).
Any error reported mysqlcheck tool should be fixed before upgrading.

- Execute query:
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE
ENGINE!='InnoDB' AND ENGINE!='ndbcluster' and CREATE_OPTIONS LIKE
'%partitioned%';

This will report any partitioned tables with storage engine without native
partitioning support.
Any table reported by above query should be changed to InnoDB or made
non-partitioned..
"ALTER TABLE table_name ENGINE=INNODB" can be used to change partitioned
tables with other Storage engines to InnoDB in mysql-5.7. Similar approach
can be used for hanging storage engine of tables to ndb.

- Execute query:

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
LOWER(TABLE_NAME) IN ('version','character_sets','collations','tablespaces','tablespace_files','catal
ogs','schemata','st_spatial_reference_systems','tables','view_table_usage','view
_routine_usage','columns','indexes','index_column_usage','column_type_elements',
'foreign_keys','foreign_key_column_usage','table_partitions','table_partition_va
lues','index_partitions','events','routines','parameters','parameter_type_elemen
ts','triggers','table_stats','index_stats') and LOWER(TABLE_SCHEMA)='mysql';

This will report any tables present in 5.7 mysql database with same name to
be created by Data Dictionary framework. All tables reported by above query
should be renamed beore uggrade process starts.

- Execute query :

SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME from
information_schema.REFERENTIAL_CONSTRAINTS WHERE LENGTH(CONSTRAINT_NAME) >
64;

This will report all tables with foreign key constraint name more than 64
characters. They should be changed to constraint name less than 64
characters before upgrade process starts.

- Execute query :
SET GLOBAL innodb_fast_shutdown=0;

This will make sure that when 5.7 server is shutdown, innodb undo logs are
empty.

b) Do a slow shutdown of mysql-5.7 server. Slow shutdown involves setting
global variable 'innodb_fast_shutdown' to zero before server shutdown.

c) Start mysql-8.0 server on 5.7 data directory.
mysql-8.0 server will detect if Dictionary tables are present or not.
If dictionary tables are not present, they will be created in old data
directory. Metadata will be populated in dictionary tables.
Then server will proceed with normal start.

If there are encrypted innodb tablespaces, --early-plugin-load option to
load keyring plugin should be used when starting the server first time on
5.7 data directory.

If this step fails, all changes to data directory will be reverted. User
should delete all redo log files, start 5.7 server on the same data
directory, fix the cause of error, do a slow shutdown of 5.7 server, and
start 8.0.0 server again.

d) Execute mysql_upgrade
This will fix any changes in system tables between mysql-5.7 and mysql-8.0.
This will also fix performance_Schema, sys and information_schema databases.

Note: Performance_schema upgrade will not be performed as part of dictionary
upgrade as mysql_upgrade drops and recreates Performance_schema
database and its tables.

Note: As part of WL#6599, we are creating I_S as system views on top of DD
tables. They are created via mysql_system_tables.sql script, user
needs to run mysql_upgrade to get the information schema views.
It is planned to move I_S creation inside server code along with DD
table creation. When this is implemented, user will get all I_S tables
as soon as server starts.

e) Shutdown and start server again with normal configuration. (Recommended)

Upgrade of Packages in distros

apt/deb and yum/rpm packages follow the following process of upgrade server binaries: - Check requirements for upgrade before deleting binaries. - Delete binaries of old version - Add new binaries

If mysql-8.0 binaries are added on a data directory which does not satisfy upgrade requirements mentioned in this worklog, upgrade will error out. Hence, it is highly recommended to do a manual check on 5.7 data directory using 5.7 binaries before upgrading. The checks are mentioned in the "Upgrade Steps for user" section above.

If package installation scripts start mysql-8.0 binaries, it can be started directly. Else, mysql-8.0 binaries will fail to start on 5.7 data directory.

Downgrade

In theory, downgrade is possible if following conditions are met:

a) FRM, TRG, TRN, PAR and other files should not be deleted.
b) There should be no DDL on tables after upgrade.
c) INNODB_SYS_* tables in data directory should not be deleted.
d) INNODB dictionary does not get out of sync.
e) Any changes in the data directory during upgrade is reverted.

However, we won't support inplace downgrade. mysqldump tool should be used to downgrade from mysql-8.0 to mysql-5.7.

Dump Upgrade

Dump upgrade will work with --force option. All user tables are restored. 
It gives a lot of errors while restoring dump from a mysql-5.7 to mysql-8.0,as 
mysql.innodb_table_stats and mysql.innodb_index_stats tables deleted in 
process. 

In mysql-5.7, mysql.innodb_table_stats and mysql.innodb_index_stats were 
treated as part of system tables. Thus mysqldump tool creates entry in dump 
file for these tables too. This involves deleting these tables, creating them 
again and populate data. But from mysql-8.0 these are part of DD tables.
Hence, DDL is not allowed on these tables.

If a dump from mysql-5.7 is tried to restore on mysql-8.0, 
mysql.innodb_table_stats and mysql.innodb_index_stats are deleted. This causes 
indeterminate behavior or asserts.  After WL#6391 is implemented DDL will not 
be allowed on DD tables.

If dump files has DDL on DD tables ( mysql.innodb_table_stats and 
mysql.innodb_index_stats ), restoration of dump will fail. In this case, 
--force option should be used while restoring dump file.

Change in behaviour

Before DD, while doing dump based upgrade, with "--all-databases" option, all
tables from mysql schema is also dumped. mysql.proc and mysql.event tables are 
dumped and restored thus completing upgrade process without special handling. Net effect is that SP/SF and Events are upgraded without specifying.

The behavior is different for Triggers which are not stored explicitly in
any table. To backup/restore Triggers via dump method, --triggers option has to 
be supplied  mysqldump too.

With DD, even though mysql.proc and mysql.event data are restored, they are
no longer treated as source of SP/SF/Events. mysqldump has explicit options for 
dumping SP/SF and Events definition as --routines and --events.

With new behavior, the working of dump/restore will be

- When 5.7 dump is restored in 8.0, Stored Procedures/Functions and Events will 
  not be restored unless dump is taken with --routines and --events.
- when 8.0 dump is restored in another 8.0 server, Stored Procedures/Functions 
  and Events will not be restored unless dump is taken with --routines and 
   --events.
This should be documented.

Documentation Notes

Certain points need to be explicitly mentioned while documenting about live and 
dump upgrade about mysql-8.0:

- It is highly recommended to take a backup of 5.7 data directory before doing 
  live upgrade to mysql-8.0.0 as mysql-8.0.0 is a milestone release. We do not 
  support upgrade between milestone releases. There might be technical reasons 
  preventing live upgrade between Milestone releases.
- Behavior of dump/restore with --all-databases has changed as mentioned above 
  if restore version of mysql is mysql-8.0.0 and above.
- The pre requisites for upgrade section mentioned above.
- "Upgrade Steps for user" section mentioned above.
- Live (In place) downgrade is not supported.
- If upgrade has failed and when user tries to start 5.7 server on the same 
  data directory to fix the cause of failure, all redo logs need to be deleted
  first. Else, an error is reported:
    [ERROR] InnoDB: Unsupported redo log format. The redo log was created with 
    MySQL 8.0.0. Please follow the instructions at 
    http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html 
- Orphan .TRG files will be ignored. 
- Orphan folders will be upgraded and treated as schema.
- F-8 from WL#6929, "InnoDB currently supports FK name larger than 64 
  characters, so this is a change of behaviour in that respect in order to get 
  better identifier length consistency."
  Checks has been added in the foreign key migration to check that foreign key 
  name is less than 65 characters.