Pre-Upgrade Checks, Deprecated Features, SQL Modes, Collations, and Authentication Changes

Learning outcomes

An upgrade is not “replace binaries and hope.” ServiceHub may contain old authentication plugins, identifiers that become reserved, SQL-mode assumptions, collation-sensitive comparisons, removed variables, and connectors that cannot authenticate to the target. Pre-upgrade work turns those unknowns into owned remediation items before a production node is touched.

Start with exact versions, not “latest”

MySQL has LTS and Innovation tracks with explicit supported paths. A change request should record current server version, target server version, operating system, MySQL Shell and Router versions when used, connector versions, storage engine assumptions, and topology. Upgrade support is a property of that matrix.

SELECT @@version AS server_version,       @@version_comment AS edition,       @@sql_mode AS sql_mode,       @@character_set_server AS server_charset,       @@collation_server AS server_collation;SHOW VARIABLES WHERE Variable_name IN('authentication_policy','lower_case_table_names', 'transaction_isolation','binlog_format','gtid_mode');SELECT user,host,plugin,account_locked,password_expiredFROM mysql.user ORDER BY user,host;

This inventory needs an administrative audit identity, not the runtime ServiceHub account. Collecting metadata is not permission to make unrelated security or collation changes in the same window.

Use MySQL Shell Upgrade Checker as one preflight layer

MySQL Shell’s util.checkForServerUpgrade() runs automated compatibility checks for a selected target and can emit JSON for release automation. Current MySQL Shell 8.4 documentation requires RELOAD, PROCESS, and SELECT for the checking account.

CREATE USER IF NOT EXISTS 'upgrade_check'@'127.0.0.1'  IDENTIFIED BY 'Use-A-Disposable-Lab-Secret!';GRANT RELOAD, PROCESS, SELECT ON *.*TO 'upgrade_check'@'127.0.0.1';SHOW GRANTS FOR 'upgrade_check'@'127.0.0.1';

