WL#9461: Upgrade for Innodb Internal Dictionary Migration
Affects: Server-8.0 — Status: Complete
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.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.