MySQL错误修复记录：Table xx is marked as crashed and should be repaired

昨晚入睡后，收到松哥的 QQ 消息，说松松商城打开报错，于是手机 QQ 上打开了首页地址，发现有如下报错：

MySQL server error report:Array ( [0] => Array ( [message] => MySQL Query Error ) [1] => Array ( [sql] => SELECT u.user_name, og.goods_number, oi.add_time, IF(oi.order_status IN (2, 3, 4), 0, 1) AS order_status FROM `hide_songsong`.`ecs_order_info` AS oi LEFT JOIN `hide_songsong`.`ecs_users` AS u ON oi.user_id = u.user_id, `hide_songsong`.`ecs_order_goods` AS og WHERE oi.order_id = og.order_id AND og.goods_id = 213 ORDER BY oi.add_time DESC LIMIT 50 ) [2] => Array ( [error] => Table 'ecs_users' is marked as crashed and should be repaired ) [3] => Array ( [errno] => 1194 ) )

关键报错信息：

Table 'ecs_users' is marked as crashed and should be repaired

提示这张表损坏了，必须修复，登陆服务器之后，开始修复，以下记录备忘。

1、尝试使用 myisamchk 命令对所有表索引文件进行修复：

myisamchk --safe-recover /path/to/*.MYI

- recovering (with sort) MyISAM-table 'ecs_users.MYI'
Data records: 2593
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
*******

2、刷新首页依然报错，于是登陆 MySQL，执行 REPAIR TABLE ecs_users;

mysql> REPAIR TABLE ecs_users;
+-------------------------+--------+----------+-------------------------------------------------------------------------------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                                                                                                    |
+-------------------------+--------+----------+-------------------------------------------------------------------------------------------------------------+
| hide_songsong.ecs_users | repair | error    | 1 when fixing table                                                                                         |
| hide_songsong.ecs_users | repair | Error    | Can't change ownership of the file '/HIDE_songsong/ecs_users.MYD' (Errcode: 1) |
| hide_songsong.ecs_users | repair | status   | Operation failed                                                                                            |
+-------------------------+--------+----------+-------------------------------------------------------------------------------------------------------------+
3 rows in set (0.02 sec)

3、提示无法修改拥有着属性，应该是上一步使用 myisamchk 命令时文件所属变成了 root 了，于是用 chown 更该拥有者：

chown -R mysql:mysql /HIDE_songsong/*

4、然后继续登陆 MySQL 执行 REPAIR TABLE ecs_users;  成功：

mysql> use hide_songsong;
Database changed
mysql> REPAIR TABLE ecs_users;
+-------------------------+--------+----------+----------+
| Table                   | Op     | Msg_type | Msg_text |
+-------------------------+--------+----------+----------+
| hide_songsong.ecs_users | repair | status   | OK       |
+-------------------------+--------+----------+----------+
1 row in set (0.02 sec)

mysql>

在刷新网站，已经正常：

事后总结：一般这个报错都是因为数据库表索引文件损坏导致的，发现这类错误可以尝试使用 phpMyAdmin 的 Repair 自动修复功能。如果不行，先将数据库文件做好备份，然后按照本文的步骤尝试修复吧。