Can’t open file: ‘wp_comments.MYI’

I’m having this same issue today–this is at the top of my blog:

WordPress database error: [Can’t open file: ‘wp_comments.MYI’ (errno: 145)]
SELECT ID, COUNT( comment_ID ) AS ccount FROM wp_posts LEFT JOIN wp_comments ON ( comment_post_ID = ID AND comment_approved = ‘1’) WHERE post_status = ‘publish’ AND ID IN (1228,1227,1226,1223,1222,1221,1220,1219,1218,1217,1216,1215,1214,1213,1212) GROUP BY ID

What to do?

Okay, here’s what happened. (read the whole thing, there’s a sadder, poorer, and wiser part to it. I’m tryin’ to save you some money here):

ipowerweb support (via chat interface) told me to email support and request a database backup. [note: as it turns out, this was bad information.] I inquired what response time would be and was told 6-8 hours.

When 20 hours went by, I called support. Was told it would cost me $50 to do a database restore. I swore a bit and then went ahead with it. Since I was paying for it, I told THEM to do it. So much for my gaining experience in database skillz.

Restore happened. Database restored to earlier version. I lost most recent posts (tho I had backed up all parts of database but the comments table before doing so, so I had info on hand from which to reconstruct posts)

As I went about restoring posts, the error recurred. More swearing, and then on the fone to tech support. What with wait time and all, there was much haggling about immediately escalating it to Tier 2 (the support people who really know their stuff) rather than his creating a ticket that’d get responded to by eventual email. Person on fone tried to get me through to ’em right away. Put me on hold, came back with, “reload the page… the error messages should be gone now. We flushed the tables.”

And so it was. Though the error message was gone, a few of the most recent posts disappeared. (actually, this may be faulty information. Lots went down, and there was an entire database crash and then restore which worked till last month and then a later restore of all data, so I really don’t remember it all.)

I still ended up having to do database repairs and stuff (of which more below). And I’ve been having an ongoing email discussion with the tech person who did database restore, etc. to help me understand and diagnose whether the $50 restore was really necessary.

Here’s what I asked the Tier 2 tech person:

THE QUESTION: If flushing took care of if the 2nd time, would flushing the database have taken care of it the first time, too? In other words, was it truly necessary to do a backup?

Here’s the response I got back:

If you give me the originall error message you received I can tell you if it was needed or not.

What I told him:

This is the error I get: ” MySQL said:
Documentation #1016 – Can’t open file: ‘wp_comments.MYI’. (errno: 144)

And his response:

A restore wasn’t needed with that error message. Repairing all tables in the database would have fixed that.

I asked (well, besides a bit of mild ranting about better communication among Tier 1 and Tier 2 support):

I’ve since noticed the Tools section of PHPMyAdmin. I could, conceivably try a “flush” myself?

His response:

Yes you could.

The big lesson: Don’t accept the word of the first level of support when they tell you it’s going to cost money. Get to the 2nd level of support and say, “are you sure it’s necessary to do this? Is there some way to fix what’s there and not have to do a restore?”

So… let me describe the part of phpmyadmin [I’m using 2.7.0-pl2] where you can try it yourself.

When you have a specific table selected, there are a number of options that run across the top right-hand side of the browser window:
[Browse | Structure | SQL | Search | Insert | Export | Import | Operations | Empty | Drop ]

Click Operations. At the right, there’s a section called Table Maintenance with some stuff underneath:
Check table
Analalyze table
Repair table
Optimize table
Flush the table (“FLUSH”)

I did try checking table and repairing tables during this operation. But Flush sounds, well, too scary to play with. But with Tier 2 tech support’s advice in hand, I say go for it.

(of course, I can’t remember exactly what I could see when I clicked the wp_comments table link at left. I know there was a big fat a** yellow error message “can’t open wp_comments.MYI” I have no recollection if I could even get to the operations part of interface. So if you can, do it, if not, get your host provider experts to do it.)

I hope this helps the next person who has this problem. Please, for my sake, post a message here saying it worked, or else come by to 2020hindsight.org and post a comment telling me so. Only not today, as I’m still getting things back on track at my site, and I haven’t yet restored the comments.

To confirm the fix for this: I had the same problem ; due to MySQL Crashing (it ran out of diskspace) the wp_comments table got corrupted. WordPress showed the same error messages a mentioned above.

The solution , as suggested , was to use phpMyAdmin to repair the MySql Database table. To do this:
1) Login in to phpMyAdmin (if you don’t know what I’m talking about , talk to your web host)
2) chose the wordpress database (it normally has ‘wordpress in the title’)
3) You should see a list of 10 tables on the right hand side of the page. One of them should have an error message against it
4) Select this table , then choose ‘repair’ from the drop down box at the bottom of the table.

That’s it. If you can restart both the Web server and the MySql database, or ask somebody else to do it for you.

Thanks everyone. I got this error this morning and repaired my table and it works great now. 🙂