MySQL使用REPAIR TABLE 在MyISAM 表中报告”Table ‘X’ is Read Only” 错误

适用于:

MySQL 服务器版本4.0及以上

本文信息适用于所有平台。

 

症状

在修复一个MyISAM表时,”Table ‘x’ is read only”错误是什么意思,如何修复它?

mysql> repair table t;

+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐ ‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

| Table | Op | Msg_type | Msg_text |

+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

| test.t | repair | Error | Table ‘t’ is read only |

| test.t | repair | status | Operation failed |

+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

 

原因

这个错误所有两个可能的起因:

  1. MyISAM文件MYD或MYI有错误权限且mysqld进程不能写它们
  2. MyISAM表被压缩

查看MyISAM表是否被压缩,运行

show table status like ‘t’;

如果表被压缩,Row_format 显示 “Compressed”:

mysql> show table status like ‘t’\G

2015/12/14 Document 1565168.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrlstate=

scibdopz6_571&id=1565168.1 2/3

*************************** 1. row ***************************

Name: t

Engine: MyISAM

Version: 10

Row_format: Compressed <‐‐‐‐‐‐‐‐

Rows: 0

Avg_row_ length: 0

Data_length: 41

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_incremen t: NULL

Create_time: 2013‐0 6‐27 19:29:49

Update_time: 2013‐06‐27 19:29:49

Check_time: NULL

Collation: latin 1_swedish_ci

Checksum: 0

Create_optio ns:

Comment:

1 row in s et (0.02 sec)

 

解决方案

对于每个可能的原因,(各个)解决方案有:

  1. MyISAM文件MYD或MYI有错误权限且mysqld进程不能写它们,要修复它,在shell上运行:

chgrp ‐R mysql <dataDir>

chown ‐R mysql <dataDir>

chmod ‐R 660 <dataDir>

 

  1. MyISAM表被压缩,要修复这个问题在shell运行:

myisamchk.exe ‐‐unpack <dataDir>\<db_name>\<table_name>

Then the table can be repaired:

mysql> flush tables;

Query OK, 0 rows aff ected (0.01 sec)

mysql> repair table t;

+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐ ‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+

| Table | Op | Msg_type | Msg_text |

+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐ ‐‐‐‐+

| test.t | repair | status | OK |

2015/12/14 Document 1565168.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrlstate=

scibdopz6_571&id=1565168.1 3/3

+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+

1 row in set (0.05 sec)

 

参考

https://dev.mysql.com/doc/en/repairtable.html

http://dev.mysql.com/doc/en/myisamchkrepairoptions.html#option_myisamchk_unpack

关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Speak Your Mind

沪ICP备14014813号

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569