.MYI and .MYD files

... missing in action

[grandpa]

I feel like I've lost something.. like a dozen or so tables. A quick look in the Windows directory shows 3 files per table, file.frm, file.MYI and file.MYD. The last two are missing from several tables. From phpMyAdmin I get this error when trying to open the table.

#1016 - Can't open file: 'file.ibd' (errno: 1)

It's pretty clear that the tables are corrupt. Unless you know a way to try to restore the files.

Here's a brief, and unfortunate history. While I was away and traversing the Appalachian's, the MySQL Service on the PC was accidentally damaged/removed. That's the long story short. The day I returned saw a new install of the MySQL Service on the PC, and I thought I had managed to restore all the tables. Now I'm discovering that I did not.

Is there a hope of recovery for these tables? A search of the PC doesn't find any of the missing files.

<OT>
I'm wondering how much longer it will be before we see a MySQL forum...
</OT>

[grandpa]

And just to reply, I found this thread [webmasterworld.com].

Thanks coopster.

[grandpa]

OK, it's time for a question.

When I run CHECK TABLE I receive the error
Table database.table_one: error = Can't open file: 'table_one.ibd' (errno: 1)

The query REPAIR TABLE `table_one` USE_FRM is not repairing the table. So, I'm recreating some of my tables using the original scripts that I have. Now when I take a peek at the Windows directory I can see the new table_one.frm file, but not the .MYI and .MYD files. What has happened to those 2 files? The table and data are OK from phpMyAdmin after I run the re-creation script.

[grandpa]

Is it a good thing if keep answering my own questions?

When I ALTER the table to MyISAM (from INNODB) the files magically re-appear :)

[coopster]

Did you originally create the tables as ISAM or InnoDB?

[jatar_k]

>> the files magically re-appear

but do they work at that point?

[grandpa]

Did you originally create the tables as ISAM or InnoDB?

It looks like the default table type is InnoDB. The affected table are all relatively new, and the scripts I used to make them do not specify a type. I'm going to say they were created InnoDB. In the future my scripts will specify a table type too, probably MyISAM.

but do they work at that point?

Only those that I have re-created and ALTER'ed. phpMyAdmin reports the other tables as being 'In Use'. They cannot be altered or repaired from phpMyAdmin or from a script.

Fortunately, all of the affected tables (21 of them) are not critical to our local operation. The older MyISAM tables seem to have been unaffected, which is a very good thing. Those are my customer, inventory and sales tables.

FWIW, I back up the web site tables on a regular basis, but not the localhost tables. Arrrgh.