When you run a MySQL data manipulation query — INSERT, UPDATE, or DELETE — you may encounter an error saying that the statement can’t be executed because of the read-only option.
For example, I have a table named cities with the following data:
+----+-----------------+
| id | city_name |
+----+-----------------+
| 1 | Washington |
| 6 | Manchester City |
| 7 | Liverpool City |
+----+-----------------+
When I try to run an UPDATE statement for the table as shown below:
UPDATE cities SET city_name = "York" WHERE id = 1;
I received an ERROR 1290 response from MySQL:
ERROR 1290 (HY000): The MySQL server is running
with the --read-only option so it cannot execute this statement
This error happens when you’re trying to run a data manipulation query in a MySQL instance that is set to read-only
You can confirm this by checking the read_only global variable in your MySQL database server:
SHOW GLOBAL VARIABLES LIKE 'read_only';
-- response:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
As you can see above, I have the read_only option in my server set to ON.
To turn it off, you can use the SET GLOBAL statement as shown below:
SET GLOBAL read_only=0;
The option should now be turned off.
Now I can run the UPDATE statement for my cities table just fine:
mysql> UPDATE cities SET city_name = "York" WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
read-only option in AWS Aurora
If you’re using a cloud service for your MySQL database like AWS Aurora, you need to check for your innodb_read_only variable instead of the read-only variable.
SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';
This is because cloud services like AWS Aurora usually have multiple database instances, and you can only write to the main (master) database instance and not the secondary (slave) database instance.
For more information, you can check the AWS Aurora documentation
I’m not familiar with other MySQL cloud service providers, but they may have a similar distributed database system.
You need to check on the documentation of the cloud service that you’re using to find out.
And that’s how you resolve the ERROR 1290 in your MySQL database server.