[QUESTION] Mitigation around AWS RDS write forwarding in read only replicas

[ls-urs-keller]

Summary

We had some issues around AWS RDS with cross region replication and rw/ro replicas with write forwarding.
I believe it is a bug on the AWS side. But it would be good if it could be mitigated.

Analysis here: https://github.com/ls-urs-keller/r2dbc-test

Mitigation: don't issue "START TRANSACTION READ WRITE" but either just "BEGIN" or "START TRANSACTION" if the transaction is readonly=false.

Checklist

• I have searched the existing issues to make sure my question has not been asked before
• I have provided all the necessary information and context to help answer my question
• I have read the documentation and/or README for this project (if applicable)

[jchrys]

Thanks for providing the details that really helps. Let me check if we can implement this without affecting current behavior.

[ls-urs-keller]

Thanks for providing the details that really helps. Let me check if we can implement this without affecting current behavior.

I have a support call with AWS, they can't reproduce, before changing anything let me come back to you with the results of that call.

[jchrys]

Got it :D

[ls-urs-keller]

Got it :D

Sorry for the late reply.
So AWS support says it is a permission problem on our side. To use START TRANSACTION READ WRITE you will need more permissions than when using BEGIN or START TRANSACTION go figure.

I guess to make this work with AWD RDS and write forwarding you must not not issue START TRANSACTION READ WRITE.

[jchrys]

Thanks for update :D
I’ll check if we can adjust our implementation accordingly.

[jchrys]

Hi, @ls-urs-keller .
I’ve tested MySQL 5.7 with Aurora 2.11.5 in a global database setup by creating a read cluster in a different region and enabling global write forwarding. However, I couldn’t reproduce the issue using the provided repository. My test involved running SET SESSION aurora_replica_read_consistency = 'GLOBAL'(EVENTUAL, SESSION), and the results were as expected.

Since the queries in the reproducible repository do not include SET aurora_replica_read_consistency, how do you set your consistency level? Also, if you could share or transfer your AWS support ticket, that would be really helpful for further troubleshooting. Could you please also provide your environment details, including the outputs of SHOW VARIABLES LIKE '%aurora%' and SHOW VARIABLES LIKE '%read%'?

Thank you!