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

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

By Samir Behara on September 16, 2019 • ( 1 )

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

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.