Disappearing Database Files 2

[Wullie]

Hi All,

Strange problem we have here and I'm looking for some insight on it.

On one of our MySQL machines, everything was working fine until we rebooted a few days ago. Immediately after the reboot, some database connections were failing with:

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

Now, this only happened on a few accounts, not all of the server. In some databases only 1 table was problematic but the rest were fine, in others every table was affected.

When we looked at the filesystem, the problem tables only exist as FRM files, there are no MYI or MYD files for them.

What makes the problem 100 times worse is that our backups are taken at the filesystem level and going back for the last 2 weeks shows only the FRM files in the backups are well, meaning we cannot restore them from these backups.

The FRM files were the only things in the database directory, however the actual databases were still in use during this time and running flawlessly.

Anyone got any ideas?

Wullie

Fresh Look - Quality Coldfusion/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[KarveR]

Has mysql started up using the right config, or have you linked an external data directory correctly ?


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Wullie]

Thanks for your reply.

Everything is setup correctly, it has been checked/double checked and nothing is out of place.

There is no external data directory setup and only some tables are corrupt in some of the databases, which indicates that it was writing to the correct place.

Wullie

Fresh Look - Quality Coldfusion/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[TonyGroves]

The file mentioned is an InnoDB table file. Such tables would not have MYI or MYD files. It looks like either the table in question was created with "engine=innodb", or InnoDB is your default table type.

If you want to prevent the use of InnoDB tables (now that the InnoDB company has been taken over by Oracle, it might be worth considering!), you need to insert the line "skip-innodb" into the [mysqld] section of your my.cnf.

One more thing; MySQL file backups are not reliable unless the tables were locked and flushed at the start of the backup routine, as MySQL uses memory caching to avoid unnecessary disc accesses. You might consider using something like an SQL "BACKUP TABLES" command to copy the (MyISAM) files.

As for recovering your lost files, well ...

 

[Wullie]

Thanks for your reply.

The strange thing here is that the server is setup to use MyISAM as default and these tables *should* be MyISAM.

The other problem I have is working out exactly where the InnoDB datafile disappeared to because there is no datafile in any of the backups of the mysql directory and I can't find it anywhere on the system. I'm guessing that it was somehow removed though and this is why they won't connect anymore.

I've changed the backup strategy we use to include a dump of the SQL code for each database into it's own file so this problem won't hit us again. With everything setup to use MyISAM it never crossed my mind that this situation could occur, however I've learnt a very expensive lesson.

Thanks everyone.

Wullie

Fresh Look - Quality Coldfusion/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell