So we have a MySQL database that suddenly started reporting "can't
open *.ibd file" errors on InnoDB tables in phpMyAdmin. (MyISAM are
fine). To recover the data is all I want at this point -- so one
suggestion I came across was to try and dump using mysqldump like
this:

mysqldump -u root -p mydatabase > databasedump.sql
Enter password: *******************
mysqldump: Got error: 1016: Can't open file: 'access.ibd' (errno: 1)
when using
LOCK TABLES

Is there a way to run this dump so that it will export the database
tables and ignore the *.ibd error? I had read that removing *.frm
files might help, but our testing suggests that it isn't effective
either.

Any help will result in hero status. :-) Holiday card list and all
those good karma things.

thanks!!

Chuck

Chuck W1CEW <cewyattjr@gmail.com> wrote:

> So we have a MySQL database that suddenly started reporting "can't
> open *.ibd file" errors on InnoDB tables in phpMyAdmin. (MyISAM are
> fine). To recover the data is all I want at this point -- so one
> suggestion I came across was to try and dump using mysqldump like
> this:
>
> mysqldump -u root -p mydatabase > databasedump.sql
> Enter password: *******************
> mysqldump: Got error: 1016: Can't open file: 'access.ibd' (errno: 1)
> when using LOCK TABLES

Sometimes it helps to read and understand error messages:

~ $ perror 1
OS error code 1: Operation not permitted

So somehow the access permissions on the 'access.ibd' file got screwed.

> Is there a way to run this dump so that it will export the database
> tables and ignore the *.ibd error?

No. The .ibd files contain the table data. No file, no data to dump.

> I had read that removing *.frm
> files might help, but our testing suggests that it isn't effective
> either.

OMG. You must never manipulate files in MySQLs data directory unless
you know *exactly* what you're doing.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/