MySQL 8 upgrade may fail using checkForServerUpgrade

These days MySQL consultants around me are busy assisting with MySQL 8 upgrades for those who couldn’t board the train before EoL.
I was recently involved in one of the MySQL 8 upgrades (actually Percona Server 8) and it failed. After MySQL upgrade, I got the error Failed to Populate DD tables. I am writing this blog to tell you the story of my failed MySQL 8 upgrade, approach, issue identification, fixture, and the hidden bug that may bite you.

An approach to MySQL 8 Upgrade

Any and every upgrade should be thoroughly tested before moving to the next version, and the same is true for MySQL. Two main steps I use to prepare for MySQL 8 upgrades are as follows:

Use Percona’s pt-upgrade tool

• This is a script to compare MySQL queries between two different MySQL servers (versions).
• Collect slow queries for MySQL 5.7 (Percona Server / MariaDB), generate digest.
• Run pt-upgrade tool against test instances, comparing the differences between the two executions for the same queries.
• This will allow us to identify if a certain query fails or performs poorly against the newer version.

MySQL-Shell’s checkForServerUpgrade function

• This function checks your MySQL 5.7 instance for compatibility errors and issues with upgrading to MySQL 8.
• Setup mysql shell utility and call the function checkForServerUpgrade() with appropriate parameters
• Review the output of the function and work on fixing the reported errors, warnings, or notices.

Error during checkForServerUpgrade()

The MySQL upgrade checker utility reported 1 Error during the first run.

MySQL  localhost  JS > util.checkForServerUpgrade('kedar@localhost:3306', {"password":"XxX", "targetVersion":"8.0.33","configPath":"/etc/my.cnf"});
The MySQL server at localhost:3306, version 5.7.23-25-log - Percona Server (GPL), Release 25, Revision 7e2732e, will now be checked for compatibility issues for upgrade to MySQL 8.0.33...

1) Usage of old temporal type
  Error: Following table columns use a deprecated and no longer supported
    timestamp disk storage format. They must be converted to the new format
    before upgrading. It can by done by rebuilding the table using 'ALTER TABLE <table_name> FORCE' command
  More information: https://mysqlserverteam.com/mysql-8-0-removing-support-for-old-temporal-datatypes/
  database_name.table_name.colDate - timestamp /* 5.5 binary format */

What’s Old Temporals again?

Ages ago, MySQL 5.6 introduced the fractional second precision for temporal data types – TIME, DATETIME, and TIMESTAMP, with up to microseconds (6 digits) precision. So, old temporals refer to the temporal type columns in tables created in MySQL versions 5.5 and older.

Fixing Old Temporals and Rerunning checkForServerUpgrade

Table database_name.table_name must be fixed by upgrading from the old temporal type column colDate. Following command was run:

ALTER TABLE database_name.table_name FORCE;

After fixing this, the upgrade checker utility, checkForServerUpgrade, was run again, and no errors were reported:

Errors:   0
Warnings: 831
Notices:  1

NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

There were a few other Warnings or Notices but they’re not blocker, for example:

column's default character set: utf8
column has zero default value: 0000-00-00

So I progressed further upgrading Percona binaries to 8.0 and started MySQL server which failed. Following was found in MySQL error log:

2023-09-04T11:53:47.764397Z 3 [ERROR] [MY-010923] [Server] Table upgrade required. Please do "REPAIR TABLE some_table_name" or dump/reload to fix it!
2023-09-04T11:53:53.235408Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2023-09-04T11:53:53.235469Z 0 [ERROR] [MY-010119] [Server] Aborting

There were multiple errors suggesting “Table Upgrade Required”. However the checkForServerUpgrade function reported no such thing or errors.

Why MySQL 8 upgrade failed?

Now is the time to identify the reason for this failure. Remember, there’s no rollback in MySQL 8 upgrade. You have to restore from the last available backup and reattempt the restore – and that’s what makes MySQL 8 upgrade to have been thoroughly tested.

I uninstalled Percona Server 8 binaries, reinstalled MySQL 5.7, restored the last good backup, and decided to review all the tables with temporal types manually.

When I executed the following query manually, I received a long list of the tables having old temporal type columns:

set show_old_temporals=ON;
SELECT table_schema, table_name FROM information_schema.columns WHERE column_type LIKE '%5.5 binary format%';

It was strange to me why MySQL upgrade checker utility’s checkForServerUpgrade couldn’t identify so many of them!

I decided to check checkForServerUpgrade for myself to understand what it is doing. The checkForServerUpgrade calls get_old_temporal_check() function, which is responsible in the upgrade checker utility to test for old-temporal types:

std::unique_ptr Sql_upgrade_check::get_old_temporal_check() {
return std::make_unique(
"oldTemporalCheck", "Usage of old temporal type",
std::vector{
"SELECT table_schema, table_name,column_name,column_type "
"FROM information_schema.columns WHERE column_type LIKE "
"'timestamp /* 5.5 binary format */';"},
Upgrade_issue::ERROR,
"Following table columns use a deprecated and no longer supported "
"timestamp disk storage format. They must be converted to the new format "
"before upgrading. It can by done by rebuilding the table using 'ALTER "
"TABLE FORCE' command",
nullptr, std::forward_list{"SET show_old_temporals = ON;"},
std::forward_list{"SET show_old_temporals = OFF;"});
}

Refer: https://github.com/mysql/mysql-shell/blob/0b6abe8b264be694d7491e9c2f1da4ca6fcc35bf/modules/util/upgrade_check.cc#L302-L304

Did you note the query? It is ONLY looking for old temporals of “timestamp” columns but totally ignoring datetime or time datatypes.

      "SELECT table_schema, table_name,column_name,column_type "
      "FROM information_schema.columns WHERE column_type LIKE "
      "'timestamp /* 5.5 binary format */';"},

Temporal types manual verification

Since MySQL upgrade checker’s checkForServerUpgrade function is not correctly identifying the temporal types, MySQL 8 upgrades may fail. This is why one must execute the following query manually and ensure that no old temporal types go unreported.

SET show_old_temporals=ON;
SELECT concat('ALTER TABLE ',table_schema,'.', table_name,' FORCE;') FROM information_schema.columns WHERE column_type LIKE '%5.5 binary format%';

I have filed a bug report already for this: Bug #112991 mysqlsh checkForServerUpgrade doesn’t identify old temporal types correctly

Update

The reported bug has been fixed https://github.com/mysql/mysql-shell/commit/27bb5a0983bc7d9adf7ba45842f758b633a92f17

This commit fixes old_temporal check to include all affected temporal types in scanning.

Temporal types (TIMESTAMP, DATETIME, TIME) after version 5.6.4 ware updated to include fractional parts, but required update of column types using them to new binary format. Version 5.6.24 introduced system variable "show_old_temporals", that enabled to detect types in old old "5.5" format. This option, when enabled, added a string "/* 5.5 binary format */" after a columnt_type in listing from infromation_schema.

Old check "old_temporal" was set to only detect the timestamp temporal type, ignoring the rest. This commit fixes it by expanding the select query to search for all instances of old format column type.

Conclusion

MySQL 8 upgrade is an important milestone, we the DBAs should leave no server untouched to make the upgrade possible :). While the MySQL upgrade checker’s checkForServerUpgrade is a valuable function, it’s important to understand what’s needed for an upgrade. By ensuring manual checks and understanding issues like the one I encountered, you can significantly increase your chances of a smooth MySQL 8 upgrade. Never shy away from diving deeper into the root causes of an issue and sharing your findings with the community. Happy upgrading to MySQL 8!