TL;DR

If you use MD5(), SHA1(), or SHA() in MySQL today, start planning the move to SHA2().

Beginning with MySQL 9.6, MD5(), SHA1(), and SHA() are no longer native built-in SQL functions in the server binary. They are available through the Legacy Hashing Component:

INSTALL COMPONENT 'file://component_classic_hashing';

That component should be treated as a stopgap solution. It gives existing applications room to keep running while you remove older hashes from application SQL, views, stored programs, triggers, events, and migration scripts. It should not be the first choice for new code.

The one place that deserves special attention during upgrade planning is generated columns. MySQL permits deterministic built-in functions in generated column expressions, but not stored functions or loadable functions. After this change, MD5(), SHA1(), and SHA() are loadable functions, so generated columns that use them need a schema change.

When moving to SHA2(), choose the digest size intentionally. MySQL supports SHA-224, SHA-256, SHA-384, and SHA-512. A low-sensitivity checksum and a long-lived security-sensitive identifier do not necessarily need the same digest size.

What Changed?

For a long time, MySQL exposed MD5(), SHA1(), and SHA() as built-in SQL functions:

  • MD5(str) returns a 128-bit checksum as 32 hexadecimal characters.
  • SHA1(str) returns a SHA-1 160-bit checksum as 40 hexadecimal characters.
  • SHA(str) is an alias for SHA1(str).

The algorithms themselves are the reason for the change. MD5 and SHA-1 are old, widely understood, and no longer appropriate for security-sensitive hashing. MySQL documentation has warned that exploits for MD5 and SHA-1 are known and points users toward alternatives such as SHA2().

In the public MySQL release notes, MD5() and SHA1() were formally deprecated in MySQL 9.4.0. In MySQL 9.6.0, they were moved out of the server binary and into the Legacy Hashing Component. The component URN is:

file://component_classic_hashing

So the practical change is this: on MySQL 9.6 and later, legacy hashing is optional. If you still depend on these functions, you can install the component. But the healthier direction is to reduce that dependency and migrate to SHA2() wherever possible.

Start With SHA2(), Not The Component

The easiest short-term answer is often to install component_classic_hashing and move on. For production upgrades, that can be reasonable. But as an application design choice, it keeps you tied to functions that were deprecated for a reason.

Use this order of preference:

  1. Migrate to SHA2() where the hash is still needed.
  2. Remove the hash entirely if it is no longer useful.
  3. Use the Legacy Hashing Component only where you need temporary compatibility.

The basic replacement looks like this:

SELECT SHA2('abc', 256);

SHA2() supports these digest sizes:

SHA2 lengthHex charactersBinary bytes with UNHEX()Typical use
2245628Compatibility with systems that already standardize on SHA-224.
2566432A good default for many application identifiers and integrity checks.
3849648Higher-sensitivity data or longer-lived identifiers.
51212864Very high-sensitivity data, long retention, or when aligned with an existing SHA-512 standard.

The right choice depends on your data. For a non-security checksum used to detect accidental changes, SHA-256 is usually a practical default. For sensitive data, long-lived identifiers, or values exposed across trust boundaries, review the digest length as part of your security design instead of copying whatever the old MD5 column happened to use.

One important caveat: plain SHA2() is not a password-storage strategy. Passwords need purpose-built password hashing such as bcrypt, Argon2, or PBKDF2 with salts and work factors, normally in the application layer or an authentication system built for that job.

Upgrade Consideration: Generated Columns

Most application SQL that calls MD5() or SHA1() can keep working temporarily if the component is installed. Generated columns are different.

Generated column expressions can use literals, deterministic built-in functions, and operators. They cannot use stored functions or loadable functions. Since the component provides MD5(), SHA1(), and SHA() as loadable functions, a generated column that uses one of these functions needs to be changed before moving to MySQL 9.6 or later.

For example, this kind of table definition needs review:

CREATE TABLE user_tokens (
  token_text varchar(255) NOT NULL,
  token_md5 char(32) AS (MD5(token_text)) STORED,
  UNIQUE KEY token_md5_uq (token_md5)
);

Installing component_classic_hashing is not enough for this case. The generated column expression itself has to change.

Prefer moving the generated expression to SHA2():

