Why do I get a read-only error after an Aurora MySQL-Compatible DB cluster fails over?

Short description

When an Aurora MySQL DB cluster experiences a Multi-AZ failover, the cluster endpoints automatically update. The endpoints reflect and point to the newly appointed roles of the writer and reader. The old writer is rebooted and set into read-only mode, and then an existing replica is promoted to a writer.

You might receive a read-only error message when you try one of the following operations through an existing node with the role of reader:

• You're performing a data definition language (DDL) operation
• You're performing a data manipulation language (DML) operation
• You're performing a data control language (DCL) operation

In Aurora MySQL, check the innodb_read_only variable to see if the role is read-only:

mysql> show variables where variable_name='innodb_read_only';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | ON    |
+------------------+-------+
1 row in set (0.01 sec)

Resolution

Use the cluster writer endpoint

The role of a DB instance in an Aurora MySQL cluster can change. It's a best practice to use the cluster writer endpoint to make sure that you always point to the latest writer. If you use a DB instance endpoint or a direct IP Address, then you might not know that failover occurs. When you reconnect to the same host, you get a read-only error and you can't perform DDL or DML changes.

Don't excessively cache DNS

If you aren't using a smart driver, then you're depending on the DNS record updates and propagation after a failover event occurs. Aurora MySQL DNS zones use a short time-to-live (TTL) of 5 seconds. Your network and client configurations must not increase the TTL. DNS caching occurs at multiple layers of an architecture, such as the operating system (OS), the network layer, or the application container. You must understand how each layer is configured. If there is unintended DNS caching beyond 5 seconds, then you might reconnect to the old writer after a failover.

Java Virtual Machines (JVM) can excessively cache DNS. When the JVM resolves a hostname to an IP address, it caches the IP address for a specified period of time. On some configurations, the JVM default TTL is set to never refresh DNS entries until the JVM is restarted. This setting can lead to read-only errors after failover. You must manually set a small TTL so that DNS entries periodically refresh.

Use the AWS Advanced JDBC Driver

The Aurora MySQL DB cluster endpoints automatically propagate DNS record updates. When an event occurs on the database, you might experience a delay in the DNS record updates. When this happens, the event is handled by the application.

The AWS Advanced JDBC (Java Database Connectivity) Wrapper Driver uses the DB cluster topography through the INFORMATION_SCHEMA.REPLICA_HOST_STATUS metadata table. Because the table is in near real-time, the AWS Advanced JDBC wrapper driver routes connections to the appropriate role. It also load-balances across the existing replicas. To implement the AWS Advanced JDBC wrapper from the GitHub website, use the Proxy pattern in Java (from the Baeldung website). The native Aurora MySQL drivers must be added as dependencies. The AWS Advanced JDBC Wrapper Driver is an advanced JDBC wrapper. To benefit from the features of clustered databases, the JDBC wrapper complements and extends the functionality of an existing JDBC driver.

For more information, see Achieve one second or less downtime with the Advanced JDBC Wrapper Driver when upgrading Amazon RDS Multi-AZ DB clusters.

Note: Even the AWS Advanced JDBC Wrapper Driver might be affected by excessive DNS Caching.

Test the instance that you connect to

If you aren't using a smart driver, then test the instance that you're logged in to after you establish a new connection. To test whether you're connected to the writer instance, use the @@innodb_read_only variable. A value of 0 means that you're connected to the writer, as the following example shows:

mysql> select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
| 0                  |
+--------------------+
1 row in set (0.00 sec)

Related information

Aurora MySQL Database administrator's handbook

Aurora connection management