Exploring AWS Aurora v3 for MySQL 8

Aurora and MySQL 8 point releases

The first release of Aurora v3 is designed to be compatible with MySQL 8.0.23. Whenever discussing MySQL 8, the specific point release is actually very important!

Background

Oracle released the first generally available (GA) version of MySQL 8.0 in April 2018, marking a huge step forwards in MySQL’s functionality. MySQL’s release versioning scheme also changed at that time: new features are included on a rolling basis in quarterly 8.0.x point releases. Here are a few examples that relate to table design and DDL:

Version	Released	DDL features
8.0.12	Jul 2018	ALGORITHM=INSTANT for adding new columns to a table
8.0.13	Oct 2018	DEFAULT values for columns can now contain expressions
8.0.16	Apr 2019	CHECK constraints
8.0.23	Jan 2021	INVISIBLE columns (excluded from SELECT *), DROP TABLE major performance improvement even with large buffer pool
8.0.27	Oct 2021	Parallel index builds via innodb_ddl_threads

Since the point version determines which new features are available, this means the specific versions offered by each DBaaS cloud vendor are important to understand, and ditto for their rate of rolling out new versions.

Cloud vendors typically lag behind upstream MySQL releases. Let’s compare AWS Aurora with other managed offerings as of January 2022:

AWS Aurora v3

Previously, AWS Aurora used its own versioning scheme, where each major Aurora release was permanently pinned to a specific upstream MySQL point release. Aurora v1 is compatible with the positively ancient MySQL 5.6.10 (Feb 2013), and Aurora v2 corresponds with MySQL 5.7.12 (Apr 2016).

Fortunately, with Aurora v3, each Aurora release can line up with a different MySQL 8.0 point release. Their first (and currently only) available version corresponds with MySQL 8.0.23:

mysql> SELECT @@global.version, @@global.aurora_version;
+------------------+-------------------------+
| @@global.version | @@global.aurora_version |
+------------------+-------------------------+
| 8.0.23           | 3.01.0                  |
+------------------+-------------------------+
1 row in set (0.00 sec)

MySQL 8.0.23 was released in Jan 2021, so Aurora v3 is currently 12 months behind upstream. The rate of newer rolling updates remains to be seen.

“Traditional” RDS

The non-Aurora RDS product on AWS first added support for MySQL 8.0 back in October 2018, and currently offers a number of different MySQL 8.0 point releases, up through 8.0.27. This is only a few months behind upstream; for context, upstream 8.0.28 was just released last week. A handful of point releases appear to have been skipped, but a majority of them are present. If you prefer to keep relatively current with 8.0 point releases, traditional RDS remains a safer choice than Aurora at this time.

Competing cloud vendors

Google CloudSQL: 6 to 22 months lag vs upstream. GCP added MySQL 8.0 to their managed MySQL offering in August 2020, launching with 8.0.19, which at that point was only 7 months behind upstream. However, this specific version remained CloudSQL’s sole 8.0 release until December 2021, when 8.0.26 was added as an additional option.

Azure Database for MySQL: 18 to 36 months lag vs upstream. Microsoft’s cloud added a managed MySQL 8.0 offering in December 2019, and now has two different managed MySQL 8.0 products. The legacy “single server” option gives you 8.0.15, released 3 years ago. The newer “flexible server” option provides 8.0.21, from July 2020.

Oracle Cloud MySQL Database Service: The gold standard, as it’s maintained by the MySQL team. Oracle Cloud’s managed offering makes the latest version available immediately (same day) upon release.

Admin privileges

Managed database services don’t grant SUPER privileges, as this provides too much opportunity to break the cloud automation layers. However, MySQL 8.0 added quite a few fine-grained “dynamic” administrative privileges, some of which are theoretically safe for admin users on cloud DBaaS platforms. Even so, many of these platforms are rather stingy with allowing these privileges.

Happily, AWS Aurora is fairly generous here. Observe the dynamic privileges in the second row:

