The reader endpoint of an Amazon Aurora cluster, the one with cluster-ro in its host name, sends a connection to an Aurora Replica when the cluster has one. When the cluster has none, the same endpoint connects to the writer instance, and statements sent through it can change data.
The AWS documentation for reader endpoints covers both cases. With one or more replicas, a session on the reader endpoint can only run read-only statements such as SELECT. With only a primary instance, the endpoint connects to it, and “you can perform write operations through the endpoint”.
I point read-only work at that endpoint: a scheduled task that confirms a nightly job did what it should, or a query that sizes a problem before I fix it. The host name reads like a guarantee. What the endpoint does is choose an instance, and the choice follows the number of replicas, which is not the same everywhere a script runs. A cluster sized as a single instance, a common choice for staging and development, has no replica to receive the session. A script that was read-only where I tested it can hold write access where it runs next.
See which instance answered
AWS documents the innodb_read_only variable for this:
SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';ON means a reader instance. OFF means the writer.
Make the session read-only
I set the access mode in the session before any query:
SET SESSION TRANSACTION READ ONLY;
SELECT @@SESSION.transaction_read_only;The second statement returns 1. In MySQL 8.0 the setting applies to every later transaction in that session, and a write then fails with error 1792, Cannot execute statement in a READ ONLY transaction.
The mysql client can run the statement at connect time, and it runs it again after an automatic reconnect:
mysql --init-command="SET SESSION TRANSACTION READ ONLY" -h "$READER_ENDPOINT" -u "$DB_USER" -pThe same session can set READ WRITE again, so this guards against my own mistakes, which is what a read-only script needs.
The endpoint’s behavior follows the cluster. Add a first replica and the same host name becomes read-only. Remove the last one and it accepts writes again, with no change on the client side. So the two statements go at the top of every read-only script I write, whichever cluster it will run against.