util.checkForServerUpgrade(  'upgrade_check@127.0.0.1:3306',  {    targetVersion: '8.4.10',    outputFormat: 'JSON'    // Add configPath when a configuration-file check requires it.  });

The target cannot exceed the Shell version used by the checker. This course therefore pairs Server 8.4.10 and Shell 8.4.10 for reproducible local work. Future upgrades must re-run with current target documentation rather than relying on this chapter’s historical result.

Negative authorization test: reject the blanket-admin shortcut

CREATE USER IF NOT EXISTS 'upgrade_check_too_small'@'127.0.0.1'  IDENTIFIED BY 'Disposable-Only!';GRANT SELECT ON servicehub_change_lab.*TO 'upgrade_check_too_small'@'127.0.0.1';SHOW GRANTS FOR 'upgrade_check_too_small'@'127.0.0.1';-- Run Upgrade Checker with this identity.-- Expected: a privilege-related failure/incomplete check.-- Repair with the documented RELOAD, PROCESS, SELECT requirement,-- not GRANT ALL ON *.*.

The failure proves the difference between application-schema read access and server-wide inspection needed by the utility. Least privilege still applies to maintenance tooling.

Authentication, SQL modes, collations, and syntax need targeted tests

In MySQL 8.4, caching_sha2_password is the default authentication plugin and the server-side mysql_native_password plugin is disabled by default. Old accounts or clients can therefore fail after an upgrade even when table data is healthy. SQL modes can alter acceptance and conversion behavior; collations affect comparison and ordering; reserved-word changes can break unquoted identifiers.

SELECT user,host,pluginFROM mysql.userWHERE plugin <> 'caching_sha2_password'ORDER BY plugin,user,host;SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAMEFROM information_schema.SCHEMATAWHERE SCHEMA_NAME='servicehub_change_lab';SELECT TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAMEFROM information_schema.COLUMNSWHERE TABLE_SCHEMA='servicehub_change_lab'  AND CHARACTER_SET_NAME IS NOT NULLORDER BY TABLE_NAME,ORDINAL_POSITION;SELECT @@sql_mode;SHOW WARNINGS;

Do not automatically “modernize everything” inside the binary upgrade. Separating authentication, collation, and SQL semantic changes reduces simultaneous variables and makes rollback/diagnosis clearer.

Convert findings into an owned register

A checker summary with zero errors still does not prove application correctness, topology safety, restore readiness, or performance. Those dimensions require separate evidence.

The Upgrade Checker is a scanner, not an oracle

The utility automates many checks that would be easy to miss manually, but it cannot understand every application invariant or operational dependency. It does not know whether a collation change alters a customer-facing ordering rule, whether a driver embedded in an old appliance can authenticate, whether an ORM generates newly reserved identifiers, or whether a query-plan change violates a latency objective. Treat its JSON output as one input to an upgrade work queue.

For each error, warning, and notice, record the check identifier, affected object/configuration, owning team, remediation change, staging test, and acceptance result. Then review the target release notes for changes not fully expressible as automatic checks. If an item is intentionally accepted, document why and who approved it.

Reserved words and collation behavior can be probed directly

MySQL exposes keyword metadata, so you can inventory identifiers against the target manual and avoid guessing. Collation compatibility requires semantic tests, not only metadata inspection. For example, a business rule that treats two strings as distinct may behave differently under a case- or accent-insensitive collation.

SELECT WORD, RESERVEDFROM information_schema.KEYWORDSWHERE WORD IN ('SYSTEM','WINDOW','RANK','GROUP')ORDER BY WORD;SELECT 'resume' = 'résumé' COLLATE utf8mb4_0900_ai_ci AS accent_insensitive;SELECT 'A' = 'a' COLLATE utf8mb4_0900_ai_ci AS case_insensitive;

Use domain-specific golden values rather than assuming one collation is universally correct. Changing collation can alter uniqueness, sort order, index behavior, and comparison semantics. If the upgrade does not require a collation migration, keeping that semantic change in a separate release often reduces risk.

Authentication compatibility must include a real client handshake

Account metadata tells you which plugin the server expects, but only a real connection from the production connector proves client compatibility, TLS behavior, certificate trust, and authentication negotiation. MySQL 8.4’s default caching_sha2_password is current best practice; the server-side mysql_native_password plugin is disabled by default. Enabling a deprecated plugin merely to preserve an obsolete client can postpone rather than solve the compatibility problem.

Stage a credential rotation with the exact connector version, verify encrypted transport, and capture the negative result from an unsupported client. The minimum fix should be connector/account modernization, not a broad server-wide weakening of authentication policy.

Manual pre-upgrade review complements automated checks

Connection security and error-log evidence belong in upgrade preflight

An account can exist with plausible grants and still fail because of authentication-plugin or transport requirements. Run a positive connection with the real supported client and inspect the negotiated TLS session. Run the intentionally insufficient checker identity as the negative authorization test and preserve the returned error. Then inspect the server error log around the test window so the client-side failure can be correlated with server-side evidence.

SHOW GRANTS FOR 'upgrade_check'@'127.0.0.1';SHOW SESSION STATUS LIKE 'Ssl_cipher';SHOW SESSION STATUS LIKE 'Ssl_version';SELECT LOGGED,PRIO,ERROR_CODE,SUBSYSTEM,DATAFROM performance_schema.error_logORDER BY LOGGED DESCLIMIT 20;

A nonempty TLS cipher proves encryption for this session, not hostname validation by itself; hostname/certificate verification is a client configuration property and should be tested using the real connector. The Performance Schema error-log table is a bounded diagnostic view, not an immutable security audit trail.

Authoritative references

• MySQL 8.4 — Upgrading MySQL
• MySQL 8.4 — Upgrade Paths
• MySQL 8.4 — Upgrade Best Practices
• MySQL Shell 8.4 — Upgrade Checker Utility
• MySQL 8.4 — InnoDB and Online DDL
• MySQL 8.4 — Upgrading Group Replication
• MySQL 8.4 — Downgrading MySQL
• MySQL 8.4 — Native Authentication Plugin
• MySQL Community Server 8.4 Downloads
• MySQL Shell 8.4 Downloads