Description:
As of MySQL 9.6.0, `MD5()`, `SHA1()` and `SHA()` were removed from the server binary and moved to the Legacy Hashing Component (`component_classic_hashing`), which is not installed by default. The upgrade checker (`util.checkForServerUpgrade()`) does not appear to have a check that flags usage of these removed functions when the target release is ≥ 9.6.0. The existing `removedFunctions` check covers the 8.0.11-era removals (e.g. `PASSWORD`, `ENCODE`), not the 9.6.0 hashing-function removal.
This lead to outage, triggered by a fully supported major-version upgrade path. Because MySQL Shell's upgrade checker has no check for the 9.6.0 removal of `MD5()`/`SHA1()`/`SHA()`, `util.checkForServerUpgrade()` returns a clean report for a server that will be broken by the upgrade, so the failure surfaces only afterward — in production, once the major-version upgrade has already completed and rollback is expensive or impossible.
- The failure mode is severe and unrecoverable in place. A table with a **functional index or CHECK constraint** using these functions prevents the server from starting at all: the data-dictionary upgrade cannot open the table and aborts (`Data Dictionary initialization failed; Aborting`), taking down the entire instance and every database on it — and the classic_hashing component cannot be installed because there is no running server to install it on.
[ERROR] Can not open table `appdb`.`users`; functions in constraints, partitions, or virtual columns may have failed.
[ERROR] Data Dictionary initialization failed.
[ERROR] Aborting
- A table with a **STORED/VIRTUAL generated column** using these functions lets the server start but leaves that table completely frozen: `SELECT` (even of unrelated columns), `INSERT`, `ALTER`, `DROP COLUMN`, and even `DROP TABLE` all fail. Installing `component_classic_hashing` does not remediate either case — it only changes the error from `ERROR 1305 (function does not exist)` to `ERROR 3763 (disallowed function)` — because loadable functions are not permitted in generated-column, functional-index, `CHECK`, or `DEFAULT` expressions.
mysql> ALTER TABLE t_stored ADD COLUMN note VARCHAR(20);
ERROR 1305 (42000) at line 1: FUNCTION test.md5 does not exist
> The only fix is a schema change applied **before** the upgrade, which is precisely what an upgrade-checker error would enable. The affected pattern is common (hash/dedup keys and checksums built with `MD5()`;
How to repeat:
### Case 1: server fails to start (functional index / CHECK constraint)
1. On MySQL 8.4.x (MD5 still built-in), create a table whose FUNCTIONAL INDEX (or CHECK constraint) uses MD5, and add data.
```
CREATE DATABASE appdb;
USE appdb;
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100),
email_hash CHAR(32) AS (MD5(email)) STORED,
KEY ((MD5(email))) -- functional index on MD5()
);
INSERT INTO users (id, email) VALUES (1,'a@xyz.com'),(2,'b@xyz.com');
```
2. Shutdown the 8.4.x server down cleanly.
3. Start a MySQL 9.6+ server (tested: 9.7.1) on the SAME data directory (in-place upgrade).
4. Observe: the server aborts during the data-dictionary upgrade and never reaches "ready for connections".
Error log:
```
[Server] Data dictionary upgrading from version '80300' to '90200'.
[Server] Error MY-001305 ... FUNCTION mysql.md5 does not exist
[Server] Error MY-001033 ... Incorrect information in file: './appdb/users.frm'
[ERROR] [MY-013135] Incorrect information in file: './appdb/users.frm'
[Server] Can not open table `appdb`.`users`; functions in constraints,
partitions, or virtual columns may have failed.
[ERROR] [MY-010020] Data Dictionary initialization failed.
[ERROR] [MY-010119] Aborting
```
Result: The entire instance is unavailable. No database is reachable, and the
classic_hashing component cannot be installed because there is no running server.
### Case 2: table permanently inaccessible (STORED / VIRTUAL generated column)
1. On MySQL 8.4.x (MD5 still built-in), create a table whose STORED (or VIRTUAL) generated column uses MD5, and add data.
```
CREATE DATABASE test;
USE test;
CREATE TABLE t_stored (
id INT PRIMARY KEY,
email VARCHAR(100),
h CHAR(32) AS (MD5(email)) STORED
);
INSERT INTO t_stored (id, email) VALUES (1,'a@xyz.com');
```
2. Shutdown the 8.4.x server down cleanly.
3. Start a MySQL 9.6+ server (tested: 9.7.1) on the SAME data directory. The data-dictionary upgrade succeeds and the server DOES start.
4. Every operation on the table fails (component NOT yet installed):
```
SELECT id, email FROM t_stored; -- ERROR 1305: FUNCTION test.md5 does not exist
INSERT INTO t_stored (id,email) VALUES (2,'c@xyz.com'); -- ERROR 1305
ALTER TABLE t_stored ADD COLUMN note VARCHAR(20); -- ERROR 1305
ALTER TABLE t_stored DROP COLUMN h; -- ERROR 1305 (cannot drop the offending column)
```
5. Install the legacy hashing component and retry — it does NOT help; the error only changes from 1305 to 3763:
```
INSTALL COMPONENT 'file://component_classic_hashing';
SELECT id, email FROM t_stored; -- ERROR 3763: Expression of generated column 'h' contains a disallowed function: md5.
ALTER TABLE t_stored DROP COLUMN h; -- ERROR 3763
DROP TABLE t_stored; -- ERROR 3763 (cannot even drop the whole table)
```
Result: the table cannot be read, written, altered, or dropped. There is no
in-place recovery path — the schema must be fixed before upgrading.
Suggested fix:
Add a check (target boundary 9.6.0) that scans views, routines, triggers, events, generated columns, functional indexes, `CHECK` constraints and `DEFAULT` expressions for MD5/SHA1/SHA usage and reports them as errors that must be resolved before upgrade, with remediation guidance to migrate to `SHA2()`.
The generated-column / functional-index / `CHECK` / `DEFAULT` cases should note that the classic_hashing component does not remediate them and a schema change is required.
Description: As of MySQL 9.6.0, `MD5()`, `SHA1()` and `SHA()` were removed from the server binary and moved to the Legacy Hashing Component (`component_classic_hashing`), which is not installed by default. The upgrade checker (`util.checkForServerUpgrade()`) does not appear to have a check that flags usage of these removed functions when the target release is ≥ 9.6.0. The existing `removedFunctions` check covers the 8.0.11-era removals (e.g. `PASSWORD`, `ENCODE`), not the 9.6.0 hashing-function removal. This lead to outage, triggered by a fully supported major-version upgrade path. Because MySQL Shell's upgrade checker has no check for the 9.6.0 removal of `MD5()`/`SHA1()`/`SHA()`, `util.checkForServerUpgrade()` returns a clean report for a server that will be broken by the upgrade, so the failure surfaces only afterward — in production, once the major-version upgrade has already completed and rollback is expensive or impossible. - The failure mode is severe and unrecoverable in place. A table with a **functional index or CHECK constraint** using these functions prevents the server from starting at all: the data-dictionary upgrade cannot open the table and aborts (`Data Dictionary initialization failed; Aborting`), taking down the entire instance and every database on it — and the classic_hashing component cannot be installed because there is no running server to install it on. [ERROR] Can not open table `appdb`.`users`; functions in constraints, partitions, or virtual columns may have failed. [ERROR] Data Dictionary initialization failed. [ERROR] Aborting - A table with a **STORED/VIRTUAL generated column** using these functions lets the server start but leaves that table completely frozen: `SELECT` (even of unrelated columns), `INSERT`, `ALTER`, `DROP COLUMN`, and even `DROP TABLE` all fail. Installing `component_classic_hashing` does not remediate either case — it only changes the error from `ERROR 1305 (function does not exist)` to `ERROR 3763 (disallowed function)` — because loadable functions are not permitted in generated-column, functional-index, `CHECK`, or `DEFAULT` expressions. mysql> ALTER TABLE t_stored ADD COLUMN note VARCHAR(20); ERROR 1305 (42000) at line 1: FUNCTION test.md5 does not exist > The only fix is a schema change applied **before** the upgrade, which is precisely what an upgrade-checker error would enable. The affected pattern is common (hash/dedup keys and checksums built with `MD5()`; How to repeat: ### Case 1: server fails to start (functional index / CHECK constraint) 1. On MySQL 8.4.x (MD5 still built-in), create a table whose FUNCTIONAL INDEX (or CHECK constraint) uses MD5, and add data. ``` CREATE DATABASE appdb; USE appdb; CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(100), email_hash CHAR(32) AS (MD5(email)) STORED, KEY ((MD5(email))) -- functional index on MD5() ); INSERT INTO users (id, email) VALUES (1,'a@xyz.com'),(2,'b@xyz.com'); ``` 2. Shutdown the 8.4.x server down cleanly. 3. Start a MySQL 9.6+ server (tested: 9.7.1) on the SAME data directory (in-place upgrade). 4. Observe: the server aborts during the data-dictionary upgrade and never reaches "ready for connections". Error log: ``` [Server] Data dictionary upgrading from version '80300' to '90200'. [Server] Error MY-001305 ... FUNCTION mysql.md5 does not exist [Server] Error MY-001033 ... Incorrect information in file: './appdb/users.frm' [ERROR] [MY-013135] Incorrect information in file: './appdb/users.frm' [Server] Can not open table `appdb`.`users`; functions in constraints, partitions, or virtual columns may have failed. [ERROR] [MY-010020] Data Dictionary initialization failed. [ERROR] [MY-010119] Aborting ``` Result: The entire instance is unavailable. No database is reachable, and the classic_hashing component cannot be installed because there is no running server. ### Case 2: table permanently inaccessible (STORED / VIRTUAL generated column) 1. On MySQL 8.4.x (MD5 still built-in), create a table whose STORED (or VIRTUAL) generated column uses MD5, and add data. ``` CREATE DATABASE test; USE test; CREATE TABLE t_stored ( id INT PRIMARY KEY, email VARCHAR(100), h CHAR(32) AS (MD5(email)) STORED ); INSERT INTO t_stored (id, email) VALUES (1,'a@xyz.com'); ``` 2. Shutdown the 8.4.x server down cleanly. 3. Start a MySQL 9.6+ server (tested: 9.7.1) on the SAME data directory. The data-dictionary upgrade succeeds and the server DOES start. 4. Every operation on the table fails (component NOT yet installed): ``` SELECT id, email FROM t_stored; -- ERROR 1305: FUNCTION test.md5 does not exist INSERT INTO t_stored (id,email) VALUES (2,'c@xyz.com'); -- ERROR 1305 ALTER TABLE t_stored ADD COLUMN note VARCHAR(20); -- ERROR 1305 ALTER TABLE t_stored DROP COLUMN h; -- ERROR 1305 (cannot drop the offending column) ``` 5. Install the legacy hashing component and retry — it does NOT help; the error only changes from 1305 to 3763: ``` INSTALL COMPONENT 'file://component_classic_hashing'; SELECT id, email FROM t_stored; -- ERROR 3763: Expression of generated column 'h' contains a disallowed function: md5. ALTER TABLE t_stored DROP COLUMN h; -- ERROR 3763 DROP TABLE t_stored; -- ERROR 3763 (cannot even drop the whole table) ``` Result: the table cannot be read, written, altered, or dropped. There is no in-place recovery path — the schema must be fixed before upgrading. Suggested fix: Add a check (target boundary 9.6.0) that scans views, routines, triggers, events, generated columns, functional indexes, `CHECK` constraints and `DEFAULT` expressions for MD5/SHA1/SHA usage and reports them as errors that must be resolved before upgrade, with remediation guidance to migrate to `SHA2()`. The generated-column / functional-index / `CHECK` / `DEFAULT` cases should note that the classic_hashing component does not remediate them and a schema change is required.