MySQL 9.7 LTS Deep Dive: Free Enterprise Features, Hypergraph Optimizer, JSON DML

Release Context and Upgrade Paths

MySQL 9.7.0 LTS was released on 2026-04-21, the same day MySQL 8.0.46 became the final 8.0 minor release, marking 8.0 end-of-life. For community users this is a substantive upgrade: five previously enterprise-only components, the Hypergraph optimizer, and DML-capable JSON Duality Views are now included in the community edition.

Two upgrade strategies are outlined:

Conservative: 8.0 → 8.4 LTS (minimal changes required).

New features: Adopt 9.7 LTS directly for new projects.

If already stable on 8.4, do not force an upgrade solely for the version number.

Before upgrading, run a compatibility check with MySQL Shell and output JSON for CI integration:

# Read-only check; --target-version specifies target LTS
mysqlsh -- util check-for-server-upgrade --user=root --host=127.0.0.1 --port=3306 \
  --target-version=9.7.0 --output-format=JSON

9.7 LTS offers 5 years Premier plus 3 years Extended support, making it suitable for production baselines.

Five Enterprise Components Moved to Community

The following components are now available in the community edition and can be installed via INSTALL COMPONENT:

Replication Applier Metrics (exposed in performance_schema)

Group Replication Flow Control Statistics

Resource Manager

Primary Election

Telemetry (OpenTelemetry metrics, traces, logs)

Example installation and query:

-- Replication Applier Metrics to performance_schema
INSTALL COMPONENT 'file://component_replication_applier_metrics';
-- OpenTelemetry metrics, traces, logs
INSTALL COMPONENT 'file://component_telemetry';

-- Note: when replica_parallel_workers=0, Applier Metrics tables are empty
SELECT * FROM performance_schema.replication_applier_metrics;
SELECT * FROM performance_schema.replication_applier_progress_by_worker;

Telemetry pushes directly to an otel-collector without custom exporters:

-- Collector HTTP endpoint
SET GLOBAL telemetry.otel_exporter_otlp_traces_endpoint = 'http://otel-collector:4318/v1/traces';
SET GLOBAL telemetry.trace_enabled = ON;

Hypergraph Optimizer

The classic optimizer uses left-deep trees, limiting join-order search to chain structures and making bushy plans and sort-property participation difficult. Hypergraph models tables as nodes and join predicates as edges, enumerates connected subgraphs via dynamic programming, and compares hash joins and nested-loop joins within a single cost model.

Official TPC-DS SF1 results (101 queries): 77 faster, 24 slower, geometric mean improvement 22.45%. However, 14 queries regressed by more than 50%, so the optimizer is disabled by default .

Three ways to enable, from least to most impact:

-- 1. Session level, verify in current connection
SET SESSION optimizer_switch = 'hypergraph_optimizer=on';

-- 2. Single statement with SET_VAR hint, no side effects
SELECT /*+ SET_VAR(optimizer_switch='hypergraph_optimizer=on') */
  c.id, COUNT(*) AS cnt
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
GROUP BY c.id ORDER BY cnt DESC LIMIT 10;

-- 3. Persistent, survives restart; benchmark thoroughly before enabling globally
SET PERSIST optimizer_switch = 'hypergraph_optimizer=on';

Validation method: run EXPLAIN ANALYZE twice (baseline and with session switch) and compare actual time and rows. Only pin the hint for statements proven faster.

-- Baseline run, then enable session switch and re-run, compare actual time and rows
EXPLAIN ANALYZE SELECT ...;
SET SESSION optimizer_switch = 'hypergraph_optimizer=on';
EXPLAIN ANALYZE SELECT ...;

External sysbench tests show read-only-distinct +26% and range=10000 +15%, mainly on multi-table joins and sorted aggregations.

JSON Duality View Supports DML

Community edition previously allowed only read-only views. 9.7 adds INSERT, UPDATE, DELETE and auto-generated primary keys (omitted in JSON). A view DML statement is split into multiple base-table sub-statements, executed atomically with full rollback on failure; triggers and foreign-key cascades fire normally.

Example schema and view definition:

CREATE TABLE customers(customer_id INT PRIMARY KEY, NAME VARCHAR(100));
CREATE TABLE orders(
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Operations declared in WITH clause are writable; undeclared objects default to read-only
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_dv AS
SELECT JSON_DUALITY_OBJECT(
  WITH (INSERT, UPDATE, DELETE)
  '_id': order_id,
  'amount': amount,
  'customer': (
    SELECT JSON_DUALITY_OBJECT(
      WITH (INSERT, UPDATE)
      'customer_id': customer_id,
      'name': name
    FROM customers WHERE customers.customer_id = orders.customer_id
  )
FROM orders;

DML examples (auto-increment order_id omitted in JSON):

-- Insert
INSERT INTO order_dv VALUES ('{"amount":1299.99,"customer":{"customer_id":1}}');

-- Update by _id (single document)
UPDATE order_dv SET data = '{"_id":1,"amount":999.00}'
WHERE JSON_EXTRACT(data, '$._id') = 1;

-- Delete by _id
DELETE FROM order_dv WHERE JSON_EXTRACT(data, '$._id') = 1;

Rolling Upgrade and Container CPU Awareness

replica_allow_higher_version_source

defaults to ON, allowing a lower-version replica to connect to a higher-version source. Recommended rolling upgrade sequence: upgrade primary first, observe, then upgrade replicas one by one.

-- Enable on replicas before upgrading primary
SET GLOBAL replica_allow_higher_version_source = ON;

CPU detection now respects cpuset and cgroup limits; in Kubernetes a 4-core limit will not be mistaken for the host's 32 cores when sizing thread pools.

Password Storage Switched to PBKDF2-SHA512

caching_sha2_password

adds a PBKDF2 storage format; default remains CRYPT5, requiring no client changes. New passwords or resets use PBKDF2-SHA512 when enabled.

-- Use PBKDF2-SHA512 for new or reset passwords
SET GLOBAL caching_sha2_password_storage_format = 'PBKDF2_SHA512';

-- Enforce format: existing accounts prompted to change password on next login, not locked
SET GLOBAL caching_sha2_password_enforce_storage_format = ON;

Replication copies the primary's hash to replicas; all nodes must share the same configuration to avoid inconsistent behavior for the same account.

Enterprise-Only and Experimental Features

Dynamic Data Masking remains enterprise-only; policies bind to columns and return masked values based on executing user/role without application changes.

Vector Search is not GA in 9.7; Oracle is paving the road via innovation releases. Do not migrate databases for vector capabilities yet.

Conclusion

Schedule 8.0 upgrades promptly; use 9.7 LTS for new projects. Enable replication metrics and Telemetry first, adopt Hypergraph optimizer per-statement via gradual rollout, and align PBKDF2 adoption with password-rotation cadence.