How to set up local write forwarding in aurora

0

The aurora user guide has a post for how to turn on local write forwarding, but offers no insight into how to actually allow a read replica to accept a write request. As a result, despite having this setting enabled, I am still getting read only errors when sending UPDATEs to the replica, despite using non-ro credentials

asked a month ago199 views
2 Answers
3

To set up local write forwarding in Aurora MySQL and allow a read replica to accept write requests, you need to follow these steps:

  1. Enable local write forwarding at the cluster level. This can be done when creating or modifying a DB cluster using the AWS Management Console, AWS CLI, or Amazon RDS API.

  2. Once local write forwarding is enabled at the cluster level, you need to enable it at the database session level using the aurora_replica_read_consistency parameter. This parameter is crucial for write forwarding to work.

  3. Set the aurora_replica_read_consistency parameter for any session where you want to forward writes. This parameter should be set to one of three values: EVENTUAL, SESSION, or GLOBAL. Each of these provides a different level of read consistency.

  4. For Aurora MySQL version 2 and version 3 lower than 3.04, use aurora_replica_read_consistency as a session variable. For Aurora MySQL version 3.04 and higher, you can use it as either a session variable or as a DB cluster parameter.

  5. Remember that the default value for aurora_replica_read_consistency is empty (''), which means write forwarding is not enabled by default for any session, even if it's enabled at the cluster level.

  6. To set this parameter in your application, you would typically execute a SQL statement like this at the beginning of your session:

SET SESSION aurora_replica_read_consistency = 'EVENTUAL';

(Replace 'EVENTUAL' with 'SESSION' or 'GLOBAL' as needed for your use case)

If you're still getting read-only errors after enabling write forwarding at the cluster level, it's likely because the aurora_replica_read_consistency parameter hasn't been set for your session. Always ensure this parameter is set appropriately for any session where you want to use write forwarding.

Also, be aware that certain SQL statements and operations are not compatible with write forwarding, such as DDL statements, updating a permanent table using data from a temporary table, and certain types of transactions. Make sure your operations are compatible with write forwarding.

Lastly, monitor your application's behavior and performance after enabling write forwarding, as it can impact consistency and latency depending on the chosen consistency mode.
Sources
Enabling local write forwarding - Amazon Aurora
Using write forwarding in an Aurora MySQL global database - Amazon Aurora
Using local write forwarding in an Amazon Aurora MySQL DB cluster - Amazon Aurora

profile picture
answered a month ago
AWS
SUPPORT ENGINEER
reviewed a month ago
AWS
SUPPORT ENGINEER
reviewed a month ago
profile picture
EXPERT
reviewed a month ago
1

Hello,

I can understand that you are facing read-only errors after attempting to enable local write forwarding. To set up local write forwarding in Aurora MySQL and allow a read replica to accept write requests, you need to follow these steps:

  1. Enable local write forwarding at the cluster level. This can be done when creating or modifying a DB cluster using the AWS Management Console, AWS CLI, or Amazon RDS API.

  2. Once local write forwarding is enabled at the cluster level, you need to enable it at the database session level using the aurora_replica_read_consistency parameter. This parameter is crucial for write forwarding to work.

  3. Set the aurora_replica_read_consistency parameter for any session where you want to forward writes. This parameter should be set to one of three values: EVENTUAL, SESSION, or GLOBAL. Each of these provides a different level of read consistency.

  4. For Aurora MySQL version 2 and version 3 lower than 3.04, use aurora_replica_read_consistency as a session variable. For Aurora MySQL version 3.04 and higher, you can use it as either a session variable or as a DB cluster parameter.

  5. Remember that the default value for aurora_replica_read_consistency is empty (''), which means write forwarding is not enabled by default for any session, even if it's enabled at the cluster level.

  6. To set this parameter in your application, you would typically execute a SQL statement like this at the beginning of your session:

SET SESSION aurora_replica_read_consistency = 'EVENTUAL'; (Replace 'EVENTUAL' with 'SESSION' or 'GLOBAL' as needed for your use case)

If you're still getting read-only errors after enabling write forwarding at the cluster level, it's likely because the aurora_replica_read_consistency parameter hasn't been set for your session. Always ensure this parameter is set appropriately for any session where you want to use write forwarding.

Also, be aware that certain SQL statements and operations are not compatible with write forwarding, such as DDL statements, updating a permanent table using data from a temporary table, and certain types of transactions. Make sure your operations are compatible with write forwarding.

Lastly, monitor your application's behavior and performance after enabling write forwarding, as it can impact consistency and latency depending on the chosen consistency mode.

For detailed information, you can refer to the below documentation:

Enabling local write forwarding - Amazon Aurora [+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-write-forwarding-enabling.html Using write forwarding in an Aurora MySQL global database - Amazon Aurora [+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-global-database-write-forwarding-ams.html Using local write forwarding in an Amazon Aurora MySQL DB cluster - Amazon Aurora [+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-write-forwarding.html

I would like to inform you that since I do not have access to your resources or environment, I will not be able to validate or confirm the process you have tested. Additionally, the performance benefits of the approach you mentioned may vary depending on the nature of your database workload. If your workload is heavily I/O-bound, involving operations such as sorts, hash aggregations, or other similar operations, you may observe significant performance improvements. However, if your workload does not fall into these categories, the performance benefits may not be as substantial.

However, if you require specific troubleshooting assistance or have any further concerns, I recommend raising a support case by creating a ticket with our support team. Our support team will have access to the necessary resources and information to provide you with more accurate and tailored guidance based on your specific environment and requirements.

AWS
SUPPORT ENGINEER
answered a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions