3

I have set up an Amazon RDS Mysql instance and it is successful. I am trying to create some tables but I am getting this error:

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

I have tried to set global to read-only = 0 but my user does not have privileges. I have tried googling for a solution to no avail. (I am pretty new to RDS)

Update I do not have a create read replica option on my actions

enter image description here

Njuguna Mureithi
  • 135
  • 1
  • 1
  • 7

2 Answers2

8

In my case, I ran into this problem on one of my stacks—but not the first time the stack was built. It seems I was referencing an AWS::RDS::DBInstance's DBInstance.Endpoint.Address, but that instance was actually the master member of an Aurora RDS database cluster.

For some reason, accessing the instance directly sometimes caused the --read-only error. I updated my template to output an AWS::RDS::DBCluster's DBCluster.Endpoint.Address instead (so my app would point at the cluster endpoint, and not the instance directly), and everything worked fine.

It was puzzling to me why this happened sometimes when I built a stack and not others... but the lesson is to make sure you use the cluster endpoint if you have a multiple-member Aurora/RDS cluster, and not just a single instance.

geerlingguy
  • 196
  • 1
  • 4
3

This also works for the RDS Blue/Green deployments recently released.

  • Create a new parameter group and set read_only = 0.

  • Set your read replica / green deployment to this parameter group.

  • Check that the parameter group has applied, it should apply without the need for rebooting.

  • Verify by running SHOW GLOBAL VARIABLES LIKE 'read_only';

It should display :

read_only OFF

Additionally if your purpose of removing the read_only is to change column types such as tiny int to int, you will also need to change the slave_type_conversions parameter. I would recommend setting it to ALL_NON_LOSSY as the safest option. See documentation for more details https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html#sysvar_slave_type_conversions

Thu 01 Jan 1970 000000 GMT
  • 131
  • 3