10

I have the mysqltable called Sample. I have edited the file /var/lib/mysql/Sample.MYI with some values. Now check TABLE Sample query shows as

Incorrect key file for table 'Sample'; try to repair it

To repair this, i have tried using the following command myisamchk -r Sample.MYI. but the result is "myisamchk:

error: Got error 140 when trying to recreate indexfile MyISAM-table Sample.MYI is not fixed because of errors".

Now how to repair this table ?

Elzo Valugi
  • 25,880
  • 14
  • 91
  • 114
ssbecse
  • 103
  • 1
  • 1
  • 5

5 Answers5

19

Just in case you don't have backup, don't have the original file and cannot redownload it, here are some links that might help you:

http://www.felipecruz.com/repair-mysql-database.php
http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html (see also links at the bottom of this page)
http://forums.mysql.com/read.php?21,362974,362974

The following command will recreate .myi files from scratch:

REPAIR TABLE tablename USE_FRM

enter image description hereBe careful with this though, this page: http://dev.mysql.com/doc/refman/5.5/en/repair-table.html says:

Use the USE_FRM option only if you cannot use regular REPAIR modes! Telling the server to ignore the .MYI file makes important table metadata stored in the .MYI unavailable to the repair process, which can have deleterious consequences:[.....]

And finally how to redo this in different ways:

http://www-users.cs.york.ac.uk/susan/joke/foot.htm

Johan
  • 73,011
  • 23
  • 185
  • 311
  • 1
    @ssbecse, which link did you try and what solution worked for you? – Johan May 30 '11 at 14:30
  • http://www.felipecruz.com/repair-mysql-database.php and http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html.In these have the option called "REPAIR TABLE tablename USE_FRM;" .its working fine – ssbecse May 30 '11 at 14:32
  • @ssbecse, cool MySQL doesn't really repair anything though, it just recreates the .myi files from scratch. Feel free to accept the answer if it solved your problem :-). – Johan May 30 '11 at 14:37
  • @ssbecse, repairing always holds a danger of dataloss, because MySQL has to make a guess what data might have been in the place where there is now corrupted data. If it guesses wrong, it might just put completly wrong data in. E.g. suppose MySQL reports your .myi files is wrong, but that's really not true, and your .myi is fine, but your data file is corrupt. Then MySQL might recreate an .myi file and overwite a good file based on a corrupt data file. No worries, always backup first. Try it and see if it works, if it doesn't restore from backup and try something else. – Johan May 31 '11 at 11:48
  • In my application, periodically data updated in MySQL table.how to take the backup upto tables gets corrupted. – ssbecse May 31 '11 at 11:54
  • @ssbecse, just google for MySQL backup(first link): http://www.google.nl/search?q=mysql%20backup&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:nl:official&client=firefox-a&source=hp&channel=np, there's lots of ways. Make sure you store the backup on a different disk! – Johan May 31 '11 at 12:23
  • This worked for me when I had REPAIR issues that would not go in the normal way – Amanada Smith Jan 24 '13 at 15:04
12

Check the status of the corrupted table

check table tablename;

e.g

mysql> check table realtime_clicks_update;
+--------------------------------+-------+----------+----------+
| Table                          | Op    | Msg_type | Msg_text |
+--------------------------------+-------+----------+----------+
| logdata.realtime_clicks_update | check | status   | OK       |
+--------------------------------+-------+----------+----------+

If status is not OK then repair it using following command

mysql> repair table tablename;
minhas23
  • 8,723
  • 3
  • 56
  • 39
8

Why did you edit the .MYI file? You're not supposed to do that.

Restore from backups then re-apply the changes in the proper fashion.

Lightness Races in Orbit
  • 369,052
  • 73
  • 620
  • 1,021
0

REPAIR TABLE Statement can help you in recovering the table. This statement works for MyISAM, Archive and CSV tables. Command is as follows: REPAIR TABLE tbl_name [, tbl_name]... [QUICK] [EXTENDED] [USE_FRM] Where QUICK: If you use this option then repair table tries to repair only the index file and not the data file. EXTENDED: If you use this option then MySQL creates the index row by row instead of creating one index at a time with sorting. USE_FRM: This option is used if the .MYI index file is missing or if its header is corrupted.

In this case you can try USE_FRM option to repair the table. If the above mentioned method doesn’t work then you can try using this MySQL repair tool from Stellar, but you should first try with the manual method and should use this method only in case if you want a quick solution to the problem. The software claims to repair the InnoDB and MyISAM tables of MySQL database along with safely restoring all inaccessible database objects including primary keys, views, triggers, tables, etc. in the original format.

Shanaya
  • 1
0

put back the original Sample.MYI file :)

Tudor Constantin
  • 25,256
  • 7
  • 48
  • 70
  • 1
    in my application,i have do negative testing.ie,if table gets corrupted, my code should take care to repair that table ... – ssbecse May 30 '11 at 14:15
  • @ssbecse, that's a stupid notion. That's what backups are for. And besides the .myi format is a moving target, MySQL never promised they will not change the layout of that file. – Johan May 30 '11 at 14:26
  • 1
    that will only work when the underlying table has not changed at all, not a very likely scenario in production. – Johan May 30 '11 at 14:43
  • Johan, the idea was to use the backup - which, usually is not the files itself. – Tudor Constantin May 30 '11 at 14:45