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

Short description

When an Aurora MySQL-Compatible DB cluster experiences a Multi-AZ failover, the cluster endpoints automatically update. The old writer reboots and is set into read-only mode, and then Aurora promotes an existing replica to a writer. The endpoints reflect this change and point to the new writer and reader roles.

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

• Data definition language (DDL) operation
• Data manipulation language (DML) operation
• Data control language (DCL) operation

Resolution

Determine whether the role is read only

To check whether the role is read only, use the innodb_read_only variable.

Example output:

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

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 a 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. If there's 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. In some configurations, the JVM default TTL refreshes DNS entries only when the JVM restarts and can cause read-only errors after failover. To resolve this issue, manually set a small TTL so that DNS entries periodically refresh.

Use the AWS Advanced JDBC Driver

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 application handles the event.

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. Use the proxy pattern in Java to implement the AWS Advanced JDBC Wrapper. You must add the native Aurora MySQL drivers as dependencies. For more information, see Proxy pattern in Java on the Baeldung website. You can download the AWS Advanced JDBC Wrapper on the GitHub website.

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: Excessive DNS caching might affect the AWS Advanced JDBC Wrapper Driver. For more information, see Improve application availability on Amazon Aurora.

Test the instance that you're connected to

If you aren't using a smart driver, then test the instance after you establish a new connection. To test whether you're connected to the writer instance, use the @@innodb_read_only variable. If you receive a value of 0, then you're connected to the writer.

Example output:

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

Related information

Amazon Aurora MySQL database administrator's handbook

Amazon Aurora endpoint connections