Strange MySQL "read-only" error

Question

I'm experiencing a strange MySQL error, seemingly related to the database's read-only flag. A Web application that uses MySQL is running on Debian 7.9. It was running well for weeks, if not more, while, suddenly, attempts to access the application-powered website started producing the following error message on a blank webpage:

Error: 500 - SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement

The following are the steps that I performed as part of my investigation:

found and read read relevant info on the Internet (some pointed to MySQL's read-only flag);
based on the above, tried to find the read-only flag in MySQL config. file (my.cnf) - couldn't find it there, but read that the default value for the flag is OFF anyway;
verified the filesystem to make sure there is plenty of disk space (df -h): Filesystem Size Used Avail Use% Mounted on udev 10M 0 10M 0% /dev tmpfs 3.2G 1.4M 3.2G 1% /run /dev/disk/by-uuid/xxxxxxxxxxxxxxxxx 113G 14G 94G 13% / tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 7.3G 72K 7.3G 1% /run/shm
ran mysqlcheck --all-databases: all tables are OK;
verified that there is plenty of RAM available on the server (free): total used free shared buffers cached Mem: 32898332 2090268 30808064 0 425436 970348 -/+ buffers/cache: 694484 32203848 Swap: 5105660 0 5105660
finally, I have decided to take a "snapshot" of MySQL-related processes (ps ax | grep mysql) during the problem's existence and after a temporary fix (DB restart), hoping that it could give people additional context for ideas; here are the corresponding results:

Problem: 20307 ? S 0:00 /bin/sh /usr/bin/mysqld_safe 20635 ? Sl 0:37 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 20636 ? S 0:00 logger -t mysqld -p daemon.error 36427 pts/0 S+ 0:00 grep mysql

No problem: 36948 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe 37275 pts/0 Sl 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 37276 pts/0 S 0:00 logger -t mysqld -p daemon.error 38313 pts/0 S+ 0:00 grep mysql

UPDATE:

I just experienced the issue again and decided to check whether the global read-only flag is set to OFF or not, assuming the latter. My assumption has confirmed:

mysql> SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

I guess, despite the default OFF value, since it is being overwritten by some process in the system, I will have to set the read-only flag to OFF explicitly and permanently via MySQL configuration file. Will report on results later in an answer.

Can you confirm that restarting the database solved the problem? Has it come back again since restarting? — Eborbob, Feb 16 '16 at 23:47
@Eborbob: Yes, I can confirm that the problem eventually comes back after restarting MySQL, hence my reference to restart as "temporary fix". — Aleksandr Blekh, Feb 16 '16 at 23:49
Next time the problem occurs try logging into MySQL and running `SET GLOBAL read_only = OFF;` and see if that gets it working again. — Eborbob, Feb 17 '16 at 00:13
@Eborbob: Ironically, so far it works with no errors :-). However, I will definitely use your recommendation next time the problem occurs. I appreciate your attention and valuable advice (+1). Is setting the read-only flag via `SET GLOBAL` persistent between MySQL server and Linux restarts? — Aleksandr Blekh, Feb 17 '16 at 01:15
No, the startup setting of the flag is in your my.cnf file, the syntax above is to change the current setting without restarting. It seems that either 1) something is connecting to your db and changing the settings or 2) MySQL is going into read-only mode of its own accord. If the later I'd expect something in the logs. — Eborbob, Feb 17 '16 at 10:14
@Eborbob: Thank you for your recent comment (+1). Please see UPDATE. — Aleksandr Blekh, Feb 18 '16 at 01:29
It will happen when you are using cluster environment, and your system is pointing to secondary node instead of common url to recover from rollover — Kanagavelu Sugumar, May 09 '18 at 13:02
@KanagaveluSugumar I was not using cluster environment at the time, so it is not applicable to this case. However, I appreciate you letting me know about this aspect (+1). — Aleksandr Blekh, May 09 '18 at 16:39

score 26 · Answer 1 · answered Nov 21 '18 at 10:49

26

If you're in AWS Aurora, you might be accessing the replica instance which is read-only so you need to use the DB Cluster endpoint instead.

answered Nov 21 '18 at 10:49

sashoalm

75,001
122
434
781

1

It wasn't AWS Aurora (and it was a while ago), but thank you for the tip. – Aleksandr Blekh Nov 21 '18 at 17:28
3

I had this issue and it was indeed the read-only endpoint for the RDS cluster that was being used for the hostname. Changing to the Writer endpoint resolved the issue for me. – Mullins Dec 18 '18 at 14:32
2

I am using cluster endpoint and still getting the same error. – Akash Arora Oct 01 '19 at 05:57

score 18 · Answer 2 · answered Feb 19 '16 at 09:48

As I see it there are two broad reasons for why your database is being set to read only:

1) MySQL is setting itself read only

I'm not sure what might cause MySQL to go read only, perhaps disk issues or corruption of database? In any case I'd expect something to appear in the logs, so check the MySQL (and system) logs.

2) A client is setting the database read only

Clients connecting to MySQL can set the database read only using the command:

SET GLOBAL read_only = ON;

