WL#12413: Move actions from mysql_upgrade client into mysqld

Affects: Server-8.0   —   Status: Complete

1.1 Goal of the WL

Move execution of actions executed by the MySQL upgrade script to the mysqld proper. The MySQL server will then have control of when these actions are executed, and is not dependent on user to run the mysql_upgrade script.

Moving execution into the server executable will also enable further protection of system tables, which will be done at a later stage.

1.2 Problems and motivation:

  1. Need faster upgrade (when we take the time needed to run mysql_upgrade into account).
  2. Need simpler upgrade (one tool less).
  3. Need better protection and security, prohibit DDL on system tables.
  4. Need to make sure upgrade client is actually being run.

1.3 Goals:

  1. The goal of this worklog is to remove the mysql_upgrade client.
  2. Move functionality related to system and DD tables into the mysqld server.
  3. Ensure that functionality related to user tables is covered by other tools (mysqlcheck).

1.4 Challenging issues to be considered in the WL:

  1. Error handling.
  2. How to handle user tables.

2.1 Functional Requirements

When we say "upgrade", we refer to the upgrade of the system tables, i.e., the execution of the SQL statements previously done by the mysql_upgrade client.

FR0. If a failure is detected during upgrade, upgrade will be aborted, and the server will exit with an error message in the error log.
FR1. The user will have an option to skip the upgrade step with the server option --upgrade=MINIMAL. Explanation in High Level Specification under "New Server Options".
FR2. Server should detect if upgrade failed during or after executing the upgrade queries and resume upgrade from the beginning of upgrade queries (since these queries are idempotent).
FR3. New system tables introduced must be added.
FR4. The existing mysql schema tables and sys schema tables must be upgraded (and possibly repaired).
FR5. Server option --upgrade=NONE will be provided to prohibit upgrade. Explanation in High Level Specification under "New Server Options".
FR6. Repairing user tables is done automatically. Restart is not required after upgrade.
FR7. Deprecate and remove the --no-dd-upgrade option, since it would have the same effect as --upgrade=NONE.
FR8. mysql_upgrade client will be changed to print a deprecated message. Executing the upgrade client will return 2 (EXIT_ALREADY_UPGRADED) for all operations. The exception is when --force is used, it returns 0 (success).
FR9. Future upgrade steps must be idempotent, and they must be added to the same SQL scripts as today.
FR10. Server option --upgrade=FORCE is provided to run the server upgrade sequence on demand.
FR11. Upgrading distributed privileges must be possible.
FR12. The server should output messages to the error log when starting and ending the upgrade.
FR13. The server should attempt an upgrade, if required, on every startup unless --upgrade=MINIMAL or --upgrade=NONE is specified.

4.1 SQL files involved

  1. mysql_system_tables.sql - Creates the system tables

  2. mysql_system_tables_fix.sql - Updates the system table

  3. mysql_system_tables_data_fix.sql - Fills the system tables with meta data

  4. mysql_sys_schema.sql - Create and/or updates the sys schema

  5. scripts/fill_help_tables.sql - Used when server is started with --initialize

  6. scripts/mysql_system_tables_data.sql - Used when server is started with --initialize

  7. scripts/mysql_system_users.sql - Used when server is started with --initialize

4.2 MySQL upgrade client steps

Step 1. Fix mysql schema - the system tables. (Executing mysql_system_tables.sql + mysql_system_tables_fix.sql + mysql_system_tables_data_fix.sql)

Step 2. Fix sys schema tables. (Executing mysql_sys_schema.sql)

Step 3. Running CHECK TABLE on all tables. If the status of any of the tables is not "OK", we either REPAIR the tables or ALTER the tables based on the response from CHECK TABLE.

4.3 New Server Option

A new server option upgrade is added to specify the upgrade mode. This option can take 4 values:

  1. NONE - to abort if upgrade is needed, to avoid accidental upgrades.

  2. MINIMAL - start a new server on the old data directory, upgrading DD but skipping the upgrade of the server. This option will simulate the current behavior of in-place upgrade. More details in 4.6.

  3. AUTO - Attempt an upgrade if required.

  4. FORCE - To force run server upgrade sequence.

