MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
MySQL 8.0.16: mysql_upgrade is going away
Posted on Apr 30, 2019 by Amitabh Das
Category: Upgrading
Tags: upgrades

As of 8.0.16, the mysql_upgrade binary is deprecated, but its functionality is moved into the server. Let’s call this functionality the “server upgrade”. This is added alongside the Data Dictionary upgrade (DD Upgrade), which is a process to update the data dictionary table definitions.

Why did we do that?

  • Faster upgrade
  • Simpler process
  • Better security
  • Significant reduction in upgrade steps and more easily automated
  • No restarts
  • Just plug and play

Before 8.0.16:

  1. Shutdown the server and install the new binaries
  2. Start the server and let the server upgrade the DD tables
  3. Run the mysql_upgrade client to update some system tables and possibly user tables
  4. Download help table update scripts from the web
  5. Load the new help tables
  6. Restart the server and accept production traffic

From MySQL 8.0.16 onwards:

  1. Shutdown the server and install the new binaries
  2. Start the server and let the server upgrade the DD tables and system tables. If user tables need to be upgraded, we now have the power of SQL at hand to analyze if any upgrade of user tables is needed. The help tables are also updated.

The time it takes to upgrade will be shorter, as you now do it in one operation instead of two. Additional time could be saved if the user tables do not require an upgrade.

Don’t want things to change?

A new server option –upgrade is provided. The possible values are: NONE, AUTO, MINIMAL, FORCE.

  • NONE makes sure that no upgrade is attempted. If an upgrade is required, the server aborts. Using this option will only allow restarting the same server version that we have already been running. Even if there are no differences between the old and new server, other than the version number, the new server version is not allowed to start.
  • AUTO is the default value of this option that makes the server attempt both the Data Dictionary upgrade and the server upgrade.
  • MINIMAL attempts only a Data Dictionary upgrade and not the server upgrade when starting the server. This state of the server would be exactly as the upgrades happened before 8.0.16. At this point, before 8.0.16, you would have to run the mysql_upgrade client. Now you would restart the server without any upgrade option (or the options AUTO or FORCE) and the server upgrade is done.
  • FORCE attempts a server upgrade, even if the server is already upgraded. This behavior is similar to the previous behavior of running the mysql_upgrade client with the option –force.

So if you don’t want things to change, you could start the server with –upgrade=MINIMAL.

At every server startup, an upgrade is attempted if required, unless –upgrade=NONE or –upgrade=MINIMAL is specified.

So what did we really do?

We have tried to strengthen and streamline the process of upgrade. This is just the beginning. Let us refer to the tables/views/functions/procedures/routines/triggers present in MySQL as objects. Let us also refer to the objects necessary for MySQL to function correctly as system objects. MySQL Upgrade involves changes to these system objects. They reside in the mysql schema and the sys schema. MySQL Upgrade also changes the user schemas, if required.

According to the MySQL docs, the mysql schema is the system schema. It contains tables that store information required by the MySQL server as it runs. A broad categorization is that the mysql schema contains data dictionary tables that store object metadata, and system tables used for other operational purposes.

With this information, we can categorize the system objects into two types for upgrade:

  • System objects that belong in the transactional Data Dictionary are called DD objects
  • The rest are server objects (including the sys schema objects)

The reason we categorize the system objects into two types is due to the two different versioning systems and infrastructures for creating and upgrading the objects. The DD objects and server objects have separate versioning systems. A change in the DD version specifies a change in the structure of DD schema. With every new server release, the server version changes. But the DD version may or may not change.

Combining the two versioning systems and the option –upgrade=MINIMAL, we create a special case. If you upgrade from MySQL version A to a compatible MySQL version B with –upgrade=MINIMAL, the Data Dictionary moves to version B and the server objects remain at version A. At this state, you cannot try to attempt an upgrade to a newer version, say, version C. If such an attempt is made, the server version C will refuse to start, stating that an upgrade is already pending.

In short, MySQL Upgrade now has 2 steps –

  1. Upgrade DD objects (This has not changed)
  2. Server Upgrade (previously the responsibility of mysql_upgrade)
    1. Upgrade server objects
    2. Upgrade user schemas

Step 2 is the new change in MySQL 8.0.16, and is the entire process of “server upgrade”.

The conditions for a server upgrade

A server upgrade is not allowed to take place if the server is started with –upgrade=MINIMIAL or –upgrade=NONE.

Next, we look at the server version stored in the data dictionary. Let’s call this server_version_a. We compare this with the server version the MySQL binary was compiled with. Let’s call this server_version_b. For an upgrade to take place, an upgrade must be officially supported from server_version_a to server_version_b.

The final condition for a server upgrade requires any pending upgrades to be completed before attempting an upgrade to another newer version.

What the server upgrade involves

  1. Upgrade the server objects in mysql schema
    This involves the execution of SQL queries to transform the system tables into the required definition. The statements are compiled into the server binary as strings and executed by the server.
  2. Upgrade the server objects in sys schema
    We check the version of the sys schema and the number of server objects in the sys schema. If either is not as expected, sys schema is upgraded.
  3. Upgrade all the user schemas
    If necessary, we run CHECK TABLE <user tables> FOR UPGRADE
  4. Update the help tables
    Unconditionally update the contents of MySQL help tables (help_topic, help_category, help_keyword, help_relation)

The possible pitfalls

Each of the steps of MySQL upgrade is self-contained.

Step 1 – DD upgrade is an atomic operation. If this fails the data directory is rolled back to its original state and you can attempt the upgrade once more, after correcting the reason for the failure. You can find all you need to know about this step here and here. 

Step 2a – Upgrade server objects consists of running SQL commands that are idempotent. If this step fails, we are at a state where the Data Dictionary is upgraded but the server objects are not completely upgraded.
The cause of failure is likely to be one of the SQL queries executed in this step. The failing query and the reason for its failure will be in the error log. In most cases, we can restart the server with –upgrade=MINIMAL, fix the issue, then restart the server for the upgrade to resume.
If you want to go back to the original state, restoring from a backup is your only option.

Step 2b – Upgrade user schemas is done by the server after Step 2a, if required, and cannot be skipped. They are either both done or both skipped, depending on the –upgrade option. This step is important if the user schemas are incompatible with the new MySQL version. Since this step involves running CHECK TABLE commands on each of the user tables, if this step fails for any reason, you can perform this step yourself when the server starts with the mysqlcheck utility.

Thanks for using MySQL!

 Search
Categories