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.

Answer 1

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.