CODEWITHSUNDEEP
phpmyadminmysqlmyisam
ido
ido

Reputation: 811

Corrupt MySQL table - "Can't find file" error; Any way to recover missing .myd and .myi files?

I'm a MySQL newbe and I'm trying to fix a corrupt table in one of my databases. It's a simple IPS forum which is currently online. Earlier today I tried to perform a mysqldump and received this error:

mysqldump: Got error: 1017: Can't find file: 'wcsf_profile_portal_views' (errno: 2) when using LOCK TABLES

So I went ahead to see what's wrong. It seems one of the tables is corrupt. I tried using repair, but I'm getting the same error:

mysql> REPAIR TABLE wcsf_profile_portal_views;
+-----------------------------------------------+--------+----------+---------------------------------------------------------+
| Table                                         | Op     | Msg_type | Msg_text                                                |
+-----------------------------------------------+--------+----------+---------------------------------------------------------+
| wecreate_finalforum.wcsf_profile_portal_views | repair | Error    | Can't find file: 'wcsf_profile_portal_views' (errno: 2) |
| wecreate_finalforum.wcsf_profile_portal_views | repair | status   | Operation failed                                        |
+-----------------------------------------------+--------+----------+---------------------------------------------------------+
2 rows in set (0.00 sec)

I looked it up, and tried a few of the suggestions here http://www.databasejournal.com/features/mysql/article.php/10897_3300511_2/Repairing-Database-Corruption-in-MySQL.htm

But so far, no luck with mysqlcheck or myisamchk.

It's important to note I'm using MyISAM. I checked the database folder, and it appears two files are missing: wcsf_profile_portal_views.MYD and wcsf_profile_portal_views.MYI. The file wcsf_profile_portal_views.frm however does exist. Accordingly I tried REPAIR TABLE wcsf_profile_portal_views USE_FRM; but received Can't open table again.

I couldn't find what caused the corruption yet. Any suggestions?

Edit: Unwillingly I tried to restore the table via older backups. No dice - apparently, it's been corrupted for awhile and this database specifically was never even backed up because it kept failing. I never noticed. Fml. That's what you get for using WHM! Lucky for me, this is not an important table. It's a part of Invision Powered boards, I suppose it counts profile views. I made a install fresh locally and copied the table back to the live DB, repaired and it seems to work now - I can mysqldump it successfully and I suppose now backups will work as well.

I wouldn't consider this question answered because I didn't quite repair it (data was lost) and I didn't find what caused the corruption to begin with. Maybe it's an IPB-related specific issue.

Upvotes: 3

Views: 10461

Answers (2)

Ruszaj
Ruszaj

Reputation: 21

Search in the folder "data/databaseName" if the file "tablename.MYI" is there. If not, get the same file in another DB (or old one) and paste it in the folder.

Start MySQL, and repair the table.

That's how I resolved it.

Sorry, my english is so so.

Upvotes: 2

user1639031
user1639031

Reputation: 26

.MYD files contain the underlying data in the table, .MYI files are the indices on the table. If you've lost the data, I don't see how you can possibly recover other than from a backup of some sort.

Upvotes: 1

Related Questions