The MySQL server is running with the –read-only option so it cannot execute this statement

Problem Statement

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

MySQL Connection Workbench.png

Resolution

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.

RDS Endpoints

Conclusion

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

Tags: , ,

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: