Confused about AWS RDS read replica databases. Why can I edit rows?

Question

Edit: I'm not trying to edit the read replica. I'm saying I did edit it and I'm confused on why I was able to.

I have a database in US-West. I made a read replica in Mumbai, so the users in India don't experience slowness. Out of curiosity, I tried to edit a row in the Mumbai read-replica database hoping to get a security error rejecting my write attempt (since after all, it is a READ replica). But the write operation was successful. Why is that? Shouldn't this be a read-only database?

I then went to the master database hoping the writing process would at least be synchronized, but my write execution didn't persist. The master database was now different than the place.

I also tried edited data in the master database, hoping it would replicate it to the slave database, but that failed as well.

Obviously, I'm not understanding something.

Answer 1

I had the same issue. (Old question, but I couldn't find an answer anywhere else and this was my exact issue)

I had created a cross-region read replica, which when complete, all the original data was there, but no updates were synchronised between the two regions.

The issue was the parameter groups.

In my case, I had changed my primary from the default parameter group to one which allowed case insensitive tables. The parameter group is not copied over to the new region, so the replication was failing with:

Error 'Table 'MY_TABLE' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE MY_TABLE SET ....''

So in short, create a parameter group in your new region that matches the primary region and assign that parameter group as soon as the replica is created.

I also ran this script on the newly created replica:

CALL mysql.rds_start_replication

I am unsure if this was required, but I ran it anyway.

Answer 2

Take a look at this link from Amazon Web Service to get an idea:

How do I configure my Amazon RDS DB instance read replica to be modifiable?

Probably your read replica has the flag read_only = false

---

Modify the newly created parameter group and set the following parameter:

• In the navigation pane, choose Parameter Groups. The available DB parameter groups appear in a list.
• In the list, select the parameter group you want to modify.
• Choose Edit Parameters and set the following parameter to the specified value: read_only = 0
• Choose Save Changes.

---

I think you should read a little about Cross region read replicas and how they work.

Working with Read Replicas of MariaDB, MySQL, and PostgreSQL DB Instances

Read Replica lag is influenced by a number of factors including the load on both the primary and secondary instances, the amount of data being replicated, the number of replicas, if they are within the same region or cross-region, etc. Lag can stretch to seconds or minutes, though typically it is under one minute.

Reference: https://stackoverflow.com/a/44442233/1715121

Facts to remember about RDS Read Replica

• In Read Replica, a snapshot is taken of the primary database.
• Read replicas are available in Amazon RDS for MySQL, MariaDB, and PostgreSQL.
• Read replicas in Amazon RDS for MySQL, MariaDB, and PostgreSQL provide a complementary availability mechanism to Amazon RDS Multi-AZ Deployments
• All traffic between the source and destination database is encrypted for Read Replica’s.
• You need to enable backups before creating Read replica’s. This can be done by setting the backup retention period to a value other than 0
• Amazon RDS for MySQL, MariaDB and PostgreSQL currently allow you to create up to five Read Replicas for a given source DB Instance
• It is possible to create a read replica of another read replica. You can create a second-tier Read Replica from an existing first-tier Read Replica. By creating a second-tier Read Replica, you may be able to move some of the replication load from the master database instance to a first-tier Read Replica.
• Even though a read replica is updated from the source database, the target replica can still become out of sync due to various reasons.
• You can delete a read replica at any point in time.

Answer 3

I think only adding index can be done on slave db in amazon rds if you put read replica in write mode and it will be continue in write mode till you change parameter read_only=1 and apply it immediately.