4.4 Relation between mysqlcheck and mysql_upgrade client

mysql_upgrade client makes two seperate checks - one for the tables in mysql schema, the second for the tables in sys schema and all the user tables. mysqlcheck tool is used to perform these checks. mysqlcheck tool is run programatically from within the mysql_upgrade client and is equivalent to running the mysqlcheck tool with --auto-repair and --check-upgrade options.

With this WL, running mysqlcheck tool will not be necessary, unless user tables/data need upgrade.

4.5 Moving upgrade client actions to the server

The queries executed in mysql_upgrade client will be referred to as "upgrade queries"

The execution of the upgrade queries will take place after the initialization of performance_schema tables. After the in-place upgrade of 5.7, the performance schema frm files are deleted. Since tables created in (section 4.2) Step 2 of mysql upgrade client references many of the performance schema tables, this needs to come after performance_schema is initialized.

4.6 Skipping server upgrade

The new upgrade sequence is:

  1. If DD version in dd_properties is not the latest, upgrade DD tables.

  2. Set new DD version and server version in dd_properties table and commit.

  3. If server version in dd_properties is not the latest OR server version in dd_properties does not match the latest upgraded server version, upgrade system tables.

  4. Make a note of the latest upgraded server version in dd_properties table and commit.

During in-place upgrade, if DD upgrade goes well, but Server upgrade happens to fail, the server exits. Subsequent server starts would attempt an upgrade and fail again (provided the server upgrade failure is repeatable) to start since the new server version isn't set yet. Now the user is stuck with a data directory with newer DD tables but older system tables. And the server would not be operational with this data directory.

To help fix this situation, we can set the server option --upgrade to MINIMAL to skip server upgrade (--upgrade=MINIMAL). With this, we will skip the steps 3 and 4 of the new upgrade sequence mentioned above. This is the same as the current behavior of in-place upgrade.

We also do not want to allow skipping server upgrade more than once. If the server is started with --upgrade=MINIMAL option with a newer MySQL server version Z on an older data directory of MySQL server version X, we check if server upgrade has been skipped before using another MySQL server version Y such that Y != Z, X < Y and X < Z. If yes, we abort. This mechanism is used to make sure that an upgrade is completed successfully before moving to a newer version.

Examples:
8.0.11 --upgrade=MINIMAL-> 8.0.12 ----upgrade=MINIMAL-> 8.0.13 (abort here)
5.7.22 --upgrade=MINIMAL-> 8.0.14 --upgrade=MINIMAL-> 8.0.15 (abort here)

The server will attempt an upgrade, if required, on every startup unless --upgrade=MINIMAL or --upgrade=NONE is specified.

4.7 New error/information messages:

  1. Errors during execution of queries in Step 1 and Step 2 (steps described in section 4.2)

  2. Log Messages if CHECK TABLE specifies any errors (For all tables when upgrading from 5.7, only system tables when upgrading from 8.0.x)

  3. Errors if repair table failed (For all tables when upgrading from 5.7, only system tables when upgrading from 8.0.x)

4.8 Upgrading with MySQL Cluster

Upgrading MySQL Cluster can be performed as a regular rolling upgrade, following the usual three ordered steps:

  1. Upgrade MGM nodes
  2. Upgrade data nodes one at a time
  3. Upgrade API nodes one at a time (including MySQL servers)

The way to upgrade each of the nodes remains almost the same, as there is a separation between upgrading the DD and upgrading the system tables. There are 2 steps to upgrade each of the individual mysqlds:

Phase 1: The Data Dictionary import Start the new server binaries with the option --upgrade=MINIMAL. This is essentially the same as starting a new binary today and postpone running mysql_upgrade. From MySQL 5.7, the DD tables are created in InnoDB; all of the legacy filesystem metadata from files .frm, .par, .opt, etc. is imported; and those files are deleted. Upgrade from a MySQL 8.0.x server will upgrade the DD tables.

