MySQL 表是“只读”的

Table is 'read only'

提问by Cyclone

When I want to execute an updatequery on my table I got an error saying:

当我想update在我的表上执行查询时，我收到一条错误消息：

1036 - Table datais read only.

1036 - 表data是只读的。

How can I fix that?

我该如何解决？

Table attributes in /var/db/mysqlare set to 777.

中的表属性/var/db/mysql设置为777。

'Repair Table' function doesnt seems to help.

“修复表”功能似乎没有帮助。

Is there anything I can do with that?

有什么我可以做的吗？

回答by Rahi

In my case, mysql config file had innodb_force_recovery = 1. Commenting that out solved the issue. Hope it helps someone.

就我而言，mysql 配置文件的 innodb_force_recovery = 1。注释掉解决了问题。希望它可以帮助某人。

回答by ncremins

who owns /var/db/mysql and what group are they in, should be mysql:mysql. you'll also need to restart mysql for changes to take affect

谁拥有 /var/db/mysql 以及他们在哪个组中，应该是 mysql:mysql。您还需要重新启动 mysql 以使更改生效

also check that the currently logged in user had GRANT access to update

还要检查当前登录的用户是否具有更新的 GRANT 访问权限

回答by kamranonline

You should change owner to MYSQL:MYSQL.

您应该将所有者更改为 MYSQL:MYSQL。

Use this command: chown -Rf mysql:mysql /var/lib/mysql/DBNAME

使用这个命令： chown -Rf mysql:mysql /var/lib/mysql/DBNAME

回答by Fom

(This answer is related to the headline, but no to the original question.)

（此答案与标题有关，但与原始问题无关。）

In case you (like me) are trying to temporarily alter data via the MySQL Workbench interface:

如果您（像我一样）尝试通过 MySQL Workbench 界面临时更改数据：

• If the table does not have a primary key, MySQL Workbench has no way of identifying the row you are trying to alter, so therefore you cannot alter it.

• Solution in that case is to either alter the data via another route, or simply to add a primary key to the table.

• 如果表没有主键，MySQL Workbench 无法识别您尝试更改的行，因此您无法更改它。

• 在这种情况下，解决方案是通过另一条路线更改数据，或者只是向表中添加主键。

In any case, I hope it helps someone :)

无论如何，我希望它可以帮助某人:)

回答by Sophie cai

My situation is everytime I needed to edit "innodb_force_recovery = 1" in my.inito force mysql to start, and the error log showed some error said:

我的情况是每次我需要在my.ini中编辑“ innodb_force_recovery = 1”来强制启动mysql，错误日志显示一些错误说：

Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_table_stats uses space ID: 1 at filepath: .\mysql\innodb_table_stats.ibd. Cannot open tablespace profile/profile_commentmeta which uses space ID: 1 at filepath: .\profile\profile_commentmeta.ibd

试图打开以前打开的表空间。以前的表空间 mysql/innodb_table_stats 在文件路径中使用空间 ID：1：.\mysql\innodb_table_stats.ibd。无法打开在文件路径中使用空间 ID：1 的表空间配置文件/profile_commentmeta：.\profile\profile_commentmeta.ibd

I didn't know why this file was not able to open and it caused so many other"table read only" problems to other databases too.

我不知道为什么这个文件无法打开，它也给其他数据库造成了许多其他“表只读”问题。

So here is how I fixed this problem in a simple way without hurting other files.

所以这里是我如何以简单的方式解决这个问题而不伤害其他文件。

1 First of all, make sure if you add innodb_force_recovery = 1below [mysqld]in my.ini file, and it is working, under path: X:\xampp\mysql\bin\my.ini

1 首先确定你在my.ini文件 中的[mysqld]下面是否添加了innodb_force_recovery = 1，并且可以正常工作，在路径下：X:\xampp\mysql\bin\my.ini

2 Then next step, export all the databases through localhost/phpmyadminunder the exporttab, and store them somewhere, like this:

2 然后下一步，通过localhost/phpmyadmin在导出选项卡下导出所有数据库，并将它们存储在某个地方，如下所示：

3 comment out the datafilefolder to data-bak, then create a new datafilefolder,

3 注释掉数据文件夹到data-bak，然后新建一个数据文件夹，

4 Next step, import all .sqldatabase back from phpmyadminpanel, pleasealso copyphpmyadminfilefolder from the old data-bakfilefolder to the new datafilefolder. If any file is necessary, go back to data-bak filefolder to copy and paste.

4下一步，导入所有的.sql从数据库返回的phpmyadmin面板，请也复制phpmyadmin的从旧filefolder数据李明博filefolder到新的数据filefolder。如果需要任何文件，请返回 data-bak 文件夹进行复制和粘贴。

Now all fixed and done, don't need to force mysql to start everytime. Hope this also works for you.

现在一切都解决了，不需要每次都强制启动mysql。希望这也适用于您。

回答by Borodin

MySQL doesn't have write access to the database file. Check the permissions and the owner of the file.

MySQL 没有对数据库文件的写访问权限。检查文件的权限和所有者。

回答by vpgodara

On windows I use Xampp server I comment the line in my.iniinnodb_force_recovery = 1 to #innodb_force_recovery = 1the problem resolved

在 Windows 上，我使用 Xampp 服务器，我将my.ini 中的行注释innodb_force_recovery = 1 为#innodb_force_recovery = 1已解决的问题

回答by eddy85br

I solved the same issue by editing app. armour configuration file. Found the answer here: https://stackoverflow.com/a/14563327/31755661

我通过编辑应用程序解决了同样的问题。装甲配置文件。在这里找到答案：https: //stackoverflow.com/a/14563327/3175566 1

回答by user1693885

One other way to receive this error is to create your table with a "Like" statement and use as source a merged table. That way the newly create table is read-only and can't "receive" any new records.

接收此错误的另一种方法是使用“Like”语句创建表并将合并表用作源。这样新创建的表是只读的，不能“接收”任何新记录。

so

所以

CREATE TABLE ic.icdrs_kw37 LIKE ic.icdrs ... #<- a merged table.

then:

然后：

REPLACE INTO ic.icdrs_kw37 ...  # -> "Table is read-only"

bug or feature?

错误或功能？