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

Njuguna Mureithi
  • 125
  • 1
  • 1
  • 7
  • 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 (http://dba.stackexchange.com/questions/106458/synchronizing-a-rds-instance-with-another-rds-instance/106461#106461) – RolandoMySQLDBA Oct 09 '16 at 06:46
  • @RolandoMySQLDBA I cannot create a read replica. See update ** – Njuguna Mureithi Oct 09 '16 at 08: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? – Michael - sqlbot Oct 09 '16 at 21:36
  • @Michael-sqlbot Yes it is. – Njuguna Mureithi Oct 10 '16 at 06:41
  • Hmmm. So, is it really in read-only mode, or is this error actually indicating something else? SELECT @@READ_ONLY; – Michael - sqlbot Oct 10 '16 at 10:22
  • @Michael - sqlbot @@READ_ONLY ==> 0 – Njuguna Mureithi Oct 10 '16 at 12:17
  • @Michael-sqlbot Trying to create a table --> http://pastebin.com/ZHdqJYCh – Njuguna Mureithi Oct 10 '16 at 12:25
  • Interesting. Take another look at your console. Failover should be grayed out on the Instance Actions drop-down list out if this is the master of a single-member (one master, zero replicas) Aurora cluster, since that action wouldn't be an option available to you (gray-out is what I see in the console for this option when viewing a single-member cluster's master). It isn't grayed out on your screen shot, which suggests that you have at least 2 members in the cluster and the one you're connected to and trying to execute this query on is in fact a replica (despite @@read_only = 0). Confirm? – Michael - sqlbot Oct 10 '16 at 14:59

2 Answers2

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.

geerlingguy
  • 186
  • 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