At present, the MySQL server must be connected to NDB for this phase to complete. If any NDB or NDBINFO tables exist, and the server cannot connect to the cluster, mysqld will abort with the message "Failed to Populate DD tables."

Phase 2: Upgrade the system tables Currently, mysql_upgrade client is used to upgrade the system tables. The same actions have been moved into the server. To upgrade the system tables, restart each of the individual mysqlds without the --upgrade=MINIMAL option.

5.1 Introduction:

This section contains an overview of the implementation. It provides details on what upgrade queries are executed and where they are executed, how the system tables are checked and repaired, and how errors are handled during this process.

5.2 Upgrade queries:

The upgrade queries modify the mysql schema and sys schema tables. Many of these drop existing tables and create new ones to replace them, with newer table definitions. Some are queries to add data to the tables such new users in mysql.user table.

The queries relevant to upgrade client are present in mysql_system_tables.sql, mysql_system_tables_fix.sql, mysql_system_tables_data_fix.sql, mysql_sys_schema.sql. Cmake converts these sql files into .c files and these sql statements are available to us as c strings.

mysql_system_tables.sql and mysql_system_tables_fix.sql -> mysql_fix_privilege_tables_sql.c
mysql_system_tables_data_fix.sql -> sql_commands_system_tables_data_fix.h
mysql_sys_schema.sql -> sql_commands_sys_schema.h

mysql_fix_privilege_tables_sql.c is also used during initialization of a new data directory.

5.3 Implementation:

This WL will move the upgrade client actions into server. Later, the mysql schema tables and sys schema tables may be moved into DD.

Since these queries must be executed for both 5.7 and 8.0 upgrades, we will move the upgrade queries in init_server_components() after P_S is initialized. Updating the server version must be done after this step so that we can catch any upgrade failures at this step. There could also be a new stage like UPGRADE_QUERIES_EXECUTED added to Upgrade_status::enum_stage to resume upgrade from this step if it fails. (May not be necessary because if SDI_INFO_UPDATED is set, we know that the only step left is to execute the upgrade queries. But if we already have executed the upgrade queries, we need not execute these again.)

A new method added to sql/dd/upgrade/upgrade.h
bool upgrade_system_schemas(THD *thd);
Once P_S is initialized, we run this method. This is run from init_server_components in sql/mysqld.cc.

A new class MySQL_check is added to run CHECK TABLE and REPAIR TABLE queries.

class MySQL_check {
  private:
    std::vector<dd::String_type> alter_cmds, repairs;
    bool needs_repair;

    static dd::String_type escape_str(const dd::String_type &src);
    void comma_seperated_join(std::vector<dd::String_type> &list, dd::String_type &dest);
    bool get_schema_tables(THD *thd, const char* schema, dd::String_type &tables);
    bool check_table(List<Ed_row>::iterator &it, const List<Ed_row>::iterator &end, bool repair);
    bool verify_response(List<Ed_row> &rset, bool repair);
    bool check_tables(THD *thd, const char *schema);

  public:
    MySQL_check() : needs_repair(false) {}
    bool check_system_schemas(THD *thd);
    bool repair_tables(THD *thd);
};

5.4 upgrade_system_schemas steps

  1. run_upgrade_scripts - The queries from mysql_system_tables.sql + mysql_system_tables_fix.sql + mysql_system_tables_data_fix.sql

  2. MySQL_check::check_system_schemas and MySQL_check::repair_tables - Checks (and repairs) system tables

MySQL_check emulates how client/check/mysqlcheck_core.cc works. MySQL_check::check_system_schemas runs a single CHECK TABLE query with a list of tables from the schema being checked - mysql schema and sys schema. The response is explained in https://dev.mysql.com/doc/refman/8.0/en/check-table.html

