Earlier in the day, I was working on one of my microservices hosted in the cloud. The service uses AWS Aurora as the database and I generally connect locally to the DB instance via MySQL Workbench. I logged into the database in MySQL Workbench with the ‘admin’ credentials, however was not able to run any DML statements. I received the below error —
Error Code: 1290. The MySQL server is running with the –read-only option so it cannot execute this statement
After an hour of troubleshooting and researching, I finally figured out what the issue was. I thought of writing a quick blog post on this in case you bump into the same issue – you know what to look out for.
With AWS Aurora DB Cluster you have 2 types of endpoints which you can connect with – Writer and Reader. When you are setting up your MySQL Workbench make sure to add the ‘Writer’ endpoint and not the ‘Reader’ endpoint.
If you have the cluster endpoint, then you can connect to the primary instance for the database cluster. You can perform both read and write operations using the cluster endpoint. The cluster endpoint always points to the primary instance.
I realized that I had used the Reader endpoint while establishing the DB connection via MySQL Workbench. This was the reason behind the ‘read-only option’ error. Once I changed the endpoint to the Writer one, I was able to successfully execute DML scripts.
While connecting to the database, ensure that you are using either the Cluster endpoint or the Writer endpoint.
Check out my other recent blog posts around troubleshooting issues —
Lost connection to MySQL server during query
Troubleshooting Issues with AWS Database Migration Service
Flyway Error: Found non-empty schema(s) without schema history table
Tomcat Error – java.net.BindException: Address already in use
Resolving Gradle issues in IntelliJ IDEA
Categories: Amazon Aurora, AWS, Troubleshooting
tell me, how I can truncate table in rds replication instance ?
because there are some old data, just ocupping space, and I need those information ok, just in master not in slave.