奇怪的 MySQL “只读"错误

2022-01-24 00:00:00 database debian mysql

我遇到了一个奇怪的 MySQL 错误，似乎与数据库的 read-only 标志有关.使用 MySQL 的 Web 应用程序在 Debian 7.9 上运行.它运行了好几个星期，甚至更多，但突然间，尝试访问应用程序驱动的网站开始在空白网页上产生以下错误消息:

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:

错误:500 - SQLSTATE[HY000]:一般错误:1290 MySQL 服务器是使用 --read-only 选项运行，因此它无法执行此操作声明

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:

  • 在网上找到并阅读了相关信息(有些指向 MySQL 的 read-only 标志)；
  • 根据上述情况，尝试在 MySQL config 中找到 read-only 标志.文件 (my.cnf) - 在那里找不到它，但读到该标志的默认值为 OFF；

  • 验证文件系统以确保有足够的磁盘空间(df -h):<代码>已使用的文件系统大小可用使用百分比已安装在udev 10M 0 10M 0%/devtmpfs 3.2G 1.4M 3.2G 1%/运行/dev/disk/by-uuid/xxxxxxxxxxxxxxxxx 113G 14G 94G 13%/tmpfs 5.0M 0 5.0M 0%/运行/锁定tmpfs 7.3G 72K 7.3G 1%/run/shm

  • 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

运行mysqlcheck --all-databases:所有表都OK；

最后，我决定在问题存在期间和临时修复(数据库重启)之后拍摄 MySQL 相关进程(ps ax | grep mysql)的快照"，希望它可以为人们提供更多的想法背景；以下是相应的结果:

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:

问题:<代码>20307 ?S 0:00/bin/sh/usr/bin/mysqld_safe20635 ?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=330620636 ?S 0:00 记录器 -t mysqld -p daemon.error36427 点/0 S+ 0:00 grep mysql

没问题:<代码>36948 点/0 S 0:00/bin/sh/usr/bin/mysqld_safe37275 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=330637276 pts/0 S 0:00 记录器-t mysqld -p daemon.error38313 点/0 S+ 0:00 grep mysql

更新:

我刚刚又遇到了这个问题，并决定检查全局只读标志是否设置为 OFF，假设是后者.我的假设已经证实:

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)

我猜，尽管默认的 OFF 值，因为它被系统中的某些进程覆盖，我将不得不通过 MySQL 配置文件将只读标志显式和永久设置为 OFF.稍后将在答案中报告结果.

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.

推荐答案

如果您在 AWS Aurora，您可能正在访问只读的副本实例，因此您需要改用数据库集群端点.

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.

相关文章