
I Tried Out the Now-GA Amazon RDS for MySQL 9.7.4
This page has been translated by machine translation. View original
Introduction
On November 18, 2026, Amazon RDS for MySQL added support for MySQL 9.7 (9.7.4).
MySQL 9.7 is the second LTS after 8.4, and 9.7.4 is the first version offered on RDS for MySQL.
The versions from 8.4 to 9.7 are as follows.
| Version | Type | Handling on RDS |
|---|---|---|
| 8.4 | LTS | Standard support until July 31, 2029 |
| 9.0–9.6 | Innovation Release | Provided in the Database Preview environment |
| 9.7 | LTS | Standard support until July 31, 2031 |
9.7.4 lets you use the Hypergraph Optimizer for queries with many joins, JSON Duality Views that let you read and write relational tables as JSON documents, and Replication Applier Metrics that let you check the replica's apply status. In this article, I try these three.
The July 2026 preview is covered in the following article.
Test Environment
| Item | Details |
|---|---|
| Engine | RDS for MySQL 9.7.4 (GA) |
| Region | ap-northeast-1 |
| Instance | db.t4g.micro (2 vCPU), Single-AZ, gp3 20 GiB. The read replica is also db.t4g.micro |
| Connection | No public access. I accessed an EC2 instance in the same VPC via SSM and connected over TLS 1.3 from the mysql 8.4.13 client |
Hypergraph Optimizer
hypergraph_optimizer can now be switched from the default off to on.
How to Enable It
Switching at the session level and switching per statement with the SET_VAR hint both ran without errors.
SET optimizer_switch='hypergraph_optimizer=on';
SELECT @@optimizer_switch LIKE '%hypergraph_optimizer=on%' AS hypergraph_on;
+---------------+
| hypergraph_on |
+---------------+
| 1 |
+---------------+
SELECT /*+ SET_VAR(optimizer_switch='hypergraph_optimizer=on') */ 1 AS hint_test;
SET GLOBAL optimizer_switch='hypergraph_optimizer=on' results in ERROR 1227 for the RDS admin user. The SUPER or SYSTEM_VARIABLES_ADMIN privilege is required. To apply it to the whole instance, use a parameter group.
aws rds modify-db-parameter-group --db-parameter-group-name rds-mysql-97-verify --parameters ParameterName=optimizer_switch,ParameterValue=hypergraph_optimizer=on,ApplyMethod=immediate
optimizer_switch is a dynamic parameter, so it takes effect without a reboot. On a connection newly opened after applying it, it was enabled without running SET.
SELECT @@global.optimizer_switch LIKE '%hypergraph_optimizer=on%' AS global_on, @@session.optimizer_switch LIKE '%hypergraph_optimizer=on%' AS session_on;
+-----------+------------+
| global_on | session_on |
+-----------+------------+
| 1 | 1 |
+-----------+------------+
With it enabled, an Inner hash join appeared in EXPLAIN FORMAT=TREE. This is a query joining three tables, customers, orders, and regions, in an e-commerce-style shop schema.
EXPLAIN FORMAT=TREE SELECT COUNT(*) FROM orders o JOIN customers c ON c.id = o.customer_id JOIN regions r ON r.id = c.region_id WHERE o.status = 2;
EXPLAIN: -> Aggregate: count(0) (cost=385723..385723 rows=1)
-> Inner hash join (c.id = o.customer_id) (cost=326803..374904 rows=84358)
-> Nested loop inner join (cost=0.325..32487 rows=99841)
-> Covering index scan on r using PRIMARY (cost=0.664..33.2 rows=50)
-> Covering index lookup on c using region_id (region_id = r.id) (cost=0.325..649 rows=1997)
-> Hash
-> Index lookup on o using status (status = 2) (cost=3.54..298600 rows=84358)
After confirming, reset it to return to the default. Run the reset after ParameterApplyStatus in describe-db-instances is no longer applying.
aws rds reset-db-parameter-group --db-parameter-group-name rds-mysql-97-verify --parameters ParameterName=optimizer_switch,ApplyMethod=immediate
JSON Duality Views
The specification and syntax are in the MySQL 9.7 reference.
Creating Views
From the two tables customers and orders, I created a two-level parent-child view. customer_orders_dv, which has WITH(INSERT,UPDATE,DELETE) attached to JSON_DUALITY_OBJECT, is updatable. customer_orders_ro, created without WITH, is read-only, and an INSERT into it resulted in ERROR 6490 (Missing INSERT tag on table dv.customers, path '$').
CREATE DATABASE dv;
USE dv;
CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE orders (
order_id INT PRIMARY KEY, customer_id INT, product VARCHAR(100), amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Updatable view (INSERT/UPDATE/DELETE allowed for both parent and child)
CREATE JSON RELATIONAL DUALITY VIEW customer_orders_dv AS
SELECT JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
'_id': customer_id,
'customer_name': name,
'orders': (
SELECT JSON_ARRAYAGG(
JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
'order_id': order_id, 'product': product, 'amount': amount))
FROM orders WHERE orders.customer_id = customers.customer_id))
FROM customers;
-- Read-only view (no WITH clause)
CREATE JSON RELATIONAL DUALITY VIEW customer_orders_ro AS
SELECT JSON_DUALITY_OBJECT(
'_id': customer_id,
'customer_name': name,
'orders': (
SELECT JSON_ARRAYAGG(JSON_DUALITY_OBJECT('order_id': order_id, 'product': product, 'amount': amount))
FROM orders WHERE orders.customer_id = customers.customer_id))
FROM customers;
INSERT, UPDATE, DELETE
I inserted a JSON document containing one parent and two children. The 3 affected rows break down into 1 row in customers and 2 rows in orders.
INSERT INTO customer_orders_dv VALUES ('{"_id": 1, "customer_name": "Alice", "orders": [{"order_id": 1, "product": "Laptop", "amount": 1299.99}, {"order_id": 2, "product": "Mouse", "amount": 19.99}]}');
Query OK, 3 rows affected (0.01 sec)
SELECTing from the view returned JSON that includes _metadata.etag.
SELECT * FROM customer_orders_dv\G
data: {"_id": 1, "orders": [{"amount": 1299.99, "product": "Laptop", "order_id": 1}, {"amount": 19.99, "product": "Mouse", "order_id": 2}], "_metadata": {"etag": "e6d40eabf2e070ffd2719c6755d50f1a"}, "customer_name": "Alice"}
For UPDATE, I passed the entire modified JSON document. With the following single statement, the amount change for order_id 1, the deletion of order_id 2, and the addition of order_id 3 were all applied.
UPDATE customer_orders_dv SET data = '{"_id": 1, "customer_name": "Alice", "orders": [{"order_id": 1, "product": "Laptop", "amount": 999.99}, {"order_id": 3, "product": "Keyboard", "amount": 29.99}]}' WHERE JSON_EXTRACT(data, '$._id') = 1;
Query OK, 3 rows affected, 1 warning (0.01 sec)
SHOW WARNINGS;
SELECT * FROM orders;
+---------+------+--------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------+
| Warning | 6493 | ETAG missing for updated value |
+---------+------+--------------------------------+
+----------+-------------+----------+--------+
| order_id | customer_id | product | amount |
+----------+-------------+----------+--------+
| 1 | 1 | Laptop | 999.99 |
| 3 | 1 | Keyboard | 29.99 |
+----------+-------------+----------+--------+
Because the JSON I passed did not include _metadata.etag, warning 6493 was raised. An UPDATE that put a value in _metadata.etag different from the one in the database, as shown below, was rejected with ERROR 6494, and customer_name in customers stayed as Alice.
UPDATE customer_orders_dv SET data = '{"_id": 1, "customer_name": "Alice2", "orders": [], "_metadata": {"etag": "00000000000000000000000000000000"}}' WHERE JSON_EXTRACT(data, '$._id') = 1;
ERROR 6494 (HY000): Cannot update JSON duality view. The ETAG of the document in the database did not match the ETAG '"00000000000000000000000000000000"' passed in.
In customer_orders_dv, where DELETE is also allowed on the child orders with WITH, a DELETE targeting the parent row also removed the child rows.
DELETE FROM customer_orders_dv WHERE JSON_EXTRACT(data, '$._id') = 1;
Query OK, 3 rows affected (0.01 sec)
Replication Applier Metrics
Replication Applier Metrics is used by installing a component. Once installed, two tables, replication_applier_metrics and replication_applier_progress_by_worker, are added to the Performance Schema.
Preparing the Replica
To create a read replica, you need to set the source's automated backup retention period to 1 day or more and make the master password self-managed. I set the retention period to 1 day.
aws rds modify-db-instance --db-instance-identifier rds-mysql-97-src --backup-retention-period 1 --apply-immediately
I switched the password to self-managed using a modify.json with the following contents.
{
"DBInstanceIdentifier": "rds-mysql-97-src",
"ManageMasterUserPassword": false,
"MasterUserPassword": "<password>",
"ApplyImmediately": true
}
aws rds modify-db-instance --cli-input-json file://modify.json
I created the read replica with the following command.
aws rds create-db-instance-read-replica --db-instance-identifier rds-mysql-97-rep --source-db-instance-identifier rds-mysql-97-src --db-instance-class db.t4g.micro --no-publicly-accessible --db-subnet-group-name <subnet-group> --vpc-security-group-ids <security-group-id>
The documentation states that the component does not work when replica_parallel_workers is 0. The value on the created replica was 4.
SHOW VARIABLES LIKE 'replica_parallel_workers';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| replica_parallel_workers | 4 |
+--------------------------+-------+
Installing the Component and Enabling performance_schema
Because read_only is ON on the replica, INSTALL COMPONENT cannot be run as is. Since the replica gets the same parameter group as the source (rds-mysql-97-verify) at creation, I temporarily set read_only to 0 in this group.
aws rds modify-db-parameter-group --db-parameter-group-name rds-mysql-97-verify --parameters ParameterName=read_only,ParameterValue=0,ApplyMethod=immediate
After it was applied, I confirmed that read_only was OFF on the replica and then installed the component.
SHOW VARIABLES LIKE 'read_only';
INSTALL COMPONENT 'file://component_replication_applier_metrics';
SELECT * FROM mysql.component;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
Query OK, 0 rows affected (0.11 sec)
+--------------+--------------------+----------------------------------------------+
| component_id | component_group_id | component_urn |
+--------------+--------------------+----------------------------------------------+
| 1 | 1 | file://component_replication_applier_metrics |
+--------------+--------------------+----------------------------------------------+
I returned read_only to its default with a reset and confirmed that it was back to ON on the replica.
aws rds reset-db-parameter-group --db-parameter-group-name rds-mysql-97-verify --parameters ParameterName=read_only,ApplyMethod=immediate
Just installing it created the two tables but they had no rows. This was because performance_schema was OFF on the replica. performance_schema in the parameter group is a static parameter whose default is 0, and a reboot is required for it to take effect. I created a replica-specific parameter group with performance_schema set to 1 and swapped it in.
aws rds create-db-parameter-group --db-parameter-group-name rds-mysql-97-verify-rep --db-parameter-group-family mysql9.7 --description "rds-mysql-97 verify replica (performance_schema=1)"
aws rds modify-db-parameter-group --db-parameter-group-name rds-mysql-97-verify-rep --parameters ParameterName=performance_schema,ParameterValue=1,ApplyMethod=pending-reboot
aws rds modify-db-instance --db-instance-identifier rds-mysql-97-rep --db-parameter-group-name rds-mysql-97-verify-rep --apply-immediately
After the instance state returned to available, I rebooted it.
aws rds reboot-db-instance --db-instance-identifier rds-mysql-97-rep
After the reboot, performance_schema was ON and the component remained installed.
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
These are the SELECT results from the two tables after writing to the source following the reboot. The cumulative values include what was applied since the reboot (LAST_APPLIER_START).
SELECT * FROM performance_schema.replication_applier_metrics\G
SELECT * FROM performance_schema.replication_applier_progress_by_worker;
CHANNEL_NAME:
TOTAL_ACTIVE_TIME_DURATION: 300736134369
LAST_APPLIER_START: 2026-11-19 02:38:01
TRANSACTIONS_COMMITTED_COUNT: 10026
TRANSACTIONS_ONGOING_COUNT: 0
TRANSACTIONS_PENDING_COUNT: 0
TRANSACTIONS_COMMITTED_SIZE_BYTES_SUM: 120796928
TRANSACTIONS_ONGOING_FULL_SIZE_BYTES_SUM: 0
TRANSACTIONS_ONGOING_PROGRESS_SIZE_BYTES_SUM: 0
TRANSACTIONS_PENDING_SIZE_BYTES_SUM: 0
EVENTS_COMMITTED_COUNT: 63458
WAITS_FOR_WORK_FROM_SOURCE_COUNT: 2826
WAITS_FOR_WORK_FROM_SOURCE_SUM_TIME: 255675499146
WAITS_FOR_AVAILABLE_WORKER_COUNT: 6384
WAITS_FOR_AVAILABLE_WORKER_SUM_TIME: 40213740222
WAITS_COMMIT_SCHEDULE_DEPENDENCY_COUNT: 6
WAITS_COMMIT_SCHEDULE_DEPENDENCY_SUM_TIME: 10746757
WAITS_FOR_WORKER_QUEUE_MEMORY_COUNT: 0
WAITS_FOR_WORKER_QUEUE_MEMORY_SUM_TIME: 0
WAITS_WORKER_QUEUES_FULL_COUNT: 0
WAITS_WORKER_QUEUES_FULL_SUM_TIME: 0
WAITS_DUE_TO_COMMIT_ORDER_COUNT: 1865
WAITS_DUE_TO_COMMIT_ORDER_SUM_TIME: 28878957632
TIME_TO_READ_FROM_RELAY_LOG_SUM_TIME: 929349216
+--------------+-----------+-----------+--------------------------+-------------------------------------+----------------------------------------+
| CHANNEL_NAME | WORKER_ID | THREAD_ID | ONGOING_TRANSACTION_TYPE | ONGOING_TRANSACTION_FULL_SIZE_BYTES | ONGOING_TRANSACTION_APPLIED_SIZE_BYTES |
+--------------+-----------+-----------+--------------------------+-------------------------------------+----------------------------------------+
| | 0 | 44 | UNASSIGNED | 0 | 0 |
| | 1 | 45 | UNASSIGNED | 0 | 0 |
| | 2 | 46 | UNASSIGNED | 0 | 0 |
| | 3 | 47 | UNASSIGNED | 0 | 0 |
+--------------+-----------+-----------+--------------------------+-------------------------------------+----------------------------------------+
Checking Changes from 8.4
Date and Time Functions
I ran SQL for date and time functions on 9.7.4.
| SQL | Result | Warnings |
|---|---|---|
| TIMEDIFF('2025-07-24 10:30:00', '2025-07-24') | 10:30:00 | 0 |
| TIMEDIFF('invalid', '2025-07-24') | NULL | 1292 Incorrect datetime value: 'invalid' |
| FROM_DAYS(-1), FROM_DAYS(0), FROM_DAYS(1), FROM_DAYS(365), FROM_DAYS(366) | NULL, NULL, 0000-01-01, 0000-12-31, 0001-01-01 | 2 |
| DATE_ADD('9999-12-31', INTERVAL 1 DAY) | NULL | 1441 Datetime function: datetime field overflow |
| DATE_ADD('9999-12-31 23:59:59', INTERVAL 1 SECOND) | NULL | 1441 Datetime function: datetime field overflow |
Default Values and Removed Features
Among the items the AWS Database Blog lists as changes from 8.4 to 9.7, I checked the following on a newly created 9.7.4 instance.
| Item | Result on 9.7.4 |
|---|---|
| explain_format | TREE (according to the blog, 8.4 is TRADITIONAL) |
| require_secure_transport | ON. The connection is TLS 1.3 |
| authentication_policy | *:caching_sha2_password |
| gtid_mode | ON_PERMISSIVE |
| innodb_log_writer_threads | OFF. On a 2 vCPU db.t4g.micro, it is OFF whether log_bin is OFF or ON (according to the blog, it is always ON in 8.4) |
| replica_parallel_type | SHOW VARIABLES returns 0 rows. SET GLOBAL gives ERROR 1193 Unknown system variable 'replica_parallel_type' |
| MD5(), SHA1() | With the database shop selected, ERROR 1305 FUNCTION shop.MD5 does not exist (same for SHA1). SHA2('a', 256) succeeds |
| mysql_native_password | CREATE USER ... IDENTIFIED WITH mysql_native_password gives ERROR 1524 Plugin 'mysql_native_password' is not loaded. It succeeds with caching_sha2_password |
| Reserved words EXTERNAL, LIBRARY | CREATE TABLE external (id INT) and CREATE TABLE library (id INT) give ERROR 1064 |
Summary
With RDS for MySQL 9.7.4, the MySQL 9.7 LTS is now available, and I was able to verify the behavior of the Hypergraph Optimizer, JSON Duality Views, and Replication Applier Metrics.
With GA, RDS for MySQL 9.7.4 is now available in the Tokyo Region (ap-northeast-1) as well. If you have workloads where you can expect the new features to pay off, try evaluating it first.
I also found places where there is no backward compatibility with 8.4, such as removed features. Standard support for 8.4, the previous LTS, lasts until July 2029, but I recommend making your migration plan early.
