How to repair MySQL crashed table

Posted on Friday, 13 of January of 2017
1245by Octávio GonçalvesMySQL

Some times, a MYSQL table with a lot of records crash.

This can happen by several resons, the server was rebooted, when the MySQL is work in a table, the process in a table wasn't finished or stucked, etc...

This article will show how we can recover and repair a MySQL (MYISAM) table.

1. The table is crash and we try to repair and we receive the following message:

myisamchk: error: Can't create new tempfile: 'table_mytable.TMD'
MyISAM-table 'table_newsletter_opens.MYI' is not fixed because of errors

2. We need to repair this table by command line:
First, we stop MySQL:

service mysql stop

Then we start the repair procedure:

myisamchk -r  table_mytable.MYI

This can solve the problem. I so, you are done and you can start MySQL again.

service mysql start

3. If you continue seeing error messages, then we have to force MySQL to do a exhaustive and hard repair.

myisamchk -r -v -f table_mytable.MYI


The output will be similar to the following:

- recovering (with sort) MyISAM-table 'table_mytable.MYI'
Data records: 7295214

- Fixing index 1
  - Searching for keys, allocating buffer for 116046 keys
Wrong bytesec:   0-  0-  0 at  399360312; Skipped
Wrong bytesec:   0-  0-  0 at  399360332; Skipped
  - Merging 7295214 keys
  - Last merge and dumping keys

- Fixing index 2
  - Searching for keys, allocating buffer for 116340 keys
  - Merging 7295214 keys
  - Last merge and dumping keys

- Fixing index 3
  - Searching for keys, allocating buffer for 116340 keys
  - Merging 7295214 keys
  - Last merge and dumping keys

- Fixing index 4
  - Searching for keys, allocating buffer for 116340 keys
  - Merging 7295214 keys
  - Last merge and dumping keys


If everything goes fine, and the repair process finish without errors, you can start again MySQL server.

service mysql start

4. If the procedure described above gives an error message like this:

myisamchk: error: myisam_sort_buffer_size is too small

You need to increase the buffer_size for a complete repair:

myisamchk -r -v -f --sort_buffer_size=2G table_mytable.MYI

Finally, the repair is complete, and you can start the MySQL server again.
The output will be:

- recovering (with sort) MyISAM-table 'table_mytable.MYI'
Data records: 0

- Fixing index 1
  - Searching for keys, allocating buffer for 19968018 keys
Wrong bytesec:   0-  0-  0 at  399360312; Skipped
Wrong bytesec:   0-  0-  0 at  399360332; Skipped
  - Dumping 7295214 keys

- Fixing index 2
  - Searching for keys, allocating buffer for 7295215 keys
  - Dumping 7295214 keys

- Fixing index 3
  - Searching for keys, allocating buffer for 7295215 keys
  - Dumping 7295214 keys

- Fixing index 4
  - Searching for keys, allocating buffer for 7295215 keys
  - Dumping 7295214 keys

- Fixing index 5
  - Searching for keys, allocating buffer for 2746142 keys
  - Last merge and dumping keys
Data records: 7295214

 

 

Share or save this article

slashdot