2

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

8
  • I answered this question back on Jul 09, 2015. Read my comment where I mention a blog that shows you how to mess with read_only (dba.stackexchange.com/questions/106458/…) Oct 9, 2016 at 6:46
  • @RolandoMySQLDBA I cannot create a read replica. See update ** Oct 9, 2016 at 8:04
  • Doesn't look like a duplicate -- this looks like Aurora, so let's collect some more info. Is this the master in a single-member Aurora cluster? Oct 9, 2016 at 21:36
  • @Michael-sqlbot Yes it is. Oct 10, 2016 at 6:41
  • Hmmm. So, is it really in read-only mode, or is this error actually indicating something else? SELECT @@READ_ONLY; Oct 10, 2016 at 10:22

2 Answers 2

7

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.

1
  • Where i will get the options to change the parameters Sep 11, 2018 at 10:40
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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.