mysql> SHOW GRANTS \G
*************************** 1. row ***************************
Grants for admin@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for admin@%: GRANT APPLICATION_PASSWORD_ADMIN, CONNECTION_ADMIN, REPLICATION_APPLIER, ROLE_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, XA_RECOVER_ADMIN ON *.* TO `admin`@`%` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for admin@%: GRANT `rds_superuser_role`@`%` TO `admin`@`%`
3 rows in set (0.00 sec)

For example, SESSION_VARIABLES_ADMIN allows use of SET SESSION sql_log_bin=0 to selectively prevent replication of administrative statements, if your Aurora cluster has any traditional binlog-based replicas. And SET_USER_ID permits use of arbitrary DEFINER clauses in stored procedures, functions, and views – especially useful for schema management tools, including Skeema!

This is actually much better than traditional RDS (non-Aurora) MySQL 8, which does not provide any of these dynamic privileges:

mysql> SHOW GRANTS \G
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO `admin`@`%` WITH GRANT OPTION
1 row in set (0.00 sec)

 

A few pain points

Here are some minor issues we stumbled over when testing out Aurora MySQL 8.

No db.t3.small instance size

Previously, the smallest (and least expensive) instance size usable with Aurora was db.t3.small. However, with MySQL 8, this size is not available; with Aurora v3 the smallest option is db.t3.medium. This change can have cost implications, for example if each engineer in your company has their own Aurora dev cluster.

In contrast, non-Aurora RDS for MySQL 8.0 still permits db.t3.small and even db.t3.micro.

Strict mode still disabled by default

Upstream MySQL started defaulting to a strict sql_mode in MySQL 5.7, released in 2015. This is a very important setting, as it prevents all of the silent truncation behaviors / silent data loss problems that historically injured MySQL’s reputation relative to other databases.

For whatever reason, all versions of all AWS RDS products default to disabling strict mode. Aurora v3 is no exception; its default sql_mode is literally a blank string:

mysql> SELECT @@session.sql_mode, @@global.sql_mode;
+--------------------+-------------------+
| @@session.sql_mode | @@global.sql_mode |
+--------------------+-------------------+
|                    |                   |
+--------------------+-------------------+
1 row in set (0.00 sec)

This is a huge land-mine for developers. If you build an application without strict mode, enabling it after the fact can be a painful and time-consuming endeavor. This is especially difficult in programming languages such as Go, where the DB interface doesn’t transmit WARNING counts for query results.

Still no compressed table support

As with previous versions of Aurora, compressed tables are not supported. If you attempt to create a compressed table, Aurora v3 simply ignores the compression aspect and creates a normal table. This happens relatively silently (even with strict sql_mode!) and is only clear if you view the WARNINGs:

mysql> CREATE TABLE test_compression (
    ->     id int unsigned NOT NULL AUTO_INCREMENT,
    ->     stuff varchar(1000),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 3 warnings (0.03 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1478 | KEY_BLOCK_SIZE is not currently supported        |
| Warning | 1478 | ROW_FORMAT=COMPRESSED is not currently supported |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.             |
+---------+------+--------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE test_compression\G
*************************** 1. row ***************************
       Table: test_compression
Create Table: CREATE TABLE `test_compression` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `stuff` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
1 row in set (0.01 sec)

This is a slight behavior change from previous versions of Aurora, which used to leave the ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 clause inside the SHOW CREATE TABLE output, despite it having no effect. The new SHOW CREATE TABLE behavior is technically more accurate, but it does prevent automated linters (including Skeema’s linter) from potentially detecting the discrepancy.

Aurora’s substitution of ROW_FORMAT=COMPACT is especially peculiar, as COMPACT is strictly inferior to the modern default of DYNAMIC!

Transparent page compression (e.g. COMPRESSION='zlib') is also not supported by any version of Aurora, although that feature at least throws a fatal error.

Conclusions

Despite a few pain points, it’s great to have a MySQL 8-based Aurora offering available. Existing Aurora 5.6-5.7 deployments finally have a path forward to 8.0 without sacrificing Aurora’s unique benefits. The release velocity remains to be seen though. For new applications, users should compare and contrast Aurora’s features with the faster release schedule of other alternatives.

Skeema offers compatibility with AWS Aurora for MySQL 8.0 beginning with Skeema v1.7.0. Our premium products are extensively tested against all three major versions of Aurora.