Description:
In a nutshell, if you create a table with the .MYI and .MYD files in a different directory, using symbolic links - either manually or using CREATE TABLE .. INDEX DIRECTORY="" DATA DIRECTORY="", and then run myisamchk on the table and specify .MYI, you will corrupt the table.
Using 5.6.14:
mysql> create table t1 (id int) engine=myisam index directory="/home/chris/Desktop/Instances/mysql-5.6.14/data-real/" data directory="/home/chris/Desktop/Instances/mysql-5.6.14/data-real";
In shell, cd to the datadir and run myisamchk:
If you do not specify the .MYI, it runs fine:
chris@chris-linux:~/Desktop/Instances/mysql-5.6.14/data/test$ ../../bin/myisamchk -rqa t1
- check record delete-chain
- recovering (with keycache) MyISAM-table 't1'
Data records: 0
If you do specify the .MYI, it fails *and* corrupts the table:
chris@chris-linux:~/Desktop/Instances/mysql-5.6.14/data/test$ ../../bin/myisamchk -rqa t1.MYI
- check record delete-chain
- recovering (with keycache) MyISAM-table 't1.MYI'
Data records: 0
myisamchk: error: Couldn't fix table with quick recovery: Found wrong number of deleted records
myisamchk: error: Run recovery again without -q
MyISAM-table 't1.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
mysql> select * from t1;
ERROR 144 (HY000): Table './test/t1' is marked as crashed and last (automatic?) repair failed
This works as expected if sumbolic links are not involved:
mysql> create table t2 (id int) engine=myisam;
chris@chris-linux:~/Desktop/Instances/mysql-5.6.14/data/test$ ../../bin/myisamchk -rqa t2
- check record delete-chain
- recovering (with keycache) MyISAM-table 't2'
Data records: 0
chris@chris-linux:~/Desktop/Instances/mysql-5.6.14/data/test$ ../../bin/myisamchk -rqa t2.MYI
- check record delete-chain
- recovering (with keycache) MyISAM-table 't2.MYI'
Data records: 0
For the record, I see the same behavior in 5.1.71.
The manual does not indicate there should be any problem with this (though there does not appear to be 5.5 nor 5.6 versions of this manual page):
http://dev.mysql.com/doc/refman/5.1/en/symbolic-links-to-tables.html
Also, myisamchk --help output shows one should be able to specify .MYI.
How to repeat:
Ensure a valid path exists for the "index directory" and "data directory" for the below command.
create table test.t1 (id int) engine=myisam index directory="/home/chris/Desktop/Instances/mysql-5.6.14/data-real/" data directory="/home/chris/Desktop/Instances/mysql-5.6.14/data-real";
cd $datadir
../../bin/myisamchk -rqa t1
../../bin/myisamchk -rqa t1.MYI
(Last command is the one that fails and causes corruption - even to an empty table.)