Example of a response for query CHECK TABLE mysql.component, mysql.db: Set of rows for mysql.component:

mysql.component check note   Table does not support optimize, doing recreate + analyze instead
mysql.component check status OK

Set of rows for mysql.db:

mysql.db check note   Table does not support optimize, doing recreate + analyze instead
mysql.db check status OK

The logic of checking response for each set of rows for a table:

char *alter_txt;
bool found_error = false;

// We are looking for the status of a table to either be 'OK' or not 'OK'.
while Msg_type is not 'status'
    if Msg_type is not 'note'
        found_error = true
        alter_txt = 'ALTER TABLE' in Msg_text
    next_row()

// The set of rows received for a table usually ends with a row with Msg_type 'status'
// Even if found_error is true, as long as status is OK, the table is fine and needs no changes.
if Msg_type is 'status' and Msg_txt is not 'OK' and found_error
    if alter_txt
        save the alter query
    else
        save table name for table rebuild

5.5 Failure detection

Currently, once the DD is initialized/updated, the MYSQL_VERSION in dd_properties table is also updated to the latest version. But we can use this property to check for the complete upgrade of the server if we only update this after the upgrade queries are run.

Once the DD tables are initialized/upgraded, we only update the DD version and commit. After the upgrade queries are executed, we update the server version and make another commit.

In case of upgrade from 5.7 to 8.0, when DD is initialized, we set the MYSQL_VERSION property to 5.7. If there were a crash during or before the upgrade queries are executes, we can compare the current MYSQL_VERSION in mysql_version.h with the MYSQL_VERSION stored in dd_properties to verify this crash and resume upgrade. The scenario is similar in 8.0.x -> 8.0.y upgrades, except that the MYSQL_VERSION property is already present and ready for comparision.

The method update_versions is split into update_server_version and update_dd_version.

5.5.1 Failure detection for 5.7 -> 8.0:

During initialization of dictionary, we set the server version to 5.7 in dd_properties.

@@ -2234,9 +2246,10 @@ bool initialize_dictionary(THD *thd, bool is_dd_upgrade_57,
       populate_tables(thd) ||
       update_properties(thd, nullptr, nullptr,
                         String_type(MYSQL_SCHEMA_NAME.str)) ||
-      verify_contents(thd) || update_versions(thd)) {
+      verify_contents(thd) ||
+      (is_dd_upgrade_57 && set_server_version(thd, bootstrap::SERVER_VERSION_50700)) ||
+      update_dd_versions(thd))
     return true;

At the end of upgrade_system_schemas, we call update_server_version which updates the dd_properties to the current version of server.

5.5.2 Failure detection for 8.0.x -> 8.0.y:

Since the previous mysql version is still available in dd_properties, we need not take any action. Same as before, the mysql version is updated after upgrade_system_schemas is completed.

5.6 Other changes:

dd::do_server_upgrade_checks in sql/dd/upgrade/upgrade.cc is updated to run only if the upgrade is from 8.0

@@ -95,7 +103,8 @@ bool examine_each(Upgrade_error_counter *error_count,
 }

 bool do_server_upgrade_checks(THD *thd) {
-  if (!DD_bootstrap_ctx::instance().is_server_upgrade()) return false;
+  if (!DD_bootstrap_ctx::instance().is_server_upgrade_from_after(bootstrap::SERVER_VERSION_50700)) return false;

5.7 Code Refactoring

  1. bootstrapper.cc and bootstrap_ctx.cc from sql/dd/impl to sql/dd/impl/bootstrap

  2. Move code shared by 5.7 server upgrade and 8.0 server upgrade, or 8.0 exclusive server upgrade code into sql/dd/impl/upgrade/server.cc

  3. Move code related to DD upgrade from bootstrapper.cc to into sql/dd/impl/upgrade/dd.cc

  4. Move dd::end_transaction and execute_query from bootstrapper.cc to utils.cc

  5. Rename sql/dd/upgrade to sql/dd/upgrade_57



Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.