Repairing a crashed MySQL Table Revision as of Sunday, 20 December 2015 at 19:56 UTC
You may be chugging along, doing your thing, logging on to MySQL, and
issuing something like this:
mysql> select * from testdatabase.user;
Your smile may lose its luster when you’re confronted with this:
ERROR 145 (HY000): Table './testdatabase/user' is marked as crashed and should be repaired
Don’t worry. There are a few things you can do to fix this.
When your job is easy
Usually, issuing this should fix the issue:
mysql> repair table testdatabase.user;
This, if all goes well, will produce output sort of like this:
+----------------------+--------+----------+------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------------+--------+----------+------------------------------------------+
| testdatabase.user | repair | warning | Number of rows changed from 0 to 1151697 |
| testdatabase.user | repair | status | OK |
+----------------------+--------+----------+------------------------------------------+
2 rows in set (2 min 7.91 sec)
When your job gets harder
It gets frustrating when you get something like this:
+------------+--------+----------+------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+--------+----------+------------------------------------------------------------------+
| mysql.user | repair | Error | Table './testdatabase/user' is marked as crashed and should be repaired |
| mysql.user | repair | Error | Table 'user' is marked as crashed and should be repaired |
| mysql.user | repair | status | Table is already up to date |
+------------+--------+----------+------------------------------------------------------------------+
3 rows in set (0.00 sec)
DON’T PANIC. This can be fixed by quitting MySQL, and doing this:
cd /var/lib/mysql/testdatabase
myisamchk --safe-recover user
“user”, of course, is the table you wanted to recover. If the Gods have
mercy, you will see something like this:
Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200
Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
- recovering (with keycache) MyISAM-table 'user'
Data records: 81
Duplicate key 1 for record at 7918 against new record at 3959
Duplicate key 1 for record at 8132 against new record at 3638
Duplicate key 1 for record at 8239 against new record at 1498
Duplicate key 1 for record at 8560 against new record at 4280
Data records: 77
myisamchk: warning: 4 records have been removed
Ta da! Assuming, of course, that your table’s the default MyISAM type.