www.bortolotto.eu

Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • Announcing VillageSQL Server 0.0.8
    VillageSQL Server 0.0.8 is now available. This release is about using extensions from applications: through prepared statements, with text in other character sets, and with the server protected when an extension function throws an exception. Highlights of this release include the following: Functions that take custom types work in prepared statements. Extensions built with the 0.0.8 SDK can tell raw bytes from text. When a function in an extension built with the 0.0.8 SDK throws an exception, only that statement fails, and the server keeps running. Custom types in prepared statements Many applications keep values out of the SQL text. They send the query once as a prepared statement and pass the values separately, and most MySQL connectors can work this way. In 0.0.8, functions that take custom types work in prepared statements: mysql> PREPARE re FROM 'SELECT complex_real(?) AS re'; mysql> SET @v = '(1.5,-2.5)'; mysql> EXECUTE re USING @v; +------+ | re | +------+ | 1.5 | +------+ Vector search works the same way: a nearest-neighbor query with its search value passed as a parameter uses the vector index. Custom indexes are still a preview feature, behind vsql_allow_preview_extensions=ON. Running a prepared statement many times also costs less. For many extension functions, the server no longer prepares a statement that calls them again at every EXECUTE. For a prepared nearest-neighbor query, that repeat preparation took about 10% of the server's time. Text and raw bytes A password hash is raw bytes, and an email address is text. MySQL keeps the two apart, and extensions built with the 0.0.8 SDK can too. A hash reaches the extension byte for byte, and text reaches it in one encoding, whatever character set your client uses. vsql-crypto 0.0.8 uses the new BINARY type. Its hashes come back as binary data, ready to store in a VARBINARY column: mysql> SELECT CHARSET(digest('hello', 'sha256')) AS charset; +---------+ | charset | +---------+ | binary | +---------+ mysql> CREATE TABLE hashes (id INT PRIMARY KEY, h VARBINARY(32)); mysql> INSERT INTO hashes VALUES (1, digest('hello', 'sha256')); mysql> SELECT id, HEX(h) FROM hashes; +----+------------------------------------------------------------------+ | id | HEX(h) | +----+------------------------------------------------------------------+ | 1 | 2CF24DBA5FB0A30E26E83B2AC5B9E29E1B161E5C1FA7425E73043362938B9824 | +----+------------------------------------------------------------------+ Custom types read text in other character sets too. Here the COMPLEX type from an example extension reads values from a ucs2 column: mysql> CREATE TABLE src (id INT PRIMARY KEY, s VARCHAR(40) CHARACTER SET ucs2); mysql> INSERT INTO src VALUES (2, '(3.0,4.0)'); mysql> CREATE TABLE points (id INT PRIMARY KEY, c COMPLEX); mysql> INSERT INTO points SELECT id, s FROM src; Query OK, 1 row affected (0.00 sec) mysql> SELECT id, c FROM points; +----+-------+ | id | c | +----+-------+ | 2 | (3,4) | +----+-------+ When an extension throws an exception Extensions run inside the server process. When a function in an extension built with the 0.0.8 SDK throws a C++ exception, VillageSQL ends that one statement with an error, and the server keeps running. This test extension throws on purpose: mysql> SELECT vsql_exceptions_test.exception_add(10, 20); ERROR 3200 (HY000): VDF error in function 'exception_add': VDF threw an exception (vsql_exceptions_test::exception_add) mysql> SELECT 'server still up' AS status; +-----------------+ | status | +-----------------+ | server still up | +-----------------+ Crash-safe extension installs If the server crashes in the middle of INSTALL EXTENSION, it does not leave a half-written extension behind. If an extension file is cut short, INSTALL EXTENSION rejects it. And if an extension update fails at restart, the server rolls back only that extension. For extension authors These features come from protocol 5 of the interface between the server and its extensions, which is the stable protocol in 0.0.8. If you built an extension with the development SDK of 0.0.4 through 0.0.7, rebuild it with the stable 0.0.8 SDK. Function arguments and results can be BINARY, raw bytes that the server never converts, and STRING arguments always arrive as utf8mb4. A function's prerun check can see the values of constant arguments, including a value bound to a ? in a prepared statement, so a function can accept a NULL literal without accepting every string. A C++ exception that escapes a function becomes a SQL error on the statement. A custom type can register a from_binary function beside from_string, to accept values in a binary form, such as a vector of packed floats. The server uses it for a _binary string, a VARBINARY column, or a hex literal. A function like tvector_dim(?) takes its type parameters from the value bound at EXECUTE. Upgrading from 0.0.7 0.0.8 does not load extensions built for protocol 4, so if your 0.0.7 server has one installed, the server will not start after you upgrade. To check whether any of your installed extensions use protocol 4, run this query on your 0.0.7 server: SELECT EXTENSION_NAME FROM INFORMATION_SCHEMA.EXTENSION_REGISTRATION WHERE NEGOTIATED_PROTOCOL = 4; To upgrade, dump your data with mysqldump, install your extensions on a new 0.0.8 data directory, and then load the dump. Bundled extensions The install script, the Docker image, and the release tarballs include these extensions with VillageSQL Server 0.0.8. To use one, run INSTALL EXTENSION with its name. vsql-ai 0.0.8 vsql-boolean 0.0.3 vsql-crypto 0.0.8 vsql-cube 0.0.8 vsql-fuzzystrmatch 0.0.2 vsql-http 0.0.4 vsql-network-address 0.0.8 vsql-oauth2 0.0.5 vsql-rest 0.0.11 vsql-trgm 0.0.5 vsql-uuid 0.0.9 You can find a list of all available extensions here. To build a new extension or port an existing PostgreSQL extension, you can use an agent skill. Special thanks Thanks to @kash2104, who made the server print <error decoding value> for a custom value it cannot decode, and cleaned up how the server reads raw custom values. Thanks to @jobala, whose work lets an extension function's prerun check tell a NULL literal from other values, and to @askdba, who added a MyVector profile to vector-dev-bench, our tool for measuring in-database vector indexes. There's more in this release than fits here. The release notes carry the per-PR detail. Our roadmap is published on GitHub, and we'd like to hear what you're building and what we should work on next, either on GitHub or on Discord. If you want to support VillageSQL, please star the repo.

  • Modernize on Your Terms: How Nutanix and AWS Accelerate Migrations, Simplify Operations, and Enable AI
    Learn how Nutanix and AWS help organizations migrate workloads without refactoring, simplify hybrid cloud operations, and build a flexible foundation for AI.

  • Why MySQL OR is slower than IN
    Queries with OR conditions are commonly used to implement business logic. However, it might be slower than we expected.  Although the query below scans only 1 million rows, it runs for 6,7254 seconds.# User@Host: msandbox[msandbox] @ localhost [127.0.0.1] Id: 188 # Schema: Last_errno: 1160 Killed: 0 # Query_time: 67254.081455 Lock_time: 0.000006 Rows_sent: 1 Rows_examined: 1000000 Rows_affected: 0 Bytes_sent: 0 ... SELECT COUNT(*) FROM db1.sbtest1 WHERE id = 1 OR id = 2 OR id = 3 OR id = 4 OR id = 5 OR id = 6 OR id = 7 OR id = 8 OR id = 9 ... ...The CPU profile indicates that 99% of CPU time was spent in Item_cond_or::val_int() on MySQL 8.4.connection └ mysql_execute_command → Sql_cmd_dml::execute → Query_expression::ExecuteIteratorQuery └ AggregateIterator::Read 99.80% └ FilterIterator::Read 99.80% ├ Item_cond_or::val_int 98.96% │ └ Item::val_bool → Item_func_eq::val_int 91.27% │ └ Arg_comparator::compare_int_signed 58.46% │ ├ Item_field::val_int 16.18% │ └ (literal) val_int 10.04% └ IndexScanIterator::Read → row_search_mvcc 0.02% How MySQL evaluates OR conditions MySQL turns id = 1 OR id = 2 OR ... OR id = N into one Item_cond_or holding N Item_func_eq  elements.longlong Item_cond_or::val_int() { assert(fixed); List_iterator_fast<Item> li(list); Item *item; null_value = false; while ((item = li++)) { if (item->val_bool()) { null_value = false; return 1; } if (item->null_value) null_value = true; if (current_thd->is_error()) return error_int(); } return 0; }It checks each OR  element in the list until it finds a match. If a row matches term k, it costs k. If a row matches nothing, it costs N. The cost of the linear search is O(N). How MySQL evaluates IN conditions bool in_longlong::find_item(Item *item) { if (m_used_size == 0) return false; packed_longlong result; val_item(item, &result); if (item->null_value) return false; return std::binary_search(base.begin(), base.begin() + m_used_size, result, Cmp_longlong()); }The cost of the binary search is O(log N). The cost of OR conditions MySQL evaluates OR conditions in a nested loop. The outer loop walks the rows from the table, and the inner loop walks the OR list:for each row r: -- outer loop: R rows for each term o: -- inner loop: up to N terms if r == o: break -- exit inner loop; continue with next rowAssume that N is the number of values in the OR list R is the number of rows the filter evaluates T is the number of rows in the table A row that matches term k leaves the inner loop after k checks, which is about N/2 on average. A row that matches nothing runs all N. The OR list costs O(R × N). R depends on the query’s access type. How access type impacts the cost Range access: O(N²) When the optimizer chooses range access type, it reads only the matching keys from the index:mysql > explain for connection 11\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sbtest1 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 34753 filtered: 100.00 Extra: Using where; Using index 1 row in set (0.00 sec)The range has already done the filtering, but the server checks each row again, term by term. R grows with N, and each row checks about N/2 terms: R × N/2 ≈ N²/2. R is equal to N when all OR conditions in the table exist. In general, the cost is O(N²). Since the table size T does not appear in this cost, the increasing number of rows does not increase the execution time. The same query on tables of 100k, 500k, and 1M rows takes about the same time: Although the number of rows in the table grows 10 times from 100k to 1M, the execution time is still close. Range access reads only the N matching rows, so only N matters. However, R is not always equal to N. R is less than N when only part of the list exists in the table. R is greater than N when each value matches several rows. In this case, the cost is O(p × N²), where p is the percentage of rows matched from the table. Index scan: O(T × N) When the optimizer chooses index access type, it reads all records from the index:mysql > explain for connection 11\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sbtest1 partitions: NULL type: index possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 1000000 filtered: 100.00 Extra: Using where; Using index 1 row in set (0.00 sec)Now R = T. Every row in the table goes through the inner loop, and the T − N rows that match nothing run all N terms. The cost is O(T × N). T does not change as N grows, so the cost is linear in N. Linear does not mean cheaper, because T is much larger than N. At N = 40,000 on this table of about 1M rows, range access checks about N²/2 = 0.8 billion terms. The index scan checks about T × N = 40 billion, roughly 50 times more. Going from 34,753 OR conditions to 34,754 OR conditions pushed the ranges past the limit. The OR query went from 37 s to 2,907 s, which is 79 times longer.   In the index  access type, the table size T is part of this cost, and the test shows it. The same 40,000-value OR query with an index scan took 3,406 s on a 1M-row table, 1,752 s on a 500k-row table, and 265 s on a 100k-row table. Full table scan: O(T × N) This is similar to the index scan, which occurs when no suitable index is available for the query.mysql> explain for connection 9\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sbtest1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000000 filtered: 100.00 Extra: Using where 1 row in set (0.01 sec) Why does the optimizer switch from range to index Reason 1: the range_optimizer_max_mem_size  (8 MB by default) limit is exceeded.mysql > show warnings limit 1\G *************************** 1. row *************************** Level: Warning Code: 3170 Message: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query. 1 row in set (0.00 sec)In this case, the server raises warning 3170 and does not use range access. Please read more blogs about it Low Value in range_optimizer_max_mem_size Might Cause Full Table Scan Why Are Queries with Many IN Values More Expensive After Upgrading to MySQL 8.x? Reason 2: the cost model thinks the index scan is cheaper. The optimizer trace indicates it does not choose the range scan because of its cost."range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "id = 1", ... "id = 9998", "id = 9999", "id = 10000" ], "index_dives_for_eq_ranges": false, "rowid_ordered": true, "using_mrr": false, "index_only": true, "in_memory": 0.000709723, "rows": 10000, "cost": 11006.7, "chosen": false, "cause": "cost" } The cost of IN conditions In general, the cost of evaluating the entire IN condition is O(R log N).  Similar to OR conditions, R depends on the query’s access type. After rewriting the query with IN, the execution time is below. Conclusion Since the time complexity of OR conditions is more expensive than that of IN, the query with OR conditions is slower than the query with IN conditions.   The post Why MySQL OR is slower than IN appeared first on Percona.

  • Building PlanetScale with Insights Agent
    We've always used PlanetScale to build PlanetScale. So when we built Insights Agent, our own databases were the first place we ran it to see what kind of gains we could get. Insights Agent identifies problematic queries or schema using Query Insights, finds the code responsible for them, and opens a pull request with a fix. Our Rails API is a large, busy app, which makes it a good testing ground.We know a thing or two about databases over here, so our databases are already quite well optimized. Even so, Insights Agent has been able to find a bunch of wins for us, continuing our drive towards the most efficient database possible.Here are three (of many) examples of how we tested and used Insights Agent in the weeks leading up to launch, and how we used these experiences to make the product better.A feature flag check we didn't needOur Rails API checks feature flags with Flipper. On top of Flipper, we have an override that can turn a flag off for specific records. Checking the flag requires reading it from the database. Our code checked the override first, every time:Each lookup took about a millisecond, which doesn't raise any significant slow-query concerns at first look. However, when this happens ~28 million times per day, it added up to more than nine hours of database CPU time. Insights Agent found it by the aggregate time instead of individual execution time. The override can only turn a flag off, so Insights Agent moved it after the Flipper check. def feature_enabled?(name) - if override_disables?(name) - false - else - Flipper.enabled?(name, actor) - end + Flipper.enabled?(name, actor) && !override_disables?(name) end If a flag is already off, there's nothing to override. The app behavior remains exactly the same as before, but now with far fewer lookups on these values:BeforeAfterCalls per day28.7 million8.3 millionDatabase time per day33,379 s9,625 sShare of all query time3.6%1.1%In this case, the traffic volume to the database did not change, but by careful inspection of both the database load and the codebase, the Insights Agent was able to significantly reduce load here.An index that made a query slowerNext, we asked Insights Agent to fix a slower query. This query looks up billing prices for an account, about 570,000 times a day (names simplified):select prices.* from prices where prices.owner_id = ? and prices.metric in (<elements>) and prices.owner_type = ? and prices.region_id is not null Insights showed this query reading about 122 rows for every returned row. This is often a tell that a query can be sped up. Insights Agent observed that the index MySQL used for this lookup had a range on region_id ahead of metric, and decided to add a new index with metric first. This might seem reasonable at first glance, but we merged it, and the query got slower:Before the indexAfter the indexAverage27.3 ms47.6 msp99129 ms222 msMySQL wasn't using the new index!Insights Agent was missing... somethingInsights Agent was working from a summary that left out a few important bits of context.First, almost none of these calls return a row, because most accounts have no prices to show. When the number of rows returned is zero (or near-zero), rows read per row returned looks terrible.Second, our Insights extension collapses IN lists when it normalizes SQL, which means <elements> was hiding 645 metric names, about 18 KB of SQL on every call. MySQL builds a lookup range for each of these values for every index that could use them. The new index gave it one more index to do that for.So we stopped sending the list of elements. The query now loads the few dozen regional prices an account has and filters them in Ruby:account.regional_prices.select do |price| price.region_id == region.id && BILLABLE_METRICS.include?(price.metric) end Old queryNew queryDatabase time per day16,212 s1,129 sAverage28.5 ms1.95 msp99137 ms8.0 msThat's about 14 times less database time. The new query also uses the index Insights Agent added, so we kept it.Teaching Insights Agent what we learnedWe have updated Insights Agent to look at more evidence to be better at these types of investigations. For these, it must look at rows read and returned per call and the size of the SQL sent, including how many values are collapsed. And we're extending our eval harness to cover cases like this one, including the cases where the best option is to leave the query as-is. Sometimes no change is better than adding an index.More indexing workYet another indexing adventure here. A job in our Rails API runs every minute to find schema snapshots that need to be reconciled (query simplified):select snapshot.id from snapshot join branch on branch.id = snapshot.branch_id where snapshot.ready = ? and snapshot.failed_at is null and snapshot.created_at > ? and branch.ready = ? order by snapshot.id asc limit ? The only index MySQL could use for this was one created on (ready, migration_applied_at). This index helps MySQL find unready snapshots. However, it does not help with the other filters because failed_at and created_at are not in it (as well as a few others which were removed). MySQL had to read every unready snapshot (ready = false) and check the additional columns on every row.There are a lot of unready snapshots in the database, but only a very small subset are both recent and not failed. MySQL had to find every match before it could sort by id and return the first few. Each call was reading 100s of thousands of rows to return, typically, less than 100 and was taking multiple seconds. The rows read / rows returned ratio on this query was really bad.Insights Agent suggested adding an index on (ready, failed_at, created_at):add_index :snapshot, [:ready, :failed_at, :created_at] With ready and failed_at now included, MySQL can jump straight to the right range of created_at instead of reading every unready snapshot. Another query that counts unready snapshots for alerting had a similar issue, and was given a fix by the agent. In this case, the only change needed to the app-side code was a database migration.Both p50 and p95 dropped from seconds per call to millisecond-scale.WrapupWe spent weeks testing, improving, and optimizing the experience with Insights Agent in the weeks leading up to launch. While doing so, we used it to automate the discovery of a bunch of places where we could improve our code, our database schema, and our queries. If you want a faster database (and we know you do) go give Insights Agent a try on your database, today.If you prefer doing all these kinds of optimizations in your own agent harness, we have some docs to get you up and running on this as well.

  • Amazon RDS for MySQL LTS version 9.7 is now generally available
    Today, Amazon Relational Database Service (Amazon RDS) announced support for MySQL version 9.7, the latest Long-Term Support (LTS) major version from the MySQL community, in addition to MySQL 8.0 and 8.4. As with prior versions, you can bring your existing MySQL code, applications and tools to Amazon RDS. For customers who need more time to upgrade, Amazon RDS continues to offer earlier major versions under RDS Extended Support: MySQL 8.0 entered RDS Extended Support on August 1, 2026, and MySQL 5.7 remains available under RDS Extended Support through June 30, 2029. MySQL 9.7 is the second LTS Release from the community, following MySQL 8.4. It includes the features delivered across the 9.0 through 9.6 Innovation Releases and adds 9.7-specific capabilities. In this post, we explore notable features, list breaking changes and provide recommendations to ease the migration of your workloads to this version. Note for MySQL 5.7 customers: RDS Extended Support for MySQL 5.7 on Amazon Aurora MySQL-Compatible Edition and Amazon RDS for MySQL has been extended through June 30, 2029 (previously February 28, 2027), with no change to Extended Support pricing. During this period, AWS plans to provide critical security patches and bug fixes. If you’re running MySQL 5.7 workloads, you can use this additional time to plan and validate upgrades to MySQL 8.4 or to the latest 9.7 without disruption (follow the upgrade path). For details, see Amazon Aurora and RDS for MySQL expand Extended Support for MySQL 5.7 through June 2029. Before we turn to RDS for MySQL 9.7, we first revisit the MySQL versioning model. The community classifies every engine release as either an Innovation Release or a Long-Term Support (LTS) Release. Innovation Releases: Released frequently for early and rapid experimentation, giving access to the latest features and changes. Each Innovation Release is supported only until the next Innovation Release, so support cycles are short. The MySQL community classified versions 9.0 through 9.6 as Innovation Releases. Because of their short lifecycle, RDS offers these versions in the Amazon RDS Database Preview Environment. Long-Term Support (LTS) Releases: LTS Releases include stable, proven features, including functionality introduced in prior Innovation Releases. Features can be added or removed only in the first release of an LTS series (for example, 9.7.0) and there are no removals later in the series. Incremental releases within an LTS series (such as 9.7.1 and 9.7.2) contain only the fixes needed to reduce behavior-change risk and prioritize backward compatibility and security. MySQL 9.7 is an LTS series and follows MySQL 8.4 as the next LTS baseline. Note that MySQL 9.7 is the final release line to use the current sequential version numbers. Subsequent Innovation and LTS Releases move to calendar versioning in a YY.M format. For example, MySQL 26.7 is the July 2026 release. An LTS Release keeps its YY.M version fixed throughout that series’ support lifecycle. For more information, review MySQL Releases: Innovation and LTS. Upgrading your Amazon RDS for MySQL 8.4 database to MySQL 9.7 Before planning your upgrade, review the MySQL community’s upgrade path guidelines and release model. MySQL supports upgrading to the next LTS series but not skipping one, so the path is LTS-to-LTS: MySQL 8.4 to MySQL 9.7. If you’re running MySQL 8.0, upgrade to 8.4 first, then to 9.7. You have multiple options when upgrading: use Amazon RDS Blue/Green deployments, read replicas, or an in-place upgrade. We recommend blue/green deployments to minimize risk and downtime. A blue/green deployment creates two environments, your current production environment (Blue) and a staging environment (Green), kept in sync with native logical replication. You can safely test your application against 9.7 on the Green environment and promote it only after you’re confident in compatibility, without impacting production. Before you upgrade, note the following requirements and limitations: rdsproxyadmin becomes a reserved user. In MySQL 9.7, rdsproxyadmin is protected on all hosts, so you can no longer create, drop, rename, or change privileges for it. RDS Proxy creates and manages this user automatically when you register a proxy target, so if you use RDS Proxy, leave it in place. If you created the rdsproxyadmin user yourself, drop or rename it before you upgrade. Blue/Green deployments are supported. RDS for MySQL supports Blue/Green deployments on all available 5.7, 8.0 and 8.4 versions. If you’re running MySQL 5.7, you can’t move directly to 9.7. Upgrade in steps, using a blue/green deployment for each hop: 5.7 to 8.0, then 8.0 to 8.4, then 8.4 to 9.7. Replication from a MySQL 5.7 source to a 9.7 replica isn’t supported. Use a supported intermediate major version if you need to replicate between these versions. Replicas running gtid_mode=OFF aren’t supported targets. RDS for MySQL 9.7 defaults to gtid_mode=ON_PERMISSIVE, which assigns global transaction identifiers (GTIDs) to new transactions. Raise any self-managed or external replica to at least OFF_PERMISSIVE before upgrading. Multi-AZ DB clusters (Multi-AZ with two readable standbys) aren’t supported at launch. Amazon RDS for MySQL 9.7 doesn’t support Multi-AZ DB cluster deployments at GA and upgrades to 9.7 from an 8.4 Multi-AZ DB cluster with two readable standbys are blocked. Use a Single-AZ or Multi-AZ DB instance (single standby) deployment for 9.7. In the following sections, we discuss changes introduced with MySQL 9.7 that might be relevant for your applications. Changed parameter defaults Amazon RDS for MySQL offers the 9.7 LTS series at minor version 9.7.4. Because MySQL 9.7 is an LTS series, its incremental releases (9.7.1 through 9.7.4) contain only bug and security fixes. No features are added or removed and no parameter defaults change, after 9.7.0. Every default change in the following table was introduced earlier in the 9.x Innovation series (specifically MySQL 9.5.0) and is carried into the 9.7 LTS baseline that 9.7.4 ships. The 9.7.0 release itself introduced no default changes. We recommend thoroughly testing your applications with these defaults before upgrading. The following table shows the default changes, relative to MySQL 8.4, for parameters available on Amazon RDS for MySQL: Parameter MySQL 8.4 Default MySQL 9.7 Default explain_format TRADITIONAL TREE explain_json_format_version 1 2 innodb_change_buffering none all innodb_log_writer_threads ON (dedicated log writer threads always enabled) Binary-log aware: if log_bin=ON, ON when CPUs > 32 (otherwise OFF). If log_bin=OFF, ON when CPUs > 4 (otherwise OFF) binlog_transaction_dependency_history_size 25,000 (max 1,000,000) 10,000,000 (max 100,000,000) The change to explain_format is especially visible: a bare EXPLAIN now returns TREE-format output instead of the traditional tabular format, which can affect tooling that parses EXPLAIN results. If you set any of these parameters explicitly in a parameter group today, review whether the new defaults better fit your workload before overriding them. In addition to the preceding community defaults, Amazon RDS for MySQL 9.7 applies the following default values changes (relative to MySQL 8.4): require_secure_transport now defaults to ON, so client connections must use SSL/TLS. gtid_mode now defaults to ON_PERMISSIVE. group_replication_ssl_mode defaults to REQUIRED, enforcing SSL for Group Replication (active-active cluster) communication. authentication_policy allows only caching_sha2_password (the removed mysql_native_password plugin is no longer accepted). innodb_change_buffer_max_size now defaults to 5 (down from 25), as a percentage of the buffer pool. back_log now defaults to 10000 (up from 151). Community MySQL 9.5.0 changed gtid_mode and enforce_gtid_consistency to ON. Amazon RDS for MySQL 9.7 keeps enforce_gtid_consistency=OFF and uses gtid_mode=ON_PERMISSIVE. See the following Amazon RDS for MySQL 9.7 enhancements section for details, including the note on active-active clusters (Group Replication is supported on all MySQL 9.7, 8.4 versions and on MySQL 8.0.35+). New capabilities in MySQL 9.7 MySQL 9.7 adds several notable capabilities, highlighted in the following sections. Hypergraph Optimizer MySQL 9.7 makes the Hypergraph Optimizer available in the engine. It’s disabled by default and controlled through the optimizer_switch system variable, which you can set per session, globally, persisted, or per statement: -- Session scope SET optimizer_switch = 'hypergraph_optimizer=on'; -- Per-statement hint SELECT /*+ SET_VAR(optimizer_switch = 'hypergraph_optimizer=on') */ ... ; The Hypergraph Optimizer can improve plan quality for complex queries with many joins. We recommend evaluating it against representative queries before enabling it broadly. On Amazon RDS for MySQL, the session and per-statement forms shown earlier work unchanged. To enable it for an entire instance, set optimizer_switch in a DB parameter group. JSON Duality Views MySQL 9.7 introduces JSON Duality Views, which you can use to access relational tables as JSON documents, with support for data manipulation language (DML), not just data definition language (DDL), and auto-increment. This provides a bridge for applications that work naturally with JSON while keeping data in normalized relational tables. Group Replication enhancements MySQL 9.7 adds several Group Replication components that improve operational visibility and resilience: Replication Applier Metrics Component: extended statistics for lag, throughput, queued work and worker behavior. Group Replication Flow Control Statistics Component: visibility into cluster throttling. Group Replication Resource Manager Component: automatic eviction of unhealthy members and automatic rejoin. Group Replication Primary Election Component: up-to-date-aware primary election that prefers the most current member. Security and authentication changes MySQL 9.7 changes how accounts authenticate and how connections are secured. Review the following changes before you upgrade. Removal of mysql_native_password The mysql_native_password authentication plugin has reached the end of its deprecation path. It was deprecated in MySQL 8.0.34, disabled by default in MySQL 8.4 and removed as of MySQL 9.0, so it doesn’t exist in MySQL 9.7. Accounts that still authenticate with mysql_native_password can’t connect after the upgrade. The server also rejects native-password authentication requests from older client programs that lack the CLIENT_PLUGIN_AUTH capability. Before upgrading, migrate any such accounts to caching_sha2_password (the default authentication plugin since MySQL 8.0) and update client drivers accordingly. To find affected accounts on your MySQL 8.4 instance, check for users whose plugin is still mysql_native_password: SELECT user, host FROM mysql.user WHERE plugin = 'mysql_native_password'; On RDS for MySQL 9.7, the authentication_policy parameter can’t name mysql_native_password, because the plugin no longer exists. If you set that parameter in your current parameter group, make sure it specifies caching_sha2_password instead. PBKDF2 storage format for caching_sha2_password MySQL 9.7 adds a PBKDF2 storage format for caching_sha2_password credentials. On Amazon RDS for MySQL you select the format with caching_sha2_password_storage_format, which accepts CRYPT5 or PBKDF2_SHA512. The default remains CRYPT5. RDS for MySQL 9.7 also makes the related caching_sha2_password_proxy_users parameter modifiable. It adds proxy-user support for the plugin and takes effect only when check_proxy_users is enabled. Removed and deprecated variables Several variables and plugins present in MySQL 8.4 are removed in the 9.x series. Review and update any automation, monitoring, or parameter groups that reference them before upgrading, because setting a removed variable can cause startup or upgrade issues. Variable / plugin Replacement or behavior mysql_native_password (plugin) Removed. Migrate accounts to caching_sha2_password mysql_native_password_proxy_users Removed with the mysql_native_password plugin. Was customer-visible and modifiable in 8.4 replica_parallel_type Parallel replication now uses LOGICAL_CLOCK exclusively group_replication_allow_local_lower_version_join Version compatibility is enforced automatically. No longer configurable temptable_use_mmap Feature removed innodb_undo_tablespaces Removed. The setting had been ignored since MySQL 8.0.14 (use CREATE/DROP UNDO TABLESPACE instead) Replication variable renames (master/slave to source/replica) MySQL has completed its shift from master/slave terminology to source/replica. As part of that change, MySQL 9.5.0 renamed the semisynchronous replication plugins: semisync_master became semisync_source and semisync_slave became semisync_replica. On Amazon RDS for MySQL, the following parameters are visible but RDS-managed, so you can’t set them yourself. Their names still change with the upgrade, which matters if your monitoring, dashboards, or automation read them through SHOW VARIABLES or SHOW STATUS: MySQL 8.4 MySQL 9.7 rpl_semi_sync_master_enabled rpl_semi_sync_source_enabled rpl_semi_sync_slave_enabled rpl_semi_sync_replica_enabled rpl_semi_sync_master_timeout rpl_semi_sync_source_timeout rpl_semi_sync_master_wait_point rpl_semi_sync_source_wait_point rpl_semi_sync_master_wait_for_slave_count rpl_semi_sync_source_wait_for_replica_count rpl_semi_sync_master_wait_no_slave rpl_semi_sync_source_wait_no_replica rpl_semi_sync_master_target_apply_lag rpl_semi_sync_source_target_apply_lag Update any monitoring or automation that reads these names before upgrading. Removed functions MySQL 9.x moves some built-in SQL functions out of the server core. Review your code for the functions described in the following section before you upgrade. MD5() and SHA1() SQL functions aren’t available In MySQL 9.x, the MD5() and SHA1() SQL functions were moved out of the server core into a separate classic_hashing component. Amazon RDS for MySQL 9.7 doesn’t offer the classic_hashing component, so the MD5() and SHA1() SQL functions aren’t available. Before upgrading, review your application and stored programs for use of MD5() and SHA1(). For hashing needs, use SHA2() (for example, SHA2(col, 256)), which remains available. There is no supported MD5() replacement, so plan application changes where it’s used. Transitional foreign key handling variables Amazon RDS for MySQL 9.7 sets innodb_native_foreign_keys=ON by default, so foreign key checks and cascade operations are handled by the InnoDB engine, the same behavior as MySQL 8.4. Cascaded changes are therefore not individually written to the binary log and change-data-capture (CDC) tools such as AWS Database Migration Service (AWS DMS) and Debezium see the same events they did on 8.4. In community MySQL, innodb_native_foreign_keys defaults to OFF and the SQL layer performs foreign key cascades, which does write each cascaded change to the binary log, increasing binlog volume and surfacing those changes to CDC. The parameter is customer-modifiable, so if you set innodb_native_foreign_keys=OFF on RDS, plan for those additional binlog entries. innodb_native_foreign_keys and enable_cascade_triggers are transitional variables introduced in the 9.x series (innodb_native_foreign_keys in 9.6, enable_cascade_triggers in 9.7) and already marked for eventual removal, so avoid building long-term dependencies on them. Amazon RDS for MySQL 9.7 enhancements Beyond the community engine changes described earlier, Amazon RDS for MySQL 9.7 makes several RDS-specific parameter-group and default choices. The changes most likely to affect an existing 8.4 workload are highlighted here. Connections require TLS by default require_secure_transport now defaults to ON in RDS for MySQL 9.7, changed from OFF in 8.4. Clients that connect without SSL/TLS are rejected. Before upgrading, confirm that your applications and drivers connect over TLS, or set require_secure_transport=OFF in your parameter group if you must continue to allow non-TLS connections. GTID defaults on RDS Community MySQL changed the GTID defaults to ON in 9.5.0. Amazon RDS for MySQL 9.7 deliberately doesn’t adopt those values as-is: enforce_gtid_consistency stays OFF, unchanged from 8.4. Enable it explicitly if your workload needs GTID consistency enforcement. gtid_mode moves from OFF_PERMISSIVE (8.4) to ON_PERMISSIVE, promoting GTIDs rather than enforcing them. This keeps GTID behavior close to 8.4 while moving in the community’s direction. Because ON_PERMISSIVE assigns GTIDs to new transactions, two things change for replication. First, a replica running gtid_mode=OFF isn’t a supported target for a 9.7 source, although it was supported for an 8.4 source at OFF_PERMISSIVE. If you replicate from Amazon RDS for MySQL to a self-managed or external replica, set that replica to at least OFF_PERMISSIVE before you upgrade. Replication into 9.7 from a source without GTIDs is unaffected. Second, GTIDs now appear in the binary log, so verify any tool that reads binlogs directly. GTID auto-positioning still requires gtid_mode=ON. Moving to ON requires setting enforce_gtid_consistency=ON first, which rejects GTID-unsafe statements such as a transaction that updates both InnoDB and MyISAM tables. Group Replication (active-active clusters) For active-active clusters that use the MySQL Group Replication plugin, group_replication_ssl_mode now defaults to REQUIRED, enforcing SSL for Group Replication communication. Authentication changes authentication_policy can’t name mysql_native_password, because the plugin is removed from the engine. Set it to caching_sha2_password instead (see Removal of mysql_native_password). ssl_cipher is now dynamic: it can be changed without a restart (it was static in 8.4). Password validation moves to the component RDS for MySQL 9.7 uses component-based password validation exclusively (the plugin form is gone): The validate-password parameter is replaced by rds.validate_password, which controls installation of the validate_password component. The underscore-style variables are renamed to dot-notation, for example validate_password_length becomes validate_password.length and validate_password_policy becomes validate_password.policy. Two new checks are available: validate_password.changed_characters_percentage and validate_password.check_user_name. Because the parameters are renamed, your existing validate_password_* values don’t carry across the upgrade. A value set in your 8.4 parameter group isn’t applied in 9.7, and password validation falls back to the component defaults, which might be weaker than your current policy. Record your current validate_password_* values before you upgrade, then set the matching validate_password.* parameters in your 9.7 parameter group. Also update any automation that references the old validate_password_* names or the validate-password plugin. New customer-visible parameters RDS for MySQL 9.7 exposes several new modifiable parameters: Parameter Purpose activate_mandatory_roles Activate mandatory roles in addition to an account’s default roles (new in 9.7. Defaults to ON) innodb_native_foreign_keys InnoDB-level foreign key checks and cascades. Defaults to ON on RDS (same as 8.4). See Transitional foreign key handling variables caching_sha2_password_storage_format Storage format for caching_sha2_password credentials (CRYPT5 or PBKDF2_SHA512. Default CRYPT5) caching_sha2_password_proxy_users Whether caching_sha2_password supports proxy users (effective only with check_proxy_users) enable_cascade_triggers Execute triggers on child tables during foreign key cascade operations (transitional) table_open_cache_triggers Maximum number of cached open tables with loaded triggers connection_memory_status_limit Per-connection memory threshold before the status counter increments global_connection_memory_status_limit Total connection-memory threshold before the status counter increments Other parameter changes Removed variables: see the preceding Removed and deprecated variables section (replica_parallel_type, innodb_undo_tablespaces and temptable_use_mmap). replica_parallel_workers minimum allowed value changes from 0 to 1: single-threaded applier mode is gone. binlog_transaction_dependency_history_size allowed range on RDS expands to 1 to 100,000,000. Character sets: the utf8 alias appears as utf8mb3 in the allowed values for the character_set_* parameters and the utf8 collations are renamed to utf8mb3_*. Migration checklist The following checklist recaps the changes covered in this post so that you can work through them as a single list when upgrading from MySQL 8.4 to 9.7. The list moves from tasks to complete before you start the upgrade, through changed defaults to review, to new capabilities worth evaluating after you’re running 9.7. If RDS Proxy isn’t attached, drop the rdsproxyadmin user if it exists. In MySQL 9.7, rdsproxyadmin becomes a reserved user on all hosts and must be absent before you upgrade. If RDS Proxy is attached, it creates and manages this user, so leave it in place. Confirm your deployment type is supported. Multi-AZ DB clusters (two readable standbys) aren’t supported at launch and upgrades from an 8.4 Multi-AZ DB cluster are blocked. Migrate any accounts using mysql_native_password to caching_sha2_password (the plugin is removed as of 9.0) and update client drivers. Review your application and stored programs for MD5() and SHA1(). These SQL functions aren’t available in RDS for MySQL 9.7. For hashing, use SHA2() (for example, SHA2(col, 256)), which remains available. There is no supported MD5() replacement, so plan application changes where it’s used. Move password validation from the validate-password plugin to the validate_password component. RDS for MySQL 9.7 provides the component form only: the plugin is replaced by rds.validate_password and the validate_password_* variables are renamed to dot notation, for example validate_password_length becomes validate_password.length. Update automation that references the plugin or the old variable names. Confirm that applications and drivers connect over SSL/TLS. require_secure_transport now defaults to ON, so non-TLS connections are rejected. Set require_secure_transport=OFF in your parameter group if you must allow them. Update monitoring and automation that read the semisynchronous replication variables and status. They were renamed from master and slave to source and replica, for example rpl_semi_sync_master_timeout becomes rpl_semi_sync_source_timeout. These parameters are visible but RDS-managed, so you can’t set them yourself. Remove references to replica_parallel_type and group_replication_allow_local_lower_version_join, which are removed in the 9.x series. Check for schema objects named with the new reserved keywords EXTERNAL and LIBRARY (reserved as of MySQL 9.7). A reserved word must be quoted with backticks to be used as an identifier, so quote or rename any database, table, or column that uses them. When you create a 9.7 instance, don’t pass EXTERNAL or LIBRARY as the initial database name (--db-name) in the create-db-instance call, because a reserved word isn’t accepted there. Review explain_format. The default changed from TRADITIONAL to TREE, so update any tooling that parses EXPLAIN output. Note that explain_json_format_version now defaults to 2. Review innodb_change_buffering. The default changed from none to all. Review innodb_log_writer_threads. The 8.4 default was always ON. In 9.7 it’s binary-log and CPU aware, so it can default to OFF on smaller instances. Review binlog_transaction_dependency_history_size. The default increased from 25,000 to 10,000,000 (allowed range up to 100,000,000). Confirm the RDS for MySQL 9.7 defaults for gtid_mode and enforce_gtid_consistency. Community MySQL changed both to ON in 9.5.0 and RDS manages them through the parameter group. Consider JSON Duality Views for JSON-relational workloads. Evaluate the Hypergraph Optimizer for complex, multi-join queries. It’s opt-in through optimizer_switch and disabled by default. Test thoroughly in a Blue/Green deployment, on the Green environment, before promoting. Conclusion In this post, we reviewed the MySQL community versioning model, the new capabilities in MySQL 9.7, key security and replication changes, removed and transitional variables and a recommended upgrade approach using Amazon RDS Blue/Green deployments. We encourage you to test MySQL 9.7’s new features with your workloads. To get started, create or upgrade a database instance on the Amazon RDS console. For more information, see the Amazon RDS for MySQL User Guide. Sources MySQL 9.7 Release Notes. MySQL 9.5.0 Release Notes. MySQL 9.7 What Is New. MySQL 9.7 Variables Added, Deprecated and Removed. Amazon Aurora and RDS for MySQL expand Extended Support for MySQL 5.7 through June 2029.   About the authors Sabalesh Mahajan Sabalesh is a Senior Database Engineer at Amazon Web Services, with 19 years of experience in databases. He collaborates closely with the Amazon RDS product teams, contributing his expertise to the development and enhancement of innovative products. Akshat Nehra Akshat is a Software Development Engineer on the Amazon RDS for MySQL and MariaDB Engine team at Amazon Web Services, where he mostly works on the data plane. He focuses on delivering new community engine versions, contributing fixes upstream to the MySQL and MariaDB communities and security enhancements, helping make engine upgrades safe, reliable and seamless for customers. Chelluru V N S S Vidyadhar Chelluru is a Senior Database Engineer at Amazon Web Services with 14 years of experience in open source databases.