# Aurora MySQL version 3 compatible with MySQL 8.0
You can use Aurora MySQL version 3 to get the latest MySQL-compatible features, performance enhancements, and bug fixes. Following, you can learn about Aurora MySQL version 3, with MySQL 8.0 compatibility. You can learn how to upgrade your clusters and applications to Aurora MySQL version 3.
Some Aurora features, such as Aurora Serverless v2, require Aurora MySQL version 3.
**Topics**
+ [Features from MySQL 8.0 Community Edition](#AuroraMySQL.8.0-features-community)
+ [Aurora MySQL version 3 prerequisite for Aurora MySQL Serverless v2](#AuroraMySQL.serverless-v2-8.0-prereq)
+ [Release notes for Aurora MySQL version 3](#AuroraMySQL.mysql80-bugs-fixed)
+ [New parallel query optimizations](#AuroraMySQL.8.0-features-pq)
+ [Optimizations to reduce database restart time](#ReducedRestartTime)
+ [New temporary table behavior in Aurora MySQL version 3](ams3-temptable-behavior.md)
+ [Comparing Aurora MySQL version 2 and Aurora MySQL version 3](AuroraMySQL.Compare-v2-v3.md)
+ [Comparing Aurora MySQL version 3 and MySQL 8.0 Community Edition](AuroraMySQL.Compare-80-v3.md)
+ [Upgrading to Aurora MySQL version 3](AuroraMySQL.mysql80-upgrade-procedure.md)
## Features from MySQL 8.0 Community Edition
The initial release of Aurora MySQL version 3 is compatible with MySQL 8.0.23 Community Edition. MySQL 8.0 introduces several new features, including the following:
+ Atomic Data Definition Language (DDL) support. For more information, see [Atomic Data Definition Language (DDL) support](AuroraMySQL.Compare-v2-v3.md#AuroraMySQL.Compare-v2-v3-atomic-ddl).
+ JSON functions. For usage information, see [JSON Functions](https://dev.mysql.com/doc/refman/8.0/en/json-functions.html) in the *MySQL Reference Manual*.
+ Window functions. For usage information, see [Window Functions](https://dev.mysql.com/doc/refman/8.0/en/window-functions.html) in the *MySQL Reference Manual*.
+ Common table expressions (CTEs), using the `WITH` clause. For usage information, see [WITH (Common Table Expressions)](https://dev.mysql.com/doc/refman/8.0/en/with.html) in the *MySQL Reference Manual*.
+ Optimized `ADD COLUMN` and `RENAME COLUMN` clauses for the `ALTER TABLE` statement. These optimizations are called "instant DDL." Aurora MySQL version 3 is compatible with the community MySQL instant DDL feature. The former Aurora fast DDL feature isn't used. For usage information for instant DDL, see [Instant DDL (Aurora MySQL version 3)](AuroraMySQL.Managing.FastDDL.md#AuroraMySQL.mysql80-instant-ddl).
+ Descending, functional, and invisible indexes. For usage information, see [Invisible Indexes](https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html), [Descending Indexes](https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html), and [CREATE INDEX Statement](https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts) in the *MySQL Reference Manual*.
+ Role-based privileges controlled through SQL statements. For more information on changes to the privilege model, see [Role-based privilege model](AuroraMySQL.Compare-80-v3.md#AuroraMySQL.privilege-model).
+ `NOWAIT` and `SKIP LOCKED` clauses with the `SELECT ... FOR SHARE` statement. These clauses avoid waiting for other transactions to release row locks. For usage information, see [Locking Reads](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html) in the *MySQL Reference Manual*.
+ Improvements to binary log (binlog) replication. For the Aurora MySQL details, see [Binary log replication](AuroraMySQL.Compare-v2-v3.md#AuroraMySQL.mysql80-binlog). In particular, you can perform filtered replication. For usage information about filtered replication, see [How Servers Evaluate Replication Filtering Rules](https://dev.mysql.com/doc/refman/8.0/en/replication-rules.html) in the *MySQL Reference Manual*.
+ Hints. Some of the MySQL 8.0–compatible hints were already backported to Aurora MySQL version 2. For information about using hints with Aurora MySQL, see [Aurora MySQL hints](AuroraMySQL.Reference.Hints.md). For the full list of hints in community MySQL 8.0, see [Optimizer Hints](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html) in the *MySQL Reference Manual*.
For the full list of features added to MySQL 8.0 community edition, see the blog post [The complete list of new features in MySQL 8.0](https://dev.mysql.com/blog-archive/the-complete-list-of-new-features-in-mysql-8-0/).
Aurora MySQL version 3 also includes changes to keywords for inclusive language, backported from community MySQL 8.0.26. For details about those changes, see [Inclusive language changes for Aurora MySQL version 3](AuroraMySQL.Compare-v2-v3.md#AuroraMySQL.8.0-inclusive-language).
## Aurora MySQL version 3 prerequisite for Aurora MySQL Serverless v2
Aurora MySQL version 3 is a prerequisite for all DB instances in an Aurora MySQL Serverless v2 cluster. Aurora MySQL Serverless v2 includes support for reader instances in a DB cluster, and other Aurora features that aren't available for Aurora MySQL Serverless v1. It also has faster and more granular scaling than Aurora MySQL Serverless v1.
## Release notes for Aurora MySQL version 3
For the release notes for all Aurora MySQL version 3 releases, see [ Database engine updates for Amazon Aurora MySQL version 3](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.30Updates.html) in the *Release Notes for Aurora MySQL*.
## New parallel query optimizations
The Aurora parallel query optimization now applies to more SQL operations:
+ Parallel query now applies to tables containing the data types `TEXT`, `BLOB`, `JSON`, `GEOMETRY`, and `VARCHAR` and `CHAR` longer than 768 bytes.
+ Parallel query can optimize queries involving partitioned tables.
+ Parallel query can optimize queries involving aggregate function calls in the select list and the `HAVING` clause.
For more information about these enhancements, see [Upgrading parallel query clusters to Aurora MySQL version 3](aurora-mysql-parallel-query-optimizing.md#aurora-mysql-parallel-query-upgrade-pqv2). For general information about Aurora parallel query, see [Parallel query for Amazon Aurora MySQL](aurora-mysql-parallel-query.md).
## Optimizations to reduce database restart time
Your Aurora MySQL DB cluster must be highly available during both planned and unplanned outages.
Database administrators need to perform occasional database maintenance. This maintenance includes database patching, upgrades, database parameter modifications requiring a manual reboot, performing a failover to reduce the time it takes for instance class changes, and so on. These planned actions require downtime.
However, downtime can also be caused by unplanned actions, such as an unexpected failover due to an underlying hardware fault or database resource throttling. All of these planned and unplanned actions result in a database restart.
In Aurora MySQL version 3.05 and higher, we've introduced optimizations that reduce the database restart time. These optimizations provide up to 65% less downtime than without optimizations, and fewer disruptions to your database workloads, after a restart.
During database startup, many internal memory components are initialized. The largest of these is the [InnoDB buffer pool](https://aws.amazon.com/blogs/database/best-practices-for-amazon-aurora-mysql-database-configuration/), which in Aurora MySQL is 75% of the instance memory size by default. Our testing has found that the initialization time is proportional to the size of InnoDB buffer pool, and therefore scales with the DB instance class size. During this initialization phase, the database can't accept connections, which causes longer downtime during restarts. The first phase of Aurora MySQL fast restart optimizes the buffer pool initialization, which reduces the time for database initialization and thereby reduces the overall restart time.
For more details, see the blog [Reduce downtime with Amazon Aurora MySQL database restart time optimizations](https://aws.amazon.com/blogs/database/reduce-downtime-with-amazon-aurora-mysql-database-restart-time-optimizations/).
# New temporary table behavior in Aurora MySQL version 3
Aurora MySQL version 3 handles temporary tables differently from earlier Aurora MySQL versions. This new behavior is inherited from MySQL 8.0 Community Edition. There are two types of temporary tables that can be created with Aurora MySQL version 3:
+ Internal (or *implicit*) temporary tables – Created by the Aurora MySQL engine to handle operations such as sorting aggregation, derived tables, or common table expressions (CTEs).
+ User-created (or *explicit*) temporary tables – Created by the Aurora MySQL engine when you use the `CREATE TEMPORARY TABLE` statement.
There are additional considerations for both internal and user-created temporary tables on Aurora reader DB instances. We discuss these changes in the following sections.
**Topics**
+ [Storage engine for internal (implicit) temporary tables](#ams3-temptable-behavior-engine)
+ [Limiting the size of internal, in-memory temporary tables](#ams3-temptable-behavior-limit)
+ [Mitigating fullness issues for internal temporary tables on Aurora Replicas](#ams3-temptable-behavior-mitigate)
+ [Optimizing the temptable\$1max\$1mmap parameter on Aurora MySQL DB instances](#ams-optimize-temptable_max_mmap)
+ [User-created (explicit) temporary tables on reader DB instances](#ams3-temptable-behavior.user)
+ [Temporary table creation errors and mitigation](#ams3-temptable-behavior.errors)
## Storage engine for internal (implicit) temporary tables
When generating intermediate result sets, Aurora MySQL initially attempts to write to in-memory temporary tables. This might be unsuccessful, because of either incompatible data types or configured limits. If so, the temporary table is converted to an on-disk temporary table rather than being held in memory. More information on this can be found in the [Internal Temporary Table Use in MySQL](https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html) in the MySQL documentation.
In Aurora MySQL version 3, the way internal temporary tables work is different from earlier Aurora MySQL versions. Instead of choosing between the InnoDB and MyISAM storage engines for such temporary tables, now you choose between the `TempTable` and `MEMORY` storage engines.
With the `TempTable` storage engine, you can make an additional choice for how to handle certain data. The data affected overflows the memory pool that holds all the internal temporary tables for the DB instance.
Those choices can influence the performance for queries that generate high volumes of temporary data, for example while performing aggregations such as `GROUP BY` on large tables.
**Tip**
If your workload includes queries that generate internal temporary tables, confirm how your application performs with this change by running benchmarks and monitoring performance-related metrics.
In some cases, the amount of temporary data fits within the `TempTable` memory pool or only overflows the memory pool by a small amount. In these cases, we recommend using the `TempTable` setting for internal temporary tables and memory-mapped files to hold any overflow data. This setting is the default.
The `TempTable` storage engine is the default. `TempTable` uses a common memory pool for all temporary tables that use this engine, instead of a maximum memory limit per table. The size of this memory pool is specified by the [temptable\$1max\$1ram](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_ram) parameter. It defaults to 1 GiB on DB instances with 16 or more GiB of memory, and 16 MB on DB instances with less than 16 GiB of memory. The size of the memory pool influences session-level memory consumption.
In some cases when you use the `TempTable` storage engine, the temporary data might exceed the size of the memory pool. If so, Aurora MySQL stores the overflow data using a secondary mechanism.
You can set the [temptable\$1max\$1mmap](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_mmap) parameter to choose whether the data overflows to memory-mapped temporary files or to InnoDB internal temporary tables on disk. The different data formats and overflow criteria of these overflow mechanisms can affect query performance. They do so by influencing the amount of data written to disk and the demand on disk storage throughput.
Aurora MySQL version 3 stores the overflow data in the following way:
+ On the writer DB instance, data that overflows to InnoDB internal temporary tables or memory-mapped temporary files resides in local storage on the instance.
+ On reader DB instances, overflow data always resides in memory-mapped temporary files in local storage.
Read-only instances can't store any data on the Aurora cluster volume.
The configuration parameters related to internal temporary tables apply differently to the writer and reader instances in your cluster:
+ On reader instances, Aurora MySQL always uses the `TempTable` storage engine.
+ The size for `temptable_max_mmap` defaults to 1 GiB for both writer and reader instances, regardless of the DB instance memory size. You can adjust this value on both writer and reader instances.
+ Setting `temptable_max_mmap` to `0` turns off the use of memory-mapped temporary files on writer instances.
+ You can't set `temptable_max_mmap` to `0` on reader instances.
**Note**
We don't recommend using the [temptable\$1use\$1mmap](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_use_mmap) parameter. It has been deprecated, and support for it is expected to be removed in a future MySQL release.
## Limiting the size of internal, in-memory temporary tables
As discussed in [Storage engine for internal (implicit) temporary tables](#ams3-temptable-behavior-engine), you can control temporary table resources globally by using the [temptable\$1max\$1ram](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_ram) and [temptable\$1max\$1mmap](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_mmap) settings.
You can also limit the size of any individual internal, in-memory temporary table by using the [tmp\$1table\$1size](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmp_table_size) DB parameter. This limit is intended to prevent individual queries from consuming an inordinate amount of global temporary table resources, which can affect the performance of concurrent queries that require these resources.
The `tmp_table_size` parameter defines the maximum size of temporary tables created by the `MEMORY` storage engine in Aurora MySQL version 3.
In Aurora MySQL version 3.04 and higher, `tmp_table_size` also defines the maximum size of temporary tables created by the `TempTable` storage engine when the `aurora_tmptable_enable_per_table_limit` DB parameter is set to `ON`. This behavior is disabled by default (`OFF`), which is the same behavior as in Aurora MySQL version 3.03 and lower versions.
+ When `aurora_tmptable_enable_per_table_limit` is `OFF`, `tmp_table_size` isn't considered for internal, in-memory temporary tables created by the `TempTable` storage engine.
However, the global `TempTable` resources limit still applies. Aurora MySQL has the following behavior when the global `TempTable` resources limit is reached:
+ Writer DB instances – Aurora MySQL automatically converts the in-memory temporary table to an InnoDB on-disk temporary table.
+ Reader DB instances – The query ends with an error.
```
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full
```
+ When `aurora_tmptable_enable_per_table_limit` is `ON`, Aurora MySQL has the following behavior when the `tmp_table_size` limit is reached:
+ Writer DB instances – Aurora MySQL automatically converts the in-memory temporary table to an InnoDB on-disk temporary table.
+ Reader DB instances – The query ends with an error.
```
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full
```
Both the global `TempTable` resources limit and the per-table limit apply in this case.
**Note**
The `aurora_tmptable_enable_per_table_limit` parameter has no effect when [ internal\$1tmp\$1mem\$1storage\$1engine](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_internal_tmp_mem_storage_engine) is set to `MEMORY`. In this case, the maximum size of an in-memory temporary table is defined by the [tmp\$1table\$1size](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmp_table_size) or [max\$1heap\$1table\$1size](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_heap_table_size) value, whichever is smaller.
The following examples show the behavior of the `aurora_tmptable_enable_per_table_limit` parameter for writer and reader DB instances.
**Example of writer DB instance with `aurora_tmptable_enable_per_table_limit` set to `OFF`**
The in-memory temporary table isn't converted to an InnoDB on-disk temporary table.
```
mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| 0 | 3.04.0 | 0 | 1073741824 | 1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)
mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)
mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n) |
+----------+
| 60000000 |
+----------+
1 row in set (13.99 sec)
mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
+-------------------------+-------+
1 row in set (0.00 sec)
```
**Example of writer DB instance with `aurora_tmptable_enable_per_table_limit` set to `ON`**
The in-memory temporary table is converted to an InnoDB on-disk temporary table.
```
mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
| 0 | 3.04.0 | 1 | 16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)
mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
+---------+
| max(n) |
+---------+
| 6000000 |
+---------+
1 row in set (4.10 sec)
mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1 |
+-------------------------+-------+
1 row in set (0.00 sec)
```
**Example of reader DB instance with `aurora_tmptable_enable_per_table_limit` set to `OFF`**
The query finishes without an error because `tmp_table_size` doesn't apply, and the global `TempTable` resources limit hasn't been reached.
```
mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| 1 | 3.04.0 | 0 | 1073741824 | 1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)
mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)
mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n) |
+----------+
| 60000000 |
+----------+
1 row in set (14.05 sec)
```
**Example of reader DB instance with `aurora_tmptable_enable_per_table_limit` set to `OFF`**
This query reaches the global TempTable resources limit with `aurora_tmptable_enable_per_table_limit` set to OFF. The query ends with an error on reader instances.
```
mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| 1 | 3.04.0 | 0 | 1073741824 | 1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)
mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.01 sec)
mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 120000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
```
**Example of reader DB instance with `aurora_tmptable_enable_per_table_limit` set to `ON`**
The query ends with an error when the `tmp_table_size` limit is reached.
```
mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
| 1 | 3.04.0 | 1 | 16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)
mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)
mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_8_2' is full
```
## Mitigating fullness issues for internal temporary tables on Aurora Replicas
To avoid size limitation issues for temporary tables, set the `temptable_max_ram` and `temptable_max_mmap` parameters to a combined value that can fit the requirements of your workload.
Be careful when setting the value of the `temptable_max_ram` parameter. Setting the value too high reduces the available memory on the database instance, which can cause an out-of-memory condition. Monitor the average freeable memory on the DB instance. Then determine an appropriate value for `temptable_max_ram` so that you will still have a reasonable amount of free memory left on the instance. For more information, see [Freeable memory issues in Amazon Aurora](CHAP_Troubleshooting.md#Troubleshooting.FreeableMemory).
It is also important to monitor the size of the local storage and the temporary table space consumption. You can monitor the temporary storage available for a specific DB instance with the `FreeLocalStorage` Amazon CloudWatch metric, described in [Amazon CloudWatch metrics for Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md).
**Note**
This procedure doesn't work when the `aurora_tmptable_enable_per_table_limit` parameter is set to `ON`. For more information, see [Limiting the size of internal, in-memory temporary tables](#ams3-temptable-behavior-limit).
**Example 1**
You know that your temporary tables grow to a cumulative size of 20 GiB. You want to set in-memory temporary tables to 2 GiB and to grow to a maximum of 20 GiB on disk.
Set `temptable_max_ram` to **2,147,483,648** and `temptable_max_mmap` to **21,474,836,480**. These values are in bytes.
These parameter settings make sure that your temporary tables can grow to a cumulative total of 22 GiB.
**Example 2**
Your current instance size is 16xlarge or larger. You don't know the total size of the temporary tables that you might need. You want to be able to use up to 4 GiB in memory and up to the maximum available storage size on disk.
Set `temptable_max_ram` to **4,294,967,296** and `temptable_max_mmap` to **1,099,511,627,776**. These values are in bytes.
Here you're setting `temptable_max_mmap` to 1 TiB, which is less than the maximum local storage of 1.2 TiB on a 16xlarge Aurora DB instance.
On a smaller instance size, adjust the value of `temptable_max_mmap` so that it doesn't fill up the available local storage. For example, a 2xlarge instance has only 160 GiB of local storage available. Hence, we recommend setting the value to less than 160 GiB. For more information on the available local storage for DB instance sizes, see [Temporary storage limits for Aurora MySQLTemporary storage limits](AuroraMySQL.Managing.Performance.md#AuroraMySQL.Managing.TempStorage).
## Optimizing the temptable\$1max\$1mmap parameter on Aurora MySQL DB instances
The `temptable_max_mmap` parameter in Aurora MySQL controls the maximum amount of local disk space that can be used by memory mapped files before overflowing to the on-disk InnoDB temporary tables (on writer DB instances) or causing an error (on reader DB instances). Setting this DB instance parameter properly can help optimize the performance of your DB instances.
**Prerequisites**
1. Make sure that the Performance Schema is enabled. You can verify this by running the following SQL command:
```
SELECT @@performance_schema;
```
An output value of `1` indicates that it's enabled.
1. Confirm that the temporary table memory instrumentation is enabled. You can verify this by running the following SQL command:
```
SELECT name, enabled FROM performance_schema.setup_instruments WHERE name LIKE '%memory%temptable%';
```
The `enabled` column shows `YES` for the relevant temporary table memory instrumentation entries.
**Monitoring temporary table usage**
When setting the initial value for `temptable_max_mmap`, we recommend that you start with 80% of the local storage size for the DB instance class that you're using. This ensures that the temporary tables have enough disk space to operate efficiently, while leaving room for other disk usage on the instance.
To find the local storage size for your DB instance class, see [Temporary storage limits for Aurora MySQLTemporary storage limits](AuroraMySQL.Managing.Performance.md#AuroraMySQL.Managing.TempStorage).
For example, if you're using the db.r5.large DB instance class, the local storage size is 32 GiB. In this case, you would initially set the `temptable_max_mmap` parameter to 80% of 32 GiB, which is 25.6 GiB.
After setting the initial `temptable_max_mmap` value, run your peak workload on the Aurora MySQL instances. Monitor the current and high temporary table disk usage using the following SQL query:
```
SELECT event_name, current_count, current_alloc, current_avg_alloc, high_count, high_alloc, high_avg_alloc
FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/temptable/%';
```
This query retrieves the following information:
+ `event_name` – The name of the temporary table memory or disk usage event.
+ `current_count` – The current number of allocated temporary table memory or disk blocks.
+ `current_alloc` – The current amount of memory or disk allocated for temporary tables.
+ `current_avg_alloc` – The current average size of temporary table memory or disk blocks.
+ `high_count` – The highest number of allocated temporary table memory or disk blocks.
+ `high_alloc` – The highest amount of memory or disk allocated for temporary tables.
+ `high_avg_alloc` – The highest average size of temporary table memory or disk blocks.
If your queries fail with a Table is full error using this setting, it indicates that your workload requires more disk space for temporary table operations. In this case, consider increasing your DB instance size to one with more local storage space.
**Setting the optimal `temptable_max_mmap` value**
Use the following procedure to monitor and set the right size for the `temptable_max_mmap` parameter.
1. Review the output of the previous query, and identify the peak temporary table disk usage, as indicated by the `high_alloc` column.
1. Based on the peak temporary table disk usage, adjust the `temptable_max_mmap` parameter in the DB parameter group for your Aurora MySQL DB instances.
Set the value to be slightly higher than the peak temporary table disk usage to accommodate future growth.
1. Apply the parameter group changes to your DB instances.
1. Monitor the temporary table disk usage again during your peak workload to make sure that the new `temptable_max_mmap` value is appropriate.
1. Repeat the previous steps as needed to fine tune the `temptable_max_mmap` parameter.
## User-created (explicit) temporary tables on reader DB instances
You can create explicit temporary tables using the `TEMPORARY` keyword in your `CREATE TABLE` statement. Explicit temporary tables are supported on the writer DB instance in an Aurora DB cluster. You can also use explicit temporary tables on reader DB instances, but the tables can't enforce the use of the InnoDB storage engine.
To avoid errors while creating explicit temporary tables on Aurora MySQL reader DB instances, make sure that you run all `CREATE TEMPORARY TABLE` statements in either or both of the following ways:
+ Don't specify the `ENGINE=InnoDB` clause.
+ Don't set the SQL mode to `NO_ENGINE_SUBSTITUTION`.
## Temporary table creation errors and mitigation
The error that you receive is different depending on whether you use a plain `CREATE TEMPORARY TABLE` statement or the variation `CREATE TEMPORARY TABLE AS SELECT`. The following examples show the different kinds of errors.
This temporary table behavior only applies to read-only instances. This first example confirms that's the kind of instance the session is connected to.
```
mysql> select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
| 1 |
+--------------------+
```
For plain `CREATE TEMPORARY TABLE` statements, the statement fails when the `NO_ENGINE_SUBSTITUTION` SQL mode is turned on. When `NO_ENGINE_SUBSTITUTION` is turned off (default), the appropriate engine substitution is made, and the temporary table creation succeeds.
```
mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';
mysql> CREATE TEMPORARY TABLE tt2 (id int) ENGINE=InnoDB;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).
mysql> SET sql_mode = '';
mysql> CREATE TEMPORARY TABLE tt4 (id int) ENGINE=InnoDB;
mysql> SHOW CREATE TABLE tt4\G
*************************** 1. row ***************************
Table: tt4
Create Table: CREATE TEMPORARY TABLE `tt4` (
`id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
```
For `CREATE TEMPORARY TABLE AS SELECT` statements, the statement fails when the `NO_ENGINE_SUBSTITUTION` SQL mode is turned on. When `NO_ENGINE_SUBSTITUTION` is turned off (default), the appropriate engine substitution is made, and the temporary table creation succeeds.
```
mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';
mysql> CREATE TEMPORARY TABLE tt1 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).
mysql> SET sql_mode = '';
mysql> show create table tt3;
+-------+----------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------+
| tt3 | CREATE TEMPORARY TABLE `tt3` (
`id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------+
1 row in set (0.00 sec)
```
For more information about the storage aspects and performance implications of temporary tables in Aurora MySQL version 3, see the blog post [Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL](https://aws.amazon.com/blogs/database/use-the-temptable-storage-engine-on-amazon-rds-for-mysql-and-amazon-aurora-mysql/).
# Comparing Aurora MySQL version 2 and Aurora MySQL version 3
Use the following to learn about changes to be aware of when you upgrade your Aurora MySQL version 2 cluster to version 3.
**Topics**
+ [Atomic Data Definition Language (DDL) support](#AuroraMySQL.Compare-v2-v3-atomic-ddl)
+ [Feature differences between Aurora MySQL version 2 and 3](#AuroraMySQL.Compare-v2-v3-features)
+ [Instance class support](#AuroraMySQL.mysql80-instance-classes)
+ [Parameter changes for Aurora MySQL version 3](#AuroraMySQL.mysql80-parameter-changes)
+ [Status variables](#AuroraMySQL.mysql80-status-vars)
+ [Inclusive language changes for Aurora MySQL version 3](#AuroraMySQL.8.0-inclusive-language)
+ [AUTO\$1INCREMENT values](#AuroraMySQL.mysql80-autoincrement)
+ [Binary log replication](#AuroraMySQL.mysql80-binlog)
## Atomic Data Definition Language (DDL) support
One of the largest changes from MySQL 5.7 to 8.0 is the introduction of the [Atomic Data Dictionary](https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-file-removal.html). Before MySQL 8.0, the MySQL data dictionary used a file-based approach to store metadata such as table definitions (.frm), triggers (.trg), and functions separately from the storage engine's metadata (such as InnoDB's). This had some issues, including the risk of tables becoming "[orphaned](https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html)" if something unexpected happened during a DDL operation, causing the file-based and storage engine metadata to get out of sync.
To fix this, MySQL 8.0 introduced the Atomic Data Dictionary, which stores all metadata in a set of internal InnoDB tables in the `mysql` schema. This new architecture provides a transactional, [ACID](https://en.wikipedia.org/wiki/ACID)-compliant way to manage database metadata, solving the "atomic DDL" problem from the old file-based approach. For more information on the Atomic Data Dictionary, see [Removal of file-based metadata storage](https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-file-removal.html) and [Atomic data definition statement support](https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html) in the *MySQL Reference Manual*.
Due to this architectural change, you must consider the following when upgrading from Aurora MySQL version 2 to version 3:
+ The file-based metadata from version 2 must be migrated to the new data dictionary tables during the upgrade process to version 3. Depending on how many database objects are migrated, this could take some time.
+ The changes have also introduced some new incompatibilities that might need to be addressed before you can upgrade from MySQL 5.7 to 8.0. For example, 8.0 has some new reserved keywords that could conflict with existing database object names.
To help you identify these incompatibilities before upgrading the engine, Aurora MySQL runs a series of upgrade compatibility checks (prechecks) to determine whether there are any incompatible objects in your database dictionary, before performing the data dictionary upgrade. For more information on the prechecks, see [Major version upgrade prechecks for Aurora MySQL](AuroraMySQL.upgrade-prechecks.md).
## Feature differences between Aurora MySQL version 2 and 3
The following Amazon Aurora MySQL features are supported in Aurora MySQL for MySQL 5.7, but these features aren't supported in Aurora MySQL for MySQL 8.0:
+ You can't use Aurora MySQL version 3 for Aurora Serverless v1 clusters. Aurora MySQL version 3 works with Aurora Serverless v2.
+ Lab mode doesn't apply to Aurora MySQL version 3. There aren't any lab mode features in Aurora MySQL version 3. Instant DDL supersedes the fast online DDL feature that was formerly available in lab mode. For an example, see [Instant DDL (Aurora MySQL version 3)](AuroraMySQL.Managing.FastDDL.md#AuroraMySQL.mysql80-instant-ddl).
+ The query cache is removed from community MySQL 8.0 and also from Aurora MySQL version 3.
+ Aurora MySQL version 3 is compatible with the community MySQL hash join feature. The Aurora-specific implementation of hash joins in Aurora MySQL version 2 isn't used. For information about using hash joins with Aurora parallel query, see [Turning on hash join for parallel query clusters](aurora-mysql-parallel-query-enabling.md#aurora-mysql-parallel-query-enabling-hash-join) and [Aurora MySQL hints](AuroraMySQL.Reference.Hints.md). For general usage information about hash joins, see [Hash Join Optimization](https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html) in the *MySQL Reference Manual*.
+ The `mysql.lambda_async` stored procedure that was deprecated in Aurora MySQL version 2 is removed in version 3. For version 3, use the asynchronous function `lambda_async` instead.
+ The default character set in Aurora MySQL version 3 is `utf8mb4`. In Aurora MySQL version 2, the default character set was `latin1`. For information about this character set, see [The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)](https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html) in the *MySQL Reference Manual*.
Some Aurora MySQL features are available for certain combinations of AWS Region and DB engine version. For details, see [Supported features in Amazon Aurora by AWS Region and Aurora DB engine](Concepts.AuroraFeaturesRegionsDBEngines.grids.md).
## Instance class support
Aurora MySQL version 3 supports a different set of instance classes from Aurora MySQL version 2:
+ For larger instances, you can use the modern instance classes such as `db.r5`, `db.r6g`, and `db.x2g`.
+ For smaller instances, you can use the modern instance classes such as `db.t3` and `db.t4g`.
**Note**
We recommend using the T DB instance classes only for development and test servers, or other non-production servers. For more details on the T instance classes, see [Using T instance classes for development and testing](AuroraMySQL.BestPractices.Performance.md#AuroraMySQL.BestPractices.T2Medium).
The following instance classes from Aurora MySQL version 2 aren't available for Aurora MySQL version 3:
+ `db.r4`
+ `db.r3`
+ `db.t3.small`
+ `db.t2`
Check your administration scripts for any CLI statements that create Aurora MySQL DB instances. Hardcode instance class names that aren't available for Aurora MySQL version 3. If necessary, modify the instance class names to ones that Aurora MySQL version 3 supports.
**Tip**
To check the instance classes that you can use for a specific combination of Aurora MySQL version and AWS Region, use the `describe-orderable-db-instance-options` AWS CLI command.
For full details about Aurora instance classes, see [Amazon AuroraDB instance classes](Concepts.DBInstanceClass.md).
## Parameter changes for Aurora MySQL version 3
Aurora MySQL version 3 includes new cluster-level and instance-level configuration parameters. Aurora MySQL version 3 also removes some parameters that were present in Aurora MySQL version 2. Some parameter names are changed as a result of the initiative for inclusive language. For backward compatibility, you can still retrieve the parameter values using either the old names or the new names. However, you must use the new names to specify parameter values in a custom parameter group.
In Aurora MySQL version 3, the value of the `lower_case_table_names` parameter is set permanently at the time the cluster is created. If you use a nondefault value for this option, set up your Aurora MySQL version 3 custom parameter group before upgrading. Then specify the parameter group during the create cluster or snapshot restore operation.
**Note**
With an Aurora global database based on Aurora MySQL, you can't perform an in-place upgrade from Aurora MySQL version 2 to version 3 if the `lower_case_table_names` parameter is turned on. Use the snapshot restore method instead.
In Aurora MySQL version 3, the `init_connect` and `read_only` parameters don't apply for users who have the `CONNECTION_ADMIN` privilege. This includes the Aurora master user. For more information, see [Role-based privilege model](AuroraMySQL.Compare-80-v3.md#AuroraMySQL.privilege-model).
For the full list of Aurora MySQL cluster parameters, see [Cluster-level parameters](AuroraMySQL.Reference.ParameterGroups.md#AuroraMySQL.Reference.Parameters.Cluster). The table covers all the parameters from Aurora MySQL version 2 and 3. The table includes notes showing which parameters are new in Aurora MySQL version 3 or were removed from Aurora MySQL version 3.
For the full list of Aurora MySQL instance parameters, see [Instance-level parameters](AuroraMySQL.Reference.ParameterGroups.md#AuroraMySQL.Reference.Parameters.Instance). The table covers all the parameters from Aurora MySQL version 2 and 3. The table includes notes showing which parameters are new in Aurora MySQL version 3 and which parameters were removed from Aurora MySQL version 3. It also includes notes showing which parameters were modifiable in earlier versions but not Aurora MySQL version 3.
For information about parameter names that changed, see [Inclusive language changes for Aurora MySQL version 3](#AuroraMySQL.8.0-inclusive-language).
## Status variables
For information about status variables that aren't applicable to Aurora MySQL, see [MySQL status variables that don't apply to Aurora MySQL](AuroraMySQL.Reference.GlobalStatusVars.md#AuroraMySQL.Reference.StatusVars.Inapplicable).
## Inclusive language changes for Aurora MySQL version 3
Aurora MySQL version 3 is compatible with version 8.0.23 from the MySQL community edition. Aurora MySQL version 3 also includes changes from MySQL 8.0.26 related to keywords and system schemas for inclusive language. For example, the `SHOW REPLICA STATUS` command is now preferred instead of `SHOW SLAVE STATUS`.
The following Amazon CloudWatch metrics have new names in Aurora MySQL version 3.
In Aurora MySQL version 3, only the new metric names are available. Make sure to update any alarms or other automation that relies on metric names when you upgrade to Aurora MySQL version 3.
| Old name | New name |
| --- | --- |
| ForwardingMasterDMLLatency | ForwardingWriterDMLLatency |
| ForwardingMasterOpenSessions | ForwardingWriterOpenSessions |
| AuroraDMLRejectedMasterFull | AuroraDMLRejectedWriterFull |
| ForwardingMasterDMLThroughput | ForwardingWriterDMLThroughput |
The following status variables have new names in Aurora MySQL version 3.
For compatibility, you can use either name in the initial Aurora MySQL version 3 release. The old status variable names are to be removed in a future release.
| Name to be removed | New or preferred name |
| --- | --- |
| Aurora\$1fwd\$1master\$1dml\$1stmt\$1duration | Aurora\$1fwd\$1writer\$1dml\$1stmt\$1duration |
| Aurora\$1fwd\$1master\$1dml\$1stmt\$1count | Aurora\$1fwd\$1writer\$1dml\$1stmt\$1count |
| Aurora\$1fwd\$1master\$1select\$1stmt\$1duration | Aurora\$1fwd\$1writer\$1select\$1stmt\$1duration |
| Aurora\$1fwd\$1master\$1select\$1stmt\$1count | Aurora\$1fwd\$1writer\$1select\$1stmt\$1count |
| Aurora\$1fwd\$1master\$1errors\$1session\$1timeout | Aurora\$1fwd\$1writer\$1errors\$1session\$1timeout |
| Aurora\$1fwd\$1master\$1open\$1sessions | Aurora\$1fwd\$1writer\$1open\$1sessions |
| Aurora\$1fwd\$1master\$1errors\$1session\$1limit | Aurora\$1fwd\$1writer\$1errors\$1session\$1limit |
| Aurora\$1fwd\$1master\$1errors\$1rpc\$1timeout | Aurora\$1fwd\$1writer\$1errors\$1rpc\$1timeout |
The following configuration parameters have new names in Aurora MySQL version 3.
For compatibility, you can check the parameter values in the `mysql` client by using either name in the initial Aurora MySQL version 3 release. You can use only the new names when modifying values in a custom parameter group. The old parameter names are to be removed in a future release.
| Name to be removed | New or preferred name |
| --- | --- |
| aurora\$1fwd\$1master\$1idle\$1timeout | aurora\$1fwd\$1writer\$1idle\$1timeout |
| aurora\$1fwd\$1master\$1max\$1connections\$1pct | aurora\$1fwd\$1writer\$1max\$1connections\$1pct |
| master\$1verify\$1checksum | source\$1verify\$1checksum |
| sync\$1master\$1info | sync\$1source\$1info |
| init\$1slave | init\$1replica |
| rpl\$1stop\$1slave\$1timeout | rpl\$1stop\$1replica\$1timeout |
| log\$1slow\$1slave\$1statements | log\$1slow\$1replica\$1statements |
| slave\$1max\$1allowed\$1packet | replica\$1max\$1allowed\$1packet |
| slave\$1compressed\$1protocol | replica\$1compressed\$1protocol |
| slave\$1exec\$1mode | replica\$1exec\$1mode |
| slave\$1type\$1conversions | replica\$1type\$1conversions |
| slave\$1sql\$1verify\$1checksum | replica\$1sql\$1verify\$1checksum |
| slave\$1parallel\$1type | replica\$1parallel\$1type |
| slave\$1preserve\$1commit\$1order | replica\$1preserve\$1commit\$1order |
| log\$1slave\$1updates | log\$1replica\$1updates |
| slave\$1allow\$1batching | replica\$1allow\$1batching |
| slave\$1load\$1tmpdir | replica\$1load\$1tmpdir |
| slave\$1net\$1timeout | replica\$1net\$1timeout |
| sql\$1slave\$1skip\$1counter | sql\$1replica\$1skip\$1counter |
| slave\$1skip\$1errors | replica\$1skip\$1errors |
| slave\$1checkpoint\$1period | replica\$1checkpoint\$1period |
| slave\$1checkpoint\$1group | replica\$1checkpoint\$1group |
| slave\$1transaction\$1retries | replica\$1transaction\$1retries |
| slave\$1parallel\$1workers | replica\$1parallel\$1workers |
| slave\$1pending\$1jobs\$1size\$1max | replica\$1pending\$1jobs\$1size\$1max |
| pseudo\$1slave\$1mode | pseudo\$1replica\$1mode |
The following stored procedures have new names in Aurora MySQL version 3.
For compatibility, you can use either name in the initial Aurora MySQL version 3 release. The old procedure names are to be removed in a future release.
| Name to be removed | New or preferred name |
| --- | --- |
| mysql.rds\$1set\$1master\$1auto\$1position | mysql.rds\$1set\$1source\$1auto\$1position |
| mysql.rds\$1set\$1external\$1master | mysql.rds\$1set\$1external\$1source |
| mysql.rds\$1set\$1external\$1master\$1with\$1auto\$1position | mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position |
| mysql.rds\$1reset\$1external\$1master | mysql.rds\$1reset\$1external\$1source |
| mysql.rds\$1next\$1master\$1log | mysql.rds\$1next\$1source\$1log |
## AUTO\$1INCREMENT values
In Aurora MySQL version 3, Aurora preserves the `AUTO_INCREMENT` value for each table when it restarts each DB instance. In Aurora MySQL version 2, the `AUTO_INCREMENT` value wasn't preserved after a restart.
The `AUTO_INCREMENT` value isn't preserved when you set up a new cluster by restoring from a snapshot, performing a point-in-time recovery, and cloning a cluster. In these cases, the `AUTO_INCREMENT` value is initialized to the value based on the largest column value in the table at the time the snapshot was created. This behavior is different than in RDS for MySQL 8.0, where the `AUTO_INCREMENT` value is preserved during these operations.
## Binary log replication
In MySQL 8.0 community edition, binary log replication is turned on by default. In Aurora MySQL version 3, binary log replication is turned off by default.
**Tip**
If your high availability requirements are fulfilled by the Aurora built-in replication features, you can leave binary log replication turned off. That way, you can avoid the performance overhead of binary log replication. You can also avoid the associated monitoring and troubleshooting that are needed to manage binary log replication.
Aurora supports binary log replication from a MySQL 5.7–compatible source to Aurora MySQL version 3. The source system can be an Aurora MySQL DB cluster, an RDS for MySQL DB instance, or an on-premises MySQL instance.
As does community MySQL, Aurora MySQL supports replication from a source running a specific version to a target running the same major version or one major version higher. For example, replication from a MySQL 5.6–compatible system to Aurora MySQL version 3 isn't supported. Replicating from Aurora MySQL version 3 to a MySQL 5.7–compatible or MySQL 5.6–compatible system isn't supported. For details about using binary log replication, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md).
Aurora MySQL version 3 includes improvements to binary log replication in community MySQL 8.0, such as filtered replication. For details about the community MySQL 8.0 improvements, see [How Servers Evaluate Replication Filtering Rules](https://dev.mysql.com/doc/refman/8.0/en/replication-rules.html) in the *MySQL Reference Manual*.
### Transaction compression for binary log replication
For usage information about binary log compression, see [Binary Log Transaction Compression](https://dev.mysql.com/doc/refman/8.0/en/binary-log-transaction-compression.html) in the MySQL Reference Manual.
The following limitations apply to binary log compression in Aurora MySQL version 3:
+ Transactions whose binary log data is larger than the maximum allowed packet size aren't compressed. This is true regardless of whether the Aurora MySQL binary log compression setting is turned on. Such transactions are replicated without being compressed.
+ If you use a connector for change data capture (CDC) that doesn't support MySQL 8.0 yet, you can't use this feature. We recommend that you test any third-party connectors thoroughly with binary log compression. Also, we recommend that you do so before turning on binlog compression on systems that use binlog replication for CDC.
# Comparing Aurora MySQL version 3 and MySQL 8.0 Community Edition
You can use the following information to learn about the changes to be aware of when you convert from a different MySQL 8.0–compatible system to Aurora MySQL version 3.
In general, Aurora MySQL version 3 supports the feature set of community MySQL 8.0.23. Some new features from MySQL 8.0 community edition don't apply to Aurora MySQL. Some of those features aren't compatible with some aspect of Aurora, such as the Aurora storage architecture. Other features aren't needed because the Amazon RDS management service provides equivalent functionality. The following features in community MySQL 8.0 aren't supported or work differently in Aurora MySQL version 3.
For release notes for all Aurora MySQL version 3 releases, see [ Database engine updates for Amazon Aurora MySQL version 3](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.30Updates.html) in the *Release Notes for Aurora MySQL*.
**Topics**
+ [MySQL 8.0 features not available in Aurora MySQL version 3](#AuroraMySQL.Compare-80-v3-features)
+ [Role-based privilege model](#AuroraMySQL.privilege-model)
+ [Finding the database server ID](#AuroraMySQL.server-id)
+ [Authentication](#AuroraMySQL.mysql80-authentication)
## MySQL 8.0 features not available in Aurora MySQL version 3
The following features from community MySQL 8.0 aren't available or work differently in Aurora MySQL version 3.
+ Resource groups and associated SQL statements aren't supported in Aurora MySQL.
+ Aurora MySQL doesn't support user-defined undo tablespaces and associated SQL statements, such as `CREATE UNDO TABLESPACE`, `ALTER UNDO TABLESPACE ... SET INACTIVE`, and `DROP UNDO TABLESPACE`.
+ Aurora MySQL doesn't support undo tablespace truncation for Aurora MySQL versions lower than 3.06. In Aurora MySQL version 3.06 and higher, [automated undo tablespace truncation](https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html#truncate-undo-tablespace) is supported.
+ Password validation plugin is supported.
+ You can't modify the settings of any MySQL plugins, including password validation plugin.
+ The X plugin isn't supported.
+ Multisource replication isn't supported.
## Role-based privilege model
With Aurora MySQL version 3, you can't modify the tables in the `mysql` database directly. In particular, you can't set up users by inserting into the `mysql.user` table. Instead, you use SQL statements to grant role-based privileges. You also can't create other kinds of objects such as stored procedures in the `mysql` database. You can still query the `mysql` tables. If you use binary log replication, changes made directly to the `mysql` tables on the source cluster aren't replicated to the target cluster.
In some cases, your application might use shortcuts to create users or other objects by inserting into the `mysql` tables. If so, change your application code to use the corresponding statements such as `CREATE USER`. If your application creates stored procedures or other objects in the `mysql` database, use a different database instead.
To export metadata for database users during the migration from an external MySQL database, you can use a MySQL Shell command instead of `mysqldump`. For more information, see [Instance Dump Utility, Schema Dump Utility, and Table Dump Utility](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html#mysql-shell-utilities-dump-about).
To simplify managing permissions for many users or applications, you can use the `CREATE ROLE` statement to create a role that has a set of permissions. Then you can use the `GRANT` and `SET ROLE` statements and the `current_role` function to assign roles to users or applications, switch the current role, and check which roles are in effect. For more information on the role-based permission system in MySQL 8.0, see [Using Roles](https://dev.mysql.com/doc/refman/8.0/en/roles.html) in the MySQL Reference Manual.
**Important**
We strongly recommend that you do not use the master user directly in your applications. Instead, adhere to the best practice of using a database user created with the minimal privileges required for your application.
**Topics**
+ [rds\$1superuser\$1role](#AuroraMySQL.privilege-model.rds_superuser_role)
+ [Privilege checks user for binary log replication](#AuroraMySQL.privilege-model.binlog)
+ [Roles for accessing other AWS services](#AuroraMySQL.privilege-model.other)
### rds\$1superuser\$1role
Aurora MySQL version 3 includes a special role that has all of the following privileges. This role is named `rds_superuser_role`. The primary administrative user for each cluster already has this role granted. The `rds_superuser_role` role includes the following privileges for all database objects:
+ `ALTER`
+ `APPLICATION_PASSWORD_ADMIN`
+ `ALTER ROUTINE`
+ `CONNECTION_ADMIN`
+ `CREATE`
+ `CREATE ROLE`
+ `CREATE ROUTINE`
+ `CREATE TEMPORARY TABLES`
+ `CREATE USER`
+ `CREATE VIEW`
+ `DELETE`
+ `DROP`
+ `DROP ROLE`
+ `EVENT`
+ `EXECUTE`
+ `FLUSH_OPTIMIZER_COSTS` (Aurora MySQL version 3.09 and higher)
+ `FLUSH_STATUS` (Aurora MySQL version 3.09 and higher)
+ `FLUSH_TABLES` (Aurora MySQL version 3.09 and higher)
+ `FLUSH_USER_RESOURCES` (Aurora MySQL version 3.09 and higher)
+ `INDEX`
+ `INSERT`
+ `LOCK TABLES`
+ `PROCESS`
+ `REFERENCES`
+ `RELOAD`
+ `REPLICATION CLIENT`
+ `REPLICATION SLAVE`
+ `ROLE_ADMIN`
+ `SET_USER_ID`
+ `SELECT`
+ `SHOW DATABASES`
+ `SHOW_ROUTINE` (Aurora MySQL version 3.04 and higher)
+ `SHOW VIEW`
+ `TRIGGER`
+ `UPDATE`
+ `XA_RECOVER_ADMIN`
The role definition also includes `WITH GRANT OPTION` so that an administrative user can grant that role to other users. In particular, the administrator must grant any privileges needed to perform binary log replication with the Aurora MySQL cluster as the target.
**Tip**
To see the full details of the permissions, enter the following statements.
```
SHOW GRANTS FOR rds_superuser_role@'%';
SHOW GRANTS FOR name_of_administrative_user_for_your_cluster@'%';
```
### Privilege checks user for binary log replication
Aurora MySQL version 3 includes a privilege checks user for binary log (binlog) replication, `rdsrepladmin_priv_checks_user`. In addition to the privileges of `rds_superuser_role`, this user has the `replication_applier` privilege.
When you turn on binlog replication by calling the `mysql.rds_start_replication` stored procedure, `rdsrepladmin_priv_checks_user` is created.
The `rdsrepladmin_priv_checks_user@localhost` user is a reserved user. Don't modify it.
### Roles for accessing other AWS services
Aurora MySQL version 3 includes roles that you can use to access other AWS services. You can set many of these roles as an alternative to granting privileges. For example, you specify `GRANT AWS_LAMBDA_ACCESS TO user` instead of `GRANT INVOKE LAMBDA ON *.* TO user`. For the procedures to access other AWS services, see [Integrating Amazon Aurora MySQL with other AWS services](AuroraMySQL.Integrating.md). Aurora MySQL version 3 includes the following roles related to accessing other AWS services:
+ `AWS_LAMBDA_ACCESS` – An alternative to the `INVOKE LAMBDA` privilege. For usage information, see [Invoking a Lambda function from an Amazon Aurora MySQL DB cluster](AuroraMySQL.Integrating.Lambda.md).
+ `AWS_LOAD_S3_ACCESS` – An alternative to the `LOAD FROM S3` privilege. For usage information, see [Loading data into an Amazon Aurora MySQL DB cluster from text files in an Amazon S3 bucket](AuroraMySQL.Integrating.LoadFromS3.md).
+ `AWS_SELECT_S3_ACCESS` – An alternative to the `SELECT INTO S3` privilege. For usage information, see [Saving data from an Amazon Aurora MySQL DB cluster into text files in an Amazon S3 bucket](AuroraMySQL.Integrating.SaveIntoS3.md).
+ `AWS_COMPREHEND_ACCESS` – An alternative to the `INVOKE COMPREHEND` privilege. For usage information, see [Granting database users access to Aurora machine learning](mysql-ml.md#aurora-ml-sql-privileges).
+ `AWS_SAGEMAKER_ACCESS` – An alternative to the `INVOKE SAGEMAKER` privilege. For usage information, see [Granting database users access to Aurora machine learning](mysql-ml.md#aurora-ml-sql-privileges).
+ `AWS_BEDROCK_ACCESS` – There's no analogous `INVOKE` privilege for Amazon Bedrock. For usage information, see [Granting database users access to Aurora machine learning](mysql-ml.md#aurora-ml-sql-privileges).
When you grant access by using roles in Aurora MySQL version 3, you also activate the role by using the `SET ROLE role_name` or `SET ROLE ALL` statement. The following example shows how. Substitute the appropriate role name for `AWS_SELECT_S3_ACCESS`.
```
# Grant role to user.
mysql> GRANT AWS_SELECT_S3_ACCESS TO 'user'@'domain-or-ip-address'
# Check the current roles for your user. In this case, the AWS_SELECT_S3_ACCESS role has not been activated.
# Only the rds_superuser_role is currently in effect.
mysql> SELECT CURRENT_ROLE();
+--------------------------+
| CURRENT_ROLE() |
+--------------------------+
| `rds_superuser_role`@`%` |
+--------------------------+
1 row in set (0.00 sec)
# Activate all roles associated with this user using SET ROLE.
# You can activate specific roles or all roles.
# In this case, the user only has 2 roles, so we specify ALL.
mysql> SET ROLE ALL;
Query OK, 0 rows affected (0.00 sec)
# Verify role is now active
mysql> SELECT CURRENT_ROLE();
+-----------------------------------------------------+
| CURRENT_ROLE() |
+-----------------------------------------------------+
| `AWS_SELECT_S3_ACCESS`@`%`,`rds_superuser_role`@`%` |
+-----------------------------------------------------+
```
## Finding the database server ID
The database server ID (`server_id`) is required for binary logging (binlog) replication. The way to find the server ID is different in Aurora MySQL from Community MySQL.
In Community MySQL, the server ID is a number, which you obtain by using the following syntax while logged into the server:
```
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
```
In Aurora MySQL, the server ID is the DB instance ID, which you obtain by using the following syntax while logged into the DB instance:
```
mysql> select @@aurora_server_id;
+------------------------+
| @@aurora_server_id |
+------------------------+
| mydbcluster-instance-2 |
+------------------------+
1 row in set (0.00 sec)
```
For more information on binlog replication, see [Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)](AuroraMySQL.Replication.MySQL.md).
## Authentication
In community MySQL 8.0, the default authentication plugin is `caching_sha2_password`. Aurora MySQL version 3 still uses the `mysql_native_password` plugin. You can't change the `default_authentication_plugin` setting. You can, however, create new users and alter current users, and their individual passwords use the new authentication plugin. Following is an example.
```
mysql> CREATE USER 'testnewsha'@'%' IDENTIFIED WITH caching_sha2_password BY 'aNewShaPassword';
Query OK, 0 rows affected (0.74 sec)
```
# Upgrading to Aurora MySQL version 3
For information on upgrading your database from Aurora MySQL version 2 to version 3, see [Upgrading the major version of an Amazon Aurora MySQL DB cluster](AuroraMySQL.Updates.MajorVersionUpgrade.md).