CREATE TABLE user_tokens (
  token_text varchar(255) NOT NULL,
  token_hash binary(32) AS (UNHEX(SHA2(token_text, 256))) STORED,
  UNIQUE KEY token_hash_uq (token_hash)
);

If you truly need to keep an MD5 or SHA-1 value for compatibility with another system, use a normal stored column and maintain it from the application or with triggers. That is a compatibility compromise, not the ideal destination.

Find Generated Columns That Need Review

Start with generated columns because they are the easiest place to get surprised during an upgrade:

SELECT
  table_schema,
  table_name,
  column_name,
  extra,
  generation_expression
FROM information_schema.columns
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND generation_expression IS NOT NULL
  AND REGEXP_LIKE(generation_expression, '\\b(MD5|SHA1|SHA)\\s*\\(', 'i')
ORDER BY table_schema, table_name, ordinal_position;

For every match, pick one of these paths:

  • Preferred: change the expression to SHA2() and adjust column length, indexes, and application expectations.
  • Acceptable during transition: replace the generated column with a normal stored column and maintain the value with application logic or triggers.
  • Best cleanup: remove the hash column if it is redundant.

Be careful with storage size. MD5() returns 32 hexadecimal characters. SHA1() returns 40. SHA2(..., 256) returns 64. If you store the binary form with UNHEX(), MD5 needs BINARY(16), SHA-1 needs BINARY(20), and SHA-256 needs BINARY(32).

Look Beyond Tables

Generated columns are the first upgrade consideration, but they are not the only place legacy hashes hide. Search for MD5(), SHA1(), and SHA() in:

  • Application SQL strings.
  • Views.
  • Stored procedures and stored functions.
  • Triggers.
  • Events.
  • Data migration scripts.
  • ETL jobs, reporting queries, dashboards, and scheduled operational scripts.

These queries help with stored database objects:

SELECT table_schema, table_name
FROM information_schema.views
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND REGEXP_LIKE(view_definition, '\\b(MD5|SHA1|SHA)\\s*\\(', 'i')
ORDER BY table_schema, table_name;
SELECT routine_schema, routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND REGEXP_LIKE(routine_definition, '\\b(MD5|SHA1|SHA)\\s*\\(', 'i')
ORDER BY routine_schema, routine_name;
SELECT trigger_schema, trigger_name, event_object_table
FROM information_schema.triggers
WHERE trigger_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND REGEXP_LIKE(action_statement, '\\b(MD5|SHA1|SHA)\\s*\\(', 'i')
ORDER BY trigger_schema, trigger_name;
SELECT event_schema, event_name
FROM information_schema.events
WHERE event_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND REGEXP_LIKE(event_definition, '\\b(MD5|SHA1|SHA)\\s*\\(', 'i')
ORDER BY event_schema, event_name;

Metadata is only half the story. It tells you what is already stored in the server, and some routine definitions require appropriate privileges to inspect. It will not find SQL assembled in application code, migration tools, cron jobs, notebooks, or dashboards. Search those repositories too.

If You Still Need The Component

Sometimes you cannot migrate everything before the upgrade window. That is what the Legacy Hashing Component is for: buying time.

Install it like this:

INSTALL COMPONENT 'file://component_classic_hashing';

Then validate the functions your application still needs:

SELECT MD5('abc'), SHA1('abc'), SHA('abc');

Keep these operational details in mind:

  • Install the component on every server that may execute affected SQL: primaries, replicas, read-only reporting servers, failover targets, and test environments.
  • Component loading is local to the server. MySQL documentation states that components are loaded locally and are not replicated.
  • Installed components are registered in the mysql.component system table and loaded again on subsequent server restarts.
  • Load the component before restoring schema objects, routines, or views that reference these functions.

Treat this as a tracked compatibility dependency. If the component has to be installed, write down why, which objects still need it, and what needs to happen before it can be removed.

Behavioral Differences To Review

The component is meant to preserve common application behavior, but it is not identical to having the old built-in functions in the server binary.

First, the component functions work on string arguments. If current code hashes another data type, make the conversion explicit:

SELECT MD5(CAST(customer_id AS CHAR));

