#1017 - Can't find file: 'table_name.MYI' (errno: 2) - MySQL does not recognize tables of type InnoDB
After copying database files, MySQL does not recognize tables of type InnoDB and gives error "#1017 - Can't find file: 'table_name.MYI' (errno: 2)"
Summary:
In a Linux environment, I made a copy of MySQL database files from system A to system B (mysqld was down). When I open some of the tables in PHPMyAdmin on system B, it returns the following error:
"#1017 - Can't find file: 'table_name.MYI' (errno: 2)"
The problem is that MySQL expects an index file (*.MYI) which do not even exist in system A. System A works well without the index files.
The question is: why dat MySQL expect index files?
Looking back at system A, it appears that the tables that give problems on system B, return the following error on system A:
"#1016 - Can't open file: 'table_name.InnoDB'. (errno: 1)"
The erroneous table are all of type "InnoDB" on system A whereas the other tables are of type MyISAM.
What did I do wrong in the backup process?
---------------------------------------------------------------------------------------
In Detail:
Systems A and B are similar systems. A is the live system and B the development system.
System A:
shell> mysqld --version
mysqld Ver 4.0.24_Debian-10sarge1-log for pc-linux-gnu on i386 (Source distribution)
System B:
shell> mysqld --version
mysqld Ver 4.0.24_Debian-10sarge2-log for pc-linux-gnu on i386 (Source distribution)
On system A, I copied all files (*.frm, *.MYD and *.MYI) from /var/lib/mysql/database_name/ to /var/lib/mysql/database_name_backup/
When I did this, mysqld was not running and no webservers accessing MySQL were running
Later, I copied /var/lib/mysql/database_name_backup/ on system A to /var/lib/mysql/database_name/ on system B
(/var/lib/mysql/database_name_backup/ on system A was not in use during this copy action)
When I opened PHPMyAdmin (version 2.5.6) in system B and clicked on some of the tables, PHPMyAdmin (and MySQL) returned:
"#1017 - Can't find file: 'table_name.MYI' (errno: 2)"
This error is only given for tables that don't have an index on system A (and therefore no *.MYI file)
If I open PHPMyAdmin, the right part of the screen display a list of tables with the following column headers:
Table Action Records Type Size Overhead
Tables that give problems, show "in use" in the (merged) columns "Records Type Size".
All other tables show numbers for the columns "Records" and "Size" and "MyISAM" for the column "Type".
"in use" could indicate to a table that is still being accessed my mysqld, but during the backup mysqld was not running.
Tables that show "in use" on system B, show "InnoDB" for the column "type" on system A. (The columns: records, type and size and not shown merged on system A).
Could it be that system B does not now how to handle tables of type "InnoDB" and shows "in use"?
On system A, when I open the backup database in PHPMyAdmin and click left on one of the tables that give problems on system B,
system A returns:
"#1016 - Can't open file: 'table_name.InnoDB'. (errno: 1)"
What did I do wrong in the backup process?
I tried to find "table_name.InnoDB" (with "locate table_name.InnoDB") I couldn't find it, not even I did "updatedb".
The orinal database (/var/lib/mysql/database_name/) is running without problems.
Why does MySQL expect a *.InnoDB file and why is is not present on system A, were the original database works without problems?
The reason why I a make a backup in this way is that I can still access the backup databases from PHPMyAdmin.
A compressed file or a backup in SQL is not accessiable from PHPMyAdmin.
Can anyone help me or tell me how I could better do a backup procedure.
Thanks in advance,
Dirk Jan
Edited 1 time(s). Last edit at 05/25/2007 03:42AM by Dirk Jan Datema.