however to do this the user is required to have SUPER privileges. This permission shouldn't be needed for websites, applications, etc that are using MySQL - keep it only for an admin account that you use for administering the database.

Lock down the permissions that each user has so they only have permission to do the things that they need on the databases / tables that are applicable. If you're using some out-of-the-box applications they should come with instructions detailing what permissions are required (e.g. SELECT, INSERT, DELETE, UPDATE).

Just saw your answer. Thank you for your advice (+1). I will double check your points. However, it seems that my fix solves the problem. — Aleksandr Blekh, Jul 11 '16 at 19:25

score 4 · Answer 3 · answered Feb 18 '16 at 02:34

Based on my question's comments (special thanks to @Eborbob) and my update, I have figured that some process in the system resets the read-only flag to ON (1), which seem to trigger the issue and results in the website becoming inaccessible. In order to fix the problem as well as make this fix persistent across software and server restarts, I decided to update MySQL configuration file my.cnf and restart the DB server.

After making the relevant update (in my case, addition) to the configuration file

read_only=0

let's verify that the flag is indeed set to OFF (0):

# mysql
mysql> SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

Finally, let's restart MySQL server (for some reason, a dynamic reloading of MySQL configuration (/etc/init.d/mysql reload) didn't work, so I had to restart the database server explicitly:

service mysql stop
service mysql start

Voila! Now access to the website is restored. Will update my answer, if any changes will occur.

**UPDATE:** It seems that I was too fast in declaring victory over this problem - it has resurfaced today and it is really frustrating. I will try to investigate a little more, but I have a feeling that it is just simpler to reinstall the whole application suite (even though, it is a pretty involved process). Having said that, without knowing root cause of this problem and how to fix it, what are the chances that the problem will not reoccur after the clean install and I will stuck in the same situation again? — Aleksandr Blekh, Feb 18 '16 at 23:24

score 3 · Answer 4 · answered Feb 01 '22 at 17:41

3

the server might be set to recovery mode find the innodb_force_recovery in my.cnf and uncomment it and restart the server then run the upgrade.

answered Feb 01 '22 at 17:41

Nguyen

31
1

score 2 · Answer 5 · answered Aug 01 '19 at 08:02

2

set global read_only = off; make read only mode off later it will work sure.

answered Aug 01 '19 at 08:02

Ajay Gadhavana

415
5
5

score 2 · Answer 6 · answered Nov 18 '20 at 10:02

2

Not related to the issue, but related to the error 'mysql read-only'.

Make sure you are not trying to write something to a slave instance of mysql.

answered Nov 18 '20 at 10:02

Yuri Tinyukov

124
6

score 1 · Answer 7 · answered Sep 20 '17 at 19:00

1

I just experienced the same error and fixed it by connecting to the hostname of the mysql server as opposed to the IP address. I'm not sure why this fixed it but it did. Just FYI

answered Sep 20 '17 at 19:00

Chris Hall

871
6
13
21

Not sure whether your fix was applicable to my environment, as I think I have already been using a hostname not IP address. Anyway, thank you for the info. – Aleksandr Blekh Sep 21 '17 at 01:08

score 0 · Answer 8 · answered Nov 24 '18 at 04:12

As Eborbob say it's probably a client,

Did you check your backup tool ?

Do you use some SQL proxy like proxySQL or maxscale ? For exemple Mascale can enforce readonly by monitoring : https://jira.mariadb.org/browse/MXS-1859

Replication Manager can also change READ ONLY flag

score 0 · Answer 9 · edited Jul 23 '19 at 12:42

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

It occurs when a user not having the write permission for the sql db tries to insert/update some data into the db.

It is quite a valid security error, as it is stating that you currently are having just --read-only rights and hence cannot execute a query that has anything to do with writing.

To resolve this error:- Get the write access from the DBA.

e.g.

GRANT ALL PRIVILEGES ON database.table TO 'user'@'localhost';

The above query will grant all privileges to the user with username 'user'.

score 0 · Answer 10 · answered Oct 13 '21 at 06:23

0

executing below statement worked for me mysql> SET GLOBAL read_only = OFF;

answered Oct 13 '21 at 06:23

adarsh kavtiyal

55
1
10

score 0 · Answer 11 · answered Jun 15 '23 at 19:00

0

We are running the Aurora cluster and strangely the issue was because we updated the parameter group with long_query_time = 0 for the read-only cluster. As we reverted long_query_time = 1 it's fixed.

answered Jun 15 '23 at 19:00

r.bhardwaj

1,603
6
28
54

AnupRaj · Answer 12 · 2020-06-25T18:43:30.877

-5

This worked for me and you can try it.

Make a backup of your .sql file ( change your query )

Find all Engine=InnoDB and replace them with Engine=MyISAM

and try executing again.

edited Jun 25 '20 at 18:43

answered Jun 25 '20 at 18:24

AnupRaj

661
5
11

This is a major change with very large consequences that it is probably not wise to follow. – James Wilson Jul 13 '22 at 15:46

Strange MySQL "read-only" error

12 Answers12

1) MySQL is setting itself read only

2) A client is setting the database read only