MySQL: Table is marked as crashed and should be repaired — ещё один вариант

Автор: | 05/25/2014
 

mysqllogoОпять посыпалась таблица в базе. Что интересно — та же, что и в прошлый раз.

Как восстанавливать — было записано в боге, потому — пришлось гуглить 🙂

Нашёлся ещё один, более простой способ.

Подключаемся к серверу:

$ mysql -u root -pMegaPassword
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 43
Server version: 5.5.37 Source distribution
...

Подключаемся к проблемной базе:

mysql> use rtfm_db1;
Database changed

Запускаем проверку базы:

mysql> check table db1_options;
+----------------------+-------+----------+--------------------------------------------------------------------+
| Table                | Op    | Msg_type | Msg_text                                                           |
+----------------------+-------+----------+--------------------------------------------------------------------+
| rtfm_db1.db1_options | check | warning  | Table is marked as crashed                                         |
| rtfm_db1.db1_options | check | warning  | 19 clients are using or haven't closed the table properly          |
| rtfm_db1.db1_options | check | error    | Deleted block at 344212 doesn't point back at previous delete link |
| rtfm_db1.db1_options | check | error    | record delete-link-chain corrupted                                 |
| rtfm_db1.db1_options | check | error    | Corrupt                                                            |
+----------------------+-------+----------+--------------------------------------------------------------------+
5 rows in set (0.03 sec)

Далее — используем опцию REPAIR TABLE. Так как она работает только с MyISAM таблицами — проверяем тип:

mysql> show table status where name="db1_options";
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| db1_options | MyISAM |      10 | Dynamic    |  622 |            740 |      468968 | 281474976710655 |        31744 |      8248 |         234432 | 2012-09-19 17:38:22 | 2014-05-25 17:06:22 | 2014-05-25 16:59:52 | utf8_general_ci |     NULL |                |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

И запускаем восстановление:

mysql> repair table db1_options;
+----------------------+--------+----------+----------------------------------------+
| Table                | Op     | Msg_type | Msg_text                               |
+----------------------+--------+----------+----------------------------------------+
| rtfm_db1.db1_options | repair | warning  | Number of rows changed from 623 to 621 |
| rtfm_db1.db1_options | repair | status   | OK                                     |
+----------------------+--------+----------+----------------------------------------+
2 rows in set (0.08 sec)

Ещё раз проверяем:

mysql> check table db1_options;
+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| rtfm_db1.db1_options | check | status   | OK       |
+----------------------+-------+----------+----------+
1 row in set (0.01 sec)

Готово.