Can't open file: 'mytable.ibd' (errno: 1)

[Andy Fish]

Hi,

I read in the mysql manual that it's possible to copy the 'frm' files from
one mysql server to another, but when I try to do this I get the error
"Can't open file: 'mytable.ibd' (errno: 1)" on the detstination server.

There are no '.ibd' files anywhere on the server I'm copying the data from.
My database is using innodb.

TIA for any ideas

Andy

[Bill Karwin]

Andy Fish wrote:

Hi,

I read in the mysql manual that it's possible to copy the 'frm' files from
one mysql server to another, but when I try to do this I get the error
"Can't open file: 'mytable.ibd' (errno: 1)" on the detstination server.

If you use InnoDB tables, but use the default mechanism of storing
multiple tables in one file, then you might not see any .ibd files.
Tables are instead stored together in InnoDB tablespace files, e.g.
"ibdata1" in the MySQL data directory.

There is an option you can use in the my.cnf to make InnoDB tables store
their data in tablename.ibd, one file per table.

I would guess that when you moved the .FRM file to another server
manually, it couldn't find the table in the InnoDB tablespace, so it
tried to find a file specific to that table: mytable.ibd. Not finding
the table in either storage location, it gave an error related to the
latter location it searched.

I would recommend using this method instead to move table definitions
between servers:

1. mysqldump --opt --no-data databasename tablename > tablename_create.sql
2. Copy tablename_create.sql to the other server
3. mysql newdatabasename < tablename_create.sql

Regards,
Bill K.