I ran into a rather obscure bug the other day, but while uncommon, it can cause damage you would not otherwise expect if you use file-level symbolic links. So this is just a warning about that.
Specifically, 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.
Creating these manually is not so common, but the CREATE TABLE .. INDEX DIRECTORY=”” DATA DIRECTORY=”” is much more common, which creates file-level symbolic links (for the .MYI and .MYD files, respectively) in the datadir and stores the actual file(s) in the location specified. So it leaves you with this setup.
Therefore, if you later run myisamchk on one of these files, do not specify .MYI in the command invocation. If you invoke myisamchk –help, you will see specifying the table name or the table name appended by .MYI are both acceptable.
shell>myisamchk --help ... Usage: myisamchk [OPTIONS] tables[.MYI]
Here is an invocation without specify the .MYI, which runs fine, and displays the expected behavior:
chris@chris-linux:~/$ ../../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:~/$ ../../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
I have filed this bug here:
However, until it is fixed, please be careful if you use file-level symbolic links, and run myisamchk.
Hope this helps.