Second, if FIPS mode is enabled when the component initializes, MD5() returns NULL. That is worth checking if the hash is used in comparisons, unique keys, application identifiers, or migration scripts.

Third, generated columns cannot use these component-provided functions. That point is easy to miss because the same function name may still work in a plain SELECT.

This is why an upgrade test should include representative schema definitions, data, SQL modes, component configuration, restore flow, and security settings.

Security, Compliance, and Business Impact

Installing the Legacy Hashing Component should also be reviewed as a security and compliance decision, not only as an upgrade workaround.

Re-enabling MD5(), SHA1(), and SHA() can trigger security scanner findings, source-code review findings, and hardening-baseline exceptions. Some tools may flag the SQL usage directly. Others may flag the presence of a legacy cryptographic component or require evidence that the functions are not being used for security-sensitive purposes.

This is especially important in regulated or government-facing environments. FIPS-oriented deployments should avoid MD5, and this component does not make MD5 a good choice in those environments. If FIPS mode is enabled when the component initializes, MD5() returns NULL, so compatibility may still fail. SHA-1 also has a shrinking set of acceptable uses, and organizations following NIST, DISA STIG, CIS Benchmark, FedRAMP, PCI DSS, or internal secure-configuration baselines may require a documented exception before allowing legacy hashes to remain enabled.

The business impact can be larger than the technical change. A customer security review may ask whether the product uses MD5 or SHA1. A procurement review may require a migration plan. A DISA or CIS-aligned environment may require a POA&M item, waiver, compensating control, or target removal date. A security team may accept the component for temporary upgrade compatibility but reject it as a permanent product dependency.

If you do install component_classic_hashing, treat it like an exception with clear reasoning:

  • why it is installed,
  • which applications, schemas, routines, or scripts still require it,
  • whether any use is security-sensitive,
  • which environments have it enabled,
  • what compensating controls apply,
  • and when it will be removed.

That record matters. Without it, a short-term compatibility decision can turn into a recurring audit finding.

Suggested Upgrade Plan

Before upgrading to MySQL 9.6 or later:

  1. Inventory all uses of MD5(), SHA1(), and SHA() in schema metadata, application code, migration scripts, and operational tooling.
  2. Move generated columns first. Prefer SHA2() expressions; use normal maintained columns only when legacy compatibility requires it.
  3. For every remaining legacy hash, decide whether it can move to SHA2() now.
  4. Choose the SHA2() digest size based on data sensitivity, retention period, interoperability needs, and storage/index cost.
  5. Use component_classic_hashing only for code that cannot be migrated before the upgrade.
  6. If using the component, install it on every server role that may execute affected SQL.
  7. Add explicit CAST() calls where legacy hashes are calculated over non-string values.
  8. Test restore, replication, failover, and application startup workflows with the component state you expect in production.
  9. Track remaining legacy-function usage so the component can eventually be removed.

A Practical Decision Matrix

Current usageRecommended action
New code needing a SQL-level hashUse SHA2(). Pick the digest size based on data sensitivity and retention needs.
Generated column using MD5() or SHA1()Redesign before upgrade. Prefer SHA2(); use a maintained normal column only for legacy compatibility.
View, stored program, trigger, or event using legacy hashesMigrate to SHA2() where possible. Install the component only if the object cannot be migrated yet.
Application query using MD5() or SHA1()Prefer an application or SQL migration to SHA2(). Use the component as temporary compatibility.
Hashing non-string values with the componentAdd explicit CAST(... AS CHAR) so the conversion is intentional.
Security-sensitive hashingDo not use MD5 or SHA-1. Use SHA2() only when a general deterministic hash is appropriate; use password-hashing algorithms for passwords.
Security & ComplianaceAvoid having dependency on SHA1()/MD5() and consider not requiring component at all. Note, the component returns NULL for MD5() when FIPS mode is read as enabled at component initialization.

Closing Thought

The headline is not just that MD5() and SHA1() moved. The headline is that MySQL is making legacy hashing optional.

That is a good moment to clean up old assumptions. If your system still depends on these functions, component_classic_hashing can help you get through the transition. But the direction of travel should be clear: prefer SHA2(), pick an appropriate digest size, review generated columns carefully, and treat the component as the last alternative rather than the default answer.

As always, a big THANK YOU for using MySQL.

References