From 7270b1f2ac24427d4e79e8206dc3f61eb2cd9e2f Mon Sep 17 00:00:00 2001 From: Akshat Nehra Date: Fri, 17 Jul 2026 03:47:34 +0000 Subject: [PATCH] Bug#120948 Add removedLegacyHashingFunctions upgrade check for MySQL 9.6+ MD5(), SHA1(), and SHA() were removed from the server binary in MySQL 9.6.0 and moved to component_classic_hashing. Generated columns using these functions become permanently inaccessible after upgrade since loadable functions are disallowed in generated column expressions. This check scans views, routines, triggers, events, generated columns, CHECK constraints, DEFAULT expressions, and functional indexes for usage of these removed functions. Severity is split based on recoverability: - ERROR for generated columns, functional indexes, CHECK constraints, and DEFAULT expressions (blocks upgrade or bricks the table) - WARNING for views, routines, triggers, and events (recoverable post-upgrade by installing component_classic_hashing) The check is registered at version 9.6.0 so it fires for upgrades from 8.4+ to 9.6+. This contribution is under the OCA signed by Amazon and covering submissions to the MySQL project. --- modules/util/upgrade_checker/common.cc | 1 + modules/util/upgrade_checker/common.h | 2 + .../upgrade_checker/upgrade_check_creators.cc | 121 ++++++++++++++++++ .../upgrade_checker/upgrade_check_creators.h | 2 + .../upgrade_checker/upgrade_check_registry.cc | 5 + res/upgrade_checker/upgrade_checker.msg | 17 +++ 6 files changed, 148 insertions(+) diff --git a/modules/util/upgrade_checker/common.cc b/modules/util/upgrade_checker/common.cc index ffa4dd472..b974c1445 100644 --- a/modules/util/upgrade_checker/common.cc +++ b/modules/util/upgrade_checker/common.cc @@ -88,6 +88,7 @@ const std::set all = { k_partitions_with_prefix_keys, k_foreign_key_references, k_spatial_index, + k_removed_legacy_hashing_functions_check, }; } // namespace ids diff --git a/modules/util/upgrade_checker/common.h b/modules/util/upgrade_checker/common.h index eb78bc340..1f2a5f3ff 100644 --- a/modules/util/upgrade_checker/common.h +++ b/modules/util/upgrade_checker/common.h @@ -113,6 +113,8 @@ inline constexpr std::string_view k_partitions_with_prefix_keys = inline constexpr std::string_view k_foreign_key_references = "foreignKeyReferences"; inline constexpr std::string_view k_spatial_index = "spatialIndex"; +inline constexpr std::string_view k_removed_legacy_hashing_functions_check = + "removedLegacyHashingFunctions"; // NOTE: Every added id should be included here extern const std::set all; diff --git a/modules/util/upgrade_checker/upgrade_check_creators.cc b/modules/util/upgrade_checker/upgrade_check_creators.cc index 4ad732cd0..4d5f8c061 100644 --- a/modules/util/upgrade_checker/upgrade_check_creators.cc +++ b/modules/util/upgrade_checker/upgrade_check_creators.cc @@ -944,6 +944,127 @@ std::unique_ptr get_removed_functions_check() { return std::make_unique(); } +class Removed_legacy_hashing_functions_check : public Sql_upgrade_check { + private: + static const std::unordered_map functions; + + public: + Removed_legacy_hashing_functions_check() + : Sql_upgrade_check( + ids::k_removed_legacy_hashing_functions_check, Category::SCHEMA, + {{"select table_schema, table_name, '', 'VIEW', " + "UPPER(view_definition) from information_schema.views where " + "<>", + Upgrade_issue::Object_type::VIEW}, + {"select routine_schema, routine_name, '', routine_type, " + "UPPER(routine_definition) from information_schema.routines where" + " <> and routine_definition is not " + "null", + Upgrade_issue::Object_type::ROUTINE}, + {"select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " + "EXTRA, UPPER(GENERATION_EXPRESSION) from " + "information_schema.columns where " + "extra in ('VIRTUAL GENERATED', 'STORED GENERATED') and " + "<>", + Upgrade_issue::Object_type::COLUMN}, + {"select TRIGGER_SCHEMA, EVENT_OBJECT_TABLE, TRIGGER_NAME, " + "'TRIGGER', UPPER(ACTION_STATEMENT) from " + "information_schema.triggers where <>", + Upgrade_issue::Object_type::TRIGGER}, + {"select event_schema, event_name, '', 'EVENT', " + "UPPER(EVENT_DEFINITION) from information_schema.events where " + "<>", + Upgrade_issue::Object_type::EVENT}, + {"select tc.TABLE_SCHEMA, tc.TABLE_NAME, cc.CONSTRAINT_NAME, " + "'CHECK CONSTRAINT', UPPER(cc.CHECK_CLAUSE) from " + "information_schema.CHECK_CONSTRAINTS cc " + "join information_schema.TABLE_CONSTRAINTS tc " + "on cc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA " + "and cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME " + "where tc.CONSTRAINT_TYPE = 'CHECK' and " + "<>", + Upgrade_issue::Object_type::TABLE}, + {"select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " + "'COLUMN DEFAULT', UPPER(COLUMN_DEFAULT) from " + "information_schema.columns where " + "COLUMN_DEFAULT is not null and " + "EXTRA not in ('VIRTUAL GENERATED', 'STORED GENERATED') " + "and <>", + Upgrade_issue::Object_type::COLUMN}, + {"select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, " + "'FUNCTIONAL INDEX', UPPER(EXPRESSION) from " + "information_schema.statistics where EXPRESSION is not null and " + "<>", + Upgrade_issue::Object_type::INDEX}}, + Upgrade_issue::WARNING) {} + + bool is_multi_lvl_check() const override { return true; } + + protected: + Upgrade_issue parse_row(const std::vector &, + const mysqlshdk::db::IRow *row, + Upgrade_issue::Object_type object_type) override { + auto res = create_issue(); + std::vector> flagged_functions; + std::string definition = row->get_as_string(4); + mysqlshdk::utils::SQL_iterator it(definition); + std::string func; + while (!(func = it.next_sql_function()).empty()) { + auto i = functions.find(func); + if (i != functions.end()) flagged_functions.emplace_back(*i); + } + + if (flagged_functions.empty()) return res; + + std::string object_type_str = row->get_as_string(3); + + // Objects that block or brick the upgrade cannot be resolved post-upgrade: + // - Generated columns: loadable functions disallowed, table bricked + // - Functional indexes: server blocks upgrade + // - CHECK constraints: server blocks upgrade + // - DEFAULT expressions: server blocks upgrade + // Recoverable objects (upgrade succeeds, install component post-upgrade): + // - Views, routines, triggers, events + bool is_recoverable = (object_type_str == "VIEW" || + object_type_str == "PROCEDURE" || + object_type_str == "FUNCTION" || + object_type_str == "TRIGGER" || + object_type_str == "EVENT"); + + std::stringstream ss; + ss << object_type_str << " uses removed function"; + if (flagged_functions.size() > 1) ss << "s"; + for (std::size_t i = 0; i < flagged_functions.size(); ++i) { + ss << (i > 0 ? ", " : " ") << flagged_functions[i].first; + if (flagged_functions[i].second != nullptr) + ss << " (consider using " << flagged_functions[i].second << " instead)"; + } + + res.schema = row->get_as_string(0); + res.table = row->get_as_string(1); + res.column = row->get_as_string(2); + res.description = ss.str(); + res.object_type = object_type; + + // WARNING for views/routines/triggers/events (recoverable via component) + // ERROR for everything else (blocks upgrade or bricks the table) + res.level = is_recoverable ? Upgrade_issue::WARNING : Upgrade_issue::ERROR; + + return res; + } +}; + +const std::unordered_map + Removed_legacy_hashing_functions_check::functions = { + {"MD5", "SHA2 or an alternative approach"}, + {"SHA1", "SHA2 or an alternative approach"}, + {"SHA", "SHA2 or an alternative approach"}, +}; + +std::unique_ptr get_removed_legacy_hashing_functions_check() { + return std::make_unique(); +} + class Groupby_asc_syntax_check : public Sql_upgrade_check { public: Groupby_asc_syntax_check() diff --git a/modules/util/upgrade_checker/upgrade_check_creators.h b/modules/util/upgrade_checker/upgrade_check_creators.h index 0741a66aa..3d6f40c0d 100644 --- a/modules/util/upgrade_checker/upgrade_check_creators.h +++ b/modules/util/upgrade_checker/upgrade_check_creators.h @@ -97,6 +97,8 @@ std::unique_ptr get_foreign_key_references_check(); std::unique_ptr get_spatial_index_check(); +std::unique_ptr get_removed_legacy_hashing_functions_check(); + } // namespace upgrade_checker } // namespace mysqlsh diff --git a/modules/util/upgrade_checker/upgrade_check_registry.cc b/modules/util/upgrade_checker/upgrade_check_registry.cc index 5c9f78e38..337103276 100644 --- a/modules/util/upgrade_checker/upgrade_check_registry.cc +++ b/modules/util/upgrade_checker/upgrade_check_registry.cc @@ -299,6 +299,11 @@ bool register_manual_checks() { &get_partitions_with_prefix_keys_check, Target::OBJECT_DEFINITIONS, "8.4.0"); +[[maybe_unused]] bool register_removed_legacy_hashing_functions = + Upgrade_check_registry::register_check( + std::bind(&get_removed_legacy_hashing_functions_check), + Target::OBJECT_DEFINITIONS, "9.6.0"); + [[maybe_unused]] bool register_get_spatial_index_check = Upgrade_check_registry::register_check( std::bind(&get_spatial_index_check), Target::OBJECT_DEFINITIONS, diff --git a/res/upgrade_checker/upgrade_checker.msg b/res/upgrade_checker/upgrade_checker.msg index 3cb6b0bf3..97690ffaf 100644 --- a/res/upgrade_checker/upgrade_checker.msg +++ b/res/upgrade_checker/upgrade_checker.msg @@ -692,6 +692,23 @@ Remove foreign keys referring to non unique key/partial columns of key. In case of multi level references which involves more than two tables change foreign key reference. +* removedLegacyHashingFunctions.title +Usage of removed legacy hashing functions (MD5, SHA1, SHA) + +* removedLegacyHashingFunctions.description +The following DB objects use MD5(), SHA1(), or SHA() functions that are removed +as of MySQL 9.6.0. If a generated column within any table uses these functions +and MySQL server is upgraded to 9.6.0 or later, the table stops working. To +resolve this, you must dispose of the generated column and either use triggers +on the table to generate the column value, or rewrite the expression to use an +alternative such as SHA2(). Functional indexes, CHECK constraints, and DEFAULT +expressions using these functions will block the upgrade. For views, routines, +triggers, and events, these can be resolved after upgrade by installing +component_classic_hashing, or rewritten before upgrading. + +* removedLegacyHashingFunctions.docLink +https://dev.mysql.com/doc/refman/9.7/en/legacy-hashing-component.html + * spatialIndex.title Checks for Spatial Indexes