WL#6392: Upgrade to Transactional Data Dictionary
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.
DD : New Data Dictionary. dd::
: 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.
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#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 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
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.
Presence of Trigger's definers will be checked in .TRG file. Limit on FK name length will be checked while doing table upgrade.
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
.TRN file .TRG is .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
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.
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.
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.
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.
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()
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.
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
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.
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.
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
/my_schema1/, it will be moved to /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.
